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

发现存在大量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]  下一页

[Access]sql随机抽取记录  [Access]ASP&SQL让select查询结果随机排序的实现方法
[系统软件]WinXP中CPU占用率100%原因及解决方法  [系统软件]为什么iexplore.exe在打开网页时CPU使用会100%？
[系统软件]SQL语句性能优化--LECCO SQL Expert  [C语言系列]SQL Server到DB2连接服务器的实现
[C语言系列]SQL Server到SYBASE连接服务器的实现  [C语言系列]SQL Server到SQLBASE连接服务器的实现
[C语言系列]SQL Server连接VFP数据库的实现  [C语言系列]ASP+SQL Server之图象数据处理



 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18