Oracle调优与深入之灌水篇-1

第第页Oracle调优与深入之灌水篇Oracle调优与深入之灌水篇

发表于：2023-05-25来源：：点击数：标签：oracle调优灌水event深入

之一：EVENT:CONTROLFEVENT:CONTROLF-Obtaining~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Itisalsosometimesusefulto:"ALTERDATABASEBACKUPCONTROLFILETOTRACE".(FromOracle7.1.x)*DUMPOFCONTROLFILES,

之一：EVENT:CONTROLF

EVENT:CONTROLF-Obtaining

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Itisalsosometimesusefulto:"ALTERDATABASEBACKUPCONTROLFILETOTRACE".

(From（Oracle）7.1.x)

*DUMPOFCONTROLFILES,Seq#202417=316b1

*FILEHEADER:

Softwarevsn=117502976=700f400,CompatibilityVsn=117477376=7009000

DbId=4188500840=f9a77368,DbName='ECI_RPT'

ControlSeq=202417=316b1,Filesize=1238=4d6

FileNumber=0,Blksiz=512,FileType=4

==>ControlSeqControlfilesequencenumber

==>FileNumberNoconceptoffilenoforcontrolfiles

==>FileSizeThisfilesSize(blocks)

==>BlksizThisfilesblocksize

==>FileType(Seekclearcase/"target="_blank">cc.h)

KCCTYPCF1*controlfile*

KCCTYPRL2*redologfile*

KCCTYPDF3*vanilladbdatafile*

KCCTYPBC4*backupcontrolfile*

*DATABASEENTRY:

(offset=0x200,size=144,max=1,hi=1)

DFVersion:creation=0x700f400compatable=0x700b000,Date05/27/9503:34:38

DBName"ECI_RPT"

Databaseflags=0x00000147

==>^^^^^^^^^^^^^^^^^^^(kcc.h)

KCCDIMRE0x0001*whethermediarecoveryenabled*

KCCDICKD0x0002*ifdictionarymustbecheckedwithcontrolfile*

KCCDIRLR0x0004*DBOPENRESETLOGSrequired*

KCCDIJNK0x0008*junkvaluefrombeta-donotreusethisflag*

KCCDIMRC0x0010*was/islastmountedREAD_COMPATIBLE*

KCCDICNV0x0020*controlfilewasjustcreatedbyconvertfromv6*

KCCDIIRA0x0040*IncompleteRecoveryAllowedwhenresettinglogs*

KCCDIDBM0x0080*K_MLS-onnextopenDataBaseMacmode*

KCCDICCF0x0100*ControlfilewascreatedwithCREATECONTROFILE*

Incmpltrecoveryscn:0.06d3ac0cResetlogsscn:0.02811906count:0xc99f490

==>^^^LastIncompleterecovery^^^LastResetlogs

RedoVersion:creation=0x700f400compatable=0x700c000

#Datafiles=204,#Onlinefiles=1

==>^^^CanderivenoofOFFLINEfilesfromthesefigures.

Databasecheckpoint:Thread=0scn:0.00000000

Threads:#Enabled=1,#Open=0,Head=0,Tail=0

enabledthreads:010000000000000000000000000000000000000000000000...

Maxlogmembers=4,Maxdatamembers=1

Loghist=0,Archlist:Head=0,Tail=0,Forcescn:0.00000000

==>^^^^^^^^^SizeofLoghistoryifbeingstored

*REDOTHREADENTRIES:

(offset=0x290,size=92,max=6,hi=1)

*THREAD#1-status:0x6threadlinksforward:0back:0

==>^^^^(kcc.h)

KCCRTOPN0x01*setifthreadisOPeN*

KCCRTENB0x02*setifthreadisENaBled*

KCCRTPUB0x04*setifthreadisenabledPUBlicly*

#logs:3first:1last:3current:3lastusedseq#:0x0

enabledatscn:0.0000000001/01/8800:00:00

Checkpointedatscn:0.0000000001/01/8800:00:00

thread:0rba:(0.0.0)

enabledthreads:000000000000000000000000000000000000000000000000...

LOGFILEENTRIES:

(offset=0x4b8,size=72,max=96,hi=3)

*LOGFILE#1:

(#3)/opt/oracle/ECI_RPT/redo_log1.dbf

Thread1redologlinks:forward=2backward=0

siz=0x64000seq=0x0hws=0x0bsz=512nab=0x0flg=0x1

==>seq=LogSeqno,hws=HdrwriteSeq#nab=Nextavailableblock(kcc.h)

==>flg:KCCLESPL0x0001*setiflogfilespooled*

KCCLENAL0x0002*setifNomoreALocationmaybedoneinlog*

KCCLEUSM0X0004*ifsetUSeMeasthenextlogtomakecurrent*

KCCLECUR0x0008*setifthisisthecurrentlog*

Archivelinks:fwrd=0back=0Prevscn:0.00000000

==>^^^LowSCNofPREVIOUSlog

Lowscn:0.0000000001/01/8800:00:00

==>^^^SCNwhenlogwasswitchedINTO

Nextscn:0.0000000001/01/8800:00:00

==>^^^SCNAfterredointhislog(Ie:should==lowofNEXTlog)

...

*DATAFILE#1:

(#207)/usr/support/oracle/m4/pms505676/system_1_bpk.dbf

size=51200bsize=2048status=x213head=207tail=207dup=1

==>^^^^(kcc.h)

KCCFESTS0x0001*belongstoSystemTableSpace*

KCCFEONL0x0002*fileisONLine*

KCCFERDE0x0004*ReaDingisEnabled*

KCCFECGE0x0008*ChanGingisEnabled*

KCCFEMRR0x0010*MediaRecoveryRequired*

KCCFEGEM0x0020*GenerateEndhotbackupMarkeratnextopen*

KCCFECKD0x0040*Fileentrygeneratedbycheckdictionary*

KCCFESOR0x0080*SaveOfflinescnRangeatnextcheckpoint*

KCCFERMF0x0100*RenamedMissingFile*

KCCFEGOI0x0200*GenerateOff-lineImmediatemarker*

Checkpointcnt:61191scn:0.06d3ac0cstopscn:ffff.ffffffff05/27/9503:34:39

==>LastCheckpointSCN^^^^^^^^^^^^^^^^^Stop=fffimpliesOPENorWARMbackup

CreationCheckpointedatscn:0.0000000312/04/9306:37:16

thread:0rba:(0.0.0)

enabledthreads:000000000000000000000000000000000000000000000000

0000000000000000

Offlinescn:0.00000000

OnlineCheckpointedatscn:0.0000000001/01/8800:00:00

thread:0rba:(0.0.0)

...

之二：EVENT:FILE_HDRS

EVENT:FILE_HDRS-Obtaining

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(FromOracle7.1.x)

*DUMPOFDATAFILES:209filesindatabase

*DATAFILE#1:NB:Heredownisthecontrolfileinfo

(#207)/usr/support/oracle/m4/pms505676/system_1_bpk.dbf

size=51200bsize=2048status=x213head=207tail=207dup=1

==>^^^^(kcc.h)

KCCFESTS0x0001*belongstoSystemTableSpace*

KCCFEONL0x0002*fileisONLine*

KCCFERDE0x0004*ReaDingisEnabled*

KCCFECGE0x0008*ChanGingisEnabled*

KCCFEMRR0x0010*MediaRecoveryRequired*

KCCFEGEM0x0020*GenerateEndhotbackupMarkeratnextopen*

KCCFECKD0x0040*Fileentrygeneratedbycheckdictionary*

KCCFESOR0x0080*SaveOfflinescnRangeatnextcheckpoint*

KCCFERMF0x0100*RenamedMissingFile*

KCCFEGOI0x0200*GenerateOff-lineImmediatemarker*

Checkpointcnt:61191scn:0.06d3ac0cstopscn:ffff.ffffffff05/27/9503:34:39

==>^^^Stop=ffffimpliesOPEN,ABORTorWARM

CreationCheckpointedatscn:0.0000000312/04/9306:37:16

thread:0rba:(0.0.0)

enabledthreads:000000000000000000000000000000000000000000000000...

Offlinescn:0.00000000

OnlineCheckpointedatscn:0.0000000001/01/8800:00:00

thread:0rba:(0.0.0)

enabledthreads:000000000000000000000000000000000000000000000000...

*FILEHEADER:Heredownisfromthefileheader

Softwarevsn=117502976=700f400,CompatibilityVsn=117485568=700b000

DbId=4188500840=f9a77368,DbName='ECI_PD'^^^OldestUsableOracleVers

ControlSeq=202416=316b0,Filesize=51200=c800

==>^^^Shouldbe=toSeq#incontrolfile.

FileNumber=1,Blksiz=2048,FileType=3

==>^^^^^^^^^^^(kcc.h)

KCCTYPCF1*controlfile*

KCCTYPRL2*redologfile*

KCCTYPDF3*vanilladbdatafile*

KCCTYPBC4*backupcontrolfile*

Creationatscn:0.0000000312/04/9306:37:16

Backuptakenatscn:0.06d3978c05/27/9502:15:20thread:1

==>^^^^^LastALTERTABLESPACEBEGINBACKUP

resetlogscount:0xc99f490scn:0.02811906recoveredat06/21/9512:14:05

==>^^^^^LastALTERDATABASEOPENRESETLOGS

status:0x101rootdba:0x04000179chkptcnt:61191ctlcnt:61190

==>^^^^^(kcv.h)

KCVFHHBP0x01*hotbackup-in-progressonfile(fuzzyfile)*

KCVFHHBM0x02*endhotbackupmarkerencountered*

KCVFHOFZ0x04*OnlineFuZzybecauseitwasonlineanddbopen*

KCVFHCRM0x08*crashrecoverymarkerencountered*

KCVFHMFZ0x10*MediarecoveryFuZzy-fileinmediarecovery*

KCVFHCMF0x20*ClearMediarecoveryFuzzyatendofrecovery*

KCVFHSTS0x100*systemtablespace.Thisbitbelongstokcf*

KCVFHFMH0x200*FreshlyMungedHeader.resetlogsnotfinished*

Checkpointedatscn:0.06d3ac0c05/27/9503:25:53

thread:1rba:(864.2.0)

==>^^^^^LastDatafilecheckpoint

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(FromOracle7.1.x)

*DUMPOFLOGFILES:4logsindatabase

*LOGFILE#1:

(#1)/dev/usupport/redo2

Thread1redologlinks:forward=2backward=0

siz=0x1800seq=0x47hws=0x2bsz=512nab=0xffffffffflg=0x8

==>hws=Hdrwriteseq,nab=Nextavailableblock,

==>flg:KCCLESPL0x0001*setiflogfilespooled*

KCCLENAL0x0002*setifNomoreALocationmaybedoneinlog*

KCCLEUSM0X0004*ifsetUSeMeasthenextlogtomakecurrent*

KCCLECUR0x0008*setifthisisthecurrentlog*

Archivelinks:fwrd=0back=0Prevscn:275.f1716c52

Lowscn:275.f1716c5e06/21/9510:30:12

Nextscn:ffff.ffffffff06/20/9514:36:09

FILEHEADER:

Softwarevsn=118514176=7106200,CompatibilityVsn=117489664=700c000

DbId=3615166773=d77b1135,DbName='P716DB'

ControlSeq=6539=198b,Filesize=6144=1800

FileNumber=1,Blksiz=512,FileType=2

==>^^^^^^^^^^^(kcc.h)

KCCTYPCF1*controlfile*

KCCTYPRL2*redologfile*

KCCTYPDF3*vanilladbdatafile*

KCCTYPBC4*backupcontrolfile*

descrip:"Thread0001,Seq#0000000071,SCN0x0275f1716c5e-0xffffffffffff"

thread:1nab:0xffffffffseq:0x47hws:0x2eot:1dis:0

==>hws=Hdrwriteseq,nab=Nextavailableblock,

==>^^^(kcrfh.h)

KCRFHENOT0*NOTendofthread*

KCRFHEPUB1*EndofthreadforPUBLICthread*

KCRFHEPVT2*EndofthreadforPRIVATEthread*

==>dis=TRUEifthreaddisabledatendofthislog.

resetlogscount:0xe28f6a6scn:275.efd7b37b

Lowscn:275.f1716c5e06/21/9510:30:12

Nextscn:ffff.ffffffff06/20/9514:36:09

Enabledscn:275.efd7b37b05/22/9514:26:45

Threadclosedscn:275.f1716c5e06/21/9510:30:12

之四：EVENT:BLOCKDUMP

EVENT:-Interpreting

OracleBLOCKDUMP(Oracle7.2.x)SeeNote:33242.1>forPhysicalLayout

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thereisablockheaderforanyblockdump.Itdeterminestheformatofthe

restofthedump.AttheheadofanyOracleblockdumpyoushouldseea

blockheaderthus:

bufferdba:0x08000106inc:0x00001001seq:0x000087ce

ver:1type:1=KTUUNDOHEADER

==>dbaDBAoftheblock.SeeNote:33311.1>

==>incIncarnationnumber

==>seqSequenceNo

==>verVersionnumber.Shouldbe1

==>typeBlocktypeasdefinedink.h:

IdTypeExample

~~~~~~~~~~~~~

1=KTUUNDOHEADERNote:33187.1>

2=KTUUNDOBLOCKNote:33186.1>

3=SAVEUNDOHDR

4=SAVEUNDOBLOCK

5=DATASEGHDRNote:31258.1>

6=KTBMANAGED(withITL)

DATATabledataNote:28983.1>

MigratedRow:Note:33189.1>

ClusterNote:52508.1>

INDEXBranch:Note:41794.1>

Leaf:Note:33190.1>

BitmapLeaf:Note:41793.1>

7=TEMPDATA(noitl)

8=SORTKEY

9=SORTRUN

10=SEGFREELIST

11=DATAFILEHDR

12=DATASEGHDRwithFreeListGroups

13=CompatibilitySEG

--HereonwardsareforUNLIMITEDEXTENTS--

14=unlimitedundosegmentheader

15=unlimitedsaveundosegmentheader

16=unlimiteddatasegmentheader

17=unlimiteddatasegmentheaderwithflgblks

18=extentmapblock

19=backupsetpieceheader

20=backupsetdirectoryblock

21=controlfileblock

22=segmentfreelistblockwith#blksinfreelists

--Bitmapsegmentsetc..--

23=bitmappedsegmentheader

24=bitmappedfreelistblock

25=bitmapindexblock

26=bitmapblock

27=LOBblock

之五：DataSegmentHeader

QREF:Blockdump-DataSegmentHeader

Thisispartofasetofrelatedblockdumps.WhereyouseeNote.X.X>>>

youcanfollowthelinktoseetherelatedinformation.

SEGMENTHEADERBLOCKDUMPSeeNote:28983.1>fortheDataBlock.

~~~~~~~~~~~~~~~~~~~~~~~~~SeeNote:33188.1>forotherblocktypes.

bufferdba:0x24000922inc:0x00000635seq:0x00000007

ver:1type:5=DATASEGMENTHEADER

==>typeBlocktype.SeeNote:33188.1>forothertypes.

nfl=1,nfb=1

==>nflNofreelist

==>nfbNofreelistgroups?

EXTCTL::nex:1cex:0ces:4

cbk:2

lockvalue:U

==>nexNumberofExtentsallocatedintheExtenttable(below)

==>cexCurrentextent)

==>cbCurrentblockincurrentextent)HighWaterMark

==>cesCurrentextentsize(blocks)

==>ExtentTable:

EXTTBL::nbk:4dba:0x24000923

==>nbkNumberofblocksavailableintheextent

NB:Thefirstextenthasthesegmentheaderallocatedfromit

Thisisreallya5blockextent(block1isseghdr)

==>dbaDBAoffirstblockintheextent

==>Freelist

SEGLST::flg:USEDlhd:0x24000924ltl:0x24000924

==>lhdHeadDBAoffreelist

==>ltlTailDBAoffreelist

==>NB:Notallblocksinanextentareformattedifnotyetused.

==>Onlythoseonthefreelist.BlocksinthelastextentandNOT

==>onthefreelistmaycontainallzeroes

之六：Blockdump-DataBlock

>QREF:Blockdump-DataBlock

DATABLOCKDUMP(Oracle7.2.x)SeeNote:33190.1>forINDEXleafblock.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Note:31258.1>forSegmentHeader.

Note:33243.1>OSdumpofthisblock.

Note:33188.1>forotherblocktypes.

Blockheaderdump:dba:0x24000923

ObjectidonBlock?Y

seg/obj:0xc0bcsc:0x2f0.4045f9eaitc:1flg:-typ:1-DATA

fsl:0fnx:0x0

==>Seg/objObjectIDindictionary

==>cscSCNoflastblockcleanout

==>itcNumberofITLslots

==>flgO=Onfreelist

==>typ1=DATA2=INDEX

==>fslITLTXfreelistslot

==>fnxDBAofNEXTblockonfreelist

==>Transactiontable(below)controlsconcurrency:

==>ItlITLIndexno(NoITLSdeterminedbyINITRANSandMAXTRANS)

==>XidTransactionID(UndoSeg.Slot.Wrap)

==>UbaUndoAddress(UndoDBA.SeqNo.RecordNo)

==>FlgC=CommittedU=CommitUpperBoundT=ActiveatCSC

B=RollbackofthisUBAgivesbeforeimageoftheITL.

Flagcombinationsinclude:

CB--Txiscommitted,RollbackofthisUBAgivesprevITL.

ActiveTX-lookatRBSheadertoseeifreallyactive

--U-Iflck=0blockiscleanedoutwithUpperBoundCommit.

Iflck!=0(7.3)ScnisFSC.Scnbase.Thisisdelayedlogging

blockcleanout-seeParameter:DELAYED_LOGGING_BLOCK_CLEANOUTS>

TheFSCcannotbediscarded.

==>LckNoRowsaffectedbythistransaction

==>Scn/FscScn=SCNofcommitedTX,Fsc=Freespacecredit(bytes)

FscoverlapstheSCNWrapwhenpresent.

ItlXidUbaFlagLckScn/Fsc

0x010x0004.00e.0000017f0x08000592.00a0.0b2fsc0x001d.00000000

Note:33187.1>>Note:33186.1>>^2rowslocked

data_block_dump

=================>First4linesarederived

tsiz:0x7b8==>TotalDataAreasize

hsiz:0x1a==>Dataheadersize(16+#tabs*2+#rows*2)

pbl:0x00635884==>ptrtobufferholdingtheblock

bdba:0x24000923==>BlockDBA

flag===>N=pctfreehit(clusters),F=dontputonfreelist

K=flushableclusterkeys

ntab=1==>Ntables(>1forclusters)

nrow=4==>Nrows

frre=-1==>Firstfreerowindexentry,-1=youhavetoaddone

fsbo=0x1a==>FreespaceBeginOffset

fseo=0x320==>FreespaceENDoffset

avsp=0x330==>Availablespaceintheblock

tosp=0x34d==>TotalavailablespacewhenallTXscommit

0xe:pti[0]nrow=4offs=0==>Nrowsfor1sttable

0x12:pri[0]offs=0x792==>ROWINDEX(offset:pri[ROWNUM])

0x14:pri[1]offs=0x766==>offs==Offsetinblock.

0x16:pri[2]offs=0x320==>sffl==Nextfreeslot(Rowindexnumber)

0x18:pri[3]offs=0x71c==>sfflformachainoffreeslots,last=-1

block_row_dump:

tab0,row0,@0x792==>Table0,row0,offset

tl:9fb:--Hlb:0x1cc:0

nrid:0x24000924.0==>NEXTrowidforthisrow.Migratedrow.

Note:33189.1>>

==>tlRowsize(header+data)

==>fbFlagByte:-ThisisH:headpieceofrowONLY

K=ClusterKey(Flagsmaychangemeaning

ifthisissettoshowHASHcluster)

C=Clustertablemember

H=Headpieceofrow

D=Deleted