升级专题研究-看在升级前后的系统性能(英文).ppt

2022/12/281An Oracle 10g Upgrade Case Study:Looking at System Performance Before and After the UpgradeRoger SchragDatabase Specialists,Inc.NoCOUG Spring Conference 20052022/12/282Todays SessionTheviewfrom30,000feet:OurOracleenvironment,upgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2022/12/283Todays SessionGoal:Help you plan for your own Oracle 10g upgrade.Wewill:Lookatonecompanysexperienceupgradingto10gDiscussreal-lifeexperiencesProvidedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g2022/12/284Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2022/12/285White PaperContainsadditionaltopicsandexampleswewonthavetimetodiscusstodayContainsadditional“supportingevidence”forconclusionsreachedintodayssessionthatwewonthavetimetodiscussorthatwontfitlegiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload: View From 30,000 FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral2022/12/287Our Oracle EnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bitOneproductionandonedevdatabaseProductiondatabase15Gbinsize2022/12/288Our Oracle EnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomersdatabaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughly50,000lines)LeveragesOracle8ifeaturesegGTTs,table()About50SQLstatementshavehints2022/12/289Our Oracle EnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)2022/12/2810Our Reasons to Upgrade to 10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingreason.)2022/12/2811Our Upgrade StrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable2022/12/2812Our Upgrade StrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedatahandyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.2022/12/2813Impressions:Upgrade ProcessOracle10gversion10.1.0.2andpatchset10.1.0.3installedverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessaryparameterchanges.IvedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)2022/12/2814Impressions:CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.2022/12/2815Impressions:Oracle 10gWorkedwelloutofthebox:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.2022/12/2816Impressions:Oracle 10gBigger,bulkier,hungrierforsystemresources:Biggerexecutablesize,sharedpool,SYSTEMtablespaceMoreoverhead:Daemonprocesses,hardparses,statisticscollectionOverheadandbulkinessweretolerableforus.2022/12/2817Impressions:Oracle 10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.2022/12/2818Impressions:Oracle 10gDiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageableafairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerfeatures.(Butwillproceedinthisdirectionverycautiously!)2022/12/2819Upgrade Issues in Greater DetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2022/12/2820Sizing the Shared Pool and SGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio97%Librarycachehitratio100%2022/12/2821Sizing the Shared Pool and SGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswell2022/12/2822Sizing the Shared Pool and SGAJusttosatisfyacuriosityshared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144Mb2022/12/2823Reasons for Larger Shared PoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusageSQLstatementsgeneratedbyOracle2022/12/2824Allocation for OverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecifies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.2022/12/2825Allocation for OverheadOnourOracle8idatabasethesharedpoolwasabout3Mb(8%)largerthanspecifiedbyshared_pool_size:SQL SELECT SUM(bytes)/1024/1024 actual_pool_size 2 FROM v$sgastat 3 WHERE pool=shared pool;ACTUAL_POOL_SIZE -43.1291847 SQL SHOW PARAMETER shared_pool_size NAME TYPE VALUE -shared_pool_size string 41943040Weveseenthedisparityashighas27%.2022/12/2826Shared SQL Area Memory UsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOracle10gthaninOracle8i.Inourenvironmentthedifferencewasalmost2x.Themovefrom32bitOraclesoftwareto64bitaccountsformuchofthisgrowth.Howmuch,wedontknow.2022/12/2827Shared SQL Area Memory UsageOnourOracle8idatabase:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2 SUM(B.persistent_mem)persistent_mem,3 SUM(B.runtime_mem)runtime_mem,4 SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5 total_mem 6 FROM dba_users A,v$sql B 7 WHERE A.username=DBRX_OWNER 8 AND B.parsing_user_id=A.user_id 9 GROUP BY A.username;USERNAME COUNT(*)SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM -DBRX_OWNER 362 6,275,020 256,176 1,996,324 8,527,520 2022/12/2828Shared SQL Area Memory UsageOnourOracle10gdatabase:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2 SUM(B.persistent_mem)persistent_mem,3 SUM(B.runtime_mem)runtime_mem,4 SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5 total_mem 6 FROM dba_users A,v$sql B 7 WHERE A.username=DBRX_OWNER 8 AND B.parsing_user_id=A.user_id 9 GROUP BY A.username;USERNAME COUNT(*)SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM -DBRX_OWNER 360 12,941,006 487,048 3,361,160 16,789,214 2022/12/2829SQL Generated by OracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.2022/12/2830SQL Generated by OracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasewasEnterpriseEditionwith“default”optionsinstalled.2022/12/2831SQL Generated by OracleOnourOracle8idatabase:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2 SUM(B.persistent_mem)persistent_mem,3 SUM(B.runtime_mem)runtime_mem,4 SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5 total_mem 6 FROM dba_users A,v$sql B 7 WHERE A.username IN(DBSNMP,SYS,SYSTEM,SYSMAN)8 AND B.parsing_user_id=A.user_id 9 GROUP BY A.username;USERNAME COUNT(*)SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM -SYS 192 2,331,619 125,356 569,688 3,026,663 SYSTEM 30 810,325 19,644 163,480 993,449 -sum 3,141,944 145,000 733,168 4,020,112 2022/12/2832SQL Generated by OracleOnourOracle10gdatabase:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_mem,2 SUM(B.persistent_mem)persistent_mem,3 SUM(B.runtime_mem)runtime_mem,4 SUM(B.sharable_mem+B.persistent_mem+B.runtime_mem)5 total_mem 6 FROM dba_users A,v$sql B 7 WHERE A.username IN(DBSNMP,SYS,SYSTEM,SYSMAN)8 AND B.parsing_user_id=A.user_id 9 GROUP BY A.username;USERNAME COUNT(*)SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM -DBSNMP 99 4,161,758 137,504 1,701,032 6,000,294 SYS 695 24,402,627 1,024,744 8,103,496 33,530,867 SYSMAN 670 16,644,400 806,904 4,403,720 21,855,024 SYSTEM 14 533,442 18,152 290,280 841,874 -sum 45,742,227 1,987,304 14,498,528 62,228,059 2022/12/2833Optimizer StatisticsCollectedoptimizerstatisticsweeklyinOracle8i:ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyoutofthebox.2022/12/2834Optimizer Statistics:CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollectOptimizerStatisticsOracle8i(ANALYZE)Oracle10g(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,926,6252022/12/2835Histogram CreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.Oracle10gcreatedlotsofhistograms:SQL SELECT histogram,COUNT(*)2 FROM user_tab_columns 3 GROUP BY histogram;HISTOGRAM COUNT(*)-FREQUENCY 267 HEIGHT BALANCED 74 NONE 1202 -sum 1543 2022/12/2836Histogram CreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswontbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses2022/12/2837Sample SizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.4%.Samplesizesonsmallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%2022/12/2838Sample SizeOnourOracle10gdatabase:SQL SELECT A.table_name,A.num_rows,B.bytes/1024/1024 mb,2 100*(A.sample_size/A.num_rows)sample_pct 3 FROM user_tables A,user_segments B 4 WHERE A.table_name IN 5 (SAMPLE_DATA_FILES,SAMPLE_JOBS,6 COMMON_SQL_PLAN_PARTS,SAMPLE_SQL_TEXTS,7 SAMPLE_LIBRARY_CACHE_STATS)8 AND B.segment_type=TABLE 9 AND B.segment_name=A.table_name 10 ORDER BY sample_pct;TABLE_NAME NUM_ROWS MB SAMPLE_PCT -SAMPLE_DATA_FILES 14,938,632 1,088.00 0.4 SAMPLE_JOBS 1,360,429 54.00 4.1 COMMON_SQL_PLAN_PARTS 174,851 9.00 6.9 SAMPLE_LIBRARY_CACHE_STATS 1,414,830 80.00 100.0 SAMPLE_SQL_TEXTS 6,346,638 760.00 100.0 2022/12/2839Sample SizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.2022/12/2840Optimizer Statistics:AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10gsestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8is.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(justaguess)2022/12/2841Optimizer Statistics:AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedontknow.2022/12/2842Query OptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewevewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.2022/12/2843Query OptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.2022/12/2844The Executive SummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.2022/12/2845Query Optimizer ChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Wellnotyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.2022/12/2846Query Optimization in DetailSQLthatransimilarlyinOracle8iand10gSQLthatra