AnOracle10gUpgradeCaseStudy:LookingatSystemPerformanceBeforeandAftertheUpgradeNoCOUGSpringConference2005Today'sSessionTheviewfrom30,000feet:OurOracleenvironment,upgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverheadToday’sSessionGoal:HelpyouplanforyourownOracle10gupgrade.Wewill:

Lookatonecompany’sexperienceupgradingto10gDiscussreal-lifeexperiencesProvidedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10gAlwaysRememberEachOraclesystemisuniqueandwillhaveitsownchallenges.Nevertakesomebodyelse’swordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompany’sexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryit—inatestenvironment—andsee.WhitePaperTheViewFrom30,000FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralOurOracleEnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bitOneproductionandonedevdatabaseProductiondatabase15GbinsizeOurOracleEnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomers’databaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughly50,000lines)LeveragesOracle8ifeatures—egGTTs,table()About50SQLstatementshavehintsOurOracleEnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)OurReasonstoUpgradeto10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingreason.)OurUpgradeStrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerableOurUpgradeStrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedata—handyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.Impressions:UpgradeProcessOracle10gversionandpatchsetinstalledverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessaryparameterchanges.I'vedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)Impressions:CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.Impressions:Oracle10gWorkedwelloutofthebox:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.Impressions:Oracle10gBigger,bulkier,hungrierforsystemresources:Biggerexecutablesize,sharedpool,SYSTEMtablespace…Moreoverhead:Daemonprocesses,hardparses,statisticscollection……Overheadandbulkinessweretolerableforus.Impressions:Oracle10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.Impressions:Oracle10gDiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageable—afairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerfeatures.(Butwillproceedinthisdirectionverycautiously!)UpgradeIssuesinGreaterDetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverheadSizingtheSharedPoolandSGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio>97%Librarycachehitratio~100%SizingtheSharedPoolandSGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswellSizingtheSharedPoolandSGAJusttosatisfyacuriosity…shared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144MbReasonsforLargerSharedPoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusageSQLstatementsgeneratedbyOracleAllocationforOverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecifies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.AllocationforOverheadOnourOracle8idatabasethesharedpoolwasabout3Mb(8%)largerthanspecifiedbyshared_pool_size:SQL>SELECTSUM(bytes)/1024/1024actual_pool_size2FROMv$sgastat3WHEREpool='sharedpool';ACTUAL_POOL_SIZE----------------43.1291847SQL>SHOWPARAMETERshared_pool_sizeNAMETYPEVALUE--------------------------------------------------------------------shared_pool_sizestring41943040We’veseenthedisparityashighas27%.SharedSQLAreaMemoryUsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOracle10gthaninOracle8i.Inourenvironmentthedifferencewasalmost2x.Themovefrom32bitOraclesoftwareto64bitaccountsformuchofthisgrowth.Howmuch,wedon’tknow.SharedSQLAreaMemoryUsageOnourOracle8idatabase:SQL>SELECTA.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2SUM(B.persistent_mem)persistent_mem,3SUM(B.runtime_mem)runtime_mem,4SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5total_mem6FROMdba_usersA,v$sqlB7WHEREA.username='DBRX_OWNER‘8ANDB.parsing_user_id=A.user_id9GROUPBYA.username;USERNAMECOUNT(*)SHARABLE_MEMPERSISTENT_MEMRUNTIME_MEMTOTAL_MEM-------------------------------------------------------------------DBRX_OWNER3626,275,020256,1761,996,3248,527,520SharedSQLAreaMemoryUsageOnourOracle10gdatabase:SQL>SELECTA.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2SUM(B.persistent_mem)persistent_mem,3SUM(B.runtime_mem)runtime_mem,4SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5total_mem6FROMdba_usersA,v$sqlB7WHEREA.username='DBRX_OWNER‘8ANDB.parsing_user_id=A.user_id9GROUPBYA.username;USERNAMECOUNT(*)SHARABLE_MEMPERSISTENT_MEMRUNTIME_MEMTOTAL_MEM-------------------------------------------------------------------DBRX_OWNER36012,941,006487,0483,361,16016,789,214SQLGeneratedbyOracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled““internalSQL””or““recursiveSQL””.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.SQLGeneratedbyOracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasewasEnterpriseEditionwith““default””optionsinstalled.SQLGeneratedbyOracleOnourOracle8idatabase:SQL>SELECTA.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2SUM(B.persistent_mem)persistent_mem,3SUM(B.runtime_mem)runtime_mem,4SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5total_mem6FROMdba_usersA,v$sqlB7WHEREA.usernameIN('DBSNMP','SYS','SYSTEM','SYSMAN')8ANDB.parsing_user_id=A.user_id9GROUPBYA.username;USERNAMECOUNT(*)SHARABLE_MEMPERSISTENT_MEMRUNTIME_MEMTOTAL_MEM-------------------------------------------------------------------SYS1922,331,619125,356569,6883,026,663SYSTEM30810,32519,644163,480993,449-----------------------------------------------sum3,141,944145,000733,1684,020,112SQLGeneratedbyOracleOnourOracle10gdatabase:SQL>SELECTA.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2SUM(B.persistent_mem)persistent_mem,3SUM(B.runtime_mem)runtime_mem,4SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5total_mem6FROMdba_usersA,v$sqlB7WHEREA.usernameIN('DBSNMP','SYS','SYSTEM','SYSMAN')8ANDB.parsing_user_id=A.user_id9GROUPBYA.username;USERNAMECOUNT(*)SHARABLE_MEMPERSISTENT_MEMRUNTIME_MEMTOTAL_MEM-------------------------------------------------------------------DBSNMP994,161,758137,5041,701,0326,000,294SYS69524,402,6271,024,7448,103,49633,530,867SYSMAN67016,644,400806,9044,403,72021,855,024SYSTEM14533,44218,152290,280841,874-----------------------------------------------sum45,742,2271,987,30414,498,52862,228,059OptimizerStatisticsCollectedoptimizerstatisticsweeklyinOracle8i:ANALYZETABLEtable_nameESTIMATESTATISTICSSAMPLE5PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms““automatic.”Thisisallsetupautomaticallyoutofthebox.OptimizerStatistics:CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollect

OptimizerStatisticsOracle8i

(ANALYZE)Oracle10g

(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,926,625HistogramCreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.Oracle10gcreatedlotsofhistograms:SQL>SELECThistogram,COUNT(*)2FROMuser_tab_columns3GROUPBYhistogram;HISTOGRAMCOUNT(*)-------------------------FREQUENCY267HEIGHTBALANCED74NONE1202----------sum1543HistogramCreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswon’tbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclausesSampleSizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.4%.Samplesizesonsmallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%SampleSizeOnourOracle10gdatabase:SQL>SELECTA.table_name,A.num_rows,B.bytes/1024/1024mb,2100*(A.sample_size/A.num_rows)sample_pct3FROMuser_tablesA,user_segmentsB4WHEREA.table_nameIN5('SAMPLE_DATA_FILES','SAMPLE_JOBS',6'COMMON_SQL_PLAN_PARTS','SAMPLE_SQL_TEXTS',7'SAMPLE_LIBRARY_CACHE_STATS')8ANDB.segment_type='TABLE‘9ANDB.segment_name=A.table_name10ORDERBYsample_pct;TABLE_NAMENUM_ROWSMBSAMPLE_PCT---------------------------------------------------------SAMPLE_DATA_FILES14,938,6321,088.000.4SAMPLE_JOBS1,360,42954.004.1COMMON_SQL_PLAN_PARTS174,8519.006.9SAMPLE_LIBRARY_CACHE_STATS1,414,83080.00100.0SAMPLE_SQL_TEXTS6,346,638760.00100.0SampleSizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.OptimizerStatistics:AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10g’’sestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8i’s.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(……justaguess)OptimizerStatistics:AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedon’tknow.QueryOptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewe’vewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.QueryOptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.TheExecutiveSummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.QueryOptimizerChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Well…notyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.QueryOptimizationinDetailSQLthatransimilarlyinOracle8iand10gSQLthatranfasterinOracle10gSQLthatranfasterinOracle8iSQLThatRanSimilarlyLoaderDaemoncomparisonPerformanceSummaryreportcomparisonSeethewhitepaperforTKPROFreportexcerptsLoaderDaemonComparisonLoaderDaemonparses,validates,andloadsfilesfromourmonitoringagentsintothedatabaseforanalysisandreporting.PL/SQLpackageroughly7,800lineslong.7SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedtheLoaderDaemononeachdatabasewhileloadingthesameagentfileintoeach.LoaderDaemonComparisonResourcesUsedbyLoaderDaemon

toLoadOneAgentFile

Oracle8i

Oracle10g

UserSQLstatementstraced110127InternalSQLstatementstraced99UniqueSQLstatementstraced109110TotalOCIcalls1,8001,792CPUseconds3.133.12Logicalreads13,76712,920Physicalreads613LoaderDaemonComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10g:Importmade10gsegmentsmorecompact.MoreuserSQLstatementstracedonOracle10g:Oracle10gdatabasehadsmallerPL/SQLcursorcacheduetobehaviorchangeimplementedinreopen_cursors.(SeeMetalinkdocument274496.1.)Cachemissesleadtoextra(soft)parsecalls.TKPROFreportedtheseextraparsecallsasextratracedstatements.PerformanceReportComparisonPerformanceSummaryreportprovidesasummaryofperformancestatisticsforonemonitoredOracledatabaseoveraspecifiedperiodoftime(likeaStatspackreport).PL/SQLpackageroughly3,200lineslong.4SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedsessionsthatcalledthereportwiththesameparametersoneachdatabase.PerformanceReportComparisonResourcesUsedbyPerformance

SummaryReport

Oracle8i

Oracle10g

UserSQLstatementstraced9898InternalSQLstatementstraced1010UniqueSQLstatementstraced9897TotalOCIcalls654531CPUseconds0.890.88Logicalreads4,6413,661Physicalreads10PerformanceReportComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10gagain.FewertotalOCIcallsinOracle10g:Samenumberofparseandexecutecalls.Oracle8ihadtwiceasmanyfetchcallsas10g.ItappearsasifOracle8ididextrafetchcallstomakesureithadretrievedallrowsfromacursor,whileperhapsOracle10gaskedformorerowsupfront.SQLThatRanFasterin10gWedidnotexpectnoticeableresponsetimeimprovementsonOracle10gbecauseeverythingalreadyran“fastenough”on8i.WeremovedthehintsfromqueriesthathadbeenslowinOracle8itoseeifOracle10gcouldfindtherightexecutionplan.InseveralcasesOracle10gdidbetterthan8ididwithouthints,but10g’sexecutionplanwasstillfarinferiortothatchosenwhenthehintswereinplace.RecentEventNotificationsQueryappearsinseveralreports.Retrievesalistofrecenteventnotificationsforalldatabasestowhichthespecifieduserhasaccess.Joins7tablesandincludesasubquery.TogetthequerytorunefficientlyinOracle8iwehadaddedahinttospecifyjoinorderandwhichjoinalgorithmtouseforeachtable.Notatrivialquery,northemostcomplex.RecentEventNotificationsSELECT/*+ORDEREDINDEX(privs)USE_NL(isaracr)USE_HASH(tl)*/t.test_severity_idseverity,i.instance_id,NVL(privs.instance_nickname,i.current_instance_name)inst_name,ar.first_detected,t.short_descriptionbrief_description,l.report_section_idFROMcustomer_user_instance_privsprivs,customer_instancesi,sampless,analysis_resultsar,analysis_common_resultsacr,analysis_testst,lookup_report_40000_formatsl

WHEREprivs.user_id=:cp_user_idANDprivs.current_cust_user_priv_levelIN('admin','readonly')ANDi.instance_id=privs.instance_id

ANDprivs.user_wishes_to_see='y'ANDs.instance_id=i.instance_idANDs.sample_typeIN('ping','full_stat')ANDs.sample_date_db_local_time>(SELECTs2.sample_date_db_local_time-(i.display_events_for_so_many_hrs/24)FROMsampless2WHEREs2.sample_id=rpt_util.most_recent_analyzed_sample(i.instance_id))ANDar.sample_id=s.sample_idANDacr.analysis_common_result_id=ar.analysis_common_result_id

ANDt.test_id=acr.test_id

ANDt.alert_type='event'ANDl.test_id=t.test_id

ORDERBYseverity,first_detectedDESC,inst_name;RecentEventNotificationsResourcesUsedbyRecentEvent

NotificationsQueryQueryWithHintQueryWithoutHint

Oracle8i

Oracle10g

Oracle8i

Oracle10g

CPUseconds0.100.0951.842.91Logicalreads2,2081,4511,678,0114,111Physicalreads7027,5510RecentEventNotificationsWithoutthehint,Oracle10gdidabetterjobthanOracle8i——butstillnotgoodenough:Good:Oracle10gfiguredouttherighttimetoperformthesubquery.Bad:Oracle10gchoseahashjointoatablewith800,000rowswhennestedloopswastherightwaytogo.Withthehint,Oracle10gdidbetterthanOracle8i(withthehint)byperformingthesubqueryasearlyaspossibleinsteadofaslateaspossible.Oracle8iWithoutHintRowsExecutionPlan----------------------------------------------------------0SELECTSTATEMENTMODE:CHOOSE0SORT(ORDERBY)0FILTER7093HASHJOIN71TABLEACCESSMODE:ANALYZED(FULL)OF'LOOKUP_REPORT_40000_FORMATS'7092HASHJOIN4TABLEACCESSMODE:ANALYZED(FULL)OF'ANALYSIS_TESTS'512382HASHJOIN512382NESTEDLOOPS832470HASHJOIN465504HASHJOIN41TABLEACCESSMODE:ANALYZED(FULL)OF'CUSTOMER_INSTANCES'465504TABLEACCESSMODE:ANALYZED(FULL)OF'SAMPLES'832469INDEXMODE:ANALYZED(FASTFULLSCAN)OF'ANALYSIS_RESULTS_PK'(UNIQUE)512382INDEXMODE:ANALYZED(UNIQUESCAN)OF'CUSTOMER_USER_INST_PRIVS_PK'(UNIQUE)126110INDEXMODE:ANALYZED(FASTFULLSCAN)OF'ANALYSIS_COMMON_RESULTS_N1'(NON-UNIQUE)42TABLEACCESSMODE:ANALYZED(BYINDEXROWID)OF'SAMPLES'42INDEXMODE:ANALYZED(UNIQUESCAN)OF'SAMPLES_PK'(UNIQUE)Oracle10gWithoutHintRowsRowSourceOperation----------------------------------------------------------0SORTORDERBY(cr=4212pr=0pw=0time=3573213us)0HASHJOIN(cr=4212pr=0pw=0time=3573077us)71TABLEACCESSFULLLOOKUP_REPORT_40000_FORMATS(cr=3pr=0pw=0time=489us)0HASHJOIN(cr=4209pr=0pw=0time=3562005us)4TABLEACCESSFULLANALYSIS_TESTS(cr=18pr=0pw=0time=853us)243HASHJOIN(cr=4191pr=0pw=0time=3554047us)126110INDEXFASTFULLSCANANALYSIS_COMMON_RESULTS_N1(cr=341pr=0pw=0time=126363us)(objectid49302)243HASHJOIN(cr=3850pr=0pw=0time=2830427us)343TABLEACCESSBYINDEXROWIDSAMPLES(cr=391pr=0pw=0time=19666us)359NESTEDLOOPS(cr=292pr=0pw=0time=578919us)15NESTEDLOOPS(cr=58pr=0pw=0time=1791us)41TABLEACCESSFULLCUSTOMER_INSTANCES(cr=15pr=0pw=0time=759us)15INDEXUNIQUESCANCUSTOMER_USER_INST_PRIVS_PK(cr=43pr=0pw=0time=1588us)(objectid49663)343INLISTITERATOR(cr=234pr=0pw=0time=40802us)343INDEXRANGESCANSAMPLES_UK2(cr=234pr=0pw=0time=40979us)(objectid49504)14TABLEACCESSBYINDEXROWIDSAMPLES(cr=147pr=0pw=0time=33644us)14INDEXUNIQUESCANSAMPLES_PK(cr=133pr=0pw=0time=33165us)(objectid49501)832469INDEXFASTFULLSCANANALYSIS_RESULTS_PK(cr=3459pr=0pw=0time=1665167us)(objectid49571)SQLThatRanSlowerin10gSQLnoticeablyslowerinveryfewcaseson10g.Areportranunacceptablysloweraftertheupgrade:CPUtimedoubled.Logicalreadsincreasedbyorderofmagnitude.Slowdownattributedtoonequery(whichrunsmanytimes):SELECTB.valueFROMcommon_stat_namesA,sample_sysstatsBWHEREA.name=:p_statnameANDB.common_stat_name_id=A.common_stat_name_idANDB.sample_id=:p_sample_id;SampleStatsQueryOnourOracle8idatabase:callcountcpuelapseddiskquerycurrentrows

-------------------------------------------------------------------

Parse10.000.000000Execute10.000.000000

Fetch20.000.000601-------------------------------------------------------------------

total40.000.000601RowsExecutionPlan----------------------------------------------------------0SELECTSTATEMENTMODE:CHOOSE1NESTEDLOOPS2INDEXMODE:ANALYZED(RANGESCAN)OF'COMMON_STAT_NAMES_PK'(UNIQUE)1INDEXMODE:ANALYZED(UNIQUESCAN)OF'SAMPLE_SYSSTATS_PK'(UNIQUE)SampleStatsQueryOnourOracle10gdatabase:callcountcpuelapseddiskquerycurrentrows

-------------------------------------------------------------------

Parse10.000.000000Execute10.000.000000

Fetch20.010.01024401-------------------------------------------------------------------

total40.010.01024401RowsRowSourceOperation----------------------------------------------------------1NESTEDLOOPS(cr=244pr=0pw=0time=893us)234INDEXRANGESCANSAMPLE_SYSSTATS_PK(cr=5pr=0pw=0time=1152us)1INDEXRANGESCANCOMMON_STAT_NAMES_UK1(cr=239pr=0pw=0time=9472us)SampleStatsQueryWhocaresabouta0.01secondquery?Supposethequeryruns50+timeseachtimeapopularreportisviewed?AddinganORDEREDhinttothequerymadeOracle10gchoosethecorrectexecutionplan.ThesameexactbehavioroccurredinbothourtestandproductionOracle10genvironments.BothtablesinthequeryareIOTs.Oraclehasdeterminedthisis““aproblemwiththeoptimizercachingcostmodel.”SQLTuningAdvisorCoolsoundingOracle10gfeaturethatstudiesaqueryandmakesrecommendations:YoutellAdvisorhowlongtostudythequery.Advisorcouldrecommendrewrite.Advisorcouldcollectadditionalstatisticsthatcanbesavedindatadictionaryasa““profile”tobeusedwheneverthestatementisparsedinthefuture.Opensthedoortofixingbadquerieswithoutmodifyingtheapplicationcode.SQLTuningAdvisorWehadalreadyaddedhintstoallqueriesthatranunacceptablyslow.We’vealreadydiscussedthattakingthosehintsawayinOracle10gledtoinferiorresponsetimes.SowhatifwetookthehintsawayandlettheSQLTuningAdvisorrecommendasolutionforeachtroublesomequery?RecentEventNotificationsSQL>SELECTdbms_sqltune.report_tuning_task2('Tuningcase47696','TEXT','ALL','ALL')3FROMSYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47696','TEXT','ALL','ALL')---------------------------------------------------------------------GENERALINFORMATIONSECTION---------------------------------------------------------------------TuningTaskName:Tuningcase47696TuningTaskID:951Scope:COMPREHENSIVETimeLimit(seconds):600CompletionStatus:COMPLETEDStartedat:01/27/200513:42:34Completedat:01/27/200513:42:48---------------------------------------------------------------------SQLID:b6c2qka14951zSQLText:SELECTt.test_severity_idseverity,i.instance_id,...ORDERBYseverity,first_detectedDESC,inst_name---------------------------------------------------------------------Therearenorecommendationstoimprovethestatement.SampleStatsQuerySQL>SELECTdbms_sqltune.report_tuning_task2('Tuningcase47694','TEXT','ALL','ALL')3FROMSYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47694','TEXT','ALL','ALL')---------------------------------------------------------------------GENERALINFORMATIONSECTION---------------------------------------------------------------------TuningTaskName:Tuningcase47694TuningTaskID:950Scope:COMPREHENSIVETimeLimit(seconds):600CompletionStatus:COMPLETEDStartedat:01/27/200513:32:02Completedat:01/27/200513:32:03---------------------------------------------------------------------SQLID:g5pqqgcuq8pmaSQLText:SELECTB.value/*tuningcase47694*/FROMcommon_stat_namesA,sample_sysstatsBWHEREA.name=:p_statnameANDB.common_stat_name_id=A.common_stat_name_idANDB.sample_id=: