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

《升级专题研究-看在升级前后的系统性能(英文).ppt》由会员分享，可在线阅读，更多相关《升级专题研究-看在升级前后的系统性能(英文).ppt（86页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、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:upgradeprocessandcompatibilityImpress

2、ions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2022/12/283Todays SessionGoal:Help you plan for your own Oracle 10g upgrade.Wewill:Lookatonecompanysexperienceupgradingto10gDiscussreal-lifeexperiencesProv

3、idedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g2022/12/284Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperi

4、ences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2022/12/285White PaperContainsadditionaltopicsandexampleswewonthavetimetodiscusstodayContainsadditional“supportingevidence”forconclusionsreachedintodayssessionthatwewonthavetimetodiscussorthatwontfi

5、tlegiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload: View From 30,000 FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral2022/12/287Our Oracle EnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864b

6、itOneproductionandonedevdatabaseProductiondatabase15Gbinsize2022/12/288Our Oracle EnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomersdatabaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(rou

7、ghly50,000lines)LeveragesOracle8ifeaturesegGTTs,table()About50SQLstatementshavehints2022/12/289Our Oracle EnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedandde

8、velopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)2022/12/2810Our Reasons to Upgrade to 10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompelli

9、ngreason.)2022/12/2811Our Upgrade StrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”dat

10、adictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable2022/12/2812Our Upgrade StrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedatahandyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupi

11、edfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.2022/12/2813Impressions:Upgrade ProcessOracle10gversion10.1.0.2andpatchset10.1.0.3installedverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutne

12、cessaryparameterchanges.IvedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)2022/12/2814Impressions:CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompiler

13、didnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.2022/12/2815Impressions:Oracle 10gWorkedwelloutofthebox

14、:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.2022/12/2816Impressions:Oracle 10gBigger,bulkier,hungrierforsystemresources:Biggerex

15、ecutablesize,sharedpool,SYSTEMtablespaceMoreoverhead:Daemonprocesses,hardparses,statisticscollectionOverheadandbulkinessweretolerableforus.2022/12/2817Impressions:Oracle 10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunno

16、ticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.2022/12/2818Impressions:Oracle 10g

17、DiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageableafairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleverag

18、enewerfeatures.(Butwillproceedinthisdirectionverycautiously!)2022/12/2819Upgrade Issues in Greater DetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2022/12/2820Sizing the Shared Pool and SGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8iset

19、tings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio97%Librarycachehitratio100%2022/12/2821Sizing the Shared Pool and SGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbW

20、hy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswell2022/12/2822Sizing the Shared Pool and SGAJusttosatisfyacuriosityshared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted

21、,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144Mb2022/12/2823Reasons for Larger Shared PoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLar

22、eamemoryusageSQLstatementsgeneratedbyOracle2022/12/2824Allocation for OverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_po

23、ol_sizespecifies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.2022/12/2825Allocation for OverheadOnourOracle8idatabasethesharedpoolwasabout3Mb(8%)largerthanspecifiedbyshared_pool_size:SQL SELECT SUM(bytes)/1024/1024 actual_pool_size

24、 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 UsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOrac

25、le10gthaninOracle8i.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)persist

26、ent_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

27、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

28、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 OracleThesharedSQLareaonanyOracleinst

29、ancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.2022/12/2830

30、SQL Generated by OracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasew

31、asEnterpriseEditionwith“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_m

32、em 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

33、 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

34、,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

35、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:Automaticjobr

36、unsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyoutofthebox.2022/12/2834Optimizer Statistics:CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollectOp

37、timizerStatisticsOracle8i(ANALYZE)Oracle10g(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,926,6252022/12/2835Histogram CreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.O

38、racle10gcreatedlotsofhistograms: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_

39、usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswontbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses2022/12/2837Sample SizeSamplesize

40、isanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.4%.Samplesizesonsmallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%20

41、22/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

42、_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_T

43、EXTS 6,346,638 760.00 100.0 2022/12/2839Sample SizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaC

44、OUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.2022/12/2840Optimizer Statistics:AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10gsestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8is.Couldhavebe

45、encausedbyexcessivelysmallsamplesizeonsometables(justaguess)2022/12/2841Optimizer Statistics:AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizers

46、tatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedontknow.2022/12/2842Query OptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewevewrittenpractical,logi

47、calSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.2022/12/2843Query OptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOrac

48、le9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.2022/12/2844The Executive SummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somes

49、tatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.2022/12/2845Query Optimizer ChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOra

50、cle8i?Isaddinghintstoqueriesathingofthepast?Wellnotyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.2022/12/2846Query Optimization in DetailSQLthatransimilarlyinOracle8iand10gSQLthatra