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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply