oracle dba手记转储分析案例一则

1、OracleDBAe 盖国强，云和恩墨创始人， 中国地区首OracleDBAe 盖国强，云和恩墨创始人， 中国地区首。1.1 状态转储的常用命 altersseteventsimmediatetrace1.1 状态转储的常用命 altersseteventsimmediatetracenameeleveloradebugoradebug ulimit oradebugdumppross altersseteventsimmediatetracenameeleveloradebugsetmy oradebug oradebugdumpeSQL oradebug setmySQLoradebugS

2、QLoradebugdumpe sqlplus-prelim/assysdba oradebug setmyoradebug oradebugdumpeSystem s e 转储案例分析一1.2WAITED TOO LONG FOR A ROW CACHE ENQUEUE System s e 转储案例分析一1.2WAITED TOO LONG FOR A ROW CACHE ENQUEUE 转储文件分析定位 Redothreadmountedbythisinstance:Oral s ub: i : 8371,image:*2010-03-27*ACTIONNAME:()2010-03-27

3、* MODULE M:SLPlu 2010-03-27 Oracle Database 10g EntriseEditionRelease10.2.0.3.0-64bitProduction With the Partitioning, OLAP and Data Mining optionsNodeSF2900 5.9 : Generic_118558-33Machine: 2929OS8317：Unixs:eObject6c10508e8：rowcacheenqueue:（S：mode: 进一步的向下检查可以找到SO 对象 6c10508e8 都O 6c10508e8,type:4,own

4、er:6c1006740,flag:INIT/-/-SO 6c1006740,type:2,owner:0,flag:INIT/-/-s)d=29,cls u/to: 6c1097430/6c1096bf0,flag:(0)error: 0,call error:0,sesserror:0, txn errortinfo)treceived:1090t received-location: kslpsr last pros totme:6c100280016 t: 0 0 24t-location:last protedbyme:6c100280016 (latch info) wait_ev

5、ent=0 bits=0sGroup:DEFAULT,pseudoproc:O/S info: user: oracle, term: UNKNOWN, : info:i : 8371,image:eWF9*SERVICENAME:(EDW)2010-03-27* I4613 2010-03-27 ITE TOO LONG FOR A ROW CACHE ENQUEUE LOCK! row cache eneue : 8e oe: N reet System e 转储案例分析一 System e 转储案例分析一 具有级联关系，下一层隶属于上一层的 Owner，第一个 SO 对象的 Owner

6、6c1006740 S29 SO lastwaitforksdxexeotherwaitblockingsess=0 x0seq=36112wait_time=5secondssince wait started=3=0,=0,Waitforksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcou

7、nt=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,forksdxexeotherwaitcount=1=0,=0,temporaryobjectcounter:) sid: 120 trans: 6c285ea98, creator: 6c1006740, flag: (100041) USR/- DID:0001-001D-001BC795,short-termDID:txnbranch:oct:2, prv:0,sql:4f528

8、d510,psql:491cbe3e8, user:O/S if us: diitrtr te: HO0 os: 3792:38132, mcioa: sll.xelit na: L*l hash vle36992 的 的 1.4ROW CACHE 对象的定4f4e57138 RowCacheParentObjectDC_OBJECTS。（mode=X文件的截取 ITE TOO LONG FOR A ROW CACHE ENQUEUE LOCK! row cache eneue : 8e oe: N reet O 6f tpe 3 er: 6c1006740, flag: INIT/-/-/0

9、 x00 (call)sess:cur6c10508e8,rec6c10508e8,usr6c10508e8;depth:0O 10e tpe , er: 6c1096bf0, flag: INIT/-/-/0 x00 (call)sess:cur6c10508e8,rec6c10691f8,usr6c10508e8;depth:1O 604 typ 3 er: 6c1096eb0, flag: INIT/-/-/0 x00 (call)sess:cur6c10691f8,rec6c10691f8,usr6c10508e8;depth:2O e0e te: , one 6c1097430, f

10、lag: INIT/-/-rowcacheenqueue:count=1 jt38System e 转储案例分析一SO:4f2e82c88, type:53,owner: 6c10508e8, flag:INIT/-/-LIBRARYOBJECTSystem e 转储案例分析一SO:4f2e82c88, type:53,owner: 6c10508e8, flag:INIT/-/-LIBRARYOBJECTLOCK:lock=4f2e82c88handle=4f528d510callpin=0 pin=0hpc=0000 user=6c10508e8LIBRARY =6c10508e8coun

11、t=1flags=0000eIER /*+ APPEND*/ CCIIVBL TO CCIIL_ GGIN SELECT :B , =03-24-2010namespace=CRSRkkkk-dddd-llll=0000-0001-0001lock=Npin=0latch#=12hpc=ffechlc=ffec lwt=4f528d5b84f528d5b8,4f528d5b8 ltm=4f528d5c84f528d5c8,4f528d5c8 pwt=4f528d5804f528d580,4f528d580 ptm=4f528d5904f528d590,4f528d590 LIBRARYOBJE

12、CT:type=CRSRflags=EXS0001pflags=0000CHILDREN:us=VALDtable 文 SSO:8c00037d0,type: 文 SSO:8c00037d0,type:2,owner:0,flag:INIT/-/-s) Oracld=16,callscur/top:8c0095028/8c0094aa8,flag:(0)- error: 0, call error: 0, sess error: 0, txn error 0row cache qee count=1 =8c005d7c8 t=fe5713 rowcacheparentobject:addres

13、s=4f4e57138cid=8(dc_objects) hash=b363a728 typ=11 tranion=8c183c258 flags=00000002own=4f472084e86f038,48f0538 tf51ef08f4 set=0, complete=FALSE0000003800134944585f434143495f494e565f424c425f44430000000000000004000009505f323031305f513100000000000000000000000000000000000000000000000000000000000000000000

14、000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000f0000000209ca14786e01786e01020e3239786e010

15、20e323901000000000000000000000000000000000004a72275184a722718814a72275184a7227420494cd5878 DATA BLOCKS:heap uspinschange04aebaa9504a7227b68I/P/A/-0 System s e 转储案例分析一(tinfo)treceived:1150treceived-location:lastpro laststme:6c10028001t:System s e 转储案例分析一(tinfo)treceived:1150treceived-location:lastpro

16、 laststme:6c10028001t: 00 t-location:tedbyme:6c10028001s(latch info)wait_event=0sGroup: DEFAULT, pseudo proc:O/Sinfo:user:oracle,term:UNKNOWN,:8200,image:leSF info:UnixsJOB 由 调度，执行Job Slave Se Object SlaveID:0,JobID:8913SO:8c005d7c8,type:4,owner:8c00037d0,flag:INIT/-/-) sid: 45 trans: 8c183c258, cre

17、ator: 8c00037d0, flag: (48100041) USR/- DID:0001-0010-0007BFA6,short-termDID:txnbranch:oct:0,prv:0,sql:0,psql:0,user:O/S info: user: oracle, term: UNKNOWN, os:8200,machine:SF2900 program: oracleSF2900 (J000)ppliti nme: BSCHU hash le27824ti nme: TADIOJ hash au=3481156witi for Wi for ri lk bli sess=0

18、x0 seq=5909 aii seconds iwi object_id=0,lock_mode=0,=0 Wait HistoryforWaitforshrinklockcount=1wait_time=380596 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1wait_time=107262 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1wait_time=107263 object_id=0, lock_mode=0, =0forWaitforshrinkl

19、ockcount=1wait_time=107246 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1 SO:4e8a2c6c0, type:53,owner: 8c005d7c8, flag:INIT/-/-LIBRARYOBJECTLOCK:lock=4e8a2c6c0handle=4c3c1ce60callpin=0 pin=0hpc=0000 user=8c005d7c8=8c005d7c8count=1flags=0000 SO:4e8a2c6c0, type:53,owner: 8c005d7c8, flag:INIT/

20、-/-LIBRARYOBJECTLOCK:lock=4e8a2c6c0handle=4c3c1ce60callpin=0 pin=0hpc=0000 user=8c005d7c8=8c005d7c8count=1flags=0000LIBRARY NDLE:handle=4c3c1ce60mtx=4c3c1cf90(1)m=lt id CCCIIN_BLBC diy priin P_2010_Q1 ri space =03-25-2010namespace=CRSRkkkk-dddd-llll=0000-0001-0001lock=Npin=0latch#=15hpc=0002hlc=0002

21、 lwt=4c3c1cf084c3c1cf08,4c3c1cf08 ltm=4c3c1cf184c3c1cf18,4c3c1cf18 pwt=4c3c1ced04c3c1ced0,4c3c1ced0 ptm=4c3c1cee04c3c1cee0,4c3c1cee0 ref=4c3c1cf384c3c1cf38,4c3c1cf38 lnd=4c3c1cf504c3c1cf50,4c3c1cf50LIBRARYOBJECT:type=CRSRflags=EXS0001pflags=0000CHILDREN:us=VALDtable049efa3fe849efa3c584c3ad91a8 149ef

22、a3fe849efa3ed8DATAheap uspinschange04c3589b384aa2bf780I/P/A/-0 object_id=0,lock_mode=0,forWaitforshrinklockcount=1wait_time=107248 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1wait_time=107003 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1wait_time=107169 object_id=0, lock_mode=0,

23、 =0forWaitforshrinklockcount=1wait_time=107233 object_id=0, lock_mode=0, =0forWaitforshrinklockcount=1wait_time=107069 object_id=0, lock_mode=0, =0temporaryobjectcounter:System s e 转储案例分析一系统通过DBMS_SCHEDULERAUTO_SPACE_ADVISOR_JOB任务发出了 SQL 语句：alter index CACI.IDX_CACI_INV_BLB_DC modify partition shrin

24、kspace System s e 转储案例分析一系统通过DBMS_SCHEDULERAUTO_SPACE_ADVISOR_JOB任务发出了 SQL 语句：alter index CACI.IDX_CACI_INV_BLB_DC modify partition shrinkspace INSERT/*+OCACI_INV_BLB_DCNOLOGGINGSELECT:B1T.*,SYSDATEFROMCACI_INV_BLB 1.5辅助分Windows上做了Unixawk工具进行分析D:awk-fass109.awk1.0.9-singSystemewaitingforrdbmsipcmess

25、age waiting for pmon timer waitingforrdbmsipcmessage waitingforrdbmsipcmessage waitingforrdbmsipcwaitingforrdbmsipcwaitingforrdbmsipcmessage 11:waitingforrdbmsipc12:forStreamsAQ:waitingfortimemanagementorcleanuptasks13:waitingforwaitingforrdbmsipcmessage 11:waitingforrdbmsipc12:forStreamsAQ:waitingf

26、ortimemanagementorcleanuptasks13:waitingforrdbmsipc14:waitingforStreamsAQ:qmncoordinatoridlewait15:waitingforrdbmsipc16:waitingforWaitforshrink17:waitingforsmon18:waitingforSQL*Netmessagefromnt19:waitingforrdbmsipcmessage 21:waitingforrdbmsipc23:waitingforrdbmsipc25:waitingforSQL*Netmessagefromnt27:

27、waitingforSQL*Netmessagefromnt29:lastwaitforCmd:Rcache30:waitingforStreamsAQ:qmnslaveidlewait33:forStreamsAQ:waitingformessages hequeueAboveisalistofallthe ses.Iftheyarewaitingfora thenitwillbegiveninsquarebrackets.Belowisasummaryofwaiteduponresour ,togetherwiththeholdertoAsidof?ttheholderwasnotsyst

28、ems ResourceHolderS Rcache16:waitingforWaitforshrinkObjectRcacheSystem s e 转储案例分析一77807Lines16 4f4e57138 Row System s e 转储案例分析一77807Lines16 4f4e57138 Row 挂起状态，无法执行INSERT 的辅 alterindexCACI.IDX_CACI_INV_BLB_DCmodifypartitionP_2010_Q1shrinkspace execute alterindexCACI.IDX_CACI_INV_BLB_DCmodifypartition

29、P_2010_Q1shrink alterindexCACI.IDX_CACI_INV_BLB_DCmodifypartitionP_2010_Q1shrinkspace SQLselectdump(IDX_CACI_INV_BLB_DC,16)from4,4,844,4,9f4,4,6f44,4,44 ,50000003800134944 585f4341 43495f49 4e565f42 4c425f44 430000000000000004000009505f3230 31305f51 31000000000000000000000000000000000000000000000000

30、00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000f0000000209ca14786e017

31、86e01020e3239786e01020e3239010000000000000000000000000000000000System s e 转储案例分析一1.7状态转储的常用命 System s e 转储案例分析一1.7状态转储的常用命 SystemedumpedtotracefileLibrary 的BufferBusyWaitsSysteme8: waitingforbufferbusy waits(2,9,11)11:waitingforlogfileswitch(archivingneeded)wait 15:waitingforlogfileswitch (archiving

32、needed)16: waiting for library cache lockLOCK:handle=20e750ca8wait 19: waiting for buffer busy waits (2,59,1b) waitCmd:20: waiting for log file switch (archiving needed) wait 21:waitingforStreamsAQ:qmncoordinatoridlewaitwait24: waiting for library cache lockLOCK:handle=20e750ca8wait 25: waiting for buffer busy waits (1,2ba2,1) wait175:waitingforlogfileswitch(archivingneeded)wait Cm