PERFORMANCE TUNING


Foreword
 
You will find:
- Short explanations about system views and files to examine for
performance tuning.
- Lists of views and parameters.
- Lots of statements for obtaining system information and
statistics.
You will not find:
- Performance tuning road maps. (I assume you have informed)
- Subject explanations. (There are lots of whitepapers in google)
- Detailed information about views, parameters. (You can be informed
any of the views by selecting any of it)
Which files should be examined
 
- Alert log file
Server log file for instance level alerts.
--
-- finding the location of the alert_<SID>.log file
--
select * from v$parameter where name = 'background_dump_dest';
-- sample output: /data1/oracle1/orahome1/admin/oradb1/bdump
 
Search for ORA 600 internal errors
ORA 1578 block corruption errors
Alert log file contains;
Checkpoint start & end times
Incomplete checkpoint
Time and performing archiving
Instance recovery and end time

– Trace files

Session log files for session(user) level alerts.
Server tracing can be enabled or disabled at the session or instance level.
Contains statistics for traced SQL statements.
Instance level tracing
--
-- how to examine and change the sql_trace parameter
--
select value from v$parameter where name='sql_trace';
-- sample output: FALSE
alter system set sql_trace=TRUE scope=spfile;
-- instance needed to be restarted for changes
select value from v$parameter where name='sql_trace';
-- sample output: TRUE
Session level tracing
--
-- enable sql tracing for a session
--
-- dbms_system.set_sql_trace_in_session(<SID>,<SERIAL#>,[TRUE|FALSE])
EXEC dbms_system.set_sql_trace_in_session(10, 1008, TRUE);
EXEC dbms_system.set_ev(10, 1008, 10046, 12, NULL);
/*** meaning of levels of a trace
LEVEL Waits Binds
1 FALSE FALSE
4 FALSE TRUE
8 TRUE FALSE
12 TRUE TRUE
*/
--
-- enable sql tracing for your own session
--
alter session set sql_trace=TRUE;
--
-- finding a sessions tracefile name
--
-- first you need to have necessary privileges on DBMS_SUPPORT system
package
grant execute on DBMS_SUPPORT to public;
create public synonym DBMS_SUPPORT for SYS.DBMS_SUPPORT;
-- now you can the following statement
select
             db_name.value ||
             '_ora_' ||
             v$process.spid ||
             nvl2(v$process.traceid, '_' || v$process.traceid, NULL ) ||
            '.trc'
from
        v$parameter u_dump
        cross join v$parameter db_name
        cross join v$process
        join v$session
        on v$process.addr = v$session.paddr
where
       u_dump.name = 'user_dump_dest' and
       db_name.name = 'db_name' and
       v$session.sid=dbms_support.MYSID;
--
-- another way of finding sid of my own session
--
select distinct(SID) from v$mystat;
--
-- identifying tracefile identifier (name) for your session
--
alter session set tracefile_identifier='my_trace_file';
alter session set sql_trace=TRUE;
-- finding the path and the name of a sessions trace file
--
select c.value || '/ora_' || b.spid || '.trc'
from v$session a, v$process b, v$parameter c
where name='user_dump_dest'
and a.paddr=b.addr
-- and a.sid=<SID>;
--
-- obtain SID and SERIAL# for a specific session
--
select SID, SERIAL#, USERNAME, MACHINE
from v$session
where username='D_ANAR';
NOTES:
1- Run oraclehome/rdbms/admin/catproc.sql script for dbms_system packages
2- If you want to obtain wait times in session trace file you should enable
timed_statistics
--
-- enabling timed_statistics
--
select value from v$parameter where name='timed_statistics';
-- sample output: FALSE
alter system set timed_statistics=TRUE;
select value from v$parameter where name='timed_statistics';

— sample output: TRUE

Which views should be interested
 
General system views and tools can be summerized as follows
V$xxx views
Dba_xxx views
Statspack
Utlbstat.sql
Utlestat.sql
Oracle Wait Interface(OWI)
Oracle diagnostics tuning packs
System related views
V$system_event
Cumulative information of waits and wait events. Ever instance
restart resets the information.
V$sysstat
V$event_name
V$fixed_table
Instance/database
V$database         //database info
V$instance        //instance info
V$option
V$parameter          //system init parameters
V$px_process_sysstat //parallel query system statistics
V$process            //information about currently active processes
V$waitstat           //contention statistics
V$system_event       //total waits for particular events
Disk
V$datafile         //datafile detailed info
V$filestat          //data file read/write statistics
V$dbfile          //datafile short list
V$tempfile         //temporary file detailed info
V$tempstat      //read/write for datafiles in temporary tablespace
Memory
V$buffer_pool_statistics     //buffer pool allocation on the instance
                              oracle_home/rdms/admin/catperf.sql
V$db_object_cache            //database object cached in library cache
V$librarycache              //library cache performance and activity
                                   statistics
V$rowcache                  //data dictionary hits and misses activity
V$sysstat                   //basic instance statistics
V$sga                      //sga brief info
V$sgastat                 //statistics about sga usage
V$pgastat                 //statistcis about pga usage
V$sql
V$sqltext
V$sqlarea                 //sql commands currently exists in sqlarea
V$sqlstat                 //statistics of sql statements
V$open_cursor
V$librarycache
v$shared_pool_advice
v$db_cache_advice
v$pga_target_advice
 
Contention
V$lock
V$rollname
V$rollstat            //statistics for all online rollback segments
V$waitstat           //block contention statistics
                      The timed_statistics init.ora parameter
                      should be set to true
V$latch                //statistics for each type of latch
V$enqueue
User/Session
V$lock               //locks currently held by the server and
                     outstanding requests for a lock or latch
V$open_cursor         //cursors curently opened by each session
V$process
V$sort_usage             //size of temporary segments and sessions
                              creating them, identification of disk sorts
V$session
V$session_longops
V$sesstat                      //user session statistics
V$statname
V$transaction
V$session_event                  //information on waits for which active
sessions are waiting
V$event_name
V$session_wait                //resources or event for which active sessions
                                   are waiting
V$px_sesstat        //information about the sessions   running in parallel
V$px_session
V$session_object_cache
NOTES:
Examples, usages and detailed explainations for these views will ve covered
lately in this document
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: