【文档说明】[计算机软件及应用]Buffercache课件.ppt，共(85)页，2.610 MB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-2115.html

以下为本文档部分文字说明：

BufferCacheWaits#.2InThisSection1.latch:cachebufferschains2.latch:cachebufferslruchain3.latch:cachebufferhandles4.F

reeBufferWait5.BufferBusyWait6.WriteCompleteWait7.BufferExterminate#.3BufferCacheRedoLibCacheBufferCacheIOLocksNetwork#.4REDOLog

FilesDataFilesDBWRLGWRUser2User1User3LogBufferBufferCacheLogBufferBufferCacheSGALibraryCacheOracleMemoryStructures#.5BufferCacheAccess➢Bu

fferCacheManagement➢LocatingFreeblocks➢Findingdatablocks➢ManagingLRUlists➢CleaningDirtyBlocks➢BufferCachemanagementcanca

usecontention➢DifferentfromIO(readingblocksofdisk)#.6Query0.Parsestatement1.Findobjectinformationindatadictionary2.Calculat

eexecutionplan3.Iffulltablescan➢Lookatallblocksoftable4.Ifindexfindrootofindexandfollowtokey5.DataDictionarywillhaveinfoabouttableorindexblock➢F

ile#➢Block#6.OnceyouknowtheblockDBA(file#+block#)…Selectenamefromempwhereempno=12;#.7IsBlockincache?Nowyouhaveafil

e#andblock#Howdoyouknowifablockiscached?ShadowProcess?Doyousearchalltheblocks?Couldbe1000sofblockstosearch.B

uffercachesareinthemultiGig#.8BufferCacheFindablockby:1)HashofDatafile#Block#2)Result=Bucket#3)Searchlinkedlistfortha

tbucket#WhatisahashvalueWhatareBucketsWhatisthelinkedlist?#.9ConceptsTounderstandcontentiononthebuffercache,needtounderstand:1.LinkedLists2.Hashing3.

Buckets#.10DoubleLinkedLists03C3900003C3947803C38F6003C3900003C3900003C38F6003C39478AddressNextPrevious#.11HashingFu

nction➢SimplehashcouldbeaModfunction➢1mod4=1➢2mod4=2➢3mod4=3➢4mod4=0➢5mod4=1➢6mod4=2➢7mod4=3➢8mod4=0➢Using

“mod4”asahashfuntioncreates4“buckets”tostorethings#.12HashBucketFillDataBlock1203HashBlock’sfile#block#’sResultinabucket#PutBlockinbucket????

HashBlock’s1file#437block#’s(1+437)mod4=2Afterawhilethebucketsbecomepopulatedwithblocks#.13LatchesProtectBuc

ketContentsBufferHeadersDataBlocksHashbucketlatchesBufferHeaderscontentsdescribedbyX$BH#.14X$bh➢Desc

ribesContentsofBufferHeadersSQL>descx$bhNameType--------------------ADDRRAW(4)DBARFILNUMBERDBABLKNUMBEROBJNUMBERHLADDR

RAW(4)NXT_HASHRAW(4)PRV_HASHRAW(4)…muchmoreADDRDBARFILDBABLKOBJHLADDRNXT_HASHPRV_HASH…AeachbufferheadercontainsIn

formationaboutthedatablockItpointstoandthepreviousandnextBufferheaderinalinkedlist#.15Cache03C3900003C3947803C38F6003C3900

003C3900003C38F6003C39478ADDRNXT_HASHPRV_HASH#.16X$BHdescribesHeadersBufferHeadersDataBlocksHashbucketlatchesHLADDRNXT_HASHPRV_HASHADDRADD

RDBARFILDBABLKOBJx$bhADDRDBARFILDBABLKOBJHLADDRNXT_HASHPRV_HASH#.17ToFindaBlock1.Hashtheblockaddress2.GetBucketlatch3.Lookforheader4.Found,r

eadblockincache5.NotFoundReadblockoffdiskShadowProcessBufferHeadersDataBlocksHashbucket2354latches12345#.18CacheBuffersChainsHashBucketss

5s4s3s2s1SessionsContentioniftoomanyaccessesonabucketlatchesBlockHeadersCacheBufferChainDataBlocks#.19ExamplesS1S2S3S41.LookupTable2.NestedLoopsSele

ctt1.val,t2.valfromt1,t2wheret1.c1={value}andt2.id=t1.id;t1Index_t2t2#.20CBCSolutionsFindSQL(Whyisapplicationhittingthebl

ocksohard?)➢Nestedloops,possibly➢HashPartition➢UsesHashJoin➢Hashclusters➢Lookuptables(“selectlanguagefromlang_

tablewhere...”)➢Changeapplication➢Useplsqlfunction➢Spreaddataouttoreducecontention➢Selectfromdual➢Possibly

usex$dualHowdoyoufindtheSQL?#.21CBC:Statspack9iTop5TimedEvents~~~~~~~~~~~~~~~~~~%TotalEventWaitsTime(s)ElaTime------------------------

-----------------------------------latchfree21,4281,91481.37CPUtime36015.29PL/SQLlocktimer16482.04SQL*Netmes

sagefromdblink4,69014.58dbfilesequentialread1,4275.19Top5TimedEvents~~~~~~~~~~~~~~~~~~%TotalEventWaitsTime(s)El

aTime-----------------------------------------------------------latchfree21,4281,91481.37CPUtime36015.29PL/SQLlocktimer16482.04SQ

L*Netmessagefromdblink4,69014.58dbfilesequentialread1,4275.19LatchSleepbreakdownforDB:CDBInstance:cdbSnaps:1-2->orderedbymissesdescLatchNameRequests

MissesSleepsSleeps1->4-------------------------------------------------------cachebufferschains12,123,500608,41515,7590/0/0/0/0librarycachepin12,02

7,599173,4462,862172694/743/8/1/0librarycache12,072,50398,0652,37397739/279/47/0/0simulatorlrulatch60643643

46/426/4/0/0FailstofindSQL#.22CBC:Statspack10gTop5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Tim

e---------------------------------------------------------CPUtime3554.3latch:cachebufferschains461124317.6latch:librarycachepin35822912.6latch

:librarycache2762319.8logfilesequentialread151601.4Top5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time------------------

---------------------------------------CPUtime3554.3latch:cachebufferschains461124317.6latch:librarycachepin35822912.6la

tch:librarycache2762319.8logfilesequentialread151601.4FailstofindSQL#.23CBC:ASHselectcount(*),sql_id,nvl(o.object_name,ash.curren

t_obj#)objn,substr(o.object_type,0,10)otype,CURRENT_FILE#fn,CURRENT_BLOCK#blocknfromv$active_session_historyash,all_objectsowhereeventlik

e'latch:cachebufferschains'ando.object_id(+)=ash.CURRENT_OBJ#groupbysql_id,current_obj#,current_file#,current_bl

ock#,o.object_name,o.object_typeorderbycount(*)/CNTSQL_IDOBJNOTYPEFNBLOCKN----------------------------------------84a09r4dwjp

v01qMYDUALTABLE193170SQLStatement:SuccessExtra:Hotblock#.24CBC:OEM#.25CBC:ADDMProblemSQLStatementSolution?#.26CBC–FurtherInvestigationselect*fromv$e

vent_namewherename='latch:cachebufferschains'EVENT#NAME--------------------------------------58latch:ca

chebufferschainsPARAMETER1PARAMETER2PARAMETER3------------------------------addressnumbertriesNOTE:_db_block_hash_buckets=#ofh

ashbuckets_db_blocks_per_hash_latch=#ofhashlatches#.27CBC:what’sthehotblock➢CangetitfromASH➢Current_fi

le#➢Current_block#➢Whereevent=‘latch:cachebufferschains”➢Sometimesfileandblock=0➢SeemstohappenforNestedLoops➢Get

thehotblockrealtime➢UseHashLatchAddress➢Ash.p2=x$bh.hladdr#.28HotBlock:X$BH.TCH➢Updatedwhenblockread➢Updatedbynomoreth

an1every3seconds➢Canbeusedtofind“hot”blocks➢Note:setbacktozerowhenblockcyclesthroughthebuffercache#.29CBC–

RealTimeselectcount(*),lpad(replace(to_char(p1,'XXXXXXXXX'),'','0'),16,0)laddrfromv$active_session_historywhereevent='latch:

cachebufferschains'groupbyp1;selecto.name,bh.dbarfil,bh.dbablk,bh.tchfromx$bhbh,obj$owheretch>100andhladdr='00000004D81

08330'ando.obj#=bh.objorderbytchCOUNT(*)LADDR--------------------------493300000004D8108330NAMEDBARFILDBABLKTCH----------------------------EMP_

CLUSTER4394120#.30PuttingintooneQueryselectname,file#,dbablk,obj,tch,hladdrfromx$bhbh,obj$owhereo.obj#(+)=bh.objandhladd

rin(selectltrim(to_char(p1,'XXXXXXXXXX'))fromv$active_session_historywhereeventlike'latch:cache%'groupbyp1havingcou

nt(*)>5)andtch>5orderbytchNAMEFILE#DBABLKOBJTCHHLADDR-----------------------------------------BBW_INDEX111099766051176BD9118

0IDL_UB1$15483773186BDB8A80VIEW$1688563206BD91180VIEW$1688663246BDB8A80DUAL12082258326BDB8A80DUAL12081258326BD91180MGMT_EMD_PING3264795031227

26BDB8A80Thiscanbemisleading,asTCHgetssetto0everraparoundtheLRUanditonlygetsupdatedonceevery3seconds,sointhiscaseDUA

LwasmyproblemtablenotMGMT_EMD_PING#.31ConsistentReadBlocksCurrentBlock(XCUR)s1s2UpdateSelectConsistentRead(CR)Clone&UndoBothhavesamefile#andblock

#andhashtosamebucket#.32latchesCBC:ConsistentReadBlocksCacheBufferChainContention:TooManyBuffersinBuckets5s4s3s2s1HashBuc

ketsBlockHeadersMaxlength:_db_block_max_cr_dba10g=6#.33ConsistentReadCopiesselectcount(*),name,file#,dbablk,hladdrfromx$bhbh,obj$owhereo.

obj#(+)=bh.objandhladdrin(selectltrim(to_char(p1,'XXXXXXXXXX'))fromv$active_session_historywhereeventlike'latch:cache%'groupbyp1)g

roupbyname,file#,dbablk,hladdrhavingcount(*)>1orderbycount(*);CNTNAMEFILE#DBABLKHLADDR----------------------------------14MYDUAL1931702C9F

4B20#.34CBC:Solution➢FinetheSQLcausingtheproblem➢ChangeApplicationLogic➢Eliminatehotspots➢Lookuptables➢Usespl/sqlfun

ctions➢Minimizedataperblock➢Possiblyusingx$dualinsteadofdual➢IndexNestedloops➢Hashjoin➢Hashpartitionindex➢HahClu

ster➢Updates,inserts,selectforupdateonblockswhilereadingthoseblocks➢Causemultiplecopiesselectash.sql_id,count

(*),sql_textfromv$active_session_historyash,v$sqlstatssqlwhereevent='latch:cachebufferschains'andsql.sql_id(+)=ash.sql_idgroupbyash.sql_id,sq

l_text;#.35Latch:cachebufferhandles➢Bufferscanbepinned➢Possiblyincrease➢_db_handles_cached5➢Unsupported➢Usedw

henpinningblockheadersforexpectedreuse#.36FreeBufferWait➢DataBlockCachelackfreebuffers➢Tuneby➢Increasedatablocks➢Try

totuneDBWR➢ImprovingInefficientSQL➢requestinglarge#ofblocks#.37FreeBufferWaitFindingaFreeBlock➢Ifthedatablockisn’tincache➢Getafreeblo

ckandheaderinthebuffercache➢Readitoffdisk➢Updatethefreeheader➢ReadtheblockintothebuffercacheNeedFreeBlocktoReadinNewDataBlock#.38FindingaFreeBlockSh

adowProcessWhenasessionreadsablockIntothebufffercachehowdoesitfindaFREEspot?#.39FindingaFreeBlockBufferHeaders

DataBlocksHashbucketlatches1.ArrangetheBufferHeadersintoanLRUList2.ScanLRUforafreeblock#.40CacheBuffersLRU=

entryinx$bh#.41X$bh➢DescribesBufferHeadersSQL>descx$bhNameType-------------------------------ADDRRAW(4)DBAR

FILNUMBERDBABLKNUMBEROBJNUMBERHLADDRRAW(4)NXT_HASHRAW(4)PRV_HASHRAW(4)NXT_REPLRAW(4)PRV_REPLRAW(4)NXT_REPLRAW(4)PRV_REPLRAW(4)HLADD

RRAW(4)NXT_HASHRAW(4)PRV_HASHRAW(4)CachebufferchainsLRU#.42LRUChain03C3900003C38F6003C38F6003C3900003C390000

3C38F6003C39478ADDRNXT_HASHPRV_HASH03C3947803C3851403C3863803C3862003C385F403C38554NXT_REPLPRV_REPL#.4

3CacheBuffersLRUlist#.44CacheBuffersLRUlistLRUChainofBufferHeadersBufferCache#.45CacheBuffersLRULatchMRULRUBufferHe

aders“Cold”LRU=LeastRecentlyUsedMRU=MostRecentlyUsedOneLRULatchprotectsthelinkedlistduringchangestotheli

st“Hot”LRUlatch#.46SessionSearchingforFreeBlocksMRULRUBufferHeadersSessionShadow1.GototheLRUendofdatablocks2.Lookforfir

stnon-dirtyblock3.IfsearchtoomanypostDBWRtomakefree4.FreeBufferwait#.47FreeBufferWaitSolutions➢Tuneby➢Increasedatablocks➢TrytotuneD

BWR➢ASYNC➢IfnoASYNCuseI/OSlaves(dbwr_io_slaves)➢MultipleDBWR(db_writer_processes)➢DirectI/O➢TuneIneffici

entSQL➢requestinglarge#ofblocks#.48SessionFindingaFreeBlockMRULRUHotEndMid-PointInsertionGetLRULatchFindFreeBlockInsertHeaderRelea

seLRULatchsessionLRULatch#.49DBWRtakingDirtyBlocksoffMRULRUBufferHeadersLRUDBWRDirtyListofBufferHeadersLRUWlatchLRUlatchalsocoversDBWRlistofd

irtyblocs#.50CacheBuffersLRULatchMRULRUMid-PointInsertionOracleTracksthetouchcountofblocks.Astheblockispush

edtotheLRUend,ifit’stouchcountis3ormore,it’spromotedtotheMRUend#.51Solution:MultipleSets_db_block_lru_latches=810gR2withc

pu_count=2X$KCBWDS–setdescriptorSet1Set2LRULatch1LRULatch2#.52WorkingSetsselectds.set_id,ds.blk_size,bp.BUFFERS,nvl(bp.name.’un

used’)fromx$kcbwdsds,v$buffer_poolbpwhereds.set_id>=bp.lo_setid(+)andds.set_id<=bp.hi_setid(+)/SET_IDBLK_SIZ

EBUFFERSNAME-----------------------------------1632768153276814163841316384128192118192104096940968204872048681924972DEFAULT581924972DE

FAULT48192381922819218192#.53TestCase➢8Sessions➢readingseparatetables➢Tablesweretoobigtoholdincache➢cacheoptionseton

eachtable➢Result:lotsofbuffercachechurn➢Expectedtoget“latch:cachebufferchainsLRU”#.54simulatorlrulatch#.55CBC–FurtherInvestigationselectp2,count(*)f

romv$active_session_historywhereevent='latchfree'groupbyp2select*fromv$latchnamewherelatch#=127P2COUNT(*)--------------------1273556LATCH#NAME----

--------------------127simulatorlrulatchselect*fromv$event_namewherename='latchfree'PARAMETER1PARAMETER2PARAMETER3-----------------

-------------addressnumbertries#.56db_cache_adviceAltersystemsetdb_cache_advice=off;Group“other”isverysmallcomparedtoI/Owaittime–notaproblem#.57

CacheBuffersLRULatch:SolutionOther➢IncreaseSizeofBufferCache➢Usingmultiplecachebuffers➢Keep,recycle➢Possiblyincrease_db_block_lru_latches➢Notsupporte

d#.58BufferBusyWaits➢User1triestochangeabufferheader➢User2hasbufferheader“locked”(pinned)1203User1User2#.59BBWSolutionPaths1.FindBlocktype➢Resolv

eifpossible2.TuneSQL➢FindSQL➢Howoftenisitcalled➢ByhowmanyUsers3.EliminateHotBlock➢FindObject➢FindBlockTypeBl

ockTypes:➢UndoHeader➢useAUM(oraddmoreRBS)➢UndoBlock–hotspotinUNDO➢Data➢index–hotspot,partition➢table–freelists,ASSM,partition➢Se

gmentheader–freelists➢tabledatablock->freelists➢Freelistblocks–freelistsgroups➢FileHeaderBlock–lookatextentallocationThereisahotblock,eliminateth

ehotblock#.60BBW:StatspackTop5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time--------

---------------------------------------bufferbusywaits5,8322634528.2logfileparallelwrite24812550513.4readbyothersession90210311411.1

dbfileparallelwrite2,166944310.1dbfilesequentialread653811258.7ClassWaitsWaitTime(s)AvgTime(ms)----------------------------

---------------------fileheaderblock264203769datablock6,07016227undoheader35501segmentheader4401failstofindObject#.61BBW:ASHFinds➢Object

➢BlockType➢SQLStatementCNTOBJOTYPESQL_IDBLOCK_TYPETBS-------------------------------------------------

------------2BBW_INDEX_VAL_IINDEX635xhydd6fzggsegmentheaderSYSTEM20635xhydd6fzggusn5headerUNDOTBS1301hsb81ypyrfs5fileheaderblockUND

OTBS132BBW_INDEX_VAL_IINDEX1hsb81ypyrfs5datablockSYSTEM33BBW_INDEX_VAL_IINDEX6avm49ys4k7t6datablockSYSTEM34BBW_INDEX_VAL_IINDEX5

wqps1quuxqr4datablockSYSTEM#.62BBW:OEM#.63Solutions#.64BBWBlockTypesselectrownumn,ws.classfromv$waitstat;NAMEP1P2P3----------------------------

-----bufferbusywaitsfile#block#class#select*fromv$event_namewherename='bufferbusywaits'NCLASS---------------------1

datablock2sortblock3saveundoblock4segmentheader5saveundoheader6freelist7extentmap81stlevelbmb92ndlevelbmb103rdlevelbmb11bitmapblock12bitmap

indexblock13fileheaderblock14unused15systemundoheader16systemundoblock17undoheader18undoblockNote:Before10g,P3wasBBWty

peIfP3in100,110,120,130thenreadNow“readbyothersession”ElseWrite,P3in200,210,220,230,231#.65JoiningASHwithv$waitstatselecto.object_nameobj,o.object_

typeotype,ash.SQL_ID,w.classfromv$active_session_historyash,(selectrownumclass#,classfromv$waitstat)w,all_objectsowhereevent='bufferbus

ywaits'andw.class#(+)=ash.p3ando.object_id(+)=ash.CURRENT_OBJ#Orderbysample_time;OBJOTYPESQL_IDCLASS--------------------------------------

-----TOTO1TABLE8gz51m9hg5yufdatablockTOTO1TABLE8gz51m9hg5yufdatablockTOTO1TABLE8gz51m9hg5yufsegmentheaderTOTO1TABLE8gz51m9hg5y

ufdatablock#.66AlternativetoASH:AWRselectto_char(BEGIN_INTERVAL_TIME,'DD-MONHH:MI'),o.name,s.BUFFER_BUSY_WAITS_DELTAfromdba_hist_seg_stats,dba

_hist_snapshotsn,obj$owhereBUFFER_BUSY_WAITS_DELTA>100andsn.snap_id=s.snap_idando.obj#=s.obj#;TO_CHAR(BEGINAMEBUFFER_BUSY_WAITS

_DELTA---------------------------------------11-JAN10:21TOTO158447#.67Example:BBWwithInsert➢Concurrentinsertswillinsertintot

hesameblock➢Eachsessionhastowaitfortheprevioussessiontofinishit’swrite➢Usuallyprettyfast➢Contentionbuil

dsonhighlyconcurrentapplications➢LackofFreeLists➢NotUsingASSM(AutomaticSegmentSpaceManagement)#.68Example:LackofFreeListS1S2S3S44Se

ssionsrunningInsertintototovalues(null,‘a’);Commit;OBJNOTYPEFILENBLOCKNSQL_IDBLOCK_TYPE---------------------------------------------

---------54962TOTO1TABLE16450128gz51m9hg5yufdatablock54962TOTO1TABLE161618gz51m9hg5yufsegmentheader#

.69Solution1:FreeListsS1S2S3S44SessionsrunningInsertintototovalues(null,‘a’);Commit;#.70Solution2:ASSM➢MultipleBitmapBlocksTrack

FreeSpace➢Unformatted➢Upto25%Free➢Upto50%Free➢Upto75%Free➢Full➢FreeblockchosenbyProcessID➢Possiblyinstance#forRAC#.71Solution2

:ASSMHeaderLevel2Level1Level1Level1DataBlocksBitmapBlocks#.72TablespaceTypes:ASSMselecttablespace_name,extent_managementLOCAL,alloc

ation_typeEXTENTS,segment_space_managementASSM,initial_extentfromdba_tablespacesTABLESPACE_NAMELOCALEXTENTSASSM--------------------------

--------------SYSTEMLOCALSYSTEMMANUALUNDOTBS1LOCALSYSTEMMANUALSYSAUXLOCALSYSTEMAUTOTEMPLOCALUNIFORMMANUALUSERSLOCA

LSYSTEMAUTOEXAMPLELOCALSYSTEMAUTODATALOCALSYSTEMMANUALcreatetablespacedata2datafile'/d3/kyle/data2_01.db

f'size200Msegmentspacemanagementauto;#.73BBW:ASSM➢ConsiderusingFreelistsinsteadofASSM➢NormallywaitsonASSMblocksshouldbetoosmalltowarrantusingF

reelists➢ASSMiseasier,automaticallymanaged1stlevelbmb2ndlevelbmb3rdlevelbmb#.74BBWonIndexIndexSession1Session2Session3Incr

easingindexkeycreatesahotspotontheleadingindexleafOBJNOTYPEFILENBLOCKNSQL_IDBLOCK_TYPE-------------------------

--------------------------BBW_INDEX_INDEX111359997dgthz60u28ddatablock1➢UseReverseKeyindexes➢BreaksIndexscans➢HashPar

titionIndex➢MoreIOsperindexaccess#.75BBWonIndex:ADDMRecsAlsoconsider“reversing”thekey#.76Example:BBWonRBS➢IFBBWhappenonoldstyl

eRBS➢Class#>18➢SwitchtoUNDO➢OldstyleRBS,theDBAhadtofigureout#ofRBSSegments➢WithUNDO,itisautomaticallymanagedaltersystemsetundo_

management=autoscope=spfile;#.77BBWandRBSSegsOBJNOTYPEFILENBLOCKNSQL_IDBLOCK_TYPE------------------------------------------------

------54962TOTO1TABLE16450128gz51m9hg5yufdatablock54962TOTO1TABLE161618gz51m9hg5yufsegmentheader01498gz51m

9hg5yuf8701498gz51m9hg5yuf87SelectCURRENT_OBJ#||''||o.object_nameobjn,o.object_typeotype,CURRENT_FILE#

filen,CURRENT_BLOCK#blockn,ash.SQL_ID,w.class||''||to_char(ash.p3)block_typefromv$active_session_historyash,(selectrown

umclass#,classfromv$waitstat)w,all_objectsowhereevent='bufferbusywaits'andw.class#(+)=ash.p3ando.object_id(+

)=ash.CURRENT_OBJ#Orderbysample_time;#.78FurtherInvestigationRBS➢OldStyleRBSifClass#>18P1P2P3SQL_IDCOUNT(*)CLASS---------------------------------

----------------1498772wa5hjpzr0by11498772wa5hjpzr0by1149873gkmtvxzu6p2m1149873gkmtvxzu6p2m1656132517zx1krfcgn88t8datablock149878s29zyzr

55z2t1selectsegment_name,segment_typefromdba_extentswherefile_id=P1andP2betweenblock_idandblock_id+b

locks–1;SEGMENT_NAMESEGMENT_TYPE----------------------------R2ROLLBACK#.79ADDMfindsoldstyleRBS#.80BBW:FileHeader➢QueryingASH,makes

ure➢P1=current_file#➢P2=current_block#➢Ifnot,usep1,p2andnotcurrent_object#TimeP1P2OBJNOTYPEFNBLOCKNBLOCK_TY

PE---------------------------------------------11:442022-100fileheaderblock11:442022TOTOTABLE160218fileheaderblock11:442022TOTOTABLE1602

18fileheaderblock11:442022TOTOTABLE160218fileheaderblock11:442022TOTOTABLE160218fileheaderblockSELECTA.OBJECT_IDFROMAL

L_OBJECTSA,(SELECT*FROMALL_OBJECTSWHEREROWNUM<1000)BORDERBYA.OBJECT_NAME#.81BBW:FileHeaderTimeP1P2OBJNOTYPEFNBLOCKNBLOCK_TYPE--------

-------------------------------------11:442022TOTOTABLE160218fileheaderblockSolutionismakeinitialandnextextentlargerinTempTable

SpaceADDMdoesn’tsaymuch#.82writecompletewaits➢Usuallyhappensintandemwithfreebuffer➢Tuneby➢Increasedatablockcache➢

HappensbecauseshadowwantstoaccessblocksthatarecurrentlybeingwrittentodiskbyDBWR➢alsoseenithappenwhenthereisalotofwriteto

sortthewaitsareonblock2ofthetemptablespacefile#.83WriteCompleteWaitsLRUDBWRDirtyListofBufferHeadersLRUWSession#.

84BufferExterminate➢Buffercachedynamicallyresized➢V$SGA_DYNAMIC_COMPONENTSdisplaysinformationaboutthedynam

icSGAcomponents.ThisviewsummarizesinformationbasedonallcompletedSGAresizeoperationssinceinstancestartup.➢V$SGA_CUR

RENT_RESIZE_OPSdisplaysinformationaboutSGAresizeoperationswhicharecurrentlyinprogress.Anoperationcanbeagroworashrinko

fadynamicSGAcomponent.➢V$SGA_DYNAMIC_FREE_MEMORYdisplaysinformationabouttheamountofSGAmemoryavailableforfuturedynam

icSGAresizeoperations.Altersystemsetdb_cache_size=50M;#.85SummaryBufferCacheWaits1.latch:cachebufferschains-find

SQL➢Eliminatehotspots2.latch:cachebufferslruchain–increasesets3.FreeBufferWait-increasecachesize4.BufferBusyWait➢Index:alleviatehotspots,pa

rtition➢DataDML:addfreelistsoruseASSM➢FileSegmentHeader:lookedathighextentallocations5.WriteCompleteWaits-increasecache

size