今天上午核心数据库由于出现大量的latch free,致使cpu资源耗尽，以下是从开始出现，到分析，诊断，解决的全过程,与网友们分享：
数据库环境 oracle 9i(9.2.0.7) AIX 4.3 P690 RAC双节点环境 1.今天上午接到一线工作人员的报告，说某节点1 CPU资源耗尽，我立刻登陆数据库，topas查看oracle进程，7个进程均占cpu资源的12%，一下就占满了cpu（94%），该节点总共8颗cpu，每个进程吃掉一个cpu，基本被吃光了! 2.马上登陆pl/sql developer客户端，进数据库查看等待事件，发现大量的latch free select * from v$session_waitwhere event not like 'rdbms%'
and event not like 'SQL%'
and event not like 'sbt%'
and event not like 'gcs%'
--------------------
1 248 55935 latch free address 5.04403166340935E17 07000001E1556EA8 number 98 0000000000000062 tries 1 0000000000000001 21 9 WAITED KNOWN TIME
2 254 543 latch free address 5.04403166341859E17 07000001E1638688 number 98 0000000000000062 tries 0 00 -1 3 WAITED KNOWN TIME
3 321 935 latch free address 5.04403166342406E17 07000001E16BDEE8 number 98 0000000000000062 tries 0 00 -1 34 WAITED KNOWN TIME
4 334 7751 latch free address 5.04403166342417E17 07000001E16C0AC8 number 98 0000000000000062 tries 0 00 -1 22 WAITED KNOWN TIME
5 489 669 latch free address 5.04403166341889E17 07000001E163FA68 number 98 0000000000000062 tries 0 00 -1 21 WAITED KNOWN TIME
6 481 76 latch free address 5.04403166342461E17 07000001E16CB4A8 number 98 0000000000000062 tries 0 00 -1 22 WAITED KNOWN TIME
7 349 51869 latch free address 5.04403166341914E17 07000001E1645D68 number 98 0000000000000062 tries 0 00 -1 32 WAITED KNOWN TIME
8 1 52438 pmon timer duration 300 000000000000012C 0 00 0 00 0 178611 WAITING
9 2 966 async disk IO count 4294967295 00000000FFFFFFFF intr 0 00 timeout 1 0000000000000001 -1 8880489 WAITED KNOWN TIME
10 4 39550 ges remote message waittime 32 0000000000000020 loop 0 00 p3 0 00 0 34 WAITING
11 494 49452 db file sequential read file# 45 000000000000002D block# 231671 00000000000388F7 blocks 1 0000000000000001 0 0 WAITING
12 12 61671 smon timer sleep time 300 000000000000012C failed 0 00 0 00 0 34 WAITING
13 56 17 jobq slave wait 0 00 0 00 0 00 0 48 WAITING
14 539 17 jobq slave wait 0 00 0 00 0 00 0 47 WAITING
15 83 17 jobq slave wait 0 00 0 00 0 00 0 48 WAITING
16 195 17 jobq slave wait 0 00 0 00 0 00 0 48 WAITING
17 339 17 jobq slave wait 0 00 0 00 0 00 0 47 WAITING
18 518 17 jobq slave wait 0 00 0 00 0 00 0 48 WAITING
19 420 17 jobq slave wait 0 00 0 00 0 00 0 47 WAITING P2参数出现98，即latch#=98,查看视图v$latchname,得的latch#=98的latch free是cache buffers chains. 3.查看cache buffers chains在数据库的历史记录信息： select name,gets,misses,sleeps from v$latch where sleeps >0 order by sleeps desc
-- cache buffers chains 529660712003 4731961237 3042193
library cache 6517924381 8585598 1966082
shared pool 3671538283 6262176 142184
row cache objects 2728209461 19635994 2305
ges resource hash list 724815784 65501 1957
gcs resource hash 9100645224 5831446 1125
KCL gc element parent latch 9598906456 10306439 815
session idle bit 9230969749 815150 580
row cache enqueue latch 2460416736 73907510 549
gcs resource freelist 2181116392 25427424 328
KCL freelist parent latch 5016908870 7555649 267
cache buffers lru chain 126481121 69032 217
simulator hash latch 20131778913 118945 188
gcs shadows freelist 491138186 49535442 147
user lock 4583258 1507 142
library cache pin 4822983856 691744 105
redo allocation 1465267892 1990654 97
library cache pin allocation 1716827591 311541 76
enqueues 726367900 964811 66
multiblock read objects 410725278 44411 45
simulator lru latch 12848968 2198 42
SQL memory manager workarea list latch 398649162 3897 40
channel operations parent latch 250981191 442 36
KCL name table parent latch 2053211285 34030 35
enqueue hash chains 1447914158 203504 34
KJC message pool free list 1151109615 665046 31
undo global data 1934288934 9048 31
ges caches resource lists 261586842 181856 16
session allocation 332642452 63035 14
active checkpoint queue latch 5292703 127 11
archive process latch 299751 86 11
transaction branch allocation 122479584 27318 10
process allocation 90309202 3803 8
KJCT flow control latch 1485555469 342318 8
messages 507826955 120082 7
gcs opaque info freelist 244672502 2786369 7
ges resource table freelist 387614251 30364 6
object stats modification 4216596 5 5
ksxp tid allocation 990061514 60788 5
checkpoint queue latch 1147902744 32904 5
ges enqueue table freelist 403265651 10022 5
channel handle pool latch 3563824 213 4
dml lock allocation 112059853 4924 4
mostly latch-free SCN 43668342 89501 2
transaction allocation 8081524425 1719 2
redo writing 141747183 2737 2
ges process parent latch 1491569856 1519 1
child cursor hash table 780137747 1175557 1
ges group parent 303281101 40469 1
sleeps 的值>300万，说明数据库累积的值数量巨大. 4.查出具体的子latch是那些,是那些个进程在频繁访问这些latch下的block header select addr,gets,misses,sleeps from v$latch_children where sleeps >0 order by sleeps desc
--
1 07000001E3A885D0 882861288 5663433 1947538
2 07000001E1542748 1542295088 263938560 294810
3 07000001E1542868 2929212401 42713547 178695
4 07000001E1542AA8 2645932367 30868916 142023
5 07000001E1542988 2749745107 31581564 128964
6 07000001E1542BC8 2507123204 28239444 128535
7 07000001E1542CE8 2264000688 23205500 109037
8 070000000006F1A8 1846979753 5478058 76692
9 070000000006F2A0 1819971830 781378 65261
10 07000001E167CF68 1099603777 5798794 28361
11 07000001E3A888B8 1816636407 1498100 9111 ........ 5.找出热块所在的对象 select obj,object_name,tch,tim
from x$bh a,dba_objects o
where a.hladdr in
(select p1raw from v$session_wait where event ='latch free')
and a.obj=o.data_object_id
order by tch desc; ---
441403 T_WY_REPAIRBILL 2747 1299642334
441403 T_WY_REPAIRBILL 2732 1299642334
441403 T_WY_REPAIRBILL 2624 1299642334
441403 T_WY_REPAIRBILL 2408 1299642302
441403 T_WY_REPAIRBILL 723 1299642334
81079 H2_TRADECONTRACT_T 624 1299642331
441405 T_WY_REPAIRBILLBACKCALL 558 129964231 前4位是比较显著的，其实从后面的分析验证中，得的，就是这张表T_WY_REPAIRBILL是造成该次等待 事件的根源所在。7个并发进程在访问同一个对象上面的块. 6.获取sql的hash_value select /*+ RULE */ a.*,
sysdate get_time,
c.name,
c.addr,
d.hladdr,
d.file#,
d.dbablk,
d.tch
from v$session a,
(select sid, P1RAW, SECONDS_IN_WAIT
from v$session_wait
where event = 'latch free') b,
v$latch_children c,
x$bh d
where a.sid = b.sid
and b.P1RAW = c.addr
and c.name = 'cache buffers chains'
and c.addr = d.hladdr
and d.TCH>10; ---- 07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 50 1813 275
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 36 699188 14
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 43412 19
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 26 911343 12
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 38 14210 13
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 45 97388 22
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 47 124064 29
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 45 138348 606
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 313748 24
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 33 350924 13
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 45 285804 495
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 45 302188 556
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 600468 29
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 682388 11
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 723348 27
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 33 752332 15
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 829844 30
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 9 966592 13
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 919956 23
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 23 993684 30
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 8 321945 11
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 0 2011-3-8 17:48:41 2504 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0 2011-3-9 11:27:40 cache buffers chains 07000001E14E5268 07000001E14E5268 15 85635 99
07000001CE6B2470 248 58201 19574558 07000001CE579568 102 WYCC 0 2147483644 ACTIVE DEDICATED 102 WYCC Administrator w2k3-nkhjpt148 unknown JDBC Thin Client USER 07000001E44DF1C0 750476387 07000001E44DF1C0 750476387 JDBC Thin Client 0 0 54858202 441597 45 0 7. select sql_text from v$sqltext where hash_value='750476387'
order by piece asc; sql文本： SELECT -:"SYS_B_00" As nk_row_num,
nk_q1.*
FROM
( SELECT billnumber,
(SELECT NAME
FROM t_ct_repair_source
WHERE code = reportsource) AS reportsource,
accepttime,
district,
address,
content,
preairtype,
reportcontent,
repairunit,
status,
feedbackcontent,
TO_CHAR (n_signtime, :"SYS_B_01") AS date1,
TO_CHAR (r_signtime, :"SYS_B_02") AS date2,
TO_CHAR (completedate, :"SYS_B_03") AS date3,
TO_CHAR (feedbacktime, :"SYS_B_04") AS date4,
RESOLVER,
backcallcontent,
backresults,
ishang,
audits,
hangcount,
CASE WHEN r_signtime > n_signtime THEN :"SYS_B_05" END AS qsyq,
CASE WHEN feedbacktime > completedate THEN :"SYS_B_06" END AS yq,
CASE WHEN monitstatus IS NOT NULL THEN :"SYS_B_07" END AS db,
dbd AS dbd,
fdb
FROM
(SELECT a.billnumber,
a.reportsource,
a.accepttime,
(SELECT NAME
FROM t_ct_district
WHERE code = a.district) AS district,
(SELECT roadname || address || room
FROM t_wy_house
WHERE houseid = a.houseid) AS address,
(SELECT NAME
FROM t_ct_repair_type
WHERE code = a.reporttype) AS preairtype,
(SELECT NAME
FROM t_ct_trouble_type
WHERE code = a.troubletype) || :"SYS_B_08" ||
(SELECT NAME
FROM t_ct_trouble_phenomen
WHERE code = a.troublephenomenon) AS content,
a.reportcontent,
c.repairunit AS repairunit,
DECODE (a.status, :"SYS_B_09", :"SYS_B_10", :"SYS_B_11", :"SYS_B_12", :"SYS_B_13", :"SYS_B_14", :"SYS_B_15", :"SYS_B_16", :"SYS_B_17", :"SYS_B_18", :"SYS_B_19", :"SYS_B_20", :"SYS_B_21", :"SYS_B_22", :"SYS_B_23", :"SYS_B_24", :"SYS_B_25", :"SYS_B_26", status ) AS status,
d.feedbackcontent,
(SELECT NAME
FROM t_ct_backcall_result
WHERE code = e.backresult) AS backresult,
(c.sendtime +
(SELECT VALUE
FROM t_ct_parameter
WHERE code = :"SYS_B_27") / :"SYS_B_28") AS n_signtime,
f.r_signtime,
a.completedate,
d.backtime AS feedbacktime,
(SELECT personnumber
FROM t_wy_user
WHERE sysuserid = a.resolverid) AS RESOLVER,
e.backcallcontent,
CASE WHEN e.backresult = :"SYS_B_29" THEN :"SYS_B_30" WHEN e.backresult = :"SYS_B_31" AND
backresult2 = :"SYS_B_32" THEN :"SYS_B_33" WHEN e.backresult = :"SYS_B_34" OR
backresult2 = :"SYS_B_35" THEN :"SYS_B_36" ELSE :"SYS_B_37" END AS backresults,
DECODE (h.hangtime, NULL, :"SYS_B_38", :"SYS_B_39") AS ishang,
(SELECT COUNT (*)
FROM t_wy_repairbillhang
WHERE repairbillid = a.repairbillid) AS hangcount,
(SELECT DECODE (auditresult, :"SYS_B_40", :"SYS_B_41", :"SYS_B_42" ) AS auditresult
FROM t_wy_audit
WHERE billnumber = a.billnumber AND
audittype = :"SYS_B_43" AND
auditdate >= h.hangtime AND
ROWNUM = :"SYS_B_44") AS audits,
monitstatus,
(SELECT :"SYS_B_45" || billnumber
FROM t_wy_monit
WHERE a.billnumber = billnumber AND
ROWNUM = :"SYS_B_46") AS dbd,
:"SYS_B_47" AS fdb
FROM t_wy_repairbill a, #####热点表
(SELECT MAX (sendorder) AS sendorder,
repairbillid,
MAX (repairbillsendid) AS repairbillsendid
FROM t_wy_repairbillsend
GROUP BY repairbillid) b,
t_wy_repairbillsend c,
(SELECT *
FROM t_wy_repairbillfeedback
WHERE repairbillfeedbackid IN
( SELECT MAX (repairbillfeedbackid)
FROM t_wy_repairbillfeedback
GROUP BY repairbillid)) d,
t_wy_repairbillbackcall e,
(SELECT MAX (resolverdate) AS r_signtime,
repairbillid
FROM t_wy_repairbillsignin
GROUP BY repairbillid) f,
(SELECT MAX (backdate) AS backdate,
repairbillid
FROM t_wy_repairbillback
GROUP BY repairbillid) g,
(SELECT MAX (hangtime) AS hangtime,
repairbillid
FROM t_wy_repairbillhang
GROUP BY repairbillid) h
WHERE a.repairbillid = b.repairbillid(+) AND
b.repairbillsendid = c.repairbillsendid AND
a.repairbillid = d.repairbillid(+) AND
a.repairbillid = e.repairbillid(+) AND
a.repairbillid = f.repairbillid(+) AND
a.repairbillid = g.repairbillid(+) AND
a.repairbillid = h.repairbillid(+) AND
a.isvalid = :"SYS_B_48" AND
a.accepttime >= TO_DATE (:"SYS_B_49", :"SYS_B_50") AND
a.accepttime <= TO_DATE (:"SYS_B_51", :"SYS_B_52" ) )
union all
SELECT a.consultationid AS billnumber,
:"SYS_B_53" AS reportsource,
a.accepttime,
(SELECT NAME
FROM t_ct_district
WHERE code = a.district) AS district,
:"SYS_B_54" AS address,
:"SYS_B_55" AS preairtype,
a.typeaname || :"SYS_B_56" || a.typebname || :"SYS_B_57" || a.typecname AS content,
:"SYS_B_58" AS reportcontent,
:"SYS_B_59" AS reapirunit,
:"SYS_B_60" AS status,
:"SYS_B_61" AS feedbackcontent,
:"SYS_B_62" AS date1,
:"SYS_B_63" AS date2,
:"SYS_B_64" AS date3,
:"SYS_B_65" AS date4,
personnumber AS RESOLVER,
:"SYS_B_66" AS backcallcontent,
:"SYS_B_67" AS backresults,
:"SYS_B_68" AS ishang,
:"SYS_B_69" AS audits,
:"SYS_B_70" AS hangcount,
:"SYS_B_71" AS qsyq,
:"SYS_B_72" AS yq,
:"SYS_B_73" AS db,
:"SYS_B_74" AS dbd,
:"SYS_B_75" AS fdb
FROM t_wy_consultation a
WHERE a.accepttime >= TO_DATE (:"SYS_B_76", :"SYS_B_77") AND
a.accepttime <= TO_DATE (:"SYS_B_78", :"SYS_B_79") ) nk_q1 8.获取哪些会话在参与latch free select sw.sid,
sw.seq#,
sw.p3text,
sw.p3,
sw.wait_time,
ln.name,
sw.p1text,
sw.p1,
sw.p1raw,
sw.p1text,
sw.p2,
sw.p2raw,
sw.p2text,
sw.p3,
sw.p3raw,
sw.p3text
from v$session_wait sw, v$latchname ln
where sw.event like 'latch free'
and sw.p2 = ln.latch#;
----- 248 55867 tries 0 -1 cache buffers chains address 5.04403166341881E17 07000001E163DE48 address 98 0000000000000062 number 0 00 tries
481 6 tries 0 -1 cache buffers chains address 5.04403166341862E17 07000001E1639408 address 98 0000000000000062 number 0 00 tries
349 51806 tries 0 -1 cache buffers chains address 5.04403166342398E17 07000001E16BBE48 address 98 0000000000000062 number 0 00 tries
254 456 tries 0 -1 cache buffers chains address 5.04403166342199E17 07000001E168B848 address 98 0000000000000062 number 0 00 tries
489 611 tries 1 1 cache buffers chains address 5.04403166342416E17 07000001E16C0528 address 98 0000000000000062 number 1 0000000000000001 tries
334 7717 tries 0 -1 cache buffers chains address 5.04403166341345E17 07000001E15BB0A8 address 98 0000000000000062 number 0 00 tries
321 892 tries 1 1 cache buffers chains address 5.04403166340447E17 07000001E14DFAA8 address 98 0000000000000062 number 1 0000000000000001 tries
9.由于持续耗尽cpu资源近半小时，经过领导的批准，kill掉这7个进程
select 'kill -9 ' ||spid||';'
from v$process
where addr in (select paddr
from v$session
where sid in (248, 489, 321, 481, 349, 254, 334));
---
kill -9 2437142;
kill -9 2609188;
kill -9 2719966;
kill -9 1462368;
kill -9 909466;
kill -9 2961650;
kill -9 2728098; 10.cpu资源idle率90%以上，问题解决. 11.下面是对该条SQL的优化工作: 下面的优化建议将提交开发部门进行调整： 建议：该语句7个会话并发执行，每个会话占用cpu资源12%左右，将cpu资源耗尽，而且每次执行的时间周期为1个小时.
消耗了大量的cpu资源.
表t_wy_repairbill的记录数20多万条，该表列repairbillid几乎与每个表列都有等值连接,但该列并没有建立索引，而且
列repairbillid的选择性很强.
建议在表t_wy_repairbill.repairbillid建立索引，尽量在与别的表做等连接之前，先索引一遍，减少与别的表等连接
参与的行源数,降低缓存的逻辑读,经查实,大量的热块就来源于该表;还有就是减少执行SQL的会话的并发数量.
转自：沪城篱笆http://blog.chinaunix.net/space.php?uid=21266384&do=blog&id=186570