Oracle中捕获问题SQL解决CPU过渡消耗(1)(3)
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’ new 5: where b.sid
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 |
对几个进程进行跟踪,分别得到以上SQL语句，这些SQL可能就是问题产生的根源。以上语句如果良好编码应该使用绑定变量.但是现在这个不是我们关心的。使用该应用用户连接，检查以上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 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) |
- 上一篇：浅议Oracle 数据库的集中复制方法(1)
- 下一篇：简介Oracle的数据字典
精彩图集
精彩文章