owI 事件的收集

1。首先建立表

create table wait1 as
SELECT a.SID, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2,
a.p2raw, a.p3text, a.p3, a.p3raw, a.wait_time, a.seconds_in_wait,
a.state, b.serial#, b.username, b.osuser, b.paddr, b.logon_time,
b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#,
b.row_wait_file#, b.row_wait_block#, b.row_wait_row#
FROM v$session_wait a, v$session b
WHERE a.SID = b.SID
AND b.username IS NOT NULL
AND b.TYPE 'BACKGROUND'
AND a.event IN
('db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits'
);

2。建立这个脚本，适当修改收集间隔以及次数，这种缺点就是可能会遗漏，不过大部分应该的问题应该收集到，可能对性能有影响，特别是已经很慢的时候。

begin
dbms_lock.sleep(1);
for i in 1 .. 30000
loop
INSERT INTO wait1
SELECT a.SID, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2, a.p2raw, a.p3text, a.p3,
a.p3raw, a.wait_time, a.seconds_in_wait, a.state, b.serial#, b.username, b.osuser, b.paddr,
b.logon_time, b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#, b.row_wait_file#,
b.row_wait_block#, b.row_wait_row#
FROM v$session_wait a, v$session b
WHERE a.SID = b.SID
AND b.username IS NOT NULL
AND b.TYPE 'BACKGROUND'
AND a.event
IN
('db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits'
);
commit ;
dbms_lock.sleep(3);
end loop;
end;
/