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

核心提示： 6.决定创建新的索引以消除全表扫描检查发现在numcatalogguid字段上并没有索引,该字段具有很好的区分度，考虑在该字段创建索引以消除全表扫描，Oracle中捕获问题SQL解决CPU过渡消耗(6)，SQL> create index hs_info_NUMCATALOGGUID

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

检查发现在numcatalogguid字段上并没有索引,该字段具有很好的区分度，考虑在该字段创建索引以消除全表扫描。SQL> create index hs_info_NUMCATALOGGUID on hs_info(NUMCATALOGGUID);
Index created.
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=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)

7.观察系统状况

原大量等待消失SQL> select sid,event,p1,p1text from v$session_wait where event not like ’SQL%’;
SID EVENT P1 P1TEXT
---------- ------------------------------ ----------
1 pmon timer 300 duration
2 rdbms ipc message 300 timeout
3 rdbms ipc message 300 timeout
6 rdbms ipc message 180000 timeout
59 rdbms ipc message 6000 timeout
118 rdbms ipc message 6000 timeout
275 rdbms ipc message 30000 timeout
147 rdbms ipc message 6000 timeout
62 rdbms ipc message 6000 timeout
11 rdbms ipc message 30000 timeout
4 rdbms ipc message 300 timeout
SID EVENT P1 P1TEXT
---------- ------------------------------ ----------
305 db file sequential read 17 file#
356 db file sequential read 17 file#
19 db file scattered read 17 file#
5 smon timer 300 sleep time
15 rows selected.