核心資料庫由於大量的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle核心資料庫由於大量的latch free導致CPU資源耗盡的現場解決過程Oracle資料庫
- inode節點耗盡導致資料庫OOM資料庫OOM
- crond不斷喚起sendmail導致資源耗盡的排查AI
- OS臨時埠耗盡導致無法建立資料庫連線資料庫
- 由hugepage設定導致的資料庫事故資料庫
- 共享記憶體段未釋放導致資料庫記憶體被耗盡記憶體資料庫
- openGauss 由於RemoveIPC未關閉導致資料庫crashREM資料庫
- 客戶資料庫出現大量cache buffer chains latch資料庫AI
- 異常程式導致大量資源佔用
- ORACLE資料庫使用者的process memory已耗盡Oracle資料庫
- PHP大量資料迴圈時記憶體耗盡問題的解決方案PHP記憶體
- 解決latch free問題的資料庫引數調整資料庫
- 如何解決PHP裡大量資料迴圈時記憶體耗盡的問題PHP記憶體
- kswapd 耗盡系統記憶體 kscand 耗盡系統cpu的解決記憶體
- oracle資料庫：耗cpu sql語句優化Oracle資料庫SQL優化
- 大量"library cache lock"事件導致資料庫無法連線事件資料庫
- 執行SQL查詢導致磁碟耗盡故障演示SQL
- 總結導致oracle資料庫主機CPU sys%高的一些原因Oracle資料庫
- 實時查詢最耗CPU資源的SQL語句SQL
- 【DataGuard】由於備庫引數設定不當導致資料檔案無法新增的故障分析
- kubernetes排程之資源耗盡處理配置
- 【DataGuard】由於備庫引數設定不當導致資料檔案無法新增的故障分析(轉）
- 實時獲得最耗CPU資源的SQL語句(zt)SQL
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- 請問，呼叫JDBC聯資料庫的時候，會耗費那些資源JDBC資料庫
- 開源資料庫的現狀資料庫
- Latch導致MySQL CrashMySql
- 由於源資料和目標資料的字符集不一致，引起無法匯入的問題
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- memlock過低導致的資料庫效能問題資料庫
- 檢測特別耗CPU，那怎麼解決由這種熱點行更新導致的效能問題呢？
- 使用mybatis-plus，由於表名關鍵字導致插入資料失敗MyBatis
- 核心是如何給容器中的程式分配CPU資源的？
- rac資料庫預設sql tuning advisor，導致大量library cache lock資料庫SQL
- 找出最耗資源的sqlSQL
- 由於基本資料型別使用姿勢不對導致的線上"死迴圈"問題排查資料型別
- 開源分散式資料庫RadonDB的核心技術與實現分散式資料庫