processoracle10g

TUNINGTHELARGEPOOLTUNINGTHELARGEPOOLFORFORRMANRMANSUCCESSFULCUSTOMBR/DR/BCMODELSNYOUGMEETING,DECEMBER2008ANTHONYDNORIEGAANTHONYDNORIEGAanthony.
noriega@adnmis.
comanthony.
noriega@adnmis.
comADNRADNR&&DDwww.
adnmis.
comwww.
adnmis.
comSpeakerQualificationsSpeakerQualificationsIndependentConsultant,ADNSpeakeratNYOUGmeetings24yearsofITexperience18yearsofOracleexperience,13asaDBARMANexperiencewithOracle8i,9i,10g,and11g,since1999.
BSSystemsEngineering,UniversidaddelNorte,1987.
MSComputerScience,NJIT,1993PhDCIScandidate,NJIT,1997MBAMIS,MontclairStateUniversity,2006CollegeMathProfessorandformerHSMathTeacherPrincipal.
ObjectivesProvideaconciseapproachtotunetheLargePoolforRMANAnalyzeandcompareOraclerecommendationsandcustomsettingsPresentarobustapproachtooptimizeRMANtimeandstoragespaceDeriveaseriesoftechnicalargumentsandusefulrulestoattainaBR/BC/DRoptimizationmethodology.
LargePool:MemoryStructuresJavaPoolBufferCacheRedoBufferSharedPoolStreamsPoolLargePoolOtherServerProcessServerProcessBackgroundProcessPGAServerProcessBackgroundProcessPGAPGAPGAPGASGALargePoolPurposesSessionmemoryforthesharedserverandtheOracleXAinterface(usedwheretransactionsinteractwithmorethanonedatabase)I/OserverprocessesOracleDatabasebackupandrestoreoperationsUsedbybackupprocessfordiskI/Obuffers.
RMANReliabilityBasedonexperimentalgatheringRMANtasksaremorereliablewithaLargepoolsizedwithOraclerecommendedsettings.
RMANtaskstendtoimproveperformancewithhigherLargePoolsettings.
ProductionEnvironmentLinux4-nodeRACs,9iand10gSANarchitectureusingSymantec(Veritas)NetbackupMPP(LinuxIntel64-bit8/16dual-coreprocessorRHLinuxservers).
OracleRACandDataGuardinplace(MAA).
Incrementalbackuplevel0mostly.
BlockChangeTracking(Oracle10g/11g)Oracle-RecommendedSettingsAccordingtoOracleCorporation,therecommendedsettingfortheLargePoolSizeis16M.
So,yourinit.
oraparametershouldread:LARGE_POOL_SIZE=16Mfast_start_mttr_target=900CustomSettingsSomeofthecustomsettingsare;Third-Party(VeritasNetbackupincludingbothRACdatabasesandStandaloneDatabases)LARGEPOOLSIZERELIABILITYPERFORMANCE4MBestDecreased8MBetterAverage16MAverageImprovedHigh-ValueExpectedBetter*e.
g.
,32M-180M,180M-2G+CustomSettingsWithASMandFileSystemDatabasesLARGEPOOLSIZERELIABILITYPERFORMANCE4MImprovedDecreased8MBetterAverage16MAverageImprovedHigh-ValueExpectedBetter*e.
g.
,32M-180M,180M-2G+SharedSegmentandLPSizeAnalogyrelatingLargePoolandSharedsegmentsizesLARGEPOOLSIZESHAREDSEGMENTUSED4M8M16MHigh-Value**e.
g.
,32M-180M,180M-2G+StorageSpaceandTimeStorageSpaceandTimeStorageSpaceandTimeBackupSizeandDuration4M8M16M2G+RelatingtheLargepoolsizeandbackupsizeanddurationSizet0DurationDurationSizeRMANCompatibilityTableOracleByVersionVERSIONLARGEPOOLREMARKSOracle8iNON/AOracle9iYESDynamicParameter,BRdiskbuffers,ParallelExecutionMessageBuffer,NoLRU,Optional.
Defaultsto0.
ksfqxcreinternalsharedmemoryallocationerrorOracle10gYESLike9i.
EnhancedforSharedServerOracle11gYESBettersatisfiedsegmentrequeststhanusingsharedpool.
Fasterparallelqueryexecution.
AutomaticMemoryManagement.
OracleByVersionVERSIONLARGEPOOLREMARKSOracle9iYESDynamicParameter,BRdiskbuffers,ParallelExecutionMessageBuffer,NoLRU,Optional.
Defaultsto0.
ksfqxcreinternalsharedmemoryallocationerrorOraclerecommendedsetting:8M300KBtoatleast2GB(actualmaximumisoperatingsystem-specific)AboutOracle9iLargePoolSELECTPOOL,NAME,SUM(BYTES)FROMV$SGASTATWHEREPOOLLIKE'%pool%'GROUPBYROLLUP(POOL,NAME);POOLNAMESUM(BYTES)largepoolPXmsgpool38092812largepoolfreememory299988largepool38392800….
….
OracleByVersionVERSIONREMARKSOracle10gDynamicParameter,BRdiskbuffers(Oracleallocatesbuffersthatareafewhundredkilobytes)Parallelquery,ParallelExecutionMessageBufferNoLRUandnoagingoutofpoolOptional.
SpecifiedLARGE_POOL_SIZEvalueaminimumvalueforthememorypool(allocationheap).
300KBtoatleast2GB(actualmaximumisoperatingsystem-specific)OracleByVersionVERSIONREMARKSOracle11gDynamicParameter,BRdiskbuffers(Oracleallocatesbuffersthatareafewhundredkilobytes)Parallelquery,ParallelExecutionMessageBufferNoLRUandnoagingoutofpoolOptional.
Defaultsto0.
MinimumValue300K.
30Mrecommendedforabout100sessions(sharedserver,about200-300k/session).
ForeffectivesortusageofLP,setsort_area_sizeandsort_area_retained_simetosamevalue.
PracticalinAutomaticMemoryManagement.
Oracle11gDynamicSizingV$MEMORY_CURRENT_RESIZE_OPS:currentmemoryresizeoperations(bothautomaticandmanual)inprogress.
V$MEMORY_DYNAMIC_COMPONENTS:currentsizesofalldynamicallytunedmemorycomponents,includingSGAandPGA.
V$MEMORY_RESIZE_OPS:last800completedmemoryresizeoperations(bothautomaticandmanual),nocurrentoperations.
V$MEMORY_TARGET_ADVICE:tuningadvicefortheMEMORY_TARGETinitializationparameter.
V$SGA_CURRENT_RESIZE_OPS:currentSGAresizeoperationsinprogress,i.
e.
,agroworashrinkofadynamicSGAcomponent.
V$SGA_RESIZE_OPS:last800completedSGAresizeoperations.
Nocurrentoperations.
V$SGA_DYNAMIC_COMPONENTS:dynamiccomponentsinSGA,summaryofallcompletedSGAresizeoperationssincestartup.
V$SGA_DYNAMIC_FREE_MEMORY:amountofSGAmemoryavailableforfuturedynamicSGAresizeoperations.
SampleRMANShellRMANBackupshellforOracleInstancesStudied(WithoutaRecoveryCatalog)BackupSizeandDurationRMANBackupDurationforOracleInstancesStudied(WithaRecoveryCatalog)BackupSizeDurationLineChartSampleShellRMANBackupshellforOracleInstancesStudied(WithaRecoveryCatalog)BackupSizeandDurationRMANBackupDurationforOracleInstancesStudied(WithaRecoveryCatalog)ASimpleRMANRegressionModelRMANConfigurationSessionLongOperationsBusinessConsiderationsOverallPreparednessforanyRMANprocess.
DisasterRecovery(DR)BusinessContinuity(BC)BackupandRecovery(BR)SDLCQualityAssuranceBackupsetapproachArchivedreologbackuppolicyRetentionpolicystrategy.
ConcludingRemarksTheLargePoolSizeSettingcouldaffectBR/DR/BCProcesses,involvingbothreliabilityandperformance.
LargePoolSizeisafactorwhenCorrelatingdatabasesize/backupsizeandduration.
Thelargepoolsizeisdirectlyproportionaltothebackupsizeandinverselyproportionaltothebackupduration.