Tuning Diagnostics

Top Timed Events: Is a good place to start when tuning.  It indicates a problem or high events in the database. 

DB Time: Tuning is about to reduce wait time and increase use response time. 

                DB Time = DB Wait Time + DB CPU Time

When time is dominated by waits, application is not scalable.  In this situation, add CPU will not help performance!

v$stastistics_level:  Basic (disable all self-tuning), Typical (Recommended default value), ALL (Additional  statistics for manual diagnosis)

Which parameters are affected by statistics_level?

  select statistics_name, activation_level

  from v$statistics_level

  order by 2

STATISTICS_NAME                               ACTIVAT

--------------------------------------------- -------

Timed OS Statistics                           ALL

Plan Execution Statistics                     ALL

Timed Statistics                              TYPICAL

Segment Level Statistics                      TYPICAL

PGA Advice                                    TYPICAL

Shared Pool Advice                            TYPICAL

Modification Monitoring                       TYPICAL

Longops Statistics                            TYPICAL

Bind Data Capture                             TYPICAL

Ultrafast Latch Statistics                    TYPICAL

Threshold-based Alerts                        TYPICAL

Global Cache Statistics                       TYPICAL

Active Session History                        TYPICAL

Undo Advisor, Alerts and Fast Ramp up         TYPICAL

Streams Pool Advice                           TYPICAL

Time Model Events                             TYPICAL

Plan Execution Sampling                       TYPICAL

Automated Maintenance Tasks                   TYPICAL

SQL Monitoring                                TYPICAL

Adaptive Thresholds Enabled                   TYPICAL

V$IOSTAT_* statistics                         TYPICAL

Buffer Cache Advice                           TYPICAL

MTTR Advice                                   TYPICAL

System Level Statistics

select name, class, value from v$sysstat

Service  Level Statistics

select service_name, stat_name, value from v$service_stats

To see SGA components, the grandual size, and free memory

select * from v$sgainfo;

select * from v$sgastat

Wait Events

Wait Event	Affected Area
Buffer Busy Wait	Buffer Cache, DBWR
Free Buffer Wait	Buffer Cache, I/O, DBWR
Enqueue wait	Locks
Library Cache waits	Mutexes, Latches
Log buffer space	Log buffer I/O
Log file sync
db file sequential read db file scatter read	SQL Tuning, I/O

select event#, name, parameter1, parameter2, parameter3 from v$event_name

select service_name, event, average_wait from v$service_event where time_waited > 10

select sid, seq#, event, wait_time from v$session_wait;

Wait Class

·               Administration

·               Application

·               Concurrency

·               Commit

·               Configuration

·               User I/O

·               Network

·               Iddle

select sid, event from v$session_wait where wait_time = 0

WAIT_TIME = 0   session is currently waiting        > 0     session last wait time

awrrpt.sql, awrrpti.sql, addmrpt.sql, addmrpti.sql,ashrpt.sql, ashrpti.sql, awrsqrpt.sql, awrsqi.sql, awrddrpt.sql, awrddrpi.sql

Database Trace

exec dbms_monitor.database_trace_enable();

exec dbms_monitor.database_trace_disable();

Session Trace

execute dbms_monitor.session_trace_enable(8,12, waits=>TRUE, binds=>TRUE);