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:upgradeprocessandcompatibilityImpressions:Oracle10gingeneralIngreaterdetail:SizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2024/8/143Todays SessionGoal: Help you plan for your own Oracle 10g upgrade.Wewill: Lookatonecompanysexperienceupgradingto10gDiscussreal-lifeexperiencesProvidedatasoyoucandrawyourownconclusionsWewillnot:WalkthroughtheactualupgradestepsMakeanyjudgmentsaboutOracle10g2024/8/144Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2024/8/145White PaperContainsadditionaltopicsandexampleswewonthavetimetodiscusstodayContainsadditional“supportingevidence”forconclusionsreachedintodayssessionthatwewonthavetimetodiscussorthatwontfitlegiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload:www.dbspecialists.com/presentations2024/8/146The View From 30,000 FeetOurOracleenvironmentOurupgradestrategyImpressions:upgradeprocessandcompatibilityImpressions:Oracle10gingeneral2024/8/147Our Oracle EnvironmentPlatformdetails:Oracle8.1.7StandardEdition32bitSunSolaris864bitOneproductionandonedevdatabaseProductiondatabase15Gbinsize2024/8/148Our Oracle EnvironmentApplication:CustomerdatabasemonitoringtoolBackenddaemonsprocessinboundagentfilesfromourcustomersdatabaseserversinthefieldWeb-baseduserinterfaceforreportgeneration,systemconfigurationAlmostallcodeisPL/SQL(roughly50,000lines)LeveragesOracle8ifeaturesegGTTs,table()About50SQLstatementshavehints2024/8/149Our Oracle EnvironmentOracle8iproductiondatabasewasverystableFiguredoutworkaroundsto8ibugslongagoApplicationenhancementsaretestedindevbeforeproductiondeploymentInstancerestarted3-4timesperyearDesignedanddevelopedfromthestartbysmallgroupofexperiencedOracleDBAs,developersWell-architectedforefficiency,performance,scalability(inouropinion)2024/8/1410Our Reasons to Upgrade to 10gOracle8imetallofourneeds.Sowhyupgrade?Oracle8idesupport.(Whatdifferencedoesitmake?)GainOracle10gexperience.(Forus,amorecompellingreason.)2024/8/1411Our Upgrade StrategyRestoreproductionhotbackupontodedicatedtestserver.ExportOracle8itestdatabaseandimportintoemptyOracle10gtestdatabase.Whyexport/importinsteadofupgradinginplace?SwitchalltablespacestoLMTsCompactallapplicationsegments(purgesleftholes)Changecharacterset“Fresh”datadictionary,databasecomponentsWorkedoutastrategytokeepthedowntimetolerable2024/8/1412Our Upgrade StrategyOurOracle8iand10gtestdatabasesstartedoutwiththesamedatahandyfortestingandcomparison.Twocriticalpointstorememberwhencomparingthesetwotestdatabases:ApplicationsegmentsinOracle10gtestdatabaseoccupiedfewerblocks.OurOracle10gtestdatabasewas64bitwhileourOracle8itestdatabasewas32bit.2024/8/1413Impressions: Upgrade ProcessOracle10gversion10.1.0.2andpatchset10.1.0.3installedverysmoothly.Oracle10gimportutilityreadourOracle8iexportfilewithnoissues.Oracle10gUpgradeInformationToolaccuratelypointedoutnecessaryparameterchanges.IvedonemyshareofOracleinstallsovertheyears,andhonestlythiswasoneofthesmootherones.(Note:Solarisplatform!)2024/8/1414Impressions: CompatibilityEncounteredtwocompatibilityissues:EXTPROCneededreconfiguring(tightersecurity)andrecompiling(32bitto64bitchange).Oracle10gPLSQLcompilerdidnotlikeourOracle8iwrappedPL/SQLcode.(CauseisprobablyanOracle8iexportbug.)RewrappingwithOracle10gwrapperutilityresolvedthis.Allotherapplicationcodefunctionedcorrectly.RetainedOracle8imodplsqlclientinitially.Nointeroperabilityissuesencountered.2024/8/1415Impressions: Oracle 10gWorkedwelloutofthebox:EnterpriseManagerDatabaseControlandiSQLPluswereterriblyslow,buttheyworked.OursystemappearsasstableonOracle10gasitwasonOracle8i:NoORA-600sorotherfunnies.Caveat:WeareusingfewOracle9iandbareminimumOracle10gnewfeatures.2024/8/1416Impressions: Oracle 10gBigger,bulkier,hungrierforsystemresources:Biggerexecutablesize,sharedpool,SYSTEMtablespaceMoreoverhead:Daemonprocesses,hardparses,statisticscollectionOverheadandbulkinessweretolerableforus.2024/8/1417Impressions: Oracle 10gApplicationperformancewasaboutthesame:MostSQLconsumedsimilarresources.Duetoourhints,OLTPnature,wehadnotexpectedOracle10gtorunnoticeablyfaster.VeryfewqueriesranslowenoughinOracle10gtobeaproblem.Oracle10gdidbetterthan8iwhenhintswereremoved,butnotaswellaseitherversionwiththehintsinplace.IfwehadstartedoutonOracle10g,dowethinkwecouldhavedonewithoutmanualqueryoptimization(hints)?Wedonotbelieveso.2024/8/1418Impressions: Oracle 10gDiscouragedbySQLTuningAdvisor.(Butdidnottestexhaustivelyduetofrustration.)Thebottomlineforus:Installandupgradewentbetterthanweexpected.Increasedoverheadandheftaremanageableafairexchangeforincreasedfunctionalityandsophistication.WeexpecttogetmoreoutofoursystemthanwaspossiblewithOracle8i,onceweleveragenewerfeatures.(Butwillproceedinthisdirectionverycautiously!)2024/8/1419Upgrade Issues in Greater DetailSizingthesharedpoolandSGAOptimizerstatisticscollectionandaccuracyQueryoptimizationSQLTuningAdvisorOverhead2024/8/1420Sizing the Shared Pool and SGAWelikeSGAtobeonlyaslargeasnecessary.Oracle8isettings:shared_pool_size=40MbTotalSGAsizewas84MbOracle8iperformancecharacteristics:50,000linesofPL/SQLcode15-20executionspersecondUnder660hardparsesperdayBuffercachehitratio97%Librarycachehitratio100%2024/8/1421Sizing the Shared Pool and SGAOracle10gsettings:shared_pool_size=144MbTotalSGAsizeis194MbWhy?Minimumshared_pool_sizesettingfor64bitplatformsis144MbaccordingtoMetalinkdocument263809.1RecommendedbyUpgradeInformationToolaswell2024/8/1422Sizing the Shared Pool and SGAJusttosatisfyacuriosityshared_pool_size=48MbonOracle10g:Instancewouldnotstartshared_pool_size=64MbonOracle10g:Instancestarted,butfrequentORA-4031errorsshared_pool_size=96MbonOracle10g:EverythingseemedtoworkproperlyWerunOracle10ginproductionwith:shared_pool_size=144Mb2024/8/1423Reasons for Larger Shared PoolThreereasonswhytheshared_pool_sizesettingneedstobeincreasedwhenupgradingtoOracle10g:AllocationforoverheadSharedSQLareamemoryusageSQLstatementsgeneratedbyOracle2024/8/1424Allocation for OverheadAportionofthesharedpoolisusedtoholdinternalmemorystructures(overhead).Oracle8iand9imakethesharedpoollargerthanshared_pool_sizespecifiesinordertoallowspaceforthisoverhead.Oracle10gdoesnotmakethesharedpoollargerthanshared_pool_sizespecifies.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 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 UsageIndividualSQLstatementsappeartooccupymorememoryinthesharedSQLareainOracle10gthaninOracle8i.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) 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 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_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 2024/8/1429SQL Generated by OracleThesharedSQLareaonanyOracleinstancewillcontainstatementsissuedbyOracleitselfandnotbytheapplication.Oftencalled“internalSQL”or“recursiveSQL”.Automaticandself-managementinfrastructureinOracle10g(databaseandEMDatabaseControl)generatesalotofinternalSQL.Thesharedpoolwillneedtobelargerinordertoaccommodatetheextrastatements.2024/8/1430SQL Generated by OracleInternalSQLtookupanorderofmagnitudemorespaceinthesharedSQLareaofourOracle10gtestdatabasethanourOracle8itestdatabase.InternalSQLtookupmorespaceinOracle10gthanourapplicationcode.Caveat:TheOracle8itestdatabasewasStandardEditionwithminimaloptionsinstalled.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_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 2024/8/1432SQL 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 2024/8/1433Optimizer StatisticsCollectedoptimizerstatisticsweeklyinOracle8i:ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;Oracle10gusesgather_stats_job:Automaticjobrunsnightly10pmto6am.Usesdbms_stats.Onlycollectsstatisticswheremissingorstale.Samplesizeandhistograms“automatic.”Thisisallsetupautomaticallyoutofthebox.2024/8/1434Optimizer Statistics: CostAutomaticstatisticscollectioninOracle10gismoreresourceintensivethanANALYZEwasinOracle8i:ResourcesUsedtoCollectOptimizerStatisticsOracle8i(ANALYZE)Oracle10g(automatic)CPUseconds1,1012,595Elapsedseconds2,0445,244Logicalreads597,71773,082,675Physicalreads545,8442,926,6252024/8/1435Histogram 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 2024/8/1436Histogram CreationIfacolumnhaseverbeenusedinaWHEREclause,Oracle10gwillconsidercreatingahistogramforit(notecol_usage$):FREQUENCYhistogramsforlowcardinalitycolumnsHEIGHTBALANCEDhistogramsforcolumnswithgapsorskeweddatadistributionManyofthehistogramswontbeuseful:OnunindexedcolumnsthatonlyappearinWHEREclausesalongsideaselective,indexedcolumnOncolumnsthatrarelyappearinWHEREclauses2024/8/1437Sample SizeSamplesizeisanotherreasonstatisticscollectioninOracle10gwassomuchmoreexpensive.Oracle8isamplesizeswereconsistent:Samplesizesontablesover1Mbwere4.5to5.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_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 2024/8/1439Sample SizeHowOracle10gcametosampleeveryrowina760Mbtable:First,Oraclesampledall35columnsofthetableon0.0892929621%oftherows.Next,Oraclesampled8ofthecolumnson0.8929296209%oftherows.Next,Oraclesampled3ofthecolumnson8.9292962091%oftherows.Finally,OracleperformedaCOUNT(DISTINCT)ononeofthecolumnswithoutaSAMPLEclause.2024/8/1440Optimizer Statistics: AccuracyOracle10goptimizerstatisticsdidnotappeartobeparticularlymoreaccuratethanthosecollectedbyANALYZEinOracle8i.InparticularOracle10gsestimateofdistinctcolumnvalueswassometimeslessaccuratethanOracle8is.Couldhavebeencausedbyexcessivelysmallsamplesizeonsometables(justaguess)2024/8/1441Optimizer Statistics: AccuracyHowaccuratedooptimizerstatisticsneedtobe?Ifeverybusinessprocessonyoursystemgivessatisfactoryresponsetime,thenthestatisticsareaccurateenough.Butifabusinessprocessrunstooslowly,canyoublametheoptimizerstatistics?WewillseesomequeriesthatgotunsatisfactoryexecutionplansinourOracle10gtestenvironment.Isitthestatistics?Wedontknow.2024/8/1442Query OptimizationQueriesinourapplicationfollowanOLTPworkloadmodel.Allrunquickly(exceptforquarterlypurge).Quick,butsomearecomplex.Webelievewevewrittenpractical,logicalSQL.Oracle8iranmostofourSQLefficiently:WeaddedhintstoSQLonlywhenresponsetimeconcernsarose.About50statementsthroughouttheapplicationhavehints.2024/8/1443Query OptimizationDidnotexpectthingstorunfasterinOracle10g.Queriesalreadyhadefficientexecutionplansin8i.WeexpectthegainstocomewhenweleverageOracle9iand10gnewfeatures.Concern:WhatifsomequeriesrunslowerinOracle10g?Inabusinessprocesswith100SQLstatements,itonlytakesonebadexecutionplantoslowthewholeprocessdown.2024/8/1444The Executive SummaryMostSQLinourapplicationconsumedroughlythesameCPUtimeandnumberoflogicalreadsinOracle10gasinOracle8i.Somestatementsranalittlefaster,andafewranalittleslower.MostworkloadoperationsyieldedsimilarresponsetimesinbothversionsofOracle.OnlyaveryfewSQLstatementswereslowenoughonOracle10gtocauseconcern.2024/8/1445Query Optimizer ChallengeCouldOracle10gfindefficientexecutionplansforthequeriesthatrequiredhintsinOracle8i?Isaddinghintstoqueriesathingofthepast?Wellnotyet:Oracle10granthetroublesomequeriesfasterwithouthintsthanOracle8iwithouthints.However,bothversionsofOracleranthequeriesfasterwithhintsthanOracle10gdidwithouthints.2024/8/1446Query Optimization in DetailSQLthatransimilarlyinOracle8iand10gSQLthatranfasterinOracle10gSQLthatranfasterinOracle8i2024/8/1447SQL That Ran SimilarlyLoaderDaemoncomparisonPerformanceSummaryreportcomparisonSeethewhitepaperforTKPROFreportexcerpts2024/8/1448Loader Daemon ComparisonLoaderDaemonparses,validates,andloadsfilesfromourmonitoringagentsintothedatabaseforanalysisandreporting.PL/SQLpackageroughly7,800lineslong.7SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedtheLoaderDaemononeachdatabasewhileloadingthesameagentfileintoeach.2024/8/1449Loader Daemon ComparisonResourcesUsedbyLoaderDaemontoLoadOneAgentFileOracle8iOracle10gUserSQLstatementstraced110127InternalSQLstatementstraced99UniqueSQLstatementstraced109110TotalOCIcalls1,8001,792CPUseconds3.133.12Logicalreads13,76712,920Physicalreads6132024/8/1450Loader Daemon ComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10g:Importmade10gsegmentsmorecompact.MoreuserSQLstatementstracedonOracle10g:Oracle10gdatabasehadsmallerPL/SQLcursorcacheduetobehaviorchangeimplementedin9.2.0.5reopen_cursors.(SeeMetalinkdocument274496.1.)Cachemissesleadtoextra(soft)parsecalls.TKPROFreportedtheseextraparsecallsasextratracedstatements.2024/8/1451Performance Report ComparisonPerformanceSummaryreportprovidesasummaryofperformancestatisticsforonemonitoredOracledatabaseoveraspecifiedperiodoftime(likeaStatspackreport).PL/SQLpackageroughly3,200lineslong.4SQLstatementsinthepackagehavehints.StartingoutwiththesamedataintheOracle8iand10gtestdatabases,wetracedsessionsthatcalledthereportwiththesameparametersoneachdatabase.2024/8/1452Performance Report Comparison ResourcesUsedbyPerformanceSummaryReportOracle8iOracle10gUserSQLstatementstraced9898InternalSQLstatementstraced1010UniqueSQLstatementstraced9897TotalOCIcalls654531CPUseconds0.890.88Logicalreads4,6413,661Physicalreads102024/8/1453Performance Report ComparisonBusinessprocessgaveroughlysameresponsetimeandloadprofileonOracle8iand10g.FewerlogicalreadsonOracle10gagain.FewertotalOCIcallsinOracle10g:Samenumberofparseandexecutecalls.Oracle8ihadtwiceasmanyfetchcallsas10g.ItappearsasifOracle8ididextrafetchcallstomakesureithadretrievedallrowsfromacursor,whileperhapsOracle10gaskedformorerowsupfront.2024/8/1454SQL That Ran Faster in 10gWedidnotexpectnoticeableresponsetimeimprovementsonOracle10gbecauseeverythingalreadyran“fastenough”on8i.WeremovedthehintsfromqueriesthathadbeenslowinOracle8itoseeifOracle10gcouldfindtherightexecutionplan.InseveralcasesOracle10gdidbetterthan8ididwithouthints,but10gsexecutionplanwasstillfarinferiortothatchosenwhenthehintswereinplace.2024/8/1455Recent Event NotificationsQueryappearsinseveralreports.Retrievesalistofrecenteventnotificationsforalldatabasestowhichthespecifieduserhasaccess.Joins7tablesandincludesasubquery.TogetthequerytorunefficientlyinOracle8iwehadaddedahinttospecifyjoinorderandwhichjoinalgorithmtouseforeachtable.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_nickname, 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.user_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.display_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 BY 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 NotificationsWithoutthehint,Oracle10gdidabetterjobthanOracle8ibutstillnotgoodenough:Good:Oracle10gfiguredouttherighttimetoperformthesubquery.Bad:Oracle10gchoseahashjointoatablewith800,000rowswhennestedloopswastherightwaytogo.Withthehint,Oracle10gdidbetterthanOracle8i(withthehint)byperformingthesubqueryasearlyaspossibleinsteadofaslateaspossible.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 ANALYSIS_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: ANALYZED (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 Rows 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 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) 359 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 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 (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 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; 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.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 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 INDEX 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?AddinganORDEREDhinttothequerymadeOracle10gchoosethecorrectexecutionplan.ThesameexactbehavioroccurredinbothourtestandproductionOracle10genvironments.BothtablesinthequeryareIOTs.Oraclehasdeterminedthisis“aproblemwiththeoptimizercachingcostmodel.”2024/8/1465SQL Tuning AdvisorCoolsoundingOracle10gfeaturethatstudiesaqueryandmakesrecommendations:YoutellAdvisorhowlongtostudythequery.Advisorcouldrecommendrewrite.Advisorcouldcollectadditionalstatisticsthatcanbesavedindatadictionaryasa“profile”tobeusedwheneverthestatementisparsedinthefuture.Opensthedoortofixingbadquerieswithoutmodifyingtheapplicationcode.2024/8/1466SQL Tuning AdvisorWehadalreadyaddedhintstoallqueriesthatranunacceptablyslow.WevealreadydiscussedthattakingthosehintsawayinOracle10gledtoinferiorresponsetimes.SowhatifwetookthehintsawayandlettheSQLTuningAdvisorrecommendasolutionforeachtroublesomequery?2024/8/1467Recent Event Notifications SQL SELECT dbms_sqltune.report_tuning_task 2 (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:42: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 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 13: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 improve 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 : 956Scope : 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_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 SECTION-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 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 column 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 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 | 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,andnewfunctionalityofOracle10gcostus?Forexample:MemoryusageThecostofaparseCPUusagebyautomationandself-mgmtprocessesAsyouwouldexpect,allofthesegoupnoticeablywithOracle10g.Forus,theincreaseswereallmanageable.2024/8/1474SYS Has Put on WeightOracle8iproduction(SE,minimaloptions):2,303objectsinSYSschema100MballocatedinSYSTEMtablespaceOracle10gproduction(SE,minimaloptions):6,284objectsinSYSschema454MballocatedinSYSTEM,SYSAUXOracle10gtest(EE,“default”options):21,848objectsinSYSschema800MballocatedinSYSTEM,SYSAUX2024/8/1475Memory UsageProcessstatsfromprstatandtopTotalVMsizeincludesSGARemember:32bitto64bitchangeOracleDedicatedServerProcessesOracle8iOracle10gResidentsetsizeofOracleprocess97Mb224MbTotalvirtualmemorysizeofOracleprocess121Mb301MbSGAsizeaccordingtov$sgastat84Mb197MbSizeoftheOracleexecutable32Mb95Mb2024/8/1476Hard Parse CostHardparseshavebeenexpensiveinOracleforalongtime.Mechanismstoreducetheneedforhardparses:SharedSQLareaBindvariablesHardparsesshouldbeaone-timeexpenseinproperlydesignedsystems.Astheoptimizergetsmoresophisticatedyoumightexpecthardparsestogetmoreexpensive.InOracle10g,theydo.2024/8/1477Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonAgentFile1(hardparse)Oracle8iOracle10gUserSQLstatementstraced110127InternalSQLstatementstraced402977UniqueSQLstatementstraced139149TotalOCIcalls9,09410,754CPUseconds7.4910.94Logicalreads26,77627,373Physicalreads6959592024/8/1478Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonAgentFile2(softparse)Oracle8iOracle10gUserSQLstatementstraced110127InternalSQLstatementstraced99UniqueSQLstatementstraced109110TotalOCIcalls1,8001,784CPUseconds3.103.09Logicalreads13,76312,912Physicalreads8132024/8/1479Hard Parse Cost Comparison ResourcesusedbyLoaderDaemonDifferenceOracle8iOracle10gUserSQLstatementstraced00InternalSQLstatementstraced393968UniqueSQLstatementstraced3039TotalOCIcalls7,2948,970CPUseconds4.397.85Logicalreads13,01314,461Physicalreads6879462024/8/1480CPU Used by Oracle DaemonsHowmuchadditionalCPUtimewillOracle10gdaemonsconsume?Simpletest:MeasureCPUusageonanidleinstance.Flawsinthistest:SomeOraclefeaturesprobablyusemoreresourcesonabusydatabasethananidleone(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:03 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/1483Wrapping UpWevebeenhappywithOracle10g:InstalledeasilyUpgradewentsmoothlyNoseriouscompatibilityissuesVeryfewresponsetimeissuescausedbyupgradeNewfeaturesoughttojustifyincreasedheft,complexity,andoverheadForus,theupgradejustificationboileddowntogettingtheexperience.Technology-wise,Oracle8iwasalreadymeetingourneeds.2024/8/1484Always RememberEachOraclesystemisuniqueandwillhaveitsownchallenges.NevertakesomebodyelseswordonanythingwhenitcomestoOracletechnology.Inthissessionweareonlyrelayingonecompanysexperiences.TheonlywayforyoutoknowhowyourspecificsystemwillfareonOracle10gistotryitinatestenvironmentandsee.2024/8/1485White PaperContainsadditionaltopicsandexampleswedidnthavetimetodiscusstodayContainsadditionalsupportingevidenceforconclusionsreachedintodayssessionthatwedidnthavetimetodiscussorthatcouldntfitlegiblyonaPowerPointslideTKPROFreports,executionplans,AWRreportsDownload:www.dbspecialists.com/presentations2024/8/1486Contact InformationRoger SchragDatabaseSpecialists,Inc.388MarketStreet,Suite400SanFrancisco,CA94111