Table of Contents

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
from   v$iostat_file
order  by small_sync_read_latency desc
/

Show sql statements running at specific times (between 2 snapshot id's)

List is taken from ASH/AWR tables 

col ssnap new_value ssnap_var
col esnap new_value esnap_var
set lines 100 pages 999

select snap_id,
  to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from
   dba_hist_snapshot
order by 1
/

col SAMPLE_TIME for a18
col SQL_EXEC_START for a20
col SESSION_ID for 9999
col SQL_OPNAME for a15
col PROGRAM for a10
col SESSION_SERIAL# for 99999
col USERNAME for a20
set long 999999999
set lines 500
set pages 50000

select '&starting_snap_id' ssnap, '&ending_snap_id' esnap  from dual;

DEFINE LOGFILE=Sessions_dmls_detail_&&ssnap_var._to_&&esnap_var..log

spool &&LOGFILE



select a.SAMPLE_TIME,a.SESSION_ID,a.SESSION_SERIAL#,c.USERNAME ,a.SQL_OPNAME, a.SQL_EXEC_START, a.program, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID and a.user_id=c.user_id and a.snap_id between &&ssnap_var and &&esnap_var and b.command_type in (2,6,7) and lower(sql_text) like 'insert into query_result%'
order by a.SQL_EXEC_START asc;
spool off;
exit
/

References