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

文章作者 100test 发表时间 2007:03:14 14:01:02
来源 100Test.Com百考试题网

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 ---------------------------------------------------------------- 0select 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 ---------------------------------------------------------------- 0select 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 ---------------------------------------------------------------- 0select 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可能就是问题产生的根源。以上语句如果良好编码应该使用绑定变量.但是现在这个不是我们关心的。使用该应用用户连接，检查以上SQL的执行计划:

SQL> set autotrace trace explain SQL> 0select 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> 0select count(*) from hs_info. COUNT(*) ---------- 227404

以上查询使用了全表扫描,该表这里有22万记录，全表扫描已经不再适合。

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

SQL> 0select 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> 0select index_name,column_name 2 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 NOT NULL NUMBER(38) VC2PRIZEFLAG VARCHAR2(1) VC2DESC VARCHAR2(1000)

6.决定创建新的索引以消除全表扫描

检查发现在numcatalogguid字段上并没有索引,该字段具有很好的区分度，考虑在该字段创建索引以消除全表扫描。

SQL> create index hs_info_NUMCATALOGGUID on hs_info(NUMCATALOGGUID). Index created. SQL> set autotrace trace explain SQL> 0select 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=12 Card=1 Bytes=106) 1 0 SORT (ORDER BY) (Cost=12 Card=1 Bytes=106) 2 1 TABLE ACCESS (BY INDEX ROWID) OF ’HS_INFO’ (Cost=2 Card=1 Bytes=106) 3 2 INDEX (RANGE SCAN) OF ’HS_INFO_NUMCATALOGGUID’ (NON-UNIQUE) (Cost=1 Card=1)