Oracle中捕获问题SQL解决CPU过渡消耗

核心提示： 发现存在大量db file scattered read及db file sequential read等待.显然全表扫描等操作成为系统最严重的性能影响因素.5.捕获相关SQL这里用到了我的以下脚本getsqlbysid:SELECT sql_textFROM v$sqltext aWHE

发现存在大量db file scattered read及db file sequential read等待.显然全表扫描等操作成为系统最严重的性能影响因素.

5.捕获相关SQL

这里用到了我的以下脚本getsqlbysid:SELECT sql_text
FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value
FROM v$session b
WHERE b.SID = ’&sid’)
ORDER BY piece ASC
/

该脚本根据用户sid,结合v$session和v$sqltext视图,获得用户sql语句的完整文本。用该脚本,通过从v$session_wait中获得的等待全表或索引扫描的进程SID，捕获问题sql:SQL> @getsql
Enter value for sid: 18
old 5: where b.sid=’&sid’
new 5: where b.sid=’18’
SQL_TEXT
----------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenab
ledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=
sysdate and i.numcatalogguid = 2047 order by i.datpublishdate d
esc, i.numorder desc
SQL> /
Enter value for sid: 54
old 5: where b.sid=’&sid’
new 5: where b.sid=’54’
SQL_TEXT
----------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenab
ledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=
sysdate and i.numcatalogguid = 33 order by i.datpublishdate des
c, i.numorder desc
SQL> /
Enter value for sid: 49
old 5: where b.sid=’&sid’
new 5: where b.sid=’49’
SQL_TEXT
----------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenab
ledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=
sysdate and i.numcatalogguid = 26 order by i.datpublishdate des
c, i.numorder desc