Difference between revisions of "Tuning"

Revision as of 15:52, 7 January 2014

Wait-based tuning query

set pages 999
set lines 190
column c1 heading 'Event|Name'             format a40
column c2 heading 'Total|Waits'            format 999,999,999,999
column c3 heading 'Seconds|Waiting'        format 999,999,999,999
column c4 heading 'Total|Timeouts'         format 999,999,999,999
column c5 heading 'Average|Wait|(in secs)' format 99,999.999
ttitle 'System-wide Wait Analysis|for current wait events'
select event                         c1
,      total_waits                   c2
,      time_waited / 100             c3
,      total_timeouts                c4
,      average_wait    /100          c5
from   sys.v_$system_event
where  event not in (
    'dispatcher timer'
,   'lock element cleanup'
,   'Null event'
,   'parallel query dequeue wait'
,   'parallel query idle wait - Slaves'
,   'pipe get'
,   'PL/SQL lock timer'
,   'pmon timer'
,   'rdbms ipc message'
,   'slave wait'
,   'smon timer'
,   'SQL*Net break/reset to client'
,   'SQL*Net message from client'
,   'SQL*Net message to client'
,   'SQL*Net more data to client'
,   'virtual circuit status'
,   'WMON goes to sleep'
)
and    event not like 'DFS%'
and    event not like '%done%'
and    event not like '%Idle%'
and    event not like 'KXFX%'
order  by c2 desc
/

Identifying I/O Problems Using V$ Views

The V$IOSTAT_FILE view captures I/O statistics of database files that are or have been accessed.
The SMALL_SYNC_READ_LATENCY column displays the latency for single block synchronous reads (in milliseconds), which translates directly to the amount of time that clients need to wait before moving onto the next operation.
This defines the responsiveness of the storage subsystem based on the current load.
If there is a high latency for critical datafiles, you may want to consider relocating these files to improve their service time.
To calculate latency statistics, timed_statistics must be set to TRUE.

select file_no
,      filetype_id
,      filetype_name
,      small_read_megabytes
,      small_write_megabytes
,      large_read_megabytes
,      large_write_megabytes
,      small_sync_read_latency/1000 small_sync_read_latency_in_secs
from   v$iostat_file
order  by small_sync_read_latency
/

References

• dba-oracle
• Oracle doc