Oracle Statistics Generating And Deleting

oracle_statistics_generating_and_seleting.sql

NOTE:
Oracle9i:	estimate_percent defaults to NULL which is COMPUTE
Oracle10g:	estimate_percent defaults to to_estimate_percent_type (get_param('ESTIMATE_PERCENT'))

-- ********************************
-- * Generating Schema Statistics *
-- ********************************
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree)
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1') -- No Histograms
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE REPEAT')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE,method_opt=>'FOR ALL INDEXED COLUMNS')
execute dbms_utility.analyze_schema(schema=>'SCHEMAOWNER',method=>'COMPUTE')
execute dbms_utility.analyze_schema(schema=>'SCHEMAOWNER',method=>'COMPUTE',method_opt=>'FOR ALL INDEXED COLUMNS')

-- ******************************
-- * Deleting Schema Statistics *
-- ******************************
execute dbms_stats.delete_schema_stats(ownname=>'SCHEMAOWNER')
execute dbms_utility.analyze_schema(schema=>'SCHEMAOWNER',method=>'DELETE')

-- *******************************
-- * Generating Table Statistics *
-- *******************************
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree)
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1') -- No Histograms
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE REPEAT')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE,method_opt=>'FOR ALL INDEXED COLUMNS')
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',cascade=>TRUE,degree=>4,method_opt=>'FOR COLUMNS DataID, ParentID')
analyze table [table_name] compute statistics
analyze table [table_name] compute statistics for all indexed columns

-- *******************************
-- * Generating Index Statistics *
-- *******************************
execute dbms_stats.gather_index_stats(ownname=>'SCHEMAOWNER',indname=>'INDEXNAME',estimate_percent=>NULL,degree=>4)

-- ***********************************
-- * Deleting Table/Index Statistics *
-- ***********************************
execute dbms_stats.delete_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',cascade_indexes=>TRUE,cascade_columns=>TRUE)
analyze table [table_name] delete statistics

-- ***********************************
-- * Generating Histogram Statistics *
-- ***********************************
execute dbms_stats.gather_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME',estimate_percent=>NULL,METHOD_OPT=>'FOR COLUMNS COLUMNNAME SIZE NUM_BUCKETS');

-- **********************************
-- * Generating Database Statistics *
-- **********************************
execute dbms_stats.gather_database_stats(estimate_percent=>null,cascade=>TRUE);

-- *****************************
-- * Locking Schema Statistics *
-- *****************************
execute dbms_stats.lock_schema_stats(ownname=>'SCHEMAOWNER');

-- ****************************
-- * Locking Table Statistics *
-- ****************************
execute dbms_stats.lock_table_stats(ownname=>'SCHEMAOWNER',tabname=>'TABLENAME');