《升级专题研究看在升级前后的系统性能英文课件》由会员分享，可在线阅读，更多相关《升级专题研究看在升级前后的系统性能英文课件（86页珍藏版）》请在金锄头文库上搜索。
1、2024/8/141An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the UpgradeRoger SchragDatabase Specialists, Inc.NoCOUG Spring Conference 20052024/8/142Todays SessionTheviewfrom30,000feet:OurOracleenvironment,upgradestrategyImpressions:upgradeprocessandcompatibilityImpress
2、ions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2024/8/143Todays SessionGoal: Help you plan for your own Oracle 10g upgrade.Wewill: Lookatonecompanysexperienceupgradingto10gDiscussreal-lifeexperiencesPro
3、videdatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g2024/8/144Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperi
4、ences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2024/8/145White PaperContainsadditionaltopicsandexampleswewonthavetimetodiscusstodayContainsadditional“supportingevidence”forconclusionsreachedintodayssessionthatwewonthavetimetodiscussorthatwontfit
5、legiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload: View From 30,000 FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral2024/8/147Our Oracle EnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bit
6、OneproductionandonedevdatabaseProductiondatabase15Gbinsize2024/8/148Our Oracle EnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomersdatabaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughl
7、y50,000lines)LeveragesOracle8ifeaturesegGTTs,table()About50SQLstatementshavehints2024/8/149Our Oracle EnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelo
8、pedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)2024/8/1410Our Reasons to Upgrade to 10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingrea
9、son.)2024/8/1411Our Upgrade StrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadicti
10、onary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable2024/8/1412Our Upgrade StrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedatahandyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewer
11、blocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.2024/8/1413Impressions: Upgrade ProcessOracle10gversion10.1.0.2andpatchset10.1.0.3installedverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessary
12、parameterchanges.IvedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)2024/8/1414Impressions: CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotl
13、ikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.2024/8/1415Impressions: Oracle 10gWorkedwelloutofthebox:Enterp
14、riseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.2024/8/1416Impressions: Oracle 10gBigger,bulkier,hungrierforsystemresources:Biggerexecutabl
15、esize,sharedpool,SYSTEMtablespaceMoreoverhead:Daemonprocesses,hardparses,statisticscollectionOverheadandbulkinessweretolerableforus.2024/8/1417Impressions: Oracle 10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeabl
16、yfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.2024/8/1418Impressions: Oracle 10gDiscour
17、agedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageableafairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerf
18、eatures.(Butwillproceedinthisdirectionverycautiously!)2024/8/1419Upgrade Issues in Greater DetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2024/8/1420Sizing the Shared Pool and SGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:sha
19、red_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio97%Librarycachehitratio100%2024/8/1421Sizing the Shared Pool and SGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimum
20、shared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswell2024/8/1422Sizing the Shared Pool and SGAJusttosatisfyacuriosityshared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequen
21、tORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144Mb2024/8/1423Reasons for Larger Shared PoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusag
22、eSQLstatementsgeneratedbyOracle2024/8/1424Allocation for OverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecif
23、ies.ThusOracle10ggivesyoulessusablespaceinthesharedpoolforthesameshared_pool_sizesetting.SeeMetalinkdocument270935.1.2024/8/1425Allocation for OverheadOnourOracle8idatabasethesharedpoolwasabout3Mb(8%)largerthanspecifiedbyshared_pool_size: SQL SELECT SUM (bytes) / 1024 / 1024 actual_pool_size 2 FROM
24、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%.2024/8/1426Shared SQL Area Memory UsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOrac
25、le10gthaninOracle8i.Inourenvironmentthedifferencewasalmost2x.Themovefrom32bitOraclesoftwareto64bitaccountsformuchofthisgrowth.Howmuch,wedontknow.2024/8/1427Shared SQL Area Memory UsageOnourOracle8idatabase: SQL SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem)
26、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 TO
27、TAL_MEM - - - - - - DBRX_OWNER 362 6,275,020 256,176 1,996,324 8,527,520 2024/8/1428Shared 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_me
28、m + 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,
29、214 2024/8/1429SQL Generated by OracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpool
30、willneedtobelargerinordertoaccommodatetheextrastatements.2024/8/1430SQL Generated by OracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewas
31、StandardEditionwithminimaloptionsinstalled.TheOracle10gtestdatabasewasEnterpriseEditionwith“default”optionsinstalled.2024/8/1431SQL 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_
32、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 - - -
33、 - - - 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 2024/8/1432SQL Generated by OracleOnourOracle10gdatabase: SQL SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem) persisten
34、t_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 RUN
35、TIME_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 2024/8/1433Optimizer Sta
36、tisticsCollectedoptimizerstatisticsweeklyinOracle8i:ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyouto
37、fthebox.2024/8/1434Optimizer Statistics: CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollectOptimizerStatisticsOracle8i(ANALYZE)Oracle10g(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545
38、,8442,926,6252024/8/1435Histogram CreationHistogramsareonereasonstatisticscollectioninOracle10gissomuchmoreexpensive:OursetuponOracle8icreatednohistograms.Oracle10gcreatedlotsofhistograms: SQL SELECT histogram, COUNT(*) 2 FROM user_tab_columns 3 GROUP BY histogram; HISTOGRAM COUNT(*) - - FREQUENCY 2
39、67 HEIGHT BALANCED 74 NONE 1202 - sum 1543 2024/8/1436Histogram CreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistograms
40、wontbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses2024/8/1437Sample SizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5t
41、o5.4%.Samplesizesonsmallertableswere100%.Oracle10gsamplesizeswerealloverthemap:Samplesizeon80Mbtable:100%Samplesizeon1,088Mbtable:0.4%Samplesizeon760Mbtable:100%2024/8/1438Sample SizeOnourOracle10gdatabase: SQL SELECT A.table_name, A.num_rows, B.bytes / 1024 / 1024 mb, 2 100 * (A.sample_size / A.num
42、_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
43、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 2024/8/1439Sample SizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oracl
44、esampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.2024/8/1440Optimizer Statistics: AccuracyOracle10goptimi
45、zerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10gsestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8is.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(justaguess)2024/8/1441Optimizer Statistics: AccuracyHowaccu
46、ratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatis
47、tics?Wedontknow.2024/8/1442Query OptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewevewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statement
48、sthroughouttheapplicationhavehints.2024/8/1443Query OptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,ito
49、nlytakesonebadexecutionplantoslowthewholeprocessdown.2024/8/1444The Executive SummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.Mostworkloadoperationsyieldedsimilarresponsetimesinbothversio
50、nsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.2024/8/1445Query Optimizer ChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Wellnotyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOr
51、acle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.2024/8/1446Query Optimization in DetailSQLthatransimilarlyinOracle8iand10gSQLthatranfasterinOracle10gSQLthatranfasterinOracle8i2024/8/1447SQL That Ran SimilarlyLoaderDaemoncomparisonPerformanceSum
52、maryreportcomparisonSeethewhitepaperforTKPROFreportexcerpts2024/8/1448Loader Daemon ComparisonLoaderDaemonparses,validates,andloadsfilesfromourmonitoringagentsintothedatabaseforanalysisandreporting.PL/SQLpackageroughly7,800lineslong.7SQLstatementsinthepackagehavehints.Startingoutwiththesamedatainthe
53、Oracle8iand10gtestdatabases,wetracedtheLoaderDaemononeachdatabasewhileloadingthesameagentfileintoeach.2024/8/1449Loader Daemon ComparisonResourcesUsedbyLoaderDaemontoLoadOneAgentFileOracle8iOracle10gUserSQLstatementstraced110127InternalSQLstatementstraced99UniqueSQLstatementstraced109110TotalOCIcall
54、s1,8001,792CPUseconds3.133.12Logicalreads13,76712,920Physicalreads6132024/8/1450Loader Daemon ComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10g:Importmade10gsegmentsmorecompact.MoreuserSQLstatementstracedonOracle10g:Oracle10gdatabasehadsm
55、allerPL/SQLcursorcacheduetobehaviorchangeimplementedin9.2.0.5reopen_cursors.(SeeMetalinkdocument274496.1.)Cachemissesleadtoextra(soft)parsecalls.TKPROFreportedtheseextraparsecallsasextratracedstatements.2024/8/1451Performance Report ComparisonPerformanceSummaryreportprovidesasummaryofperformancestat
56、isticsforonemonitoredOracledatabaseoveraspecifiedperiodoftime(likeaStatspackreport).PL/SQLpackageroughly3,200lineslong.4SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedsessionsthatcalledthereportwiththesameparametersoneachdatabase.2024/8/1452Perf
57、ormance Report Comparison ResourcesUsedbyPerformanceSummaryReportOracle8iOracle10gUserSQLstatementstraced9898InternalSQLstatementstraced1010UniqueSQLstatementstraced9897TotalOCIcalls654531CPUseconds0.890.88Logicalreads4,6413,661Physicalreads102024/8/1453Performance Report ComparisonBusinessprocessga
58、veroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10gagain.FewertotalOCIcallsinOracle10g:Samenumberofparseandexecutecalls.Oracle8ihadtwiceasmanyfetchcallsas10g.ItappearsasifOracle8ididextrafetchcallstomakesureithadretrievedallrowsfromacursor,whileperhapsOracle10gaskedf
59、ormorerowsupfront.2024/8/1454SQL That Ran Faster in 10gWedidnotexpectnoticeableresponsetimeimprovementsonOracle10gbecauseeverythingalreadyran“fastenough”on8i.WeremovedthehintsfromqueriesthathadbeenslowinOracle8itoseeifOracle10gcouldfindtherightexecutionplan.InseveralcasesOracle10gdidbetterthan8ididw
60、ithouthints,but10gsexecutionplanwasstillfarinferiortothatchosenwhenthehintswereinplace.2024/8/1455Recent Event NotificationsQueryappearsinseveralreports.Retrievesalistofrecenteventnotificationsforalldatabasestowhichthespecifieduserhasaccess.Joins7tablesandincludesasubquery.Togetthequerytorunefficien
61、tlyinOracle8iwehadaddedahinttospecifyjoinorderandwhichjoinalgorithmtouseforeachtable.Notatrivialquery,northemostcomplex.2024/8/1456Recent Event Notifications SELECT /*+ ORDERED INDEX (privs) USE_NL (i s ar acr) USE_HASH (t l) */ t.test_severity_id severity, i.instance_id, NVL (privs.instance_nicknam
62、e, i.current_instance_name) inst_name, ar.first_detected, t.short_description brief_description, l.report_section_idFROM customer_user_instance_privs privs, customer_instances i, samples s, analysis_results ar, analysis_common_results acr, analysis_tests t, lookup_report_40000_formats lWHERE privs.u
63、ser_id = :cp_user_idAND privs.current_cust_user_priv_level IN (admin, read only)AND i.instance_id = privs.instance_idAND privs.user_wishes_to_see = yAND s.instance_id = i.instance_idAND s.sample_type IN (ping, full_stat)AND s.sample_date_db_local_time ( SELECT s2.sample_date_db_local_time - (i.displ
64、ay_events_for_so_many_hrs / 24) FROM samples s2 WHERE s2.sample_id = rpt_util.most_recent_analyzed_sample (i.instance_id) )AND ar.sample_id = s.sample_idAND acr.analysis_common_result_id = ar.analysis_common_result_idAND t.test_id = acr.test_idAND t.alert_type = eventAND l.test_id = t.test_idORDER B
65、Y severity, first_detected DESC, inst_name; 2024/8/1457Recent Event Notifications ResourcesUsedbyRecentEventNotificationsQueryQueryWithHintQueryWithoutHintOracle8iOracle10gOracle8iOracle10gCPUseconds0.100.0951.842.91Logicalreads2,2081,4511,678,0114,111Physicalreads7027,55102024/8/1458Recent Event No
66、tificationsWithoutthehint,Oracle10gdidabetterjobthanOracle8ibutstillnotgoodenough:Good:Oracle10gfiguredouttherighttimetoperformthesubquery.Bad:Oracle10gchoseahashjointoatablewith800,000rowswhennestedloopswastherightwaytogo.Withthehint,Oracle10gdidbetterthanOracle8i(withthehint)byperformingthesubquer
67、yasearlyaspossibleinsteadofaslateaspossible.2024/8/1459Oracle 8i Without Hint Rows Execution Plan- - 0 SELECT STATEMENT MODE: CHOOSE 0 SORT (ORDER BY) 0 FILTER 7093 HASH JOIN 71 TABLE ACCESS MODE: ANALYZED (FULL) OF LOOKUP_REPORT_40000_FORMATS 7092 HASH JOIN 4 TABLE ACCESS MODE: ANALYZED (FULL) OF A
68、NALYSIS_TESTS 512382 HASH JOIN 512382 NESTED LOOPS 832470 HASH JOIN 465504 HASH JOIN 41 TABLE ACCESS MODE: ANALYZED (FULL) OF CUSTOMER_INSTANCES 465504 TABLE ACCESS MODE: ANALYZED (FULL) OF SAMPLES 832469 INDEX MODE: ANALYZED (FAST FULL SCAN) OF ANALYSIS_RESULTS_PK (UNIQUE) 512382 INDEX MODE: ANALYZ
69、ED (UNIQUE SCAN) OF CUSTOMER_USER_INST_PRIVS_PK (UNIQUE) 126110 INDEX MODE: ANALYZED (FAST FULL SCAN) OF ANALYSIS_COMMON_RESULTS_N1 (NON-UNIQUE) 42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF SAMPLES 42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF SAMPLES_PK (UNIQUE)2024/8/1460Oracle 10g Without Hint R
70、ows Row Source Operation- - 0 SORT ORDER BY (cr=4212 pr=0 pw=0 time=3573213 us) 0 HASH JOIN (cr=4212 pr=0 pw=0 time=3573077 us) 71 TABLE ACCESS FULL LOOKUP_REPORT_40000_FORMATS (cr=3 pr=0 pw=0 time=489 us) 0 HASH JOIN (cr=4209 pr=0 pw=0 time=3562005 us) 4 TABLE ACCESS FULL ANALYSIS_TESTS (cr=18 pr=0
71、 pw=0 time=853 us) 243 HASH JOIN (cr=4191 pr=0 pw=0 time=3554047 us) 126110 INDEX FAST FULL SCAN ANALYSIS_COMMON_RESULTS_N1 (cr=341 pr=0 pw=0 time=126363 us)(object id 49302) 243 HASH JOIN (cr=3850 pr=0 pw=0 time=2830427 us) 343 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=391 pr=0 pw=0 time=19666 us) 35
72、9 NESTED LOOPS (cr=292 pr=0 pw=0 time=578919 us) 15 NESTED LOOPS (cr=58 pr=0 pw=0 time=1791 us) 41 TABLE ACCESS FULL CUSTOMER_INSTANCES (cr=15 pr=0 pw=0 time=759 us) 15 INDEX UNIQUE SCAN CUSTOMER_USER_INST_PRIVS_PK (cr=43 pr=0 pw=0 time=1588 us)(object id 49663) 343 INLIST ITERATOR (cr=234 pr=0 pw=0
73、 time=40802 us) 343 INDEX RANGE SCAN SAMPLES_UK2 (cr=234 pr=0 pw=0 time=40979 us)(object id 49504) 14 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=147 pr=0 pw=0 time=33644 us) 14 INDEX UNIQUE SCAN SAMPLES_PK (cr=133 pr=0 pw=0 time=33165 us)(object id 49501) 832469 INDEX FAST FULL SCAN ANALYSIS_RESULTS_PK
74、 (cr=3459 pr=0 pw=0 time=1665167 us)(object id 49571)2024/8/1461SQL That Ran Slower in 10gSQLnoticeablyslowerinveryfewcaseson10g.Areportranunacceptablysloweraftertheupgrade:CPUtimedoubled.Logicalreadsincreasedbyorderofmagnitude.Slowdownattributedtoonequery(whichrunsmanytimes): SELECT B.value FROM co
75、mmon_stat_names A, sample_sysstats B WHERE A.name = :p_statname AND B.common_stat_name_id = A.common_stat_name_id AND B.sample_id = :p_sample_id; 2024/8/1462Sample Stats QueryOnourOracle8idatabase: call count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0
76、.00 0 0 0 0Fetch 2 0.00 0.00 0 6 0 1- - - - - - - -total 4 0.00 0.00 0 6 0 1 Rows Execution Plan- - 0 SELECT STATEMENT MODE: CHOOSE 1 NESTED LOOPS 2 INDEX MODE: ANALYZED (RANGE SCAN) OF COMMON_STAT_NAMES_PK (UNIQUE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF SAMPLE_SYSSTATS_PK (UNIQUE)2024/8/1463Sample
77、 Stats QueryOnourOracle10gdatabase: call count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.01 0.01 0 244 0 1- - - - - - - -total 4 0.01 0.01 0 244 0 1 Rows Row Source Operation- - 1 NESTED LOOPS (cr=244 pr=0 pw=0 time=893 us) 234 IN
78、DEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=5 pr=0 pw=0 time=1152 us) 1 INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=239 pr=0 pw=0 time=9472 us)2024/8/1464Sample Stats QueryWhocaresabouta0.01secondquery?Supposethequeryruns50+timeseachtimeapopularreportisviewed?AddinganORDEREDhinttothequerymadeOracle10gchoos
79、ethecorrectexecutionplan.ThesameexactbehavioroccurredinbothourtestandproductionOracle10genvironments.BothtablesinthequeryareIOTs.Oraclehasdeterminedthisis“aproblemwiththeoptimizercachingcostmodel.”2024/8/1465SQL Tuning AdvisorCoolsoundingOracle10gfeaturethatstudiesaqueryandmakesrecommendations:Youte
80、llAdvisorhowlongtostudythequery.Advisorcouldrecommendrewrite.Advisorcouldcollectadditionalstatisticsthatcanbesavedindatadictionaryasa“profile”tobeusedwheneverthestatementisparsedinthefuture.Opensthedoortofixingbadquerieswithoutmodifyingtheapplicationcode.2024/8/1466SQL Tuning AdvisorWehadalreadyadde
81、dhintstoallqueriesthatranunacceptablyslow.WevealreadydiscussedthattakingthosehintsawayinOracle10gledtoinferiorresponsetimes.SowhatifwetookthehintsawayandlettheSQLTuningAdvisorrecommendasolutionforeachtroublesomequery?2024/8/1467Recent Event Notifications SQL SELECT dbms_sqltune.report_tuning_task 2
82、(Tuning case 47696, TEXT, ALL, ALL) 3 FROM SYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNINGCASE47696,TEXT,ALL,ALL)-GENERAL INFORMATION SECTION-Tuning Task Name : Tuning case 47696Tuning Task ID : 951Scope : COMPREHENSIVETime Limit(seconds): 600Completion Status : COMPLETEDStarted at : 01/27/2005 13:4
83、2:34Completed at : 01/27/2005 13:42:48-SQL ID : b6c2qka14951zSQL Text: SELECT t.test_severity_id severity, i.instance_id, . ORDER BY severity, first_detected DESC, inst_name-There are no recommendations to improve the statement.2024/8/1468Sample Stats Query SQL SELECT dbms_sqltune.report_tuning_task
84、 2 (Tuning case 47694, TEXT, ALL, ALL) 3 FROM SYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNINGCASE47694,TEXT,ALL,ALL)-GENERAL INFORMATION SECTION-Tuning Task Name : Tuning case 47694Tuning Task ID : 950Scope : COMPREHENSIVETime Limit(seconds): 600Completion Status : COMPLETEDStarted at : 01/27/2005 1
85、3:32:02Completed at : 01/27/2005 13:32:03-SQL ID : g5pqqgcuq8pmaSQL Text: SELECT B.value /* tuning case 47694 */ FROM common_stat_names A, sample_sysstats B WHERE A.name = :p_statname AND B.common_stat_name_id = A.common_stat_name_id AND B.sample_id = :p_sample_id-There are no recommendations to imp
86、rove the statement.2024/8/1469Sample Stats Query Try #2 SQL SELECT dbms_sqltune.report_tuning_task 2 (Tuning case 47725, TEXT, ALL, ALL) 3 FROM SYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNINGCASE47725,TEXT,ALL,ALL)-GENERAL INFORMATION SECTION-Tuning Task Name : Tuning case 47725Tuning Task ID : 956S
87、cope : COMPREHENSIVETime Limit(seconds): 600Completion Status : COMPLETEDStarted at : 01/27/2005 15:09:12Completed at : 01/27/2005 15:09:13-SQL ID : 3kt66qm84bcnzSQL Text: SELECT B.value FROM common_stat_names A, sample_sysstats B WHERE A.name = user commits AND B.common_stat_name_id = A.common_stat
88、_name_id AND B.sample_id = 575783-There are no recommendations to improve the statement.2024/8/1470A Trivial Query SQL SELECT dbms_sqltune.report_tuning_task 2 (Tuning case 47702, TEXT, ALL, ALL) 3 FROM SYS.dual;DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNINGCASE47702,TEXT,ALL,ALL)-GENERAL INFORMATION SECTIO
89、N-Tuning Task Name : Tuning case 47702Tuning Task ID : 952Scope : COMPREHENSIVETime Limit(seconds): 600Completion Status : COMPLETEDStarted at : 01/27/2005 13:51:45Completed at : 01/27/2005 13:51:57-SQL ID : 9cz4z8xvtxbm1SQL Text: SELECT instance_id, sample_type, sample_date_db_local_time /* tuning
90、case 47702 */ FROM samples WHERE sample_id + 1 = :sample_id2024/8/1471A Trivial Query -FINDINGS SECTION (1 finding)- 1- Restructure SQL finding (see plan 1 in explain plans section)- The predicate SAMPLES.SAMPLE_ID+1=:B1 used at line ID 1 of the execution plan contains an expression on indexed colum
91、n SAMPLE_ID. This expression prevents the optimizer from selecting indices on table DBRX_OWNER.SAMPLES. Recommendation - Rewrite the predicate into an equivalent form to take advantage of indices. Alternatively, create a function-based index on the expression. Rationale - The optimizer is unable to
92、use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.2024/8/1472A Trivial Query -EXPLAIN PLANS SECTION-1- Original-Plan hash value: 3806118825-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 |
93、 SELECT STATEMENT | | 4656 | 122K| 2375 (4)| 00:00:29 | 1 | TABLE ACCESS FULL| SAMPLES | 4656 | 122K| 2375 (4)| 00:00:29 |-Query Block Name / Object Alias (identified by operation id):- 1 - SEL$1 / SAMPLESSEL$1-2024/8/1473OverheadWhatdoestheautomation,self-management,andnewfunctionalityofOracle10gco
94、stus?Forexample:MemoryusageThecostofaparseCPUusagebyautomationandself-mgmtprocessesAsyouwouldexpect,allofthesegoupnoticeablywithOracle10g.Forus,theincreaseswereallmanageable.2024/8/1474SYS Has Put on WeightOracle8iproduction(SE,minimaloptions):2,303objectsinSYSschema100MballocatedinSYSTEMtablespaceO
95、racle10gproduction(SE,minimaloptions):6,284objectsinSYSschema454MballocatedinSYSTEM,SYSAUXOracle10gtest(EE,“default”options):21,848objectsinSYSschema800MballocatedinSYSTEM,SYSAUX2024/8/1475Memory UsageProcessstatsfromprstatandtopTotalVMsizeincludesSGARemember:32bitto64bitchangeOracleDedicatedServerP
96、rocessesOracle8iOracle10gResidentsetsizeofOracleprocess97Mb224MbTotalvirtualmemorysizeofOracleprocess121Mb301MbSGAsizeaccordingtov$sgastat84Mb197MbSizeoftheOracleexecutable32Mb95Mb2024/8/1476Hard Parse CostHardparseshavebeenexpensiveinOracleforalongtime.Mechanismstoreducetheneedforhardparses:SharedS
97、QLareaBindvariablesHardparsesshouldbeaone-timeexpenseinproperlydesignedsystems.Astheoptimizergetsmoresophisticatedyoumightexpecthardparsestogetmoreexpensive.InOracle10g,theydo.2024/8/1477Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonAgentFile1(hardparse)Oracle8iOracle10gUserSQLstatementstrac
98、ed110127InternalSQLstatementstraced402977UniqueSQLstatementstraced139149TotalOCIcalls9,09410,754CPUseconds7.4910.94Logicalreads26,77627,373Physicalreads6959592024/8/1478Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonAgentFile2(softparse)Oracle8iOracle10gUserSQLstatementstraced110127InternalSQ
99、Lstatementstraced99UniqueSQLstatementstraced109110TotalOCIcalls1,8001,784CPUseconds3.103.09Logicalreads13,76312,912Physicalreads8132024/8/1479Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonDifferenceOracle8iOracle10gUserSQLstatementstraced00InternalSQLstatementstraced393968UniqueSQLstatements
100、traced3039TotalOCIcalls7,2948,970CPUseconds4.397.85Logicalreads13,01314,461Physicalreads6879462024/8/1480CPU Used by Oracle DaemonsHowmuchadditionalCPUtimewillOracle10gdaemonsconsume?Simpletest:MeasureCPUusageonanidleinstance.Flawsinthistest:SomeOraclefeaturesprobablyusemoreresourcesonabusydatabaset
101、hananidleone(egAWR).HowdoyoumeasureCPUtimeaccurately?(Weusedsar.)2024/8/1481CPU Usage ComparisonNoOracleprocessesrunning: 02:00:03 %usr %sys %wio %idle 02:05:03 0 4 0 96 02:10:03 0 4 0 96 02:15:03 0 4 0 96IdleOracle8iinstance: 02:00:03 %usr %sys %wio %idle 02:05:03 1 4 1 94 02:10:03 0 4 1 95 02:15:0
102、3 0 4 0 95IdleOracle10ginstanceplusEMDC: 13:00:05 %usr %sys %wio %idle 13:05:05 5 6 3 87 13:10:05 3 6 2 89 13:15:05 3 6 4 88 2024/8/1482Activity in Idle Oracle 10gAnAWRreportforaonehourperiodonanOracle10ginstancewithnouseractivityshowed:27,000statementexecutions49CPUsecondsused8Mbredogenerated2024/8
103、/1483Wrapping UpWevebeenhappywithOracle10g:InstalledeasilyUpgradewentsmoothlyNoseriouscompatibilityissuesVeryfewresponsetimeissuescausedbyupgradeNewfeaturesoughttojustifyincreasedheft,complexity,andoverheadForus,theupgradejustificationboileddowntogettingtheexperience.Technology-wise,Oracle8iwasalrea
104、dymeetingourneeds.2024/8/1484Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenviron
105、mentandsee.2024/8/1485White PaperContainsadditionaltopicsandexampleswedidnthavetimetodiscusstodayContainsadditionalsupportingevidenceforconclusionsreachedintodayssessionthatwedidnthavetimetodiscussorthatcouldntfitlegiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload: InformationRoger SchragDatabaseSpecialists,Inc.388MarketStreet,Suite400SanFrancisco,CA94111
