ITPub博客

首页 > Linux操作系统 > Linux操作系统 > owI 事件的收集

owI 事件的收集

原创 Linux操作系统 作者:lfree 时间:2006-03-14 00:00:00 0 删除 编辑

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;
/


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-82823/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6294715