【效能調整】等待事件(七) io相關等待查詢指令碼
與io相關的等待諸如：
db file sequential read, db filescattered read, direct path read,
direct path write, log file parallel write, db file parallel write,controlfile parallel write
可以使用指令碼查詢某個時間的瞬時值：
export filenames=$ORACLE_HOME/monitor/iostats$ORACLE_SID$(date +%y_%m_%d_%H_%M_%S).txt
sqlplus / as sysdba
spool $filenames
set head on
set pages 1000
set lines 1000
set termout on
set trimout on
set trimspool on
col sid on format 9999
col WAIT_CLASS on format a20
col total_waits on format 9999999999
col time_waited on format 9999999999
col AVERAGE_WAIT on format 9999999.99
col all_cnt on format 999999
col active_cnt on format 999999
select a.sid,
a.WAIT_CLASS,
sum(a.TOTAL_WAITS) total_waits,
sum(a.TIME_WAITED) time_waited,
sum(a.AVERAGE_WAIT) AVERAGE_WAIT
from v$session_event a
group by a.sid, a.WAIT_CLASS;
select count(*) all_cnt,
sum(case
when a.STATUS = 'ACTIVE' then
1
else
0
end) active_cnt
from v$session a;
select a.event,
a.total_waits,
a.time_waited,
a.time_waited / a.total_waits average_wait,
(sysdate - b.startup_time) days_old
from v$system_event a, v$instance b
order by a.time_waited desc;
select a.sid,
a.event,
a.time_waited,
trunc(a.time_waited / c.sum_time_waited * 100,3) pct_wait_time,
round((sysdate - b.logon_time) * 24) hours_connected
from v$session_event a,
v$session b,
(select sid, sum(time_waited) sum_time_waited
from v$session_event
where event not in
('Null event', 'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal', 'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer', 'Wait for credit - free buffer',
'PX Deq Credit: free buffer', 'parallel query dequeue wait',
'PX Deque wait', 'Parallel Query Idle Wait - Slaves',
'PX Idle Wait', 'slave wait', 'dispatcher timer',
'virtual circuit status', 'pipe get', 'rdbms ipc message',
'rdbms ipc reply', 'pmon timer', 'smon timer',
'PL/SQL lock timer', 'SQL*Net message from client',
'WMON goes to sleep') having sum(time_waited) > 0
group by sid) c
where a.sid = b.sid
and a.sid = c.sid
and a.time_waited > 0
and a.event in ('db file sequential read','db file scattered read','direct path read','direct path write',
'log file parallel write','db file parallel write')
order by hours_connected desc, a.SID,pct_wait_time,a.EVENT;
select b.sid,
nvl(substr(a.object_name, 1, 30),
'P1=' || b.p1 || ' P2=' || b.p2 || ' P3=' || b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
union
select b.sid,
nvl(substr(a.object_name, 1, 30),
'P1=' || b.p1 || ' P2=' || b.p2 || ' P3=' || b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.data_object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event in ('db file sequential read','db file scattered read','direct path read','direct path write',
'log file parallel write','db file parallel write')
order by 1;
select a.file#,
b.file_name,
a.singleblkrds,
a.singleblkrdtim,
a.singleblkrdtim / a.singleblkrds average_wait
from v$filestat a, dba_data_files b
where a.file# = b.file_id
and a.singleblkrds > 0
order by average_wait;
select hash_value,
child_number,
lpad(' ', 2 * depth) || operation || ' ' || options ||
decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) kbytes
from v$sql_plan
where hash_value in
(select a.sql_hash_value
from v$session a, v$session_wait b
where a.sid = b.sid
and b.event = 'db file scattered read')
order by hash_value, child_number, id;
select a.name,
b.sid,
b.value,
round((sysdate - c.logon_time) * 24) hours_connected
from v$statname a, v$sesstat b, v$session c
where b.sid = c.sid
and a.statistic# = b.statistic#
and b.value > 0
and a.name = 'physical reads direct'
order by b.value;
select a.event,
a.sid,
c.sql_hash_value hash_value,
decode(d.ktssosegt,
1,
'SORT',
2,
'HASH',
3,
'DATA',
4,
'INDEX',
5,
'LOB_DATA',
6,
'LOB_INDEX',
null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.sql_hash_value hash_value,
decode(e.ktssosegt,
1,
'SORT',
2,
'HASH',
3,
'DATA',
4,
'INDEX',
5,
'LOB_DATA',
6,
'LOB_INDEX',
null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a,
dba_temp_files b,
v$parameter c,
v$session d,
x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1, 2;
select *
from (select a.*, row_number() over(order by a.TOTAL_EXECUTIONS desc) rn
from (select a.ADDRESS,
a.HASH_VALUE,
a.SQL_ID,
a.OPERATION_TYPE,
sum(a.LAST_MEMORY_USED),
sum(a.TOTAL_EXECUTIONS) TOTAL_EXECUTIONS,
sum(a.ACTIVE_TIME) ACTIVE_TIME
from V$SQL_WORKAREA a
group by a.ADDRESS, a.HASH_VALUE, a.SQL_ID, a.OPERATION_TYPE) a)
where rn <= 30;
select a.SQL_HASH_VALUE,
a.SQL_ID,
a.SID,
a.OPERATION_TYPE,
a.ACTIVE_TIME,
a.WORK_AREA_SIZE
from V$SQL_WORKAREA_ACTIVE a;
select event, time_waited, average_wait
from v$system_event
where event in ('log file parallel write', 'log file sync');
select sid, value
from v$sesstat a
where statistic# =
(select statistic# from v$statname where name = 'user commits')
and a.VALUE >0
order by value;
select b.name, a.value, round(sysdate - c.startup_time) days_old
from v$sysstat a, v$statname b, v$instance c
where a.statistic# = b.statistic#
and b.name in ('redo wastage', 'redo size');
select /*+ ordered */
a.sid,
decode(a.type,
'BACKGROUND',
'BACKGROUND-' || substr(a.program, instr(a.program, '(', 1, 1)),
'FOREGROUND') type,
b.time_waited,
round(b.time_waited / b.total_waits, 4) average_wait,
round((sysdate - a.logon_time) * 24) hours_connected
from v$session_event b, v$session a
where a.sid = b.sid
and b.event = 'control file parallel write'
order by type, time_waited;
select thread#,
to_char(first_time, 'DD-MM-YYYY') creation_date,
to_char(first_time, 'HH24:MI') time,
sequence#,
first_change# lowest_SCN_in_log,
next_change# highest_SCN_in_log,
recid controlfile_record_id,
stamp controlfile_record_stamp
from v$log_history a where a.FIRST_TIME >= sysdate - 14
order by first_time;
spool off;
exit;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-676190/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【效能調整】等待事件(五)log相關等待事件
- 【效能調整】等待事件(一)事件
- 【效能調整】等待事件(二)事件
- 【效能調整】等待事件(三) 常見等待事件（一）事件
- 【效能調整】等待事件(四) 常見等待事件（二）事件
- 【效能調整】等待事件(九) latch原理事件
- gc 等相關等待事件描述GC事件
- 【效能調整】等待事件read by other session事件Session
- 【效能調整】等待事件 enq: SQ - contention事件ENQ
- 【效能調整】等待事件(十) 10g中的latch等待事件
- mysql鎖等待查詢分析MySql
- oracle之 調整 I/O 相關的等待Oracle
- 【效能調整】等待事件(八) library cache locks and pins事件
- 【效能調整】等待事件(六) direct path read&write事件
- 查詢等待事件及處理指令碼事件指令碼
- 與IO相關的等待事件troubleshooting-系列9事件
- 與IO相關的等待事件troubleshooting-系列8事件
- 與IO相關的等待事件troubleshooting-系列7事件
- 與IO相關的等待事件troubleshooting-系列6事件
- 與IO相關的等待事件troubleshooting-系列5事件
- 與IO相關的等待事件troubleshooting-系列4事件
- 與IO相關的等待事件troubleshooting-系列3事件
- 與IO相關的等待事件troubleshooting-系列2事件
- 與IO相關的等待事件troubleshooting-系列1事件
- 等待事件指令碼事件指令碼
- latch 相關等待事件事件
- 等待事件相關(zt)事件
- 【等待事件之二】log 相關的等待事件
- Oracle效能異常查詢及調整指令碼-不斷更新(old versionl)Oracle指令碼
- log file switch相關等待事件事件
- Oracle 效能相關常用指令碼(SQL)Oracle指令碼SQL
- zt_eygle大師_如何與io相關的wait event等待事件AI事件
- Oracle效能 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- buffer cache與相關的latch等待事件事件
- Oracle效能問題 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- oracle記憶體調整相關Oracle記憶體
- oracle筆記整理11——效能調優之oracle等待事件與表關聯Oracle筆記事件
- 【指令碼】快速排除空閒等待事件得到敏感的非空閒等待事件指令碼事件