FollowingaTuningMethodologyObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DetermineperformanceproblemsManageperformanceDescribetuningmethodologiesIdentifygoalsfortuningDescribeautomaticSQLtuningfeaturesListmanualSQLtuningstepsPerformanceProblemsInadequateconsumableresourcesCPUI/OMemory(maybedetectedasanI/Oproblem)DatacommunicationsresourcesHigh-loadSQLContentionFactorstoBeManagedSchemaDatadesignIndexesApplicationSQLstatementsProceduralcodeInstanceDatabaseUserexpectationsHardwareandnetworktuningFullNotesPageTuningGoalsReducetheresponsetimeReduceresourceusageFullNotesPageOverviewofSQLTuning1. Identifycausesofpoorperformance.2. IdentifyproblematicSQL.Automatic:ADDM,TopSQLManual:V$views,statspack3. Applyatuningmethod.ManualtuningAutomaticSQLtuning4. Implementchangesto:SQLstatementconstructsAccessstructuressuchasindexesIdentifyingHigh-LoadSQLIdentifyhigh-loadorproblematicSQLDynamicperformanceviewsStatspackADDMTopSQLreportManualTuning1. Gatherinformationaboutthereferencedobjects.2. Gatheroptimizerstatistics.3. Reviewexecutionplans.4. RestructureSQLstatements.5. Restructureindexesandcreatematerializedviews.6. Maintainexecutionplans.GatherInformationAbout

ReferencedObjectsSQLtextStructureoftablesandindexesOptimizerstatisticsViewsOptimizerplan:currentandpriorGatheringOptimizerStatisticsGatherstatisticsforalltables.Gathernewstatisticswhenexistingstatisticsbecomestale.FullNotesPageReviewingtheExecutionPlanDrivingtablehasthebestfilter.Fewestnumberofrowsarebeingreturnedtothenextstep.Thejoinmethodisappropriateforthenumberofrowsbeingreturned.Viewsareusedefficiently.TherearenounintentionalCartesianproducts.Eachtableisbeingaccessedefficiently.ExaminethepredicatesintheSQLstatementandthenumberofrowsinthetable.Afulltablescandoesnotmeaninefficiency.FullNotesPageRestructuringtheSQLStatementsComposepredicatesbyusingANDand=.AvoidtransformedcolumnsintheWHEREclause.Avoidmixed-modeexpressionsandbewareofimplicittypeconversions.WriteseparateSQLstatementsforspecifictasksanduseSQLconstructsappropriately.UseEXISTS

orIN

forsubqueriesasrequired.Cautiouslychangetheaccesspathandjoinorderwithhints.FullNotesPageRestructuringtheIndexesRemoveunnecessaryindexestospeedtheDML.Indextheperformance-criticalaccesspaths.Reordercolumnsinexistingconcatenatedindexes.Addcolumnstotheindextoimproveselectivity.Createappropriateindexesbasedonusagetype:B*treeBitmapBitmapjoinConcatenatedConsiderindex-organizedtables.MaintainingExecutionPlansOverTimeStoredoutlinesStoredstatisticsLockingstatisticsAutomaticSQLTuningAutomaticSQLtuningfacilitatesthesesteps:Gatherinformationonthereferencedobjects.Verifyoptimizerstatistics.Reviewexecutionplans.RestructureSQLstatements.Restructureindexesandcreatematerializedviews.Maintainexecutionplans.FourtypesofanalysisareperformedinautomaticSQLtuning:StatisticsanalysisSQLprofilingAccesspathanalysisSQLstructureanalysisFullNotesPageAutomaticTuningMechanismsYoucanperformautomaticSQLtuningusing:SQLTuningAdvisorSQLAccessadvisorSQLTuningAdvisorTheSQLTuningAdvisordoesthefollowing:Acceptsinputfrom:AutomaticDatabaseDiagnosticMonitor(ADDM)AutomaticWorkloadRepository(AWR)CursorcacheCustomSQLasdefinedbytheuserProvides:RecommendationsRationaleExpectedbenefitsSQLcommandsforimplementingtherecommendationsSQLAccessAdvisorTheSQLAccessAdvisordoesthefollowing:Providescomprehensiveadviceonschemadesignbyacceptinginputfrom:CursorcacheAutomaticWorkloadRepository(AWR)User-definedworkloadHypotheticalworkloadifaschemacontainsdimensionsorprimary/foreignkeyrelationshipsAnalyzestheentireworkloadandrecommends:CreatingnewindexesasneededDroppinganyunusedindexesCreatingnewmaterializedviewsandmaterializedviewlogsSummaryInthislesson,youshouldhavelearnedhowto:ManageperformanceStartearly;beproactiveSetmeasurableobjectivesMonitorrequirementscomplianceHandleexceptionsandchangesIdentifyperformanceproblemsInadequateconsumableresourcesInadequatedesignresourcesCriticalresourcesExcessivedemandSummaryInthislesson,you