ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 核心数据库由于大量的latch free导致CPU资源耗尽的现场

核心数据库由于大量的latch free导致CPU资源耗尽的现场

原创 Linux操作系统 作者:victorymoshui 时间:2011-04-21 00:19:59 0 删除 编辑
今天上午核心数据库由于出现大量的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的会话的并发数。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9390331/viewspace-693021/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    173
  • 访问量
    303308