升级专题研究-看在升级前后的系统性能.pptx

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

1、1升级专题研究-看在升级前后的系统性能2023/3/32Todays SessionTheviewfrom30,000feet:OurOracleenvironment,upgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2023/3

2、/33Todays SessionGoal:Help you plan for your own Oracle 10g upgrade.Wewill:Lookatonecompanysexperienceupgradingto10gDiscussreal-lifeexperiencesProvidedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g2023/3/34Always RememberEachOraclesystemisun

3、iqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2023/3/35White Paper2023/3/36The View From 30,000 Fee

4、tOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral2023/3/37Our Oracle EnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bitOneproductionandonedevdatabaseProductiondatabase15Gbinsize2023/3/38Our Oracle EnvironmentApplic

5、ation:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomersdatabaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughly50,000lines)LeveragesOracle8ifeaturesegGTTs,table()About50SQLstatementshavehints2023/3/39Our Or

6、acle EnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,perfor

7、mance,scalability(inouropinion)2023/3/310Our Reasons to Upgrade to 10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingreason.)2023/3/311Our Upgrade StrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8

8、itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable2023/3/312Our Upgrade StrategyO

9、urOracle8iand10gtestdatabasesstartedoutwiththesamedatahandyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.2023/3/313Impressions:Up

10、grade ProcessOracle10gversion10.1.0.2andpatchset10.1.0.3installedverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessaryparameterchanges.IvedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(No

11、te:Solarisplatform!)2023/3/314Impressions:CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutili

12、tyresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.2023/3/315Impressions:Oracle 10gWorkedwelloutofthebox:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasi

13、twasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.2023/3/316Impressions:Oracle 10gBigger,bulkier,hungrierforsystemresources:Biggerexecutablesize,sharedpool,SYSTEMtablespaceMoreoverhead:Daemonprocesses,hardparses,statisticscollectionOverheadandbulk

14、inessweretolerableforus.2023/3/317Impressions:Oracle 10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnot

15、aswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.2023/3/318Impressions:Oracle 10gDiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbette

16、rthanweexpected.Increasedoverheadandheftaremanageableafairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerfeatures.(Butwillproceedinthisdirectionverycautiously!)2023/3/319Upgrade Issues in Greater DetailSizingthesharedp

17、oolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2023/3/320Sizing the Shared Pool and SGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspers

18、econdUnder660hardparsesperdayBuffercachehitratio97%Librarycachehitratio100%2023/3/321Sizing the Shared Pool and SGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationTo

19、olaswell2023/3/322Sizing the Shared Pool and SGAJusttosatisfyacuriosityshared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_

20、pool_size=144Mb2023/3/323Reasons for Larger Shared PoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusageSQLstatementsgeneratedbyOracle2023/3/324Allocation for OverheadAportionofthesharedpoolisusedtoholdinternalmemorystru

21、ctures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecifies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.2

22、023/3/325Allocation 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_p

23、ool_size string 41943040Weveseenthedisparityashighas27%.2023/3/326Shared SQL Area Memory UsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOracle10gthaninOracle8i.Inourenvironmentthedifferencewasalmost2x.Themovefrom32bitOraclesoftwareto64bitaccountsformuchofthisgrowth.Howmuch,w

24、edontknow.2023/3/327Shared 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 WHER

25、E 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 2023/3/328Shared SQL Area Memory UsageOnourOracle10gdatabase:SQL SELECT A.username,COUNT(*),SUM(B.s

26、harable_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_ME

27、M PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM -DBRX_OWNER 360 12,941,006 487,048 3,361,160 16,789,214 2023/3/329SQL Generated by OracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfra

28、structureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.2023/3/330SQL Generated by OracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.Inte

29、rnalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasewasEnterpriseEditionwith“default”optionsinstalled.2023/3/331SQL Generated by OracleOnourOracle8idatabase:SQL SELECT A.username,COUNT(*),SUM(B.sh

30、arable_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 COU

31、NT(*)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 2023/3/332SQL Generated by OracleOnourOracle10gdatabase:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_mem

32、,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 PERSI

33、STENT_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 2023/3/333Optimizer Statistic

34、sCollectedoptimizerstatisticsweeklyinOracle8i:ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyoutofthebo

35、x.2023/3/334Optimizer Statistics:CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollectOptimizerStatisticsOracle8i(ANALYZE)Oracle10g(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,92

36、6,6252023/3/335Histogram CreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.Oracle10gcreatedlotsofhistograms:SQL SELECT histogram,COUNT(*)2 FROM user_tab_columns 3 GROUP BY histogram;HISTOGRAM COUNT(*)-FREQUENCY 267 HEIGHT BALANCE

37、D 74 NONE 1202 -sum 1543 2023/3/336Histogram CreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswontbeuseful:Onunin

38、dexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses2023/3/337Sample SizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.4%.Samplesizesons

39、mallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%2023/3/338Sample 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_tabl

40、es 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,08

41、8.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 2023/3/339Sample SizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherow

42、s.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.2023/3/340Optimizer Statistics:AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccurateth

43、anthosecollectedbyANALYZEinOracle8i.InparticularOracle10gsestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8is.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(justaguess)2023/3/341Optimizer Statistics:AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocesso

44、nyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedontknow.2023/3/342Query OptimizationQueriesinoura

45、pplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewevewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.2023/3/343Query Optimiz

46、ationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.2023/3

47、/344The Executive SummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10

48、gtocauseconcern.2023/3/345Query Optimizer ChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Wellnotyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesf

49、asterwithhintsthanOracle10gdidwithouthints.2023/3/346Query Optimization in DetailSQLthatransimilarlyinOracle8iand10gSQLthatranfasterinOracle10gSQLthatranfasterinOracle8i2023/3/347SQL That Ran SimilarlyLoaderDaemoncomparisonPerformanceSummaryreportcomparisonSeethewhitepaperforTKPROFreportexcerpts2023

50、/3/348Loader Daemon ComparisonLoaderDaemonparses,validates,andloadsfilesfromourmonitoringagentsintothedatabaseforanalysisandreporting.PL/SQLpackageroughly7,800lineslong.7SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedtheLoaderDaemononeachdatabas