Oracle Redo管理好文章

MugheesA.MinhasPrincipalProductManagerDatabaseManageabilityOracleCorporationTheDo’sandDont’sofSpace&UndoManagement:BestPracticesforOracleDatabase10gAgendaySpaceManagement–PermanentTablespaceManagement–TemporaryTablespaceManagementyUndoManagement–Manualvs.AUM–AUMAdministrationyProactiveProblemPreventionandAutomaticErrorHandlingPermanentTablespaceManagementBestPracticesSpaceManagementGoalsySpaceUtilization–Optimizespaceusagebyeliminating/minimizingfragmentationyPerformance–OptimizedataaccessandtransactionperformanceyChoicesthataffectspaceutilizationandperformanceare:–Extentmanagement–Segmentspacemanagement–Numberofextentsintablespace–SizeofextentsintablespaceExtentManagementyOptions:–DictionaryManagedTablespacesyExtentsmanagedindictionarytablesyFinecontroloverextentsizesthroughstorageparametersINITIAL,NEXTandPCTINCREASE–LocallyManagedTablespacesySpacemanagedlocallybybitmapsindatafileheadersyTwoextentmanagementtypes-Auto-allocate:Extentsizedeterminedbydatabase-Uniform:AllextentsofsamesizeyPoorextentmanagementcanleadto–Externalfragmentation–PoorperformanceExtentManagementyExternalFragmentation–DefinitionyFragmentsoffreeextentsnotlargeenoughfornewallocations–Cause:DictionaryManagedTablespacesyImproperchoiceofstorageparameters(INITIAL,NEXT,PCTINCREASE)yFrequentcreation/droppingofobjectsleavingnumerousdifferentsizefreeextents–ImpactyPoorspaceutilizationySpacemanagementperformancecandeteriorateby10%to20%ExtentManagementyPoorPerformance–Cause:useofDictionaryManagedTablespacesyDatabasewideserializationthroughSTenqueueyMetadatamaintenanceCPUandI/Ointensive-RecursiveSQLstoupdatemetadatayFragmentationitselfalsoresultsinpoorresponsetimeBestPracticeyUseLocallyManagedTablespaces–Serializationofspacemanagementatfilelevel–Spacemanagementfasterby100%to200%–EliminatesexternalfragmentationyExtentmanagementtypes–Auto-allocate(Recommended):ExtentsizedeterminedbydatabaseyStep-downalgorithmminimizesfragmentationbyloweringspaceallocationinmultiplesof1Mwhendesiredsizenotavailable–Uniform:AllextentsofsamesizeyUsewhenallsegmenthaveroughlysamesizeySegmentgrowthratesareknownpreciselyyParalleldirectloadsnotcommonBestPracticeyMigratetolocallymanaged–UsePL/SQLpackageDBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL–Migrationdonein-place,online–Multipletablespacescanbemigratedinparallel–Out-of-placemigrationiftablespacealreadyfragmentedSegmentSpaceManagementyManualSegmentSpaceManagement(MSSM)–Freespacemanagedusinglinkedlistoffreeblocks–MovementofblocksinfreelistcontrolledbyPCTFREE,PCTUSED–BufferbusywaitsreducedbyyFREELISTS:datablocksyFREELISTGROUPS:segmentheaderblockyAutomaticSegmentSpaceManagement(ASSM)–Segmentspacemanagedusingbitmaps–Bitmapsstoredinmetadatablockscalledbitmapblocks–Numberofbitmapblocksdynamicallygrownasneeded–PCTUSED,FREELISTS,FREELISTGROUPSdon’thavetobesetSegmentSpaceManagementyProblemswithMSSM–Canleadtointernalfragmentation–FREELISTSparameternotdynamicyContentionaffectsperformance–FREELISTGROUPSyStaticallypartitionfreespace:InRACspacefreedbyoneinstancegroupcannotbeusedinotherinstancesyCannotbereconfiguredwithoutrebuildingtheobjectyDoesnotadjusttovaryingloadsBestPracticeyUseAutomaticSegmentSpaceManagement(ASSM)–Minimizesinternalfragmentation–Contentiononmetadatablocksautomaticallymanaged–Easierconfiguration–PerformanceonparorbetterthantunedMSSM–Inter-instancedatablockcontentionreducedbydynamicinstanceaffinityASSMvs.MSSM0123412481632ASSMMSSMResponseTimeInsertWorkload024681248ASSMMSSMConcurrentUsersResponseTimeMixedDMLWorkloadConcurrentUsersInternalFragmentationyDefinition:Fragmentationofspacewithinasegment–Under-utilizationofdataunderHWMSegmentHighwaterMarkSegmentGrowthB1B2B3Ext1Ext2Ext3yAlthoughminimized,canoccurinASSMtablespaceaswellInternalFragmentationyCauses–Heapsegment:yBadchoiceofPCTFREE,PCTUSED–LOBsegmentyBadchoiceofPCTVERSION,RETENTION–MostdatainsertedintosegmentisdeletedySteadystatedensityofdatainsegmentissmall–QueuelikebehaviorwithtemporaldatainheapsegmentsyApplicationtypicallydoesdirectloadsfollowedbydeletes–Indexsegmentswithrandomupdates&deleteswithnofurtherinsertsyImpact–Poorsegmentspaceutilizationinthesegment–Performanceoffulltablescan,rangescan,fastfullscanetc.,deterioratesbycloseto100%InternalFragmentationyPerformanceImpact–Internalfragmentationsslowscertainaccesspaths,e.g.,fulltablescan,fastfullscan,etc.,05101520253020%50%70%%FragmentationFullTableScanPerformanceDegradationNoFragWithFragResponseTimeInternalFragmentation:SolutionyOnlineSegmentShrinkremediesinternalfragmentation–ROWMOVEMENTmustbeENABLEDforheaporganizedsegments–SegmentmustbeinASSM,locallymanagedtablespaceYYNParallelYNYSegmentLevelReorgNNYDependecyMaintenanceNNYIncrementalNNYIn-placeNYYOnlineAlterMOVEOnlineRedefShrinkPropertyyNote:Tableswithlargenumberofindexes,reorgisfasterInternalFragmentation:SolutionyWhentouseOnlineSegmentShrink?–Notallfragmentedsegmentsarecauseforconcern–SegmentAdvisorisfragmentationadvisoryRecommendswhen/howtodefragmentsegmentyConsiderssegmentgrowthtrendyRecommendationsavailablethroughEMandadvisorframeworktablesAutomaticSegme