打印本文 打印本文 关闭窗口 关闭窗口
Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题
作者：武汉SEO闵涛  文章来源：敏韬网  点击数3846  更新时间：2009/4/22 22:05:04  文章录入：mintao  责任编辑：mintao
file sequential read 17 file# 316 db file sequential read 17 file# 313 db file sequential read 17 file# SID EVENT P1 P1TEXT ---------- ------------------------------ ---------- ---------------------------------------------------------------- 305 db file sequential read 17 file# 303 db file sequential read 17 file# 301 db file sequential read 17 file# 293 db file sequential read 17 file# 290 db file sequential read 17 file# 288 db file sequential read 17 file# 287 db file sequential read 17 file# 273 db file sequential read 17 file# 271 db file sequential read 17 file# 257 db file sequential read 17 file# 256 db file sequential read 17 file# SID EVENT P1 P1TEXT ---------- ------------------------------ ---------- ---------------------------------------------------------------- 254 db file sequential read 17 file# 252 db file sequential read 17 file# 159 db file sequential read 17 file# 153 db file sequential read 17 file# 146 db file sequential read 17 file# 142 db file sequential read 17 file# 135 db file sequential read 17 file# 133 db file sequential read 17 file# 132 db file sequential read 17 file# 126 db file sequential read 17 file# 79 db file sequential read 17 file# SID EVENT P1 P1TEXT ---------- ------------------------------ ---------- ---------------------------------------------------------------- 77 db file sequential read 17 file# 72 db file sequential read 17 file# 70 db file sequential read 17 file# 69 db file sequential read 17 file# 67 db file sequential read 17 file# 63 db file sequential read 17 file# 55 db file sequential read 17 file# 102 db file sequential read 17 file# 96 db file sequential read 17 file# 95 db file sequential read 17 file# 91 db file sequential read 17 file# SID EVENT P1 P1TEXT ---------- ------------------------------ ---------- ---------------------------------------------------------------- 81 db file sequential read 17 file# 15 db file sequential read 17 file# 19 db file scattered read 17 file# 50 db file scattered read 17 file# 285 db file scattered read 17 file# 279 db file scattered read 17 file# 255 db file scattered read 17 file# 243 db file scattered read 17 file# 196 db file scattered read 17 file# 187 db file scattered read 17 file# 170 db file scattered read 17 file# SID EVENT P1 P1TEXT ---------- ------------------------------ ---------- ---------------------------------------------------------------- 162 db file scattered read 17 file# 138 db file scattered read 17 file# 110 db file scattered read 17 file# 108 db file scattered read 17 file# 92 db file scattered read 17 file# 330 db file scattered read 17 file# 310 db file scattered read 17 file# 302 db file scattered read 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

该表这里有22万记录，全表扫描已经不再适合.

检查该表，存在以下索引:

SQL> select index_name,index_type  from user_indexes where table_name=''''HS_INFO'''';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
HSIDX_INFO1                    FUNCTION-BASED NORMAL
HSIDX_INFO_SEARCHKEY           DOMAIN
PK_HS_INFO                     NORMAL

检查索引键值:

SQL> select index_name,column_name from user_ind_columns  where table_name =''''HS_INFO'''';

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
HSIDX_INFO1                    NUMORDER
HSIDX_INFO1                    SYS_NC00024$
HSIDX_INFO_SEARCHKEY           VC2INDEXWORDS
PK_HS_INFO                     NUMINFOGUID

SQL> desc hs_info
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------------------
 NUMINFOGUID                                                       NOT NULL NUMBER(15)
 NUMCATALOGGUID                                                    NOT NULL NUMBER(15)
 INTTEXTTYPE                                                       NOT NULL NUMBER(38)
 VC2TITLE                                                          NOT NULL VARCHAR2(60)
 VC2AUTHOR                                                                  VARCHAR2(100)
 NUMPREVINFOGUID                                                            NUMBER(15)
 NUMNEXTINFOGUID                                                            NUMBER(15)
 NUMORDER                                                          NOT NULL NUMBER(15)
 DATPUBLISHDATE                                                    NOT NULL DATE
 INTPUBLISHSTATE                                                   NOT NULL NUMBER(38)
 VC2PUBLISHERID                                                             VARCHAR2(30)
 VC2INDEXWORDS                                                              VARCHAR2(200)
 VC2WAPPREVPATH                                                             VARCHAR2(200)
 VC2WEBPREVPATH                                                             VARCHAR2(200)
 VC2WAP2PREVPATH                                                            VARCHAR2(200)
 NUMVISITED                                                        NOT NULL NUMBER(15)
 INTENABLEDFLAG                                                    NOT NULL NUMBER(38)
 DATCREATETIME                                                     NOT NULL DATE
 DATMODIFYTIME                                                     NOT NULL DATE
 VC2NOTES                                                                   VARCHAR2(1000)
 INTINFOTYPE                   
上一页  [1] [2] [3]  下一页
打印本文 打印本文 关闭窗口 关闭窗口