oracleload过高的剖析过程

1、-问题背景同事电话我，说oracle数据库很慢，很卡，cpu99%了。1 ,查看问题现场，cpu咼单核不咼，load咼多核，看到每个核只有20%左右使用了 1/5，所以数据库应该没事，没有到故障级别，只是load 高已经接近 90 了， E:uazure_cloudoracle05.png :tcp - £0:21:44 up 12 daLT3R ISs2 users, loadi avezage: E£-57ji 吐农心 时££ Icaka: E54 trOLaly. 90 rujinLoLg, 834 slEpingp D stcppedm C zc

2、mtieSOt 9G.4U9RD_DlniF O_Q4idr 0,'Olwa,.0_0»hirO.DlarNfiJA： 11£423«6LL33E&33k 站亡古 23764321c frec lf£f46k buffersSwap:OkDk g酸.Ok £0# ICOCCCCk cochedFIDLERPRKI吕首:rro丽民:riHEj1S4J9ardcle20077,70ZDH1711R Q0«0122；19.71oicclpower<l£rIIX)CAL=JO1北MEoriae If30077,

3、7g2 En22Mw30.00.036fi=U.B3orlepowrdle?ILOC1L=NO|mczelcIe20077,?g23s15aR20.00.034E:C2.22elepewerde 3LOCAL-NO40579oracle20077.7g25r20sR"二0.0332：:4E.1oraclepowerdes|LOCAL=NOcracle20Q77-.7g25n213R23*00*032£:S4*E5era clepcwe rde a|.WAL=1IO42*774cxacle20077.7g27m24ziR20.00.0522:SC.e7cr c clepcwe

4、 rde 日WCAl-NOl14071CldClfe20077.7a24jq2031R19.70.05712C.4SOTfiClttcwertitdlLOCAL=NOl14310cracle20077-7-3込込RL9.7D.O123:19.76CTaclepcwerdsILOCILHOI22BC1craclc20077.7g2G&22aR19*70.0672156,92cxcclcpcwerdcs1. LOCAL-NO2C30Coracle20017.7g咖迓N19 + 70 + 0442E02.01oreclercwrdesLOCAL-JTO21U320077.7-321R10.7

5、0.0llfiiQC.33criel«pcvtrdei-LOCXL-1TO)35939czaalc20077.7g139R19.70«0367s&9.fi2czeclcpcvczdca(WCXL-NOl北护1DTiSClP2Q71.7g柯咖二19.70.03fl：51.51orsclPrrwMPS(LOCALsNOI39131oracle200衍22»ISn=19.7ox345:44«&3ers clepcve rde 吁(1OC1L-NO139329皿qgI匸20077.7gSin1831F.19.70,0542;5S,CBcrcGlc

6、pvre rdc 5|WCAL=-NQ3345oracle2Q0"旳21mY19.7n.o34Z：0C.9Zc-recei;<,-werde3,LOGALJTOCL；3e1 0_ln_D_"T7 Pr汕*JA-nLFo Pn p二尸 1 arijrvxjia Trlq 口TAml .ltrfti2 , 看io读写比较低，磁盘io不是瓶颈实时观察，才148，可以看出io性能没有问题，毕竟是ssd磁盘，E:uazure_cloudoracle04.png04/2B/201608:26PMavg-cpu:已 r%nicefeaystem tiowa工i:% steal

7、67;bxdle9S.S90.001.41O.OD0.000.00Device：tpaBlk_rEad/3Dlt_jwrtn/gBllf_read51Jr_jwrtn3d a0.00o.ao0.00000.000.000.0000j de5.500.0064. DQ012Sdd0.000.000.000004/2B/2016iOS PMavg-epui已 rtnicefesyst&m tiGwair%3T：eal1bidlegs.ei0.001.190.000.000.00Device:tpaBlk_read/3snxn/TBlk_readBl_wrzn0-000,000,0000皿Q

8、.QOQtQQ006.000.00 I11600296add10.000.00"TToo003，查看锁表情况，比较少select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where tl.sessi on id=t2.sid order by t2.logo n time; 执行没有记录。SQL> select t2.user name,t2.sid,t2.serial#,t2 .logon _timefrom v$locked_objectt1,v$sessi

9、 on t2 where t1.sessi on _id=t2.sid order by t2.logo n_time;no rows selectedSQL>4，查看session等待，没有等待SQL>select /*+ rule */ lpad(' ',decode(t1.xidus n,0,3,0)| t1.oracle_usernameuser_ name,o.ow ner,o.object_ name,o.object_type,s.sid,s.serial#,p.spidfrom v$locked_object t1,dba_objects o, v$

10、sessi on s,v$process pwhere t1.object_id=o.object_idand t1.sessi on _id=o.object_idand t1.sessi on _id=s.sid6 and s.paddr=p.addr;no rows selectedSQL>5，查看到有很多inactive进程#查看in active进程，kill掉这些进程SELECT SID, SERIAL#,MODULE, STATUSFROM V$SESSION SWHERE S.USERNAME IS NOT NULLAND UPPER(S.PROGRAM) IN (

11、9;TOAD.EXE', 'W3WIXE')AND S.LAST_CALL_ET >= 60*60*2AND S.STATUS = 'INACTIVE'ORDER BY SID DESC;#通过如下sql获取kill命令：SELECT'alter system kill session ”'|SID,'|SERIAL#”;'FROM V$SESSIONSWHERE S.USERNAME IS NOT NULL AND S.LAST_CALL_ET >= 10 AND S.STATUS = 'INACTI

12、VE' ORDER BY S.MACHINE,SID DESC;Kill掉'INACTIVE'进程后，db的load还是没有降下来，证明资源没有被释放掉。这么看来， 根源在正在运行的 sql,系统load高io低，说明系统cpu耗费高，有大量的逻辑运算而且 可能会有wait等待。6，去查看 wait_class等待，有大量 cursor: mutex S 事件#查询等待sql语句SELECT tl.sid, tl.serial#, tl.user name, tl.eve nt, t2.sql_text, t1.sql_id, tl.sec onds_in _wait,

13、 gram, tl.block in g_sessi on,-object_type | ': ' | object_ name object, t1.wait_class, tl.stateFROM v$session t1 LEFT OUTER JOIN v$sqlarea t2 on (tl.sql id = t2.sql id)-LEFT OUTER JOIN dba_objects t3 -ON (object_id = row_wait_obj#) where t1.wait class <> 'Idle'7，查看awr报告，

14、也指向了cursor: mutex SE:uazure_cloudoracle06.p ngForeground Wait Events* s-setofld - (Mistcond - n?cah -or a setisnd Only even wiUi Tslal Wat Tirie & 皿.00' ac shown- Bdered bv wait ifrewaila 电亦 I idle cerU lot'- iTiTEouts vduc- af !J indcates、due 応0 © .FT .寸lc: ； null 旺 ruL1 |匚YC.ltWta

15、ls| %E1C <!Uk |Tcriai Wart Time (s) |Arg wait (vna)| Wa ts JUn |cltsoi. raiuH Sr.iMi.715ioq6r:|13J!92 02htaftE ihic conpleticn512ioa54C 3Gleant pafi rent,4.3513-25 IEC.02|nb it sequelial readE74341 1C.Di(ibiary cschB look9.D11二C.1J7E-t.oaccrtol fie s«；L£flial bieacG833303S2C D'JSQL-

16、 Nel mare Js1e 1c dHsnt僭113 22c na|cb 韦la pErslIell rs&dS7q二143 07c.naeriq: T> rxjcx: cortentcrqg崎C-OG12q12：001c.ooIbraryc&cne- rrvt&x X123a卫iDIEC.00|lag Flw4CPi3c 45OODE:uazure_cloudoracle01.p ngTop 5 Timed Foreground Everts|EventWartsTimefs)Avg vfflit (ms| % DB timeWart Classcurtw:r

17、riut*xS44.276KK.1&71170.78CcrEurrerity6 CPU115266iai7latch - ea:he buffers thains49orcurreritycursor: pin S wail on K&153034S20.05CcncurTencylog file sync斗 3.CK427560 04Commit问题基本可以推测出来是cursor等待造成的8，验证 cursor_sharing 参数为 EXACT，正常SQL> show parameter cursor_shari ng;NAMETYPE VA

18、LUEcursor shari ngstri ng EXACTSQL>9，查看4月28日今天的记录数是 35W多，而且这个执行 次数还在不停的增加中select sql_id, count (*)from dba_hist_active_sess_historywhere eve nt like 'cursor:mutex S' and sample time>to date('28-04-201600:00:00', 'dd-mm-yyyy hh24:mi:ss')group by sql_id;SQL>select sql_

19、id,co un t(*)from dba_hist_active_sess_historywhere event like 'cursor: mutex S' and sample_time >to_date('28-04-201600:00:00','dd-mm-yyyy hh24:mi:ss')4 group by sql_id;SQL_ID COUNT(*)945088n 23w37kwd n3p108n smbsvfkzv6353543SQL>-乜丄匕占常eqU I心v衣-event 1 二ku工工了匸:51 and nmp

20、lu ti二udat® C125-43-2dD：03iGO11 1arv¥ir hhlf;u? '1ijzaap- iy 3ql_i J:®*| 晶m 陛 _ m e y-爲kjticGUISltl' |111财吋2佔*JgdnJp13Q&wnii55他35954310，查看昨天4月27日的记录数，才执行了 3W多selectsql id,count (*)from dba hist active sess historywhereeve nt like 'cursor:mutexS' andsample_time<t

21、o_date('28-04-201600:00:00','dd-mm-yyyyhh24:mi:ss')andsample time>to date('27-04-201600:00:00','dd-mm-yyyy hh24:mi:ss')group by sql_id;select S41_ia.UEhgiOdw=-si«-fieilTt=3Msiii>EaryjvlhL UlIe- 'Erypr；13 agI 23|3|§一丘01尋 PIP；ffll；口CTp：and 好円一："

22、 l:m pqd Mhiis I *是了辭”金Ml.w二口 Lwrcj,EXECSQL>selectsn ap_id,ELAPSED_TIME_DELTA/EXECUTIONS_DELTA,CPU_TIME_DELTA/EXECUTIONS_DELT UTIONS_DELTA from dba_hist_sqlstat where sql_id='O8 nsmbsvfkzv6' order by sn ap_id;no rows selectedselect sql_id,co un t(*)from dba_hist_active_sess_historywhere e

23、vent like 'cursor: mutex S' and sample_time <to_date('28-04-201600:00:00','dd-mm-yyyy hh24:mi:ss') and sample_time >to_date('27-04-201600:00:00', 'dd-mm-yyyyhh24:mi:ss')4 group by sql_id;SQL_ID COUNT(*)1165308n smbsvfkzv638468SQL>而昨天db数据库没有这么慢，这么卡，那么已

24、经基本确定了是由于这今天爆发增长的几十 万sql语句引起的cursor异常wait事件，接下来去找到这个具体的sql记录11，通过sql_id ( 08nsmbsvfkzv6 )查询到具体的 sql记录select sql_textfrom dba_hist_sqltextwhere sql_id= '08 nsmbsvfkzv6'>Bn_D±rr.E13-ra -a el gore rroE i MlticrE： fm Cselectwferezi Ciucz l ! B11r&xpeh 11 1 a 11. devei-Dfr-plui * j

25、71;tLea fcweitrp-pLBnJ else ' anBead ' tnd i 工託NN -Ek ijarKid«_lrFtl|r|pF4!H¥li ftRSl MN (CFCifef«4_»XtF 站*«Et4三J4T*etzter, ytaxil-Sj mzjgDea*n ;>B/E.4d skE：4>JpHyBcolacikffi.mrmnQCTQauDtTU" .叮tgvcMi. i mx rplAId», d 3胃.#7*冲fi-a Caelsct'M由M41g=UlA

26、£ AAaadsa -nssvid * xd.,t _ =亠-nDaa_nuxa & 0uoh r. nvw tw«A 1xxr C J arfT. p j *=E _：=Kfl JBh p?= j«ErE rSdki ”.±JMd«ijL_.ri9_Eii. O b3iTWIinJa . ngftTBl>_Bft43<Kg._44ia. u =HSE.«r ,F 2 ”乂土血"nZW_3nM3£H 11033 ( FUarTI 3S 53f »dj« - EKilHT_Jn

27、dl FraJ-BCE.piWBFL pr«J*EK Ld "HEiER 1¥ MkEH-Ahl. *rM Kd «*< JU IE I iifl 附匚环旺#I aalacx eixji | c-"H*d'|f xax p£ ksi4 rw ib xaht ra*d c. ueuan c Ps3adHidvca - ruadti id and.匕aa九e£* x-""!±-flDZjEl *gxs'jf：b-y 匕.nniLn Xd 3.良' 1ruCriHtwE&

28、#171; t-a P lsFa . =du*jn gx m. iraZc . t丄蛊piKEZ dr -5 =tt« Jh yaiF reenv-rSxcnpWnQjninu 阿turningkT.u udbSql太长了，截图截取不过来，贴出来如下: select * from ( select * from (select (case when (instr(','|wm_concat(type),',',develop-plan,')>0)then 'develop-plan' else'un Read

29、9; end) type,max(id) id, name ,no de_level,project,e nd,max(created_date) created_date,cen ter,year_flg,seque nee,min (isRead) isRead,MyReplace(wm_c on cat(msgCreateDate),',')msgCreateDate,wm_c on cat(group_ name),max(pla nl d),0 owe_days,0 owe_ moneyfrom (select'develop-pla n' as ty

30、pe,node.no de_id as id,template_ node.node_n ame as n ame, template_ node.no de_level, ject_ name as project, no de.schedule_e nd_date as end, warni ng.CREATED_DATE as created_date,''as center ,'' as year_flg,ROW_NUMBER() OVER(PARTITION Bno de.templet_id, ject_p

31、roject_id ORDER BY warnin g.created_date ASC) as seque nee,(selectmin (t.is_read)frompla n6_message_user_readt wheret.no de_id=node.no de_id and t.n eed_reader='baolm1' group by t.no de_id) isRead,”msgCreateDate,i nfo.n amegroup_ name,i _cdpla nld,0owe_days,0owe_ moneyfrom pla n6_war

32、ningwarni ng,pla n6_node project_distributi on project_projectno de,in fo,project_period pp,project,pla n6_templet_ node template_ nodewhere warning.no de_id = node.no de_idand no de.pla n_id = in _cdand ject_period_id = ject_period_id and pp.is_enabled=1 ject project id

33、 = ject project idand template, node.no de_id = no de.templet_idand in fo.if_in_pla n = 1and (warni ng.charger_cd = 'baolml' OR no de.charger_cd='baolm1' OR no de.ce nter_ma nager_cd='baolm1'OR ject_charger_cd='baolm1'ORject_charger_cd

34、2='baolm1'OR ject_charger_cd_bis='baolm1'OR('wubc'='baolm1' AND template_node.node_level in(1,2) )OR('xuhf='baolm1' OR 'zha nghf='baolm1' ) and template_ node.no de_level=1) and warnin g.stat = 2and no de.status = 1 and no de.is_e na

35、bled<>0 and warnin g.stat = 2unionselect'un Read' tyep ,node.no de_id id,template_ node.node_n ame as n ame,template_ no de. no de_level,ject_ name as project, no de.schedule_e nd_date as end,to_date(”,'yyyymmdd hh24:mi:ss') as created_date,” as cen ter，” as year_flg

36、,ROW_NUMBER() OVER(PARTITION BY node.templet_id, ject_project_id ORDER BY r.created_date ASC) as seque nee, r.is_read isRead,to_char(r.created_date,'yyyymmddhh24:mi:ss')msgCreateDate,i nfo. name group_ name,i _cd pla nld,0 owe_days,0 owe_ moneyfrom pla n6_message_user_read

37、r,pla n6_node no de,project_distributio nin fo,project_periodpp,project_projectproject,pla n6_templet_ node template_ nodewhere r.no de_id = node.no de_idand no de.pla n_id = in _cdand ject_period_id = ject_period_id and pp.is_enabled=1 and ject_project_id = ject

38、_project_idand template_ node.no de_id = no de.templet_idand info.if_in_plan = 1and no de.is_e nabled<>0and r.n eed_reader='baolm1'and r.is_read='0') warni ngwhere warnin g.seque nee = 1 group by n ame ,no de_level,project,e nd,ce nter,year_flg,seque nee ) union(select 'oa_

39、meeti ng' as type,mett in g.oa_meeti ng_id as id,TO_CHAR (mett in g.bus in ess) as n ame,10 as no de_level,” as project,mett in g.target_date as end,warnin g.created_date as created_date,” as center,” as year_flg,0 as sequenee," isRead,'' msgCreateDate,''group_ name," p

40、la nl d,0 owe_days,0 owe_ moneyfrom oa_meet ing mett ing, pla n_warning warni ngwhere metting.oa meeting id = warning.targetand metti ng.HIDDEN_FLG = 0 and metting.status in (1,2, 4)andmett in g.target_dateis not nullandin str(''|war nin g.resp on sible_pers on，:baolm1;')>0 ) union(se

41、lect 'work-center-plan'as type,cen. pla n_work_ce nter_idas id,cen.content as name,10 as node_level,as project,cen .target_date as end,year_flg,owe_days,0 owe_moneyfrom plan_work_center cen,plan_warning warningwherecen .pla n_work_ce nter_id = warni ng.targetand cen. status_cd in (1)andcen.

42、ope n_type= 'O'and cen .target_date is not n ullandwarnin g.created_date as created_date,” cen ter ,0 as seque nee," isRead," msgCreateDate," group_ name," pla nld ,0ject_detail_idin str(''|ce n.pri ncipal ,'baolm1;')>0) union (select 'project

43、_detail' as type, as id, pd.content as name,12 as node_level, '' as project, pd.target_date as end, pd.created_date as created_date," as center , ” as year_flag, 0 as sequenee, ” isRead,'' msgCreateDate, ” group_ name," pla nl d,0 owe_days,0 owe_ money from project_deta

44、il pd wherepd.status in ('1','2','4') and ject_detail_id in (select ject_detail_id from project_pri ncipal pp where pp.pri ncipal_cd='baolm1') warning order by(casewhe ntype='work-zc-plan' then -1 else 1 end) asc, (case when type='unRead' t

45、hen 0 else 1 end) asc,(case whe n isRead=0 the n 0 else 1 end) asc, (case whe n msgCreateDate is not n ullthen 0 else 1 end) asc, warning.type asc,(case when warning.node_level=0 then 4 elsewarning.node_level end) asc, trunc(warning.end)-trunc(sysdate)asc,warning.created_datedesc,owe money desc ) wh

46、ere row num <= :1By the way，基本可以肯定是这个 sql引起的，那么除了这个 sql是否还有别的记录呢，这需 要现场实时观察。12，分析当前的时间段记录最近时间段的 mutex记录都是08nsmbsvfkzv6这条sql引起的：selectsql id,to char(sample time,'yyyy-mm-dd hh24:mi'), count (*)from dba_hist_active_sess_historywhere eve nt like 'cursor: mutex S'and sample_time >

47、to_date('28-04-2016 22:30:00', 'dd-mm-yyyyhh24:mi:ss')AND sample time < to date('28-04-2016 23:10:00', 'dd-mm-yyyyhh24:mi:ss')groupby sql_id,to_char(sample_time,'yyyy-mm-dd hh24:mi')orderby sqld,to char(sample time,'yyyy-mm-dd hh24:mi') descE:uazure

48、_cloudoracle07.pngUutput ' Stati琳心aelect sql idr 七口 chai： sample tliner 1 yyyy Turndd hh2:mi 1 ) r count (* from 业a hist active seas history where evenc Like 'cursor: mutex S *and 3unple_uuue > to_daEe26-0-2016 22;30; 00. dd-mrn-yyyy lih? AND sample time < to date I*23-0-52016 23:30:00',dd-mrn-yyyy hhE弓 group by Bql_id, uo_char (sanople_t:iiner bii24:nLL, > order t>y sql idf 匸口 char (sample rime, 1 yyyy-mrn-cld tih24:rni1) desc :.tl.£EF,ao ch5QLJD二TO CHARSAPLEE：Y