Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题[4]|中文方案文档站(提供各类方案下载,WORD文档下载,范文,案例等)

17 file# 299 db file scattered read 17 file# 89 db file scattered read 17 file# 5 smon timer 300 sleep time sid event p1 p1text ---------- ------------------------------ ---------- ---------------------------------------------------------------- 20 sql*net message to client 1952673792 driver id 103 sql*net message to client 1650815232 driver id .... 148 sql*net more data from client 1952673792 driver id 291 sql*net more data from client 1952673792 driver id 244 rows selected.

发现存在大量db file scattered read及db file sequential read等待.

5.捕获相关sql

这里用到了我的以下脚本getsqlbysid.sql:

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
/

 
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

对几个全表扫描进程跟踪以后，得到以上sql语句.
以上语句如果良好编码应该使用绑定变量.但是现在这个不是我们关心的.

使用该应用用户连接，检查其执行计划:

sql> set autotrace trace explain
sql> select i.vc2title,i.numinfoguid  
  2  from  hs_info i where i.intenabledflag = 1  
  3  and i.intpublishstate = 1  and i.datpublishdate <=sysdate  
  4  and i.numcatalogguid = 3475 
  5  order by i.datpublishdate desc, i.numorder desc  ;

execution plan
----------------------------------------------------------
   0      select statement optimizer=choose (cost=228 card=1 bytes=106)
   1    0   sort (order by) (cost=228 card=1 bytes=106)
   2    1     table access (full) of 'hs_info' (cost=218 card=1 bytes=106)

sql> select count(*) from hs_info;

  count(*)
----------
    227404

Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题[4]

[入库：2005年8月18日] [更新：2007年3月24日]