Oracle英文版培训课件之SQL Tuning：Les07-RW

GatheringStatisticsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Identifytable,index,andcolumnstatisticsDescribetheAutomaticStatisticsGatheringmechanismUsetheDBMS_STATSpackagetocollectstatisticsmanuallyIdentifypredicateselectivitycalculationsWhatAreOptimizerStatistics?CollectionofdatathatdescribesthedatabaseandtheobjectsinthedatabaseInformationusedbyqueryoptimizertoestimate:SelectivityofpredicatesCostofeachexecutionplanAccessmethodandjoinmethodCPUandI/OcostsTypesofOptimizerStatisticsObjectstatisticsTablestatisticsColumnstatisticsIndexstatisticsSystemstatisticsI/OperformanceandutilizationCPUperformanceandutilizationFullNotesPageHowStatisticsAreGatheredAutomaticstatisticsgatheringGATHER_STATS_JOBManualstatisticsgatheringDBMS_STATSpackageDynamicsamplingAutomaticStatisticsGatheringOracleDatabase10gautomatesoptimizerstatisticscollection:Statisticsaregatheredautomaticallyonalldatabaseobjects.GATHER_STATS_JOBisusedforstatisticscollectionandmaintenance.Schedulerinterfaceisusedforschedulingthemaintenancejob.Automatedstatisticscollection:EliminatesneedformanualstatisticscollectionSignificantlyreducesthechancesofgettingpoorexecutionplansManualStatisticsGathering YoucanusetheDBMS_STATSpackageto:GenerateandmanagestatisticsforusebytheoptimizerGather,modify,view,export,import,anddeletestatisticsIdentifyornamestatisticsthataregatheredGatherstatisticson:Indexes,tables,columns,andpartitionsAllschemaobjectsinaschemaordatabaseGatherstatisticseitherseriallyorinparallelManagingAutomaticStatisticsCollectionJobconfigurationoptionsStatistics-collectionconfigurationoptionsJobConfigurationOptionsSettingstatus:ENABLEDorDISABLEDMaintainingschedule:maintenancewindowManagingtheJobSchedulerVerifyingAutomaticStatisticsGathering:SELECTowner,job_name,enabled

FROMDBA_SCHEDULER_JOBS

WHEREJOB_NAME='GATHER_STATS_JOB';BEGINDBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');END;/BEGINDBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');END;/DisablingandenablingAutomaticStatisticsGathering:ManagingtheMaintenanceWindowWEEKNIGHT_WINDOWWEEKEND_WINDOWEXECUTEDBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;

byhour=0;byminute=0;bysecond=0');ChangingtheGATHER_STATS_JOBScheduleStatisticsCollectionConfigurationDMLmonitoringSamplingDegreeofparallelismHistogramsCascadeDMLMonitoringTheDMLmonitoringfacility:TracksDMLstatementsandtruncationoftablesIsusedbytheAutomaticStatisticsGatheringmechanismforidentifyingsegmentswithstalestatisticsIsenabledbydefaultwhenSTATISTICS_LEVELissettoTYPICALorALLYoucan:ViewtheinformationonDMLchangesintheUSER_TAB_MODIFICATIONSviewUseDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOtoupdatetheviewwithcurrentinformationUseGATHER_DATABASE_STATSorGATHER_SCHEMA_STATSformanualstatisticsgatheringfortableswithstalestatisticswhenOPTIONSissettoGATHERSTALEorGATHERAUTOFullNotesPageSamplingStatisticsgatheringreliesonsamplingtominimizeresourceusage.YoucanusetheESTIMATE_PERCENTargumentoftheDBMS_STATSprocedurestochangethesamplingpercentagetoanyvalue.SettoDBMS_STATS.AUTO_SAMPLE_SIZE(default)tomaximizeperformancegains.AUTO_SAMPLE_SIZEenablesthedatabasetodeterminetheappropriatesamplesizeforeachobjectautomatically.EXECUTEDBMS_STATS.GATHER_SCHEMA_STATS('SH',DBMS_STATS.AUTO_SAMPLE_SIZE);FullNotesPageDegreeofParallelismAutomaticStatisticsGatheringoperationscanruneitherseriallyorinparallel.Bydefault,thedegreeofparallelismisdeterminedautomatically.YoucanalsomanuallyspecifythedegreeofparallelismusingtheDEGREEargumentoftheDBMS_STATSprocedures.SettingtheDEGREEparametertoDBMS_STATS.AUTO_DEGREE(default)enablestheOracleDatabasetochooseanappropriatedegreeofparallelismevenwhencollectingstatisticsmanually.HistogramsInfluenceoptimizerdecisionsonselectingtheoptimalexecutionplanProvideimprovedselectivityestimatesinthepresenceofdataskewEnableoptimalexecutionplanswithnonuniformdatadistributionsColumnValueCountofRows10102010503012640567502481050203040Numberofbuckets=501050203040CreatingHistogramsTheAutomaticStatisticsGatheringmechanismcreateshistogramsasneededbydefault.YoucanusetheDBMS_STATSpackagetochangethisdefault.YoucanuseDBMS_STATStocreatehistogramsmanually.Thefollowingexampleshowshowtocreateahistogramwith50bucketsonPROD_LIST_PRICE:EXECUTEdbms_stats.gather_table_stats

('sh','products',

method_opt=>'forcolumnssize50

prod_list_price');FullNotesPageViewingHistogramStatisticsSELECTcolumn_name,num_distinct,num_buckets,histogramFROMUSER_TAB_COL_STATISTICSWHEREhistogram<>'NONE';SELECTcolumn_name,num_distinct,num_buckets,histogramFROMUSER_TAB_COL_STATISTICSWHEREcolumn_name='PROD_LIST_PRICE';12HistogramTipsThedefaultoptionforDBMS_STATSMETHOD_OPTSisFORALLCOLUMNSSIZEAUTO,whichenablesautomaticcreationofhistogramsasneeded.Alternatively,youcancreatehistogramsmanually:OnskewedcolumnsthatareusedfrequentlyinWHEREclausesofqueriesOncolumnsthathaveahighlyskeweddatadistributionDonotusehistogramsunlesstheysubstantiallyimproveperformance.Histogramsallocateadditionalstorage.Histograms,likeallotheroptimizerstatistics,arestatic.Recomputethehistogramwhenthedatadistributionofacolumnchangesfrequently.ForquerieswithbindvariablesFullNotesPageBindVariablePeekingTheoptimizerpeeksatthevaluesofbindvariablesonthefirstinvocationofacursor.Thisisdonetodeterminetheselectivityofthepredicate.Peekingdoesnotoccurforsubsequentinvocationsofthecursor.Cursorisshared,basedonthestandardcursor-sharingcriteriaevenfordifferentbindvalues.CascadingtoIndexesTheAutomaticStatisticsGatheringmechanismisconfiguredbydefaulttogatherindexstatisticswhilegatheringstatisticsontheparenttables.YoucanchangethedefaultbehaviorbymodifyingtheCASCADEoptionoftheDBMS_STATSpackage.SettheCASCADEoptionto:TRUEtogatherindexstatisticsDBMS_STATS.AUTO_CASCADEtohavetheOracleDatabasedeterminewhetherindexstatisticsaretobecollectedornotManagingStatisticsCollection:Exampledbms_stats.gather_table_stats('sh'--schema,'customers'--table,null--partition,20--samplesize(%),false--blocksample?,'forallcolumns'--columnspec,4--degreeofparallelism,'default'--granularity,true);--cascadetoindexesdbms_stats.set_param('CASCADE',

'DBMS_STATS.AUTO_CASCADE');dbms_stats.set_param('ESTIMATE_PERCENT','5');dbms_stats.set_param('DEGREE','NULL');WhentoGatherManualStatisticsRelymostlyonautomaticsstatisticscollectionChangefrequencyofautomaticstatisticscollectiontomeetyourneedsGatherstatisticsmanually:ForobjectsthatarevolatileForobjectsmodifiedinbatchoperationsStatisticsGathering:ManualApproachesDynamicsampling:BEGINDBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');END;BEGINDBMS_STATS.GATHER_TABLE_STATS('OE','ORDERS');DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');END;Forobjectsmodifiedinbatchoperations:GatherstatisticsaspartofthebatchoperationFornewobjects:GatherstatisticsimmediatelyafterobjectcreationManualstatisticscollection:DynamicSamplingDynamicsamplingisusedtoautomaticallycollectstatisticswhen:ThecostofcollectingthestatisticsisminimalcomparedtotheexecutiontimeThequeryisexecutedmanytimesLockingStatisticsPreventsautomaticgatheringIsusedprimarilyforvolatiletablesLockwithoutstatisticsimpliesdynamicsampling.Lockwithstatisticsisforrepresentativevalues.EXECUTEDBMS_STATS.LOCK_TABLE_STATS('ownername','tablename');EXECUTEDBMS_STATS.LOCK_SCHEMA_STATS('ownername');SELECTstattype_lockedFROMdba_tab_statistics;VerifyingTableStatisticsSELECTlast_analyzedanalyzed,sample_size,

monitoring,table_nameFROMdba_tables

WHEREtable_name='EMPLOYEES';ANALYZEDSAMPLE_SIZEMONTABLE_NAME09-FEB-042000YESEMPLOYEESVerifyingColumnStatisticsSELECTcolumn_name,num_distinct,histogram,

num_buckets,density,last_analyzedanalyzedFROMdba_tab_col_statistics

WHEREtable_name='SALES'

ORDERBYcolumn_name;COLUMN_NAMENUM_DISTINCTHISTOGRAMNUM_BUCKETSDENSITYANALYZED

AMOUNT_SOLD3586NONE1.00027886209-FEB-04CHANNEL_ID4NONE1.2509-FEB-04CUST_ID7059NONE1.00014166309-FEB-04PROD_ID72FREQUENCY725.4416E-0709-FEB-04PROMO_ID4NONE1.2509-FEB-04QUANTITY_SOLD1NONE1109-FEB-04TIME_ID1460NONE1.00068493209-FEB-047rowsselected.VerifyingIndexStatisticsSELECTindex_namename,num_rowsn_r,

last_analyzedl_a,distinct_keys

d_k,leaf_blocksl_b,

avg_leaf_blocks_per_keya_l,join_indexj_IFROMdba_indexes

WHEREtable_name='EMPLOYEES'

ORDERBYindex_name;FullNotesPageHistoryofOptimizerStatisticsGATHER_STATSIMPORT_STATSSET_STATSDBA_TAB_STATS_HISTORYNew

statisticsOld

statisticsDBA_OPTSTATS_OPERATIONS31

daysRESTORE_TABLE_STATSOperation

dateManagingHistoricalOptimizerStatisti