Oracle英文版培训课件之Performance Tuning：les-11Checkpoint and Redo Tuning

CheckpointandRedoTuningObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DiagnosecheckpointandredoissuesImplementFastStartMTTRTargetMonitortheperformanceimpactofFastStartMTTRTargetTunetheredochainSizetheredologfileSizetheredologbufferCheckpointandRedoCheckpoint:TransferschangeddatatodiskMakesbufferspaceavailableformoredatablocksControlsmeantimetorecover(MTTR)Redo:RecoverscommitteddatanotondiskProvidesuncommitteddataforrollbackoperationProvidessourcedataforcompleterecoverySpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

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

poolCheckpointArchitectureThecheckpointarchitectureprovides:Checkpointposition:AstartingpositionintheredologstobeginrecoveryCheckpointtarget:AcalculatedpositionintheredologswherethecheckpointpositionshouldbeAnestimatedmeantimetorecoverAhigh-performanceincrementalcheckpointAfullcheckpointwhenrequiredDatabaseWriter(DBWn)ProcessDBWnwriteswhenoneofthefollowingeventsoccurs:CheckpointDirtybuffers’thresholdNofreebuffersTimeoutRACpingrequestTablespaceOFFLINETablespaceREAD

ONLYTableDROPorTRUNCATETablespaceBEGIN

BACKUPSGADatabasebuffercacheDatafilesDatabaseWriter(DBWn)BackgroundInformationCheckpoint(CKPT)ProcessResponsiblefor:SignalingDBWnatcheckpointsUpdatingdatafileheaderswith

checkpointinformationUpdatingcontrolfileswith

checkpointinformationDatabaseWriter(DBWn)Checkpoint(CKPT)SGADatabasebuffercacheRedoArchitectureRedoisdesignedforminimumperformanceimpact.Serverprocesseswritetotheredologbuffer:CircularbufferMemory-to-memorywritesSmallfastwritesLGWRwriteslogbufferblockstologfiles:CircularfilesMemory-to-diskwriteFullblocksifpossibleARCncopieslogfilestoarchivelogfiles:Disk-to-diskwritesMultiplearchiverprocessescanbestarted.DatabasebuffercacheRedologbuffer

SharedpoolLibrarycacheDatadictionarycache

Userglobalarea

TheRedoLogBufferSQL>UPDATEemployees2SETsalary=salary*1.13WHEREemployee_id=736;Server

process

LGWRControlfilesARCnArchived

logfilesRedologfilesDatafilesRedoLogFilesandLogWriterRedologfiles:RecordchangestothedatabaseShouldbemultiplexedtoprotectagainstlossLogWriterwrites:AtcommitWhenone-thirdfullEverythreesecondsBeforeDBWnwritesGroup3LogWriter(LGWR)Group1Group2RedologbufferSGARedologfilesArchiver(ARCn)IsanoptionalbackgroundprocessAutomaticallyarchivesonlineredologfileswhenARCHIVELOGmodeissetforthedatabasePreservestherecordofallchangesmadetothedatabaseLogWriter(LGWR)Archiver(ARCn)RedologbufferSGAIncrementalCheckpointingc1

Checkpointpositionc2c3c4c5

Tailof

redothreadRedostreamCheckpoint

queueTargetRBAb3b4t2t1FAST_START_MTTR_TARGET=Tt1+t2<Tb1b2IncrementalcheckpointNotesonlyslideIncrementalCheckpointandLogFileSizeThemaximumcheckpointlagis: 90%*(SUM(log_sizei)–MAX(log_sizei))Checkpointlagisdesignedtopreventlogswitchfromblocking.Afewsmalllogfilescanresultinexcesscheckpointwrites.Current

tailTargetcheckpoint9,000

blocksLogfile#1:

10,000blocksLogfile#2:

10,000blocksAdjustingtheCheckpointRateThecheckpointrateisdeterminedbythemostaggressiveof:FAST_START_MTTR_TARGETparameter(onlyonEnterpriseEdition)SizeofthesmallestredologfileLOG_CHECKPOINT_TIMEOUTparameter(overridesFAST_START_MTTR_TARGETifset)LOG_CHECKPOINT_INTERVALparameter(overridesFAST_START_MTTR_TARGETifset)RedoLogfileSizeAdvisorThisadvisor

determinesthe

optimalsizeofyour

onlineredologs:Noadditional

checkpointwrites

beyondthose

causedbyFAST_START_MTTR_TARGET.FAST_START_MTTR_TARGETmustbeset.Thiscolumnshowstheredologfilesize(inmegabytes)thatisconsideredasminimal.DescriptionOPTIMAL_LOGFILE_SIZEColumnnameV$INSTANCE_RECOVERYViewnameImpactoftheCheckpointRateSELECTc.value-nc.value

FROMV$SYSSTATc,V$SYSSTATnc

WHERE='physicalwrites'AND

='physicalwritesnoncheckpoint';StatisticTotal--------------------------------------------physicalwrites47,308physicalwritesnoncheckpoint44,674FromtheV$views:FromtheStatspackreport:NotesonlyslideAutomaticCheckpointTuningThereisnolongeracontinuousmanualtuningeffort.Automaticcheckpointtuningisthebest-effortcheckpointing,withoutmuchoverhead.Itreducesaveragerecoverytimebymakinguseofunusedbandwidth.AutomaticcheckpointtuningisenabledwhenFAST_START_MTTR_TARGETisnotexplicitlysettozero.ADDMReport:CheckpointsADDMReport:RedoLogsStatspackandAWRReportsTop5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time-----------------------------------------------------CPUtime55156.8logfileparallelwrite3,8992015220.7logfilesync82358716.0latch:redocopy63544704.6latch:redoallocation1,10942384.4Checkpointandredoshowcertainsymptoms:AlertlogshowslogswitchnotcompleteI/OsymptomscausedbyexcessivecheckpointsLogswitchesperhour>4logfileandlatch:redoinTopTimedEventsNotesonlyslideCheckParametersParameterNameBeginvalue---------------------------------------fast_start_mttr_target25Reviewcheckpointparametersforreasonablevalues:SQL>SELECTmttr_target_for_estimate,2>estd_total_ios,estd_total_io_factor3>FROMV$MTTR_TARGET_ADVICEORDERBY1MTTR_TARGET_FOR_ESTIMATEESTD_TOTAL_IOSESTD_TOTAL_IO_FACTOR----------------------------------------------------------2024366901.07392223306741.02722522689731372204817.9717622181841.9616UseV$MTTR_TARGET_ADVICEforoptimumvalue:ChecktheRedoLogSizeReviewthecurrentsizeoftheredologfiles:Checkalertlogforlogswitchrate:RedoLogChainTuningRedotuningstartswiththeslowestpart.Reducetheamountofredogenerated.Checkarchivelogging(waitsforarchivingneeded).Checktheredologfilesizeandlogswitchrate.Checkthecheckpointparameters.Lookforlogspacerequests.TheRedoBufferAllocationRetriesvalueshouldbenear0andshouldbelessthan1%ofredoentries.ReducingRedoOperationsWaystoavoidloggingbulkoperationsintheredolog:DirectPathloadingwithoutarchivingdoesnotgenerateredo.DirectPathloadingwitharchivingcanuseNOLOGGINGmode.DirectLoadINSERTcanuseNOLOGGINGmode.SomeSQLstatementscanuseNOLOGGINGmode.IncreasingthePerformanceofArchivingSharethearchivingworkduringatemporaryincreaseinworkload:IncreasethenumberofarchiverprocesseswithLOG_ARCHIVE_MAX_PROCESSES.Multiplextheredologfiles,andaddmoremembers.Changethenumberofarchivedestinations:LOG_ARCHIVE_DEST_nALTERSYSTEMARCHIVELOGALLTO<log_archive_dest>DiagnosticToolsV$ARCHIVE_DESTV$ARCHIVED_LOGV$ARCHIVE_PROCESSESLOG_ARCHIVE_DEST_nArchived

logsLOG_ARCHIVE_DEST_STATE_nLGWRRedoLogGroupsandMembersGroup2Group3Group1Disk1Disk2MemberMemberMemberMemberMemberMemberOnlineRedoLogFileConfigurationSizeredologfilestominimizecontention.Provideenoughgroupstopreventwaiting.Storeredologfilesonseparate,fastdevices.Monitortheredologfileconfigurationwith:V$LOGFILEV$LOGV$LOG_HISTORYMonitoringOnlineRedoLogFileI/OSizingtheRedoLogBufferThesizeoftheredologbufferisdeterminedby:LOG_BUFFERparameterRemainingspaceinthefixedareagranuleDefaultvalue:Either2MBor128KBthevalueofCPU_COUNT,whicheverisgreaterArchived

logfilesRedologfilesDiagnosingRedoLogBufferInefficiencySQL>UPDATEemployees2SETsalary=salary*1.13WHEREemployee_id=736;Server

processLGRWARCHServer

process