Oracle Gather Schema | Table Statistics | Extended Statistics | method_opt

Select Histograms for tables:-

SELECT column_name, num_distinct, histogram FROM   user_tab_col_statistics WHERE  table_name = '<table_name>';

Gather tables stats using DBMS_STATS for schema
exec dbms_stats.gather_schema_stats

(

ownname => '<owner_name>',

options => 'GATHER AUTO',

estimate_percent => dbms_stats.auto_sample_size,

method_opt  => 'FOR ALL COLUMNS SIZE 1',

cascade => true,

degree => 10,

granularity => 'ALL'

);

exec dbms_stats.gather_schema_stats

(

ownname=> 'SAMAPPL',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'FOR ALL COLUMNS SIZE 1',

cascade=> true,

granularity=>'ALL',

degree => 10

);

Extended statistics Ref.:
https://blogs.oracle.com/optimizer/extended-statistics
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm
      

method_opt options

FOR ALL COLUMNS SIZE 1

FOR COLUMNS SIZE 254 <column_name>

FOR ALL INDEXED COLUMNS SIZE 1

Size 1 means no histtograms will be created

Size 254 means histograms will be calculated for the column with size 254

exec dbms_stats.Gather_table_stats

('SH', 'SALES', -

method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID'

);

Ref. : https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

garanularity options
Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.

SUBPARTITION: Gather subpartition-level statistics.

PARTITION: Gather partition-level statistics.

GLOBAL: Gather global statistics.

ALL: Gather all (subpartition, partition, and global) statistics.

Explore scripts at Guy Harrison
http://guyharrison.squarespace.com/opsgsamples/

Prerequisite for Incremental statistics:-

• Schema / Table preference should be TRUE for Incremental
• Estimate_Percent should be AUTO_SAMPLE_SIZE
• Granularity should be AUTO

Check incremental statistics is implemented or not:

SELECT dbms_stats.get_prefs('INCREMENTAL','<schema_name>','<table_name>') "INCREMENTAL" FROM   dual;

Enable incremental statistics:

exec dbms_stats.set_table_prefs('<schema_name>','<table_name>','INCREMENTAL','TRUE');

Implement incremental statistics at partition level:

exec dbms_stats.gather_table_stats('<schema_name>','<table_name>',partname=>'<partition_name>',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'PARTITION');

Ref.:
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1

https://www.toadworld.com/platforms/oracle/w/wiki/11434.sophisticated-incremental-statistics-gathering-feature-in-12c
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Explore scripts at Guy Harrison - http://guyharrison.squarespace.com/opsgsamples/