Oracle Database 12c - Statistics Gather Reporting
Database 12c came up with a brilliant feature of reporting of stats, i.e, you can run stats gather in reporting mode, rather than just running it.
This will tell you on what all tables statistics are going to be gathered when you run your stats gather procedure with specific arguments.
6 new procedures introduced for this
dbms_stats.report_gather_?_stats
where ? can be any of - Table, schema, dictionary, database, fixed_obj, auto
All the arguments to gather_stats procedure are valid and 2 new arguments introduced which are -
1. detail_level - basic, typical, and all; depending on the type of details. typical is the deafult value
2. format - xml, html, and text. Text is the default value (I personally prefer html)
Below is an example of getting details of getting schema stats gather.
SQL> variable report_out clob;
SQL> set long 10000000
SQL> exec :report_out:=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS ( ownname => 'MY_SCHEMA', -
estimate_percent => 30, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
granularity => 'ALL', -
cascade => TRUE, -
detail_level => 'ALL', -
format => 'HTML');
SQL> spool /tmp/stats_report.html
SQL> print report_out
..
Below is a screenshot from the html file output. I have truncated the output and kept screenshot of only relevant data to this discussion.
As is clear from the screenshot, we get a picture on what all tables the stats gather will take place.
This is a really good feature, helping in developing a good stats gather technique..
This will tell you on what all tables statistics are going to be gathered when you run your stats gather procedure with specific arguments.
6 new procedures introduced for this
dbms_stats.report_gather_?_stats
where ? can be any of - Table, schema, dictionary, database, fixed_obj, auto
All the arguments to gather_stats procedure are valid and 2 new arguments introduced which are -
1. detail_level - basic, typical, and all; depending on the type of details. typical is the deafult value
2. format - xml, html, and text. Text is the default value (I personally prefer html)
Below is an example of getting details of getting schema stats gather.
SQL> variable report_out clob;
SQL> set long 10000000
SQL> exec :report_out:=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS ( ownname => 'MY_SCHEMA', -
estimate_percent => 30, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
granularity => 'ALL', -
cascade => TRUE, -
detail_level => 'ALL', -
format => 'HTML');
SQL> spool /tmp/stats_report.html
SQL> print report_out
..
Below is a screenshot from the html file output. I have truncated the output and kept screenshot of only relevant data to this discussion.
As is clear from the screenshot, we get a picture on what all tables the stats gather will take place.
This is a really good feature, helping in developing a good stats gather technique..
