mysql数据库热块_核心数据库由于大量的latch free导致CPU资源耗尽的现场解决过程...

最新推荐文章于 2021-06-18 13:03:54 发布

小熊叭在云朵

最新推荐文章于 2021-06-18 13:03:54 发布

阅读量267

点赞数 1

文章标签： mysql数据库热块

本文链接：https://blog.csdn.net/weixin_32101685/article/details/113952661

版权

本文记录了一起Oracle 9i数据库因latchfree问题导致CPU资源耗尽的故障排查过程。通过分析等待事件、latch子类型、热块及SQL语句，定位到特定表T_WY_REPAIRBILL是问题根源。最终通过kill掉相关会话恢复了CPU资源。建议对T_WY_REPAIRBILL表的repairbillid字段建立索引以优化SQL执行。

摘要由CSDN通过智能技术生成

今天上午核心数据库由于出现大量的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

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

FROMt_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的会话的并发数量.

小熊叭在云朵

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫