移动crmdb1故障分析报告

Oracle工程师李永平于 月日，在移动进行standby，对9月29日11点左右的CPUsys偏高的问题进行分析。故障一：语句性能不佳，导致CPU库等等待和系统的稳定性调整业务语句5b2k6xuxfvrkt减少对PMAX的扫描全扫描和排序操作，从而达到减少GC等待，最终达到减少系统CPU负载的目的。将统一个表的业务通过serverGC的根据反馈（osw数据说明）92910:5011点期间，CPUSYS相对ASH报告，信息如下：SampleDataysisBegin29-Sep-16inAWRsnapshotysisEnd29-Sep-16inAWRsnapshotElapsed5.0SampleAverageActiveAvg.ActiveSessionperReportNoneTopUserEvent%AvgActiveCPU+WaitfordbfilesequentialUserlogfilegcbufferbusylatch，我们知道，一般gc相关等待，对CPU影响较大里面有出现gcbufferbusyacquire，我们再提取和gcbufferbusyacquire有关的语句：，SQLSampled#%%Top%SQLgcbufferACCESS-select*from(select由上面看到，gcbufferbusyacquire5b2k6xuxfvrkt导致，其执行计划SQL_IDSQL_ID5b2k6xuxfvrkt,childnumberselect*from(selectreceptionv0_.OIDasOID980_,receptionv0_.INRECOIDasINRECOID980_,receptionv0_.REGIONasREGION980_,receptionv0_.FORMNUMasFORMNUM980_,receptionv0_.CUSTIDasCUSTID980_,receptionv0_.SUBSIDasSUBSID980_,receptionv0_.SERVNUMBERasSERVNUMBER980_,receptionv0_.RECDEFIDasRECDEFID980_,receptionv0_.VERFITYTYPEasVERFITYT8_980_,receptionv0_.RECORGIDasRECORGID980_,receptionv0_.CONTACTTYPEasCONTACT10_980_,receptionv0_.RECOPIDasRECOPID980_,receptionv0_.RECDATEasRECDATE980_,receptionv0_.ISBACKPROCESSasISBACKP13_980_,receptionv0_.ISNOTIFYasISNOTIFY980_,receptionv0_.ISBATCHasISBATCH980_,receptionv0_.ISROLLBACKasISROLLBACK980_,receptionv0_.NOTESasNOTES980_,receptionv0_.RELATERECOIDRELATER19_980_fromCS_REC_RECEPTIONreceptionv0_orderbyreceptionv0_.RECDATEdesc)whererownum<=:1nhash|Id|Operation |Name |Rows|Bytes|TempSpc|Cost(%CPU)|Time Pstart|Pstop||0|SELECT|||||| 1|COUNT||||||| |2 PARTITIONRANGE|||79M|56 1|3 |||(1)|266:15:13 4 SORTORDERBY | |5 TABLEACCESS |CS_REC_RECEPTION|(2)||56 1PredicateInformation(identifiedbyoperation1-4- INSTANCE_NUMBERPARSING_SCHEMA_NAME EXECUTIONS_DELTAAVG_ELAPSED_TIMEAVG_DISK_READSAVG_CPU_TIMEAVG_BUFFER_GETS2016-09-29 1 02016-09-291 02016-09-291 02016-09-29 1 02016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-102016-09-10SegmentsbyGlobalCacheBuffer%ofCaptureshows%ofGCBufferBusyforeachtopsegmentwithGCBufferBusyforallsegmentscapturedbytheObjectObj.GC%NGNG5b2k6xuxfvrkttext:selectselectfrom(select as as as as as as as as as as as as as receptionv0_.ISBACKPROCESSas as as as as receptionv0_.RELATERECOIDas fromCS_REC_RECEPTION orderbyreceptionv0_.RECDATEwhererownum<=recdatedescpartitionPMAX，partitionPMAX1.8GBrownum<:1partitionPMAX，86%GlobalCacheBufferBusy5b2k6xuxfvrkt离（一个业务表只在一遍做业务操作），GCDMLinsertintoCS_REC_RECEPTION(REGION,FORMNUM,CUSTID,SUBSID,SERVNUMBER,RECDEFID,VERFITYTYPE,RECORGID,CONTACTTYPE,RECOPID,RECDATE,ISBACKPROCESS,ISNOTIFY,ISBATCH,ISROLLBACK,NOTES,OID)val(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,14,:15,:16,:17selectselectthis.ORDERID,this.OPERID,this.ORGID,this.SERVNUMBER,this.ACCESSTYPE,this.SUBSID,this.FIRSTCLASS,this.SECCLASS,this.THIRDCLASS,this.RULEID,this.ADDPRODID,this.FORMNUM,this.ROLEfrom(selecto.orderid,o.operid,id,o.servnumber,o.accesstype,p.subsid,c.firstclass,c.secclass,c.thirdclass,c.ruleid,c.addprodid,did,o.formnum,c.rolefromCS_REC_ORDER_WAITINGo,cs_rec_productp,SA_DB_CALOPRIDWLRULEcwhereo.orderid=p.orderidandc.status=1andtrunc(c.startdate)<=sysdateandNVL(c.enddate,sysdate+1)>sysdateandc.thirdclass='TC0032'andp.recdatebetweento_date(:1,'yyyy-MM-ddhh24:mi:ss')andto_date(:2,'yyyy-MM-ddhh24:mi:ss')ando.recdatebetweento_date(:3,'yyyy-MM-ddhh24:mi:ss')andto_date(:4,'yyyy-MM-ddhh24:mi:ss')ando.status<>'5'andp.operType='N'andexists(select1fromcm_subs_subscribersubs,im_pr_simsimwheresubs.enum=sim.iccidandsim.simtypein(selectdictidfromsa_db_dictitemdictwheredict.groupid='IM_USIMTYPE')andp.subsid=subs.subsidanddidin(selectdictidfromSA_DB_DICTITEMwhereGROUPID='LHXC4GPROD'))andexists(select1fromcm_subs_productprodwherep.SUBSPRODID=prod.SUBSPRODIDanddidin(selectParamValuefromSA_DB_SysParamwhereparamidlike'LHXC4GPROD%'))andexists(select1fromcs_rec_receptionrwherer.oid=p.orderidandr.recdatebetweento_date(:5,'yyyy-MM-ddhh24:mi:ss')andto_date(:6,'yyyy-MM-ddhh24:mi:ss')andr.recdefidin(selectdictidfromsa_db_dictitemwheregroupid='TC0032')andexists(select1fromcs_rec_receptionrswherers.recopid=r.recopidandrs.recdefid='ChangeEnum'andrs.subsid=p.subsidandto_char(rs.recdate,'yyyymmdd')=to_char(r.recdate,'yyyymmdd')))and(exists(select1fromCH_PW_WAYwwherew.wayid=idandw.waytypein('ET','EC'))oro.accesstype='bsacWidget')unionallselecto.orderid,o.operid,id,did,o.formnum,c.rolefromCS_REC_ORDERo,cs_rec_productp,SA_DB_CALOPRIDWLRULEcwhereo.orderid=did,o.formnum,c.rolefromCS_REC_ORDERo,cs_rec_productp,SA_DB_CALOPRIDWLRULEcwhereo.orderid=p.orderidandc.status=1andtrunc(c.startdate)<=sysdateandNVL(c.enddate,+1)>sysdateandc.thirdclass='TC0032'andp.recdatebetweento_date(:7,'yyyy-MM-ddhh24:mi:ss')andto_date(:8,'yyyy-MM-ddhh24:mi:ss')ando.recdatebetweento_date(:9,'yyyy-MM-ddhh24:mi:ss')andto_date(:10,'yyyy-MM-ddhh24:mi:ss')ando.status<>'5'andp.operType='N'andexists(select1fromcm_subs_subscrbersubs,im_pr_simsimwheresubs.enum=sim.iccidandsim.simtypein(selectdictidfromsa_db_dictitemdictwheredict.groupid='IM_USIMTYPE')andp.subsid=subs.subsidanddidin(selectdictidfromSA_DB_DICTITEMwhereGROUPID='LHXC4GPROD'))andexists(select1fromcm_subs_productprodwherep.SUBSPRODID=prod.SUBSPRODIDanddidin(selectParamValuefromSA_DB_SysParamwhereparamidlike'LHXC4GPROD%'))andexists(select1fromcs_rec_receptionrwherer.oid=p.orderidandr.recdatebetweento_date(:11,'yyyy-MM-ddhh24:mi:ss')andto_date(:12,'yyyy-MM-ddhh24:mi:ss')andr.recdefidin(selectdictidfromsa_db_dictitemwheregroupid='TC0032')andexists(select1fromcs_rec_receptionrswhere=r.recopidandrs.recdefid='ChangeEnum'andrs.subsid=p.subsidandto_char(rs.recdate,=to_char(r.recdate,'yyyymmdd')))and(exists(select1fromCH_PW_WAYwwherew.wayid=idandw.waytypein('ET','EC'))oro.accesstype='bsacWidget'))this9jy929tz0507jinsertintoCS_REC_RECEPTION(INRECOID,REGION,FORMNUM,CUSTID,SUBSID,SERVNUMBER,RECDEFID,VERFITYTYPE,RECORGID,CONTACTTYPE,RECOPID,RECDATE,ISBACKPROCESS,ISNOTIFY,ISBATCH,ISROLLBACK,NOTES,RELATERECOID,OID)(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,14,:15,:16,:17,:18,:19withtab1as(selecta.*,b.recdefidfromNG _SZ.cs_rec_ordera,NG _SZ.cs_rec_receptionbwherea.orderid=b.oidandb.recdefidin('BlankMendCard','YDChangeEnumRW','ChangeEnum','YkshChangeEnum','ChangeEnumByBlkCard')anda.status!='0'anda.recdate>=to_date('20160929000000','yyyymmddhh24miss')anda.recdate<to_date('20160930000000','yyyymmddhh24miss')),tab2as(selecta.*fromNG _SZ.cs_rec_resourcea,NG withtab1as(selecta.*,b.recdefidfromNG _SZ.cs_rec_ordera,NG _SZ.cs_rec_receptionbwherea.orderid=b.oidandb.recdefidin('BlankMendCard','YDChangeEnumRW','ChangeEnum','YkshChangeEnum','ChangeEnumByBlkCard')anda.status!='0'anda.recdate>=to_date('20160929000000','yyyymmddhh24miss')anda.recdate<to_date('20160930000000','yyyymmddhh24miss')),tab2as(selecta.*fromNG _SZ.cs_rec_resourcea,NG b,tab1wherea.resid=b.iccidanda.orderid=tab1.orderidanda.resclass='rsclS'anda.inout='0'andexists(selectdictidfrom m.sa_db_dictitemwheregroupid='IM_USIMTYPE'anddictid=a.restypeid)),tab3as(selecta.*fromNG _SZ.cs_rec_resourcea,NG _SZ.im_pr_simb,tab2wherea.resid=b.iccidanda.orderid=tab2.orderidanda.resclass='rsclS'and