column group statistics aka extended statistics in 11g

Using extended statistics in Oracle 11g

when queries have a list of predicates, it may be beneficial to collect statistics for these columns listed in the predicates. Luckily this could be down by oracle automatically once it is setup:

1. Observe workload that use those tables for 300 seconds by simply run explain plan for the long running query

Exec dbms_stats.seed_col_usage(null,null,300)

2. review the column usage information recorded in DBMS_STATS.REPORT_COL_USAGE

select dbms_stats.report_col_usaage(schema_owner,’table_name’) from dual;

3. Create the column groups

Exec dbms_stats.create_extended_stats(schema_owner,’table_name’);

4. review extended statistics collection that will be maintained by oracle

select dbms_stats.create_extended_stats(schema_owner,’table_name’) from dual;

5. Re-gather Statistics so that extended statistics for the column groups will be collected

Exec dbms_stats.gather_table_stats(schema_owner,’table_name’);

6. confirm what new statistics have been collected

select column_name,num_distinct, histogram from user_tab_col_statistics where table_name=’table_name’ order by 1;