升級(jí)專題研究看在升級(jí)前后的系統(tǒng)性能

AnOracle10gUpgradeCaseStudy:LookingatSystemPerformanceBeforeandAftertheUpgradeNoCOUGSpringConference2005第一頁(yè)，共八十七頁(yè)。Today'sSessionTheviewfrom30,000feet:OurOracleenvironment,upgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead第二頁(yè)，共八十七頁(yè)。Today’sSessionGoal:HelpyouplanforyourownOracle10gupgrade.Wewill:

Lookatonecompany’sexperienceupgradingto10gDiscussreal-lifeexperiencesProvidedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g第三頁(yè)，共八十七頁(yè)。AlwaysRememberEachOraclesystemisuniqueandwillhaveitsownchallenges.Nevertakesomebodyelse’swordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompany’sexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryit—inatestenvironment—andsee.第四頁(yè)，共八十七頁(yè)。WhitePaper第五頁(yè)，共八十七頁(yè)。TheViewFrom30,000FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral第六頁(yè)，共八十七頁(yè)。OurOracleEnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bitOneproductionandonedevdatabaseProductiondatabase15Gbinsize第七頁(yè)，共八十七頁(yè)。OurOracleEnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomers’databaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughly50,000lines)LeveragesOracle8ifeatures—egGTTs,table()About50SQLstatementshavehints第八頁(yè)，共八十七頁(yè)。OurOracleEnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)第九頁(yè)，共八十七頁(yè)。OurReasonstoUpgradeto10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingreason.)第十頁(yè)，共八十七頁(yè)。OurUpgradeStrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable第十一頁(yè)，共八十七頁(yè)。OurUpgradeStrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedata—handyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.第十二頁(yè)，共八十七頁(yè)。Impressions:UpgradeProcessOracle10gversionandpatchsetinstalledverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessaryparameterchanges.I'vedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)第十三頁(yè)，共八十七頁(yè)。Impressions:CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.第十四頁(yè)，共八十七頁(yè)。Impressions:Oracle10gWorkedwelloutofthebox:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.第十五頁(yè)，共八十七頁(yè)。Impressions:Oracle10gBigger,bulkier,hungrierforsystemresources:Biggerexecutablesize,sharedpool,SYSTEMtablespace…Moreoverhead:Daemonprocesses,hardparses,statisticscollection…Overheadandbulkinessweretolerableforus.第十六頁(yè)，共八十七頁(yè)。Impressions:Oracle10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.第十七頁(yè)，共八十七頁(yè)。Impressions:Oracle10gDiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageable—afairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerfeatures.(Butwillproceedinthisdirectionverycautiously!)第十八頁(yè)，共八十七頁(yè)。UpgradeIssuesinGreaterDetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead第十九頁(yè)，共八十七頁(yè)。SizingtheSharedPoolandSGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio>97%Librarycachehitratio~100%第二十頁(yè)，共八十七頁(yè)。SizingtheSharedPoolandSGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswell第二十一頁(yè)，共八十七頁(yè)。SizingtheSharedPoolandSGAJusttosatisfyacuriosity…shared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144Mb第二十二頁(yè)，共八十七頁(yè)。ReasonsforLargerSharedPoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusageSQLstatementsgeneratedbyOracle第二十三頁(yè)，共八十七頁(yè)。AllocationforOverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecifies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.第二十四頁(yè)，共八十七頁(yè)。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%.第二十五頁(yè)，共八十七頁(yè)。SharedSQLAreaMemoryUsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOracle10gthaninOracle8i.Inourenvironmentthedifferencewasalmost2x.Themovefrom32bitOraclesoftwareto64bitaccountsformuchofthisgrowth.Howmuch,wedon’tknow.第二十六頁(yè)，共八十七頁(yè)。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,520第二十七頁(yè)，共八十七頁(yè)。SharedSQLAreaMemoryUsageOnourOracle10gdatabase: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,214第二十八頁(yè)，共八十七頁(yè)。SQLGeneratedbyOracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.第二十九頁(yè)，共八十七頁(yè)。SQLGeneratedbyOracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasewasEnterpriseEditionwith“default”optionsinstalled.第三十頁(yè)，共八十七頁(yè)。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,112第三十一頁(yè)，共八十七頁(yè)。SQLGeneratedbyOracleOnourOracle10gdatabase: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,059第三十二頁(yè)，共八十七頁(yè)。OptimizerStatisticsCollectedoptimizerstatisticsweeklyinOracle8i:

ANALYZETABLEtable_nameESTIMATESTATISTICSSAMPLE5PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyoutofthebox.第三十三頁(yè)，共八十七頁(yè)。OptimizerStatistics:CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollect

OptimizerStatisticsOracle8i

(ANALYZE)Oracle10g

(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,926,625第三十四頁(yè)，共八十七頁(yè)。HistogramCreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.Oracle10gcreatedlotsofhistograms:SQL>SELECThistogram,COUNT(*)2FROMuser_tab_columns3GROUPBYhistogram;HISTOGRAMCOUNT(*)-------------------------FREQUENCY267HEIGHTBALANCED74NONE1202----------sum1543第三十五頁(yè)，共八十七頁(yè)。HistogramCreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswon’tbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses第三十六頁(yè)，共八十七頁(yè)。SampleSizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.4%.Samplesizesonsmallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%第三十七頁(yè)，共八十七頁(yè)。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.0第三十八頁(yè)，共八十七頁(yè)。SampleSizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.第三十九頁(yè)，共八十七頁(yè)。OptimizerStatistics:AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10g’sestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8i’s.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(…justaguess)第四十頁(yè)，共八十七頁(yè)。OptimizerStatistics:AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedon’tknow.第四十一頁(yè)，共八十七頁(yè)。QueryOptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewe’vewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.第四十二頁(yè)，共八十七頁(yè)。QueryOptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.第四十三頁(yè)，共八十七頁(yè)。TheExecutiveSummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.第四十四頁(yè)，共八十七頁(yè)。QueryOptimizerChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Well…notyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.第四十五頁(yè)，共八十七頁(yè)。QueryOptimizationinDetailSQLthatransimilarlyinOracle8iand10gSQLthatranfasterinOracle10gSQLthatranfasterinOracle8i第四十六頁(yè)，共八十七頁(yè)。SQLThatRanSimilarlyLoaderDaemoncomparisonPerformanceSummaryreportcomparisonSeethewhitepaperforTKPROFreportexcerpts第四十七頁(yè)，共八十七頁(yè)。LoaderDaemonComparisonLoaderDaemonparses,validates,andloadsfilesfromourmonitoringagentsintothedatabaseforanalysisandreporting.PL/SQLpackageroughly7,800lineslong.7SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedtheLoaderDaemononeachdatabasewhileloadingthesameagentfileintoeach.第四十八頁(yè)，共八十七頁(yè)。LoaderDaemonComparisonResourcesUsedbyLoaderDaemon

toLoadOneAgentFile

Oracle8i

Oracle10g

UserSQLstatementstraced110127InternalSQLstatementstraced99UniqueSQLstatementstraced109110TotalOCIcalls1,8001,792CPUseconds3.133.12Logicalreads13,76712,920Physicalreads613第四十九頁(yè)，共八十七頁(yè)。LoaderDaemonComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10g:Importmade10gsegmentsmorecompact.MoreuserSQLstatementstracedonOracle10g:Oracle10gdatabasehadsmallerPL/SQLcursorcacheduetobehaviorchangeimplementedinreopen_cursors.(SeeMetalinkdocument274496.1.)Cachemissesleadtoextra(soft)parsecalls.TKPROFreportedtheseextraparsecallsasextratracedstatements.第五十頁(yè)，共八十七頁(yè)。PerformanceReportComparisonPerformanceSummaryreportprovidesasummaryofperformancestatisticsforonemonitoredOracledatabaseoveraspecifiedperiodoftime(likeaStatspackreport).PL/SQLpackageroughly3,200lineslong.4SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedsessionsthatcalledthereportwiththesameparametersoneachdatabase.第五十一頁(yè)，共八十七頁(yè)。PerformanceReportComparisonResourcesUsedbyPerformance

SummaryReport

Oracle8i

Oracle10g

UserSQLstatementstraced9898InternalSQLstatementstraced1010UniqueSQLstatementstraced9897TotalOCIcalls654531CPUseconds0.890.88Logicalreads4,6413,661Physicalreads10第五十二頁(yè)，共八十七頁(yè)。PerformanceReportComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10gagain.FewertotalOCIcallsinOracle10g:Samenumberofparseandexecutecalls.Oracle8ihadtwiceasmanyfetchcallsas10g.ItappearsasifOracle8ididextrafetchcallstomakesureithadretrievedallrowsfromacursor,whileperhapsOracle10gaskedformorerowsupfront.第五十三頁(yè)，共八十七頁(yè)。SQLThatRanFasterin10gWedidnotexpectnoticeableresponsetimeimprovementsonOracle10gbecauseeverythingalreadyran“fastenough”on8i.WeremovedthehintsfromqueriesthathadbeenslowinOracle8itoseeifOracle10gcouldfindtherightexecutionplan.InseveralcasesOracle10gdidbetterthan8ididwithouthints,but10g’sexecutionplanwasstillfarinferiortothatchosenwhenthehintswereinplace.第五十四頁(yè)，共八十七頁(yè)。RecentEventNotificationsQueryappearsinseveralreports.Retrievesalistofrecenteventnotificationsforalldatabasestowhichthespecifieduserhasaccess.Joins7tablesandincludesasubquery.TogetthequerytorunefficientlyinOracle8iwehadaddedahinttospecifyjoinorderandwhichjoinalgorithmtouseforeachtable.Notatrivialquery,northemostcomplex.第五十五頁(yè)，共八十七頁(yè)。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_id

FROMcustomer_user_instance_privsprivs,customer_instancesi,

sampless,analysis_resultsar,analysis_common_resultsacr,

analysis_testst,lookup_report_40000_formatsl

WHEREprivs.user_id=:cp_user_id

ANDprivs.current_cust_user_priv_levelIN('admin','readonly')

ANDi.instance_id=privs.instance_id

ANDprivs.user_wishes_to_see='y'

ANDs.instance_id=i.instance_id

ANDs.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)

FROMsampless2

WHEREs2.sample_id=rpt_util.most_recent_analyzed_sample(i.instance_id)

)

ANDar.sample_id=s.sample_id

ANDacr.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;

第五十六頁(yè)，共八十七頁(yè)。RecentEventNotificationsResourcesUsedbyRecentEvent

NotificationsQueryQueryWithHintQueryWithoutHint

Oracle8i

Oracle10g

Oracle8i

Oracle10g

CPUseconds0.100.0951.842.91Logicalreads2,2081,4511,678,0114,111Physicalreads7027,5510第五十七頁(yè)，共八十七頁(yè)。RecentEventNotificationsWithoutthehint,Oracle10gdidabetterjobthanOracle8i—butstillnotgoodenough:Good:Oracle10gfiguredouttherighttimetoperformthesubquery.Bad:Oracle10gchoseahashjointoatablewith800,000rowswhennestedloopswastherightwaytogo.Withthehint,Oracle10gdidbetterthanOracle8i(withthehint)byperformingthesubqueryasearlyaspossibleinsteadofaslateaspossible.第五十八頁(yè)，共八十七頁(yè)。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)第五十九頁(yè)，共八十七頁(yè)。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)14INDEXUNI