Elapsed time represents the time interval between the begin snapid and end snapid.
DB time indicates CPU time in processing + wait time. If the DB time is much greater than elapsed time drill down further in the report to understand the wait events.
1.Load Profile : In the load profile section we can identify TPS(Transaction Per Second)in the database.
In the load profile section, if the value for physical reads is much greater than logical reads, then we need to check weather a proper value is set for sga_target (or) memory_target.
if the hard parses value is muck bigger, increase the value for shared_pool_size parameter.
2.Instance Efficiency Percentage : In this section, we can understand the hit ratio of various sub components. Ideally, oracle recommands to have the hit ratio greater than or equal to 90%.
If ASMM/AMM is not enabled, if the hit ratios are less than 90%, if there is a possibility, increase the values of parameters db_cache_size and shared_pool_size.
3.Top 5 wait events :
3.1. DB file scatter read : This wait event indicates many full table scans.
Create indexes wherever possible.
Cache small historical tables and tune the joins.
3.2. DB file sequential read : This wait event indicates many index scans which is quiet natural.
Wherever possible tune the SQL code(joins).
3.3. Buffer busy : This wait event indicates some blocks are busy(data block,undo block).
Query to identify buffer busy waits :
SQL>select row_wait_object# from v$session where event='buffer busy waits';
Query to identify the object causing buffer busy waits :
SQL>select owner,object_name,subobject_name,object_type from dba_objects where data_object_id=&row_wait_obj;
-where row_wait is from previous query.
If the wait event is because of data block, move the segment to a tablespace where the block size is small.
-Implement ASMM.
Log file sync : This wait event indicates frequent commits(commit frequency is more).
-Commit more records at a time.
-Place redo log file in faster disks.
Free Buffer : This wait event is because of lack of free buffers in database buffer cache.
Increase db_cache_size parameter value.
4.SQL Statistics : In this section we can identify top 10 resource intensive SQL statements.
We can identify top 10 SQL statements consuming significant CPU resource.
Generate the execution plans of top 10 resource intensive SQL statements and tune them either by creating indexes/passing hints/creating materialized views etc..
oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
sys>startup
sys>@$ORACLE_HOME/rdbms/admin/sqltrpt.sql
enter the value for sqlid :
5.Tablespace I/O statisics : This section indicates the tablespaces and their I/O's.
6.Advisory statistics :
6.1.PGA memory advisory : size factor 1 indicates the current value for the parameter pga_aggregate_target. Based on estimated cache_hit% we need to set a proper value for pga_aggregate_target.
6.2. Shared pool advisory : Shared pool size factor 1 indicates the current value for the parameter shared_pool_size. Based on estimated Library Cache(LC) memory object values we can set a proper value for the parameter shared_pool_size.
6.3. SGA target advisory : sga size factor 1 indicates the current value for the parameter sga_target. Based on estimated physical reads value, we can set a proper value for the parameter sga_target.
7.Undo statistics : Based on this section, we can understand, how many times snapshot too old error and how many times we got out of space errors. If the occurances of these values are more, increase undo tablespace size or create undo tablespace with retention guarantee.
8.Segment statistics : If we notice any small historical object and it is most frequently accessed, cache the object.
