Oracle 9i 数据库性能调优技术les12

Copyright©OracleCorporation,2002.Allrightsreserved.ManagingStatistics12-2Copyright©OracleCorporation,2002.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Collectsystemstatistics•Collectstatisticsonindexesandtables•Collectandmanagehistogramstatistics•Copystatisticsbetweendatabases•Monitorindexestodetermineusage12-3Copyright©OracleCorporation,2002.Allrightsreserved.ManagingStatisticsUsethedbms_statspackage:•gather_table_stats•gather_index_stats•gather_schema_stats•gather_database_stats•gather_stale_stats12-5Copyright©OracleCorporation,2002.Allrightsreserved.TableStatistics•Numberofrows•Numberofblocksandemptyblocks•Averageavailablefreespace•Numberofchainedormigratedrows•Averagerowlength•Lastanalyzedateandsamplesize•Datadictionaryview:dba_tables12-6Copyright©OracleCorporation,2002.Allrightsreserved.CollectingSegment-LevelStatisticsStatisticscollectedandthemethodofcollection:•Logicalreads:Sampled•Bufferbusywaits:Continuous•Dbblockchanges:Sampled•Physicalreads:Continuous•Physicalwrites:Continuous•Physicalreadsdirect:Continuous•Physicalwritesdirect:Continuous•Globalcachecrblocksserved:Continuous•Globalcachecurrentblocksserved:Continuous•ITLwaits:Continuous•Rowlockwaits:Continuous12-7Copyright©OracleCorporation,2002.Allrightsreserved.QueryingSegment-LevelStatisticsSegment-levelstatisticsarequeriedusing:•v$segstat_name:Liststhesegmentstatisticsbeingcollected•v$segstat:Displaysthestatisticvalue,statisticname,andotherbasicinformation•v$segment_statistics:Displaysthesegmentownerandtablespacenameinadditiontoalltherowscontainedinv$segstat12-8Copyright©OracleCorporation,2002.Allrightsreserved.UsingDynamicSamplingDynamicsamplingshouldbeusedwhen:•Abetterplancouldbefound•Thecostofcollectingthestatisticsisminimalcomparedtotheexecutiontime•Thequeryisexecutedmanytimes12-9Copyright©OracleCorporation,2002.Allrightsreserved.EnablingDynamicSamplingDynamicsamplingissetusing:•OPTIMIZER_DYNAMIC_SAMPLING=0Dynamicsamplingisnotperformed•OPTIMIZER_DYNAMIC_SAMPLING=1Dynamicsamplingperformedwhen:–Thequeryaccessesmorethanonetable.–Atablehasnotbeenanalyzedandthereisnoindex.–Theoptimizerdeterminesthatafulltablescanisrequiredduetononexistentstatistics.•OPTIMIZER_DYNAMIC_SAMPLING1–Thehigherthevaluethemoreaggressivedynamicsamplingisperformed.Theupperlimitis10.12-10Copyright©OracleCorporation,2002.Allrightsreserved.IndexStatistics•Indexlevel(height)•Numberofleafblocksanddistinctkeys•Averagenumberofleafblocksperkey•Averagenumberofdatablocksperkey•Numberofindexentries•Clusteringfactor•Datadictionaryview:dba_indexes12-11Copyright©OracleCorporation,2002.Allrightsreserved.IndexTuningWizard12-12Copyright©OracleCorporation,2002.Allrightsreserved.ColumnStatistics•Numberofdistinctvalues•Lowestvalue,highestvalue(storedinRAW[binary]format)•Lastanalyzedateandsamplesize•Datadictionaryview:user_tab_col_statistics12-13Copyright©OracleCorporation,2002.Allrightsreserved.Histograms•Histogramsdescribethedatadistributionofaparticularcolumninmoredetail.•Theygivebetterpredicateselectivityestimatesforunevenlydistributeddata.•Youcreatehistogramswiththedbms_stats.gather_table_statsprocedure.•Datadictionaryviews:dba_histograms,dba_tab_histograms12-14Copyright©OracleCorporation,2002.Allrightsreserved.GeneratingHistogramStatisticsHistogramstatisticsaregeneratedby:SQLEXECUTEdbms_stats.gather_table_stats-2('HR','EMPLOYEES',METHOD_OPT=-3'FORCOLUMNSSIZE10salary');12-15Copyright©OracleCorporation,2002.Allrightsreserved.GatheringStatisticEstimates•dbms_stats.auto_sample_size:Newestimate_percentvalue•METHOD_OPToptions:–REPEAT:Newhistogramwithsamenumberofbuckets–AUTO:Newhistogrambasedondatadistributionandapplicationworkload–SKEWONLY:NewhistogrambasedondatadistributionSQLEXECUTEdbms_stats.gather_schema_stats(-2ownname='OE',estimate_percent=-3DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt-4='forallcolumnssizeAUTO');12-17Copyright©OracleCorporation,2002.Allrightsreserved.AutomaticStatisticCollectingForthedbms_stats.gather_schema_statsproceduresetOPTIONSto:•GatherStale•GatherEmpty•GatherAutoSQLEXECUTEdbms_stats.gather_schema_stats-2(OWNNAME='OE',-3OPTIONS='GATHERAUTO');12-18Copyright©OracleCorporation,2002.Allrightsreserved.OptimizerCostModel•Threecolumnsinplan_tableare:–cpu_cost:EstimatedCPUcostoftheoperation–io_cost:EstimatedI/Ocostoftheoperation–temp_space:Estimatedtemporaryspace(inbytes)usedbytheoperation•IncludesCPUusage•Accountsfortheeffectofcaching•Accountsforindexprefetching12-20Copyright©OracleCorporation,2002.Allrightsreserved.UsingSystemStatistics•SystemstatisticsenabletheCBOtouseCPUandI/Ocharacteristics.•Systemstatisticsmustbegatheredonaregularbasis;thisdoesnotinvalidatecachedplans.•Gatheringsystemstatisticsequalsanalyzingsystemactivityforaspecifiedperiodoftime.12-21Copyright©OracleCorporation,2002.Allrightsreserved.GatheringSystemStatisticsProceduresofthedbms_statspackageusedtocollectsystemstatistics:•gather_system_stats•set_system_stats•get_system_stats12-22Copyright©OracleCorporation,2002.Allrightsreserved.AutomaticGatheringofSyste