Table Statistics With Histograms procedure – TSWH

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply