Oracle英文版培训课件之Performance Tuning：les-10Automatic Shared Memory Management

AutomaticSharedMemoryManagementObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:EnableEnterpriseManager(EM)memoryparametersSetauto-tunedmemoryparametersOverrideminimumsizewiththemanuallytunedSGAparametersUsetheSGAadvisortosetSGA_TARGETSpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

sizeLarge

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

fileShared

poolDynamicSGAFeatureImplementsaninfrastructuretoallowtheservertochangeitsSGAconfigurationwithoutshuttingdowntheinstanceSGAsizeislimitedbySGA_MAX_SIZE:UsedforreservingvirtualmemoryaddressspaceatinstancestartupCannotbechangeddynamicallyAllowsforcertainSGAcomponentstobedynamicallyresizedSELECTbytes

FROMV$SGAINFO

WHEREname='FreeSGAMemoryAvailable';GranuleSGAmemoryisallocatedinunitsofcontiguousmemorychunkscalledgranules.ThesizeofagranuledependsontheestimatedtotalSGA.IftheestimatedSGAsizeis:Lessthanorequalto1GB,thegranulesizeis4MBGreaterthan1GB,thegranulesizeis16MBSELECTbytes

FROMV$SGAINFO

WHEREname='GranuleSize';MemoryAdvisoriesBufferCacheAdvice(introducedin9iR1):V$DB_CACHE_ADVICEPredictsphysicalreadsfordifferentcachesizesSharedPoolAdvice(in9iR2):V$SHARED_POOL_ADVICEPredictsparsetimesavingsfromhavingdifferentsizesofthesharedpoolJavaPoolAdvice(in9iR2):V$JAVA_POOL_ADVICEPredictsJavaclassloadtimesavingsfromhavingdifferentsizesofJavapoolStreamsPoolAdvice(10g

R2)V$STREAMS_POOL_ADVICEPredictsspillandunspillactivityforvarioussizesManuallyAddingGranulestoComponentsUsetheALTER

SYSTEMcommandtodynamicallyincreasememoryallocationtoacomponent.Increasingthememoryuseofacomponentsucceedsonlyifthereareenoughfreegranulestosatisfytherequest.Memorygranulesarenotfreedautomaticallyfromanothercomponenttosatisfytheincrease.Decreasingthesizeofacomponentispossible,butonlyifthegranulesbeingreleasedareunusedbythecomponent.IncreasingtheSizeofanSGAComponentSQL>showparameterNAMETYPEVALUE-----------------------------------------------sga_max_sizebiginteger200Mshared_pool_sizebiginteger84Mdb_cache_sizebiginteger92MSQL>altersystemsetshared_pool_size=120M;altersystemsetshared_pool_size=120M*ERRORatline1:ORA-02097:parametercannotbemodified…ORA-04033:InsufficientmemorytogrowpoolSQL>altersystemsetdb_cache_size=50M;Systemaltered.SQL>altersystemsetshared_pool_size=120M;Systemaltered.AutomaticSharedMemoryManagement:OverviewUsesdynamicSGAandmemoryadvisorstoautomaticallyadapttoworkloadchangesMaximizesmemoryutilizationHelpseliminate

out-of-memory

errorsAvoidsrelearning

whenusingSPFILEOnlineusersBatchjobsBuffercacheLargepoolSharedpoolJavapoolBuffercacheLargepoolSharedpoolJavapoolOnlineusersBatchjobsStreamspoolStreamspoolSGASizingParameters:OverviewWithASMM,fiveimportantSGAcomponentscanbeautomaticallysized.Nondefaultbufferpoolsarenotauto-tuned.Logbufferisnotadynamiccomponentbuthasagooddefault.DB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_nK_CACHE_SIZELOG_BUFFER_SIZESHARED_POOL_SIZEDB_CACHE_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZEAuto-tuned

parametersManual

dynamicparametersManual

staticparametersSGA_TARGETSGA_MAX_SIZEBenefitsofAutomaticShared

MemoryManagementDB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZETotalSGAsizeSGA_TARGETDynamicSGATransferModesASMMIMMEDIATEtransfermode:Out-of-memory(ORA-04031)errorsPartialgranulescanbeused.ASMMDEFERREDtransfermode:TransparentlyexecutedinthebackgroundPartialgranulescanbeused.MANUALtransfermode:UsedwithALTER

SYSTEMcommandsResizemustusefullgranules.MemoryBrokerArchitectureStatisticdeltasacrossdifferenttimeperiodsCircularSGAbufferofstatscapturedbyMMONMemoryBrokerPolicyModuleAdd

twogranules

toshared

pool.Output:resizerequestsTrade-offdifferentcomponentsbenefit/lostMMANtransfers

thememory.resizequeueMMANMMONManuallyResizingDynamic

SGAParametersForauto-tunedparameters,manualresizing:ResultsinimmediatecomponentresizeifthenewvalueisgreaterthanthecurrentsizeChangestheminimumsizeifthenewvalueissmallerthanthecurrentsizeManuallytunedparameterresizingaffectsthetunableportionoftheSGA.BehaviorofAuto-TunedSGAParameters

WhenSGA_TARGETisnotsetorissettozero:Auto-tunedparametersareexplicitlysetNote:SHARED_POOL_SIZE

Internalstartupoverheadisincluded

ValuemayneedtobeincreasedfrompreviousreleasesWhenSGA_TARGETisset:Defaultvalueofauto-tunedparametersiszeroAnonzerovalueisalowerboundCurrentvaluesinmegabytesareshownby:SELECTcomponent,current_size/1024/1024FROMV$SGA_DYNAMIC_COMPONENTS;BehaviorofManuallyTuned

SGAParametersManuallytunedcomponentsare:KEEPandRECYCLEbuffercachesNondefaultblocksizecachesLOG_BUFFERManuallytunedcomponentsareuserspecified.ManuallytunedcomponentsareincludedinSGA_TARGETtopreciselycontroltheSGAsize.UsingtheV$PARAMETERViewSGA_TARGET=8GSELECTname,value,isdefaultFROMV$PARAMETERWHEREnameLIKE'%size';DB_CACHE_SIZE=0JAVA_POOL_SIZE=0LARGE_POOL_SIZE=0SHARED_POOL_SIZE=0STREAMS_POOL_SIZE=0ResizingSGA_TARGETTheSGA_TARGETinitializationparameter:IsdynamicCanbeincreaseduptoSGA_MAX_SIZECanbereduceduntilallcomponentsreachminimumsizeChangesaffectonlyautomaticallysizedcomponentsIncludeseverythingintheSGA:FixedSGAandotherinternalallocationsAutomaticallysizedSGAcomponentsManualSGAcomponentsAllowsprecisesizingofthetotalsharedmemoryallocationbytheOracleserverSGAsize=8GBDisablingAutomaticShared

MemoryManagementSettingSGA_TARGETtozerodisablesauto-tuning.Auto-tunedparametersaresettotheircurrentsizes.SGAsizeasawholeisunaffected.Parameters:SGA_TARGET=8GSHARED_POOL_SIZE=1GParameters:SGA_TARGET=0DB_CACHE_SIZE=4GSHARED_POOL_SIZE=1.5GLARGE_POOL_SIZE=512MJAVA_POOL_SIZE=512MSTREAMS_POOL_SIZE=512MSGAsize=8GBOriginalvaluesSGA_TARGET=0ConfiguringASMMSGAAdvisorMonitoringASMM