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

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

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

BufferCacheWaits#.2InThisSection1.latch:cachebufferschains2.latch:cachebufferslruchain3.latch:cachebufferhandles4.FreeBufferWait5.BufferBusyWait6.

WriteCompleteWait7.BufferExterminate#.3BufferCacheRedoLibCacheBufferCacheIOLocksNetwork#.4REDOLogFilesDataFilesDBWRLGWRUser2User1User3LogBufferBu

fferCacheLogBufferBufferCacheSGALibraryCacheOracleMemoryStructures#.5BufferCacheAccess➢BufferCacheManagement➢

LocatingFreeblocks➢Findingdatablocks➢ManagingLRUlists➢CleaningDirtyBlocks➢BufferCachemanagementcancausecontention➢DifferentfromIO(rea

dingblocksofdisk)#.6Query0.Parsestatement1.Findobjectinformationindatadictionary2.Calculateexecutionplan3.Iffulltablescan➢Look

atallblocksoftable4.Ifindexfindrootofindexandfollowtokey5.DataDictionarywillhaveinfoabouttableorindexblock➢File#➢Block#6.OnceyouknowtheblockDB

A(file#+block#)…Selectenamefromempwhereempno=12;#.7IsBlockincache?Nowyouhaveafile#andblock#Howdoyouknowifablockiscached?ShadowProcess?Doyousearch

alltheblocks?Couldbe1000sofblockstosearch.BuffercachesareinthemultiGig#.8BufferCacheFindablockby:1)HashofDatafile#Block#2)Res

ult=Bucket#3)Searchlinkedlistforthatbucket#WhatisahashvalueWhatareBucketsWhatisthelinkedlist?#.9ConceptsTounderstandconte

ntiononthebuffercache,needtounderstand:1.LinkedLists2.Hashing3.Buckets#.10DoubleLinkedLists03C3900003C3947803C38F60

03C3900003C3900003C38F6003C39478AddressNextPrevious#.11HashingFunction➢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#’sResultinabuc

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

LatchesProtectBucketContentsBufferHeadersDataBlocksHashbucketlatchesBufferHeaderscontentsdescribedbyX$BH#.14X$bh➢DescribesCont

entsofBufferHeadersSQL>descx$bhNameType--------------------ADDRRAW(4)DBARFILNUMBERDBABLKNUMBEROBJNUMBERH

LADDRRAW(4)NXT_HASHRAW(4)PRV_HASHRAW(4)…muchmoreADDRDBARFILDBABLKOBJHLADDRNXT_HASHPRV_HASH…AeachbufferheadercontainsInformationaboutthedatabloc

kItpointstoandthepreviousandnextBufferheaderinalinkedlist#.15Cache03C3900003C3947803C38F6003C3900003C3900003

C38F6003C39478ADDRNXT_HASHPRV_HASH#.16X$BHdescribesHeadersBufferHeadersDataBlocksHashbucketlatchesHLADDRNXT_HAS

HPRV_HASHADDRADDRDBARFILDBABLKOBJx$bhADDRDBARFILDBABLKOBJHLADDRNXT_HASHPRV_HASH#.17ToFindaBlock1.Hashtheblockaddress2.GetBucketlatch3.

Lookforheader4.Found,readblockincache5.NotFoundReadblockoffdiskShadowProcessBufferHeadersDataBlocksHashbucket2354latches1

2345#.18CacheBuffersChainsHashBucketss5s4s3s2s1SessionsContentioniftoomanyaccessesonabucketlatchesBlock

HeadersCacheBufferChainDataBlocks#.19ExamplesS1S2S3S41.LookupTable2.NestedLoopsSelectt1.val,t2.valfromt1,t2wheret1.c1={value}andt2.id=t1.id;t1I

ndex_t2t2#.20CBCSolutionsFindSQL(Whyisapplicationhittingtheblocksohard?)➢Nestedloops,possibly➢HashPartition➢UsesHashJoin➢Hashclusters➢Lookuptables

(“selectlanguagefromlang_tablewhere...”)➢Changeapplication➢Useplsqlfunction➢Spreaddataouttoreducecontention➢Selectfromdual➢Po

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

------------------------------------------------latchfree21,4281,91481.37CPUtime36015.29PL/SQLlocktimer16482.04SQL*Netmessagefromdblink4,69014.58dbfi

lesequentialread1,4275.19Top5TimedEvents~~~~~~~~~~~~~~~~~~%TotalEventWaitsTime(s)ElaTime-----------------------------------------------------------la

tchfree21,4281,91481.37CPUtime36015.29PL/SQLlocktimer16482.04SQL*Netmessagefromdblink4,69014.58dbfilesequentialread1,4275

.19LatchSleepbreakdownforDB:CDBInstance:cdbSnaps:1-2->orderedbymissesdescLatchNameRequestsMissesSleepsSleeps1->4------------

-------------------------------------------cachebufferschains12,123,500608,41515,7590/0/0/0/0librarycachepin12,027,599173,4462,862172694/743/

8/1/0librarycache12,072,50398,0652,37397739/279/47/0/0simulatorlrulatch6064364346/426/4/0/0FailstofindSQL#.22CBC:Statspack10gTop5TimedEventsAvg%Total

~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time---------------------------------------------------------CPUtime3

554.3latch:cachebufferschains461124317.6latch:librarycachepin35822912.6latch:librarycache2762319.8logfilesequentialread151601.4To

p5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time--------------------------------------------

-------------CPUtime3554.3latch:cachebufferschains461124317.6latch:librarycachepin35822912.6latch:librarycache2762319.8logfilesequenti

alread151601.4FailstofindSQL#.23CBC:ASHselectcount(*),sql_id,nvl(o.object_name,ash.current_obj#)objn,su

bstr(o.object_type,0,10)otype,CURRENT_FILE#fn,CURRENT_BLOCK#blocknfromv$active_session_historyash,all_objectsowhe

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

rrent_block#,o.object_name,o.object_typeorderbycount(*)/CNTSQL_IDOBJNOTYPEFNBLOCKN----------------------------------------84a09r4dwjpv01qMYDUA

LTABLE193170SQLStatement:SuccessExtra:Hotblock#.24CBC:OEM#.25CBC:ADDMProblemSQLStatementSolution?#.26CBC–FurtherInvestigationsele

ct*fromv$event_namewherename='latch:cachebufferschains'EVENT#NAME--------------------------------------58latch:cachebufferschainsPARAME

TER1PARAMETER2PARAMETER3------------------------------addressnumbertriesNOTE:_db_block_hash_buckets=#

ofhashbuckets_db_blocks_per_hash_latch=#ofhashlatches#.27CBC:what’sthehotblock➢CangetitfromASH➢Current_file#➢Current_block#➢Wh

ereevent=‘latch:cachebufferschains”➢Sometimesfileandblock=0➢SeemstohappenforNestedLoops➢Getthehotblockrealtime➢UseHas

hLatchAddress➢Ash.p2=x$bh.hladdr#.28HotBlock:X$BH.TCH➢Updatedwhenblockread➢Updatedbynomorethan1every3seconds➢Canbeusedtofind“hot”blocks➢Note:setb

acktozerowhenblockcyclesthroughthebuffercache#.29CBC–RealTimeselectcount(*),lpad(replace(to_char(p1,'X

XXXXXXXX'),'','0'),16,0)laddrfromv$active_session_historywhereevent='latch:cachebufferschains'groupbyp

1;selecto.name,bh.dbarfil,bh.dbablk,bh.tchfromx$bhbh,obj$owheretch>100andhladdr='00000004D8108330'ando.

obj#=bh.objorderbytchCOUNT(*)LADDR--------------------------493300000004D8108330NAMEDBARFILDBABLKTCH----------------------------EMP_CLUSTER4394120#

.30PuttingintooneQueryselectname,file#,dbablk,obj,tch,hladdrfromx$bhbh,obj$owhereo.obj#(+)=bh.objandhladdrin(selectltrim(to_char(p1,'XXXXXXX

XXX'))fromv$active_session_historywhereeventlike'latch:cache%'groupbyp1havingcount(*)>5)andtch>5orderbytchNAMEFILE#DBABLKOBJTCHHLADDR------

-----------------------------------BBW_INDEX111099766051176BD91180IDL_UB1$15483773186BDB8A80VIEW$1688563206BD91180VIEW$1688663246

BDB8A80DUAL12082258326BDB8A80DUAL12081258326BD91180MGMT_EMD_PING326479503122726BDB8A80Thiscanbemisleadi

ng,asTCHgetssetto0everraparoundtheLRUanditonlygetsupdatedonceevery3seconds,sointhiscaseDUALwasmyproblemtablenotMGMT_EMD_PING#.31Consiste

ntReadBlocksCurrentBlock(XCUR)s1s2UpdateSelectConsistentRead(CR)Clone&UndoBothhavesamefile#andblock#andhashtosamebucket#.32latchesCBC:Con

sistentReadBlocksCacheBufferChainContention:TooManyBuffersinBuckets5s4s3s2s1HashBucketsBlockHeadersM

axlength:_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)groupbyname,fi

le#,dbablk,hladdrhavingcount(*)>1orderbycount(*);CNTNAMEFILE#DBABLKHLADDR----------------------------------14MYDUAL1931702C

9F4B20#.34CBC:Solution➢FinetheSQLcausingtheproblem➢ChangeApplicationLogic➢Eliminatehotspots➢Lookuptables➢Usespl/sqlfunctions➢Minimizedata

perblock➢Possiblyusingx$dualinsteadofdual➢IndexNestedloops➢Hashjoin➢Hashpartitionindex➢HahCluster➢Updates,inserts,selectforu

pdateonblockswhilereadingthoseblocks➢Causemultiplecopiesselectash.sql_id,count(*),sql_textfromv$active_session_historyash,v$sqlstatssqlwher

eevent='latch:cachebufferschains'andsql.sql_id(+)=ash.sql_idgroupbyash.sql_id,sql_text;#.35Latch:cachebuffer

handles➢Bufferscanbepinned➢Possiblyincrease➢_db_handles_cached5➢Unsupported➢Usedwhenpinningblockheadersforexpected

reuse#.36FreeBufferWait➢DataBlockCachelackfreebuffers➢Tuneby➢Increasedatablocks➢TrytotuneDBWR➢ImprovingInefficientSQL➢requestinglarge#ofb

locks#.37FreeBufferWaitFindingaFreeBlock➢Ifthedatablockisn’tincache➢Getafreeblockandheaderinthebuffercache➢Readitoffdisk➢Updatethefre

eheader➢ReadtheblockintothebuffercacheNeedFreeBlocktoReadinNewDataBlock#.38FindingaFreeBlockShadowProcessWhenasessionr

eadsablockIntothebufffercachehowdoesitfindaFREEspot?#.39FindingaFreeBlockBufferHeadersDataBlocksHashbucket

latches1.ArrangetheBufferHeadersintoanLRUList2.ScanLRUforafreeblock#.40CacheBuffersLRU=entryinx$bh#.41X$bh➢DescribesBufferHe

adersSQL>descx$bhNameType-------------------------------ADDRRAW(4)DBARFILNUMBERDBABLKNUMBEROBJNUMBERHLADDRRAW(4)NXT

_HASHRAW(4)PRV_HASHRAW(4)NXT_REPLRAW(4)PRV_REPLRAW(4)NXT_REPLRAW(4)PRV_REPLRAW(4)HLADDRRAW(4)NXT_HASHRAW(4)P

RV_HASHRAW(4)CachebufferchainsLRU#.42LRUChain03C3900003C38F6003C38F6003C3900003C3900003C38F6003C39478ADDRNXT_HASHPRV_HASH03C

3947803C3851403C3863803C3862003C385F403C38554NXT_REPLPRV_REPL#.43CacheBuffersLRUlist#.44CacheBuffersLRUlistLRUChainofBufferHeadersB

ufferCache#.45CacheBuffersLRULatchMRULRUBufferHeaders“Cold”LRU=LeastRecentlyUsedMRU=MostRecentlyUsedOneLRULatchprotect

sthelinkedlistduringchangestothelist“Hot”LRUlatch#.46SessionSearchingforFreeBlocksMRULRUBufferHeadersSessionShadow1.Go

totheLRUendofdatablocks2.Lookforfirstnon-dirtyblock3.IfsearchtoomanypostDBWRtomakefree4.FreeBufferwait#.47Free

BufferWaitSolutions➢Tuneby➢Increasedatablocks➢TrytotuneDBWR➢ASYNC➢IfnoASYNCuseI/OSlaves(dbwr_io_slaves

)➢MultipleDBWR(db_writer_processes)➢DirectI/O➢TuneInefficientSQL➢requestinglarge#ofblocks#.48SessionFindingaFreeBlockMRULRUHotEndMid-PointInsert

ionGetLRULatchFindFreeBlockInsertHeaderReleaseLRULatchsessionLRULatch#.49DBWRtakingDirtyBlocksoffMRULRUBufferHeadersLRUDBWRDirtyListofBufferHe

adersLRUWlatchLRUlatchalsocoversDBWRlistofdirtyblocs#.50CacheBuffersLRULatchMRULRUMid-PointInsertionOracleTracksthetouchcountofblocks.Astheblockispus

hedtotheLRUend,ifit’stouchcountis3ormore,it’spromotedtotheMRUend#.51Solution:MultipleSets_db_block_lru_latches=810gR2wi

thcpu_count=2X$KCBWDS–setdescriptorSet1Set2LRULatch1LRULatch2#.52WorkingSetsselectds.set_id,ds.blk_size,bp.BUFFERS,nvl(bp.

name.’unused’)fromx$kcbwdsds,v$buffer_poolbpwhereds.set_id>=bp.lo_setid(+)andds.set_id<=bp.hi_setid(+)/SET_IDBLK_SIZEBUFFERSNAME-------

----------------------------1632768153276814163841316384128192118192104096940968204872048681924972DEFAULT581924972DEFAULT48192381

922819218192#.53TestCase➢8Sessions➢readingseparatetables➢Tablesweretoobigtoholdincache➢cacheoptionsetoneachtable➢

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'PARAMETER1PARAMETER2PARAMET

ER3------------------------------addressnumbertries#.56db_cache_adviceAltersystemsetdb_cache_advice=off;Group“other”isverysmal

lcomparedtoI/Owaittime–notaproblem#.57CacheBuffersLRULatch:SolutionOther➢IncreaseSizeofBufferCache➢Usingmultiplecachebuffe

rs➢Keep,recycle➢Possiblyincrease_db_block_lru_latches➢Notsupported#.58BufferBusyWaits➢User1triestochangeabuffe

rheader➢User2hasbufferheader“locked”(pinned)1203User1User2#.59BBWSolutionPaths1.FindBlocktype➢Resolveif

possible2.TuneSQL➢FindSQL➢Howoftenisitcalled➢ByhowmanyUsers3.EliminateHotBlock➢FindObject➢FindBlockTypeBlockTypes:➢UndoHeade

r➢useAUM(oraddmoreRBS)➢UndoBlock–hotspotinUNDO➢Data➢index–hotspot,partition➢table–freelists,ASSM,partition➢Segmentheader–freelists➢tabledatablock->f

reelists➢Freelistblocks–freelistsgroups➢FileHeaderBlock–lookatextentallocationThereisahotblock,eliminatethehotblock#.60BBW:StatspackTop

5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time-------------------------------

----------------bufferbusywaits5,8322634528.2logfileparallelwrite24812550513.4readbyothersession902103

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

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

pe➢SQLStatementCNTOBJOTYPESQL_IDBLOCK_TYPETBS-------------------------------------------------------------2

BBW_INDEX_VAL_IINDEX635xhydd6fzggsegmentheaderSYSTEM20635xhydd6fzggusn5headerUNDOTBS1301hsb81ypyrfs5fileheaderblockUNDOTBS132B

BW_INDEX_VAL_IINDEX1hsb81ypyrfs5datablockSYSTEM33BBW_INDEX_VAL_IINDEX6avm49ys4k7t6datablockSYSTEM34BBW_INDEX_VAL_IINDEX5wqps1quuxqr4da

tablockSYSTEM#.62BBW:OEM#.63Solutions#.64BBWBlockTypesselectrownumn,ws.classfromv$waitstat;NAMEP1P2P

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

---------1datablock2sortblock3saveundoblock4segmentheader5saveundoheader6freelist7extentmap81stlevelbmb92ndlevelbmb103rdlevelbmb11bitmapblock12bitma

pindexblock13fileheaderblock14unused15systemundoheader16systemundoblock17undoheader18undoblockNote:Befo

re10g,P3wasBBWtypeIfP3in100,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,(selec

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

J#Orderbysample_time;OBJOTYPESQL_IDCLASS-------------------------------------------TOTO1TABLE8gz51m9hg5

yufdatablockTOTO1TABLE8gz51m9hg5yufdatablockTOTO1TABLE8gz51m9hg5yufsegmentheaderTOTO1TABLE8gz51m9hg5yufdatablock#.66AlternativetoASH:AWRsele

ctto_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➢Concurrentinsertswillinsertintothe

sameblock➢Eachsessionhastowaitfortheprevioussessiontofinishit’swrite➢Usuallyprettyfast➢Contentionbuildsonhighlyconcurre

ntapplications➢LackofFreeLists➢NotUsingASSM(AutomaticSegmentSpaceManagement)#.68Example:LackofFreeListS1S2S3S44SessionsrunningIn

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

----------------54962TOTO1TABLE16450128gz51m9hg5yufdatablock54962TOTO1TABLE161618gz51m9hg5yufsegmentheade

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

tted➢Upto25%Free➢Upto50%Free➢Upto75%Free➢Full➢FreeblockchosenbyProcessID➢Possiblyinstance#forRAC#.71Solution2:

ASSMHeaderLevel2Level1Level1Level1DataBlocksBitmapBlocks#.72TablespaceTypes:ASSMselecttablespace_name,extent_man

agementLOCAL,allocation_typeEXTENTS,segment_space_managementASSM,initial_extentfromdba_tablespacesTABLESPACE_NAMELOCALEXTENTSASSM--------------------

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

LSYSTEMAUTOEXAMPLELOCALSYSTEMAUTODATALOCALSYSTEMMANUALcreatetablespacedata2datafile'/d3/kyle/data2_01.dbf'size200Msegmentspacemanagementauto;

#.73BBW:ASSM➢ConsiderusingFreelistsinsteadofASSM➢NormallywaitsonASSMblocksshouldbetoosmalltowarrantusingFreelists➢AS

SMiseasier,automaticallymanaged1stlevelbmb2ndlevelbmb3rdlevelbmb#.74BBWonIndexIndexSession1Session2Session3Increasin

gindexkeycreatesahotspotontheleadingindexleafOBJNOTYPEFILENBLOCKNSQL_IDBLOCK_TYPE-------------------------

--------------------------BBW_INDEX_INDEX111359997dgthz60u28ddatablock1➢UseReverseKeyindexes➢BreaksIndexscans➢HashPartitionIndex➢MoreIOs

perindexaccess#.75BBWonIndex:ADDMRecsAlsoconsider“reversing”thekey#.76Example:BBWonRBS➢IFBBWhappenonold

styleRBS➢Class#>18➢SwitchtoUNDO➢OldstyleRBS,theDBAhadtofigureout#ofRBSSegments➢WithUNDO,itisautomaticallymanagedaltersystemsetund

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

---------------54962TOTO1TABLE16450128gz51m9hg5yufdatablock54962TOTO1TABLE161618gz51m9hg5yufsegmentheader01498gz51m9hg5yuf8701498gz51m9hg5yuf87

SelectCURRENT_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,(selectrownumclass#,classfromv

$waitstat)w,all_objectsowhereevent='bufferbusywaits'andw.class#(+)=ash.p3ando.object_id(+)=ash.CURRENT_OBJ#Orderbysample_time;#.78FurtherInvestigati

onRBS➢OldStyleRBSifClass#>18P1P2P3SQL_IDCOUNT(*)CLASS-------------------------------------------------1498772wa5hjpzr0by114

98772wa5hjpzr0by1149873gkmtvxzu6p2m1149873gkmtvxzu6p2m1656132517zx1krfcgn88t8datablock149878s29zyzr55z2t1selectsegment_nam

e,segment_typefromdba_extentswherefile_id=P1andP2betweenblock_idandblock_id+blocks–1;SEGMENT_NAMESEGMENT_TY

PE----------------------------R2ROLLBACK#.79ADDMfindsoldstyleRBS#.80BBW:FileHeader➢QueryingASH,makesure➢P1=cu

rrent_file#➢P2=current_block#➢Ifnot,usep1,p2andnotcurrent_object#TimeP1P2OBJNOTYPEFNBLOCKNBLOCK_TYPE----------------------------------------

-----11:442022-100fileheaderblock11:442022TOTOTABLE160218fileheaderblock11:442022TOTOTABLE160218fileheaderblock11:44202

2TOTOTABLE160218fileheaderblock11:442022TOTOTABLE160218fileheaderblockSELECTA.OBJECT_IDFROMALL_OBJECTSA,(SELECT*FROMALL_OBJECTSWH

EREROWNUM<1000)BORDERBYA.OBJECT_NAME#.81BBW:FileHeaderTimeP1P2OBJNOTYPEFNBLOCKNBLOCK_TYPE------------------------------------------

---11:442022TOTOTABLE160218fileheaderblockSolutionismakeinitialandnextextentlargerinTempTableSpaceADDMdo

esn’tsaymuch#.82writecompletewaits➢Usuallyhappensintandemwithfreebuffer➢Tuneby➢Increasedatablockcache➢Happensbecauseshadowwant

stoaccessblocksthatarecurrentlybeingwrittentodiskbyDBWR➢alsoseenithappenwhenthereisalotofwritetosortt

hewaitsareonblock2ofthetemptablespacefile#.83WriteCompleteWaitsLRUDBWRDirtyListofBufferHeadersLRUWSession#.84BufferEx

terminate➢Buffercachedynamicallyresized➢V$SGA_DYNAMIC_COMPONENTSdisplaysinformationaboutthedynamicSGAcompone

nts.ThisviewsummarizesinformationbasedonallcompletedSGAresizeoperationssinceinstancestartup.➢V$SGA_CURRENT_RESIZE_OPSdisplaysinformationaboutSGAres

izeoperationswhicharecurrentlyinprogress.AnoperationcanbeagroworashrinkofadynamicSGAcomponent.➢V$SGA_DYNAMIC_FREE_

MEMORYdisplaysinformationabouttheamountofSGAmemoryavailableforfuturedynamicSGAresizeoperations.Altersyste

msetdb_cache_size=50M;#.85SummaryBufferCacheWaits1.latch:cachebufferschains-findSQL➢Eliminatehotspots2.latch:cachebufferslruchain–incre

asesets3.FreeBufferWait-increasecachesize4.BufferBusyWait➢Index:alleviatehotspots,partition➢DataDML:addfreelistsoruseASSM➢FileSegmentHeader:

lookedathighextentallocations5.WriteCompleteWaits-increasecachesize