| NOT NULL NUMBER(38)
VC2PRIZEFLAG VARCHAR2(1)
VC2DESC VARCHAR2(1000)
6.决定创建新的索引以消除全表扫描
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.
持续观察的CPU使用情况
bash-2.03$ vmstat 3
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id
20 0 0 5421792 1503488 38 434 136 0 0 0 0 0 0 2 0 2931 7795 2622 91 9 0
23 1 0 5416080 1500632 95 734 56 0 0 0 0 0 0 0 0 2949 8057 2598 89 11 0
26 0 0 5412016 1498480 210 1170 21 5 5 0 0 0 2 1 0 3301 9647 3116 90 10 0
25 0 0 5394912 1490160 242 1606 56 0 0 0 0 0 0 1 0 3133 9318 2850 89 11 0
40 0 0 5390200 1488112 162 1393 66 0 0 0 0 0 0 0 0 2848 9080 2502 90 10 0
40 0 0 5377120 1481792 136 1180 120 2 2 0 0 0 1 1 0 2846 9099 2593 92 8 0
36 0 0 5363216 1475168 134 1169 53 0 0 0 0 0 3 2 0 2871 7989 2621 88 12 0
39 0 0 5348936 1469160 157 1448 210 0 0 0 0 0 0 0 0 3660 10062 3480 88 12 0
35 0 0 5344552 1466472 7 15 56 0 0 0 0 0 0 0 0 2885 7663 2635 92 8 0
34 0 0 5343016 1465416 44 386 77 0 0 0 0 0 0 0 0 3197 8486 2902 92 8 0
31 0 0 5331568 1459696 178 1491 122 0 0 0 0 0 0 3 0 3237 9461 3005 89 11 0
31 0 0 5317792 1453008 76 719 80 0 0 0 0 0 0 0 0 3292 8736 3025 93 7 0
31 2 0 5311144 1449552 235 1263 69 2 2 0 0 0 1 0 0 3473 9535 3357 88 12 0
25 0 0 5300240 1443920 108 757 18 2 2 0 0 0 1 1 0 2377 7876 2274 95 5 0
19 0 0 5295904 1441840 50 377 0 0 0 0 0 0 0 1 0 1915 6598 1599 98 1 0
----以上为创建索引之前部分
----以下为创建索引之后部分，CPU使用率恢复正常
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id
40 1 0 5290040 1439208 315 3894 8 2 2 0 0 0 1 6 0 3631 13414 5206 61 9 30
0 1 0 5237192 1414744 731 6749 45 0 0 0 0 0 2 7 0 3264 13558 4941 52 14 34
0 0 0 5163632 1380608 747 6585 10 0 0 0 0 0 0 1 0 2617 12291 3901 46 12 41
1 0 0 5090224 1348152 712 6079 29 0 0 0 0 0 0 6 0 2825 12416 4178 50 12 39
1 0 0 5023672 1317296 714 6183 24 0 0 0 0 0 0 5 0 3166 12424 4745 47 13 40
0 0 0 4955872 1287136 737 6258 16 0 0 0 0 0 0 3 0 2890 11777 4432 44 12 44
1 0 0 4887888 1256464 809 6234 8 2 2 0 0 0 0 2 0 2809 12066 4247 45 12 43
0 0 0 4828912 1228200 312 2364 13 5 5 0 0 0 2 1 0 2410 6816 3492 38 6 57
0 0 0 4856816 1240168 8 138 0 0 0 0 0 0 1 0 0 2314 4026 3232 34 4 62
0 0 0 4874176 1247712 0 86 0 0 0 0 0 0 0 0 0 2298 3930 3324 35 2 63
2 0 0 4926088 1270824 34 560 0 0 0 0 0 0 0 0 0 2192 4694 2612 29 16 55
0 0 0 5427320 1512952 53 694 0 0 0 0 0 0 3 2 0 2443 5085 3340 33 12 55
0 0 0 5509120 1553136 0 37 0 0 0 0 0 0 0 0 0 2309 3908 3321 35 1 64
0 0 0 5562048 1577000 16 234 0 0 0 0 0 0 0 0 0 2507 5187 3433 35 8 57
0 0 0 5665672 1623848 252 1896 8 2 2 0 0 0 1 0 0 2091 6548 2939 34 5 61
0 0 0 5654752 1618208 5 173 16 0 0 0 0 0 0 0 0 2226 4218 3051 35 4 60
0 0 0 5727024 1651120 28 254 0 0 0 0 0 0 0 0 0 2126 4224 2982 38 2 60
0 0 0 5723184 1648880 93 562 8 2 2 0 0 0 1 1 0 2371 5140 3432 38 3 59
0 0 0 5730744 1652512 7 177 26 2 2 0 0 0 1 0 0 2465 4442 3575 36 3 61
至此，此问题得以解决.
本文作者:
eygle,Oracle技术关注者,来自中国最大的Oracle技术论坛itpub.
www.eygle.com是作者的个人站点.你可通过Guoqiang.Gai@gmail.com来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/case/How.To.Capture.Problem.SQL.htm
上一页 [1] [2] [3] | 
