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:

Share this:

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.