les-09-调整数据缓冲区(-44-).ppt

TuningtheBufferCache

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribethebuffercachearchitectureSizethebuffercacheResolvecommonperformanceissuesrelatedtothebuffercacheUsecommondiagnosticindicatorstosuggestapossiblesolution

SpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

sizeShared

poolLarge

poolJava

poolStreams

poolDefault

buffer

cacheKeep

buffer

cacheRecycle

buffer

cachenK

buffer

cachesRedo

log

bufferASH

bufferSort

extent

poolGlobal

context

poolSGAFlash

back

bufferInstanceFlashback

logsRedolog

filesArchive

logfilesControl

filesSYSTEMSYSAUXChange

tracking

filePassword

file

BufferCache:HighlightsScalablearchitecture:MultiversionconcurrencycontrolProprietaryLRU-basedreplacementpolicyCachefusionIncrementalcheckpointingmechanismAdvisorsPrivatepoolforI/Ointensiveoperations

DatabaseBuffersSGADBbuffercacheDBWnServer

BufferHashTableForLookupsHash

buckets

tableCache

buffer

chains

latchesBuffer

hash

chainsCRclonesBufferheadersABCCEFBuffer

cacheCCEABF

WorkingSetsDefaultKeepRecyclenKBuffer

cacheCABACWorkingset1Workingset2Auto-tuned…DFEDFBDEFBuffer

cacheLRUCKPTsLRUCKPTsCA

Notesonlypage

TuningGoalsandTechniquesItisrecommendedtouseAutomaticSharedMemoryManagement(ASMM).Tuninggoals:ServersfinddatainmemoryNowaitsonthebuffercacheDiagnosticmeasures:WaiteventsTheV$DB_CACHE_ADVICEviewandcachehitratioTuningtechniques:ReviewADDMrecommendationsifASMMisused.ReducethenumberofblocksrequiredbySQLstatements.Increasebuffercachesize,usemultiplebufferpools.

Notesonlypage

SymptomsThesymptomsthatindicateabuffercacheproblem:Latch:cachebufferchainsLatch:cachebufferLRUchainsBufferbusywaitsReadwaitsFreebufferwaitsCachehitratio

CacheBufferChainsLatchContentionContentionforthislatchindicates:Multipleprocessesattemptingtoaccessthesame“hot”