I’ve uploaded the most recent version of a stored procedure that I use for gathering optimizer statistics on a table in Oracle. You can download a zip here. See the file buildandtest.bat to see how to build the needed tables and procs and run the supplied tests.
By default the proc tab_stats_with_hists will gather statistics on a large table with a small estimate percentage and no histograms. I like this as a default because if you just run dbms_stats.gather_table_stats with all the defaults sometimes it will run forever on a large table. If you run tab_stats_with_hists with the defaults it will run quickly on a large table.
If you want to override the defaults you do so by inserting rows into the tables ESTIMATE_TABLES and HIST_COLUMNS. So, if you want to set the estimate percentage to 1 percent on a table you would do this:
insert into ESTIMATE_TABLES values (‘YOURSCHEMA’,’YOURTABLE’,1);
If you wanted a histogram on a column you would do this:
insert into HIST_COLUMNS values (‘YOURSCHEMA’,’YOURTABLE’,’YOURCOLUMN’);
To run the proc on a table you would run it like this:
execute tab_stats_with_hists(‘YOURSCHEMA’,’YOURTABLE’,NULL);
To gather stats on one partition you would add the partition name:
execute tab_stats_with_hists(‘YOURSCHEMA’,’YOURTABLE’,’YOURPARTITION’);
The zip includes a test of a non-partitioned table, a partitioned table, and a subpartitioned table.
– Bobby
