核心資料庫由於大量的latch free導致CPU資源耗盡的現場
今天上午核心資料庫由於出現大量的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_wait
where 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的會話的併發數。
資料庫環境
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_wait
where 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的會話的併發數。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-693021/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- crond不斷喚起sendmail導致資源耗盡的排查AI
- openGauss 由於RemoveIPC未關閉導致資料庫crashREM資料庫
- kubernetes排程之資源耗盡處理配置
- 總結導致oracle資料庫主機CPU sys%高的一些原因Oracle資料庫
- java由於越界導致的報錯Java
- 執行SQL查詢導致磁碟耗盡故障演示SQL
- (效能測試)--記錄一次高可用場景導致CPU資源升高
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- 開源分散式資料庫RadonDB的核心技術與實現分散式資料庫
- 檢測特別耗CPU，那怎麼解決由這種熱點行更新導致的效能問題呢？
- 由於無法分配ip而導致的FailedCreatePodSandBoxAI
- laravel實現100w大量資料插入資料庫Laravel資料庫
- 使用mybatis-plus，由於表名關鍵字導致插入資料失敗MyBatis
- 核心是如何給容器中的程式分配CPU資源的？
- 【案例】Oracle報錯ORA-01194 ORA-01110 由於資料庫SCN不一致導致無法啟動Oracle資料庫
- 資料庫不使用悲觀鎖導致問題的一種復現方式資料庫
- 記一次 gocode 在高版本 Go 高耗 CPU 導致的 LiteIDE 卡頓GoIDE
- 故障分析 | 血的教訓-由慢查詢引發的備份等待導致資料庫連線打滿資料庫
- 由於基本資料型別使用姿勢不對導致的線上"死迴圈"問題排查資料型別
- 使用資料庫處理併發可能導致的問題資料庫
- 磁碟IO故障導致的SQLServer資料庫無法寫入SQLServer資料庫
- 由OGG引發的資料庫故障資料庫
- Latch free等待事件(轉)事件
- 【資料庫資料恢復】磁碟空間不足導致sql server錯誤的資料恢復資料庫資料恢復SQLServer
- 關於 iconv 轉碼導致資料丟失的問題
- 避免PHP-FPM記憶體洩漏導致記憶體耗盡PHP記憶體
- 資料庫事務耗時過長導致Could not retrieve transaction read-only status from server異常資料庫Server
- 伺服器資料恢復-誤操作導致mysql資料庫資料丟失的資料恢復案例伺服器資料恢復MySql資料庫
- 碰到一個latch free相關的BUG
- 【YashanDB知識庫】archivelog磁碟滿導致資料庫abnormalHive資料庫ORM
- 【儲存資料恢復】IBM DS5300儲存由於硬碟壞道導致RAID5崩潰的資料恢復案例資料恢復IBM硬碟AI
- 【北亞資料恢復】vmfs還原快照操作導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- 【YashanDB知識庫】資料庫審計shutdown immediate操作導致資料庫異常退出資料庫
- 基於程式覆蓋資訊的資料庫核心問題定位工具資料庫
- 導致資料洩露的 6 個疏忽
- 有問題的mybatis的sql導致對資料庫進行了批量的修改MyBatisSQL資料庫
- 【北亞資料恢復】異常斷電導致Oracle資料庫報錯的oracle資料恢復資料恢復Oracle資料庫
- Latch free等待事件四(轉)事件