RSS Feed

Method R Discussion Group

In 10g, what method does the GATHER_STATS_JOB use to gather histograms (i.e., estimate, sample size, etc.)?

The method for gathering histograms for columns is specified by the method_opt option in the call to the DBMS_STATS procedure.

Mahesh

I think I need more clarification on this.  It seems more complex than "DBMS_STATS method_opt".  The way I see it is...

In Oracle 10g:
GATHER_DATABASE_STATS_JOB_PROC is the procedure that gathers statistics.
DBMS_STATS is the package that contains GATHER_DATABASE_STATS_JOB_PROC.
GATHER_STATS_PROG is the scheduler program that
    DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC runs under.
GATHER_STATS_JOB is the scheduler job that runs GATHER_STATS_PROG.
MAINTENANCE_WINDOW_GROUP is the scheduler window group
 that GATHER_STATS_JOB is a member of.
WEEKNIGHT_WINDOW (10pm - 6am) and WEEKEND_WINDOW (12am Sat - 12am Mon) are members of MAINTENANCE_WINDOW_GROUP.

Assuming all of the above is correct, (or even if its not :) how do I cut thru the confusion to know what the
 method opt is set to when histogram statistics are gathered?

the dbms_stats.gather_table_stats procedure uses default values for method_opt and other parameters in the procedure call. Unlike pre-10g the defaults are not set in the procedure definition but are controlled in a table (SYS.OPTSTAT_HIST_CONTROL$). The official way to retrieve the default for a parameter is the get_param function:

select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
-----------------------------------
FOR ALL COLUMNS SIZE AUTO

You can also use "select sname, spare4 from SYS.OPTSTAT_HIST_CONTROL$" to get all the defaults.

And there is dbms_stats.set_param to change the default.

What has that to do with the auto stats gathering job. Well, it uses dbms_stats.gather_table_stats and therefore uses whatever is set as default for method_opt, estimate_percent, ...

Wolfgang, right on, good info, and thank you!
The reason I posted this question to begin with is that
I have a simple query that the optimizer is obviously
getting wrong.  It chooses a full table scan (FTS) when an
index lookup is much more efficient.  The reason I know is
when I create a histogram at 100% estimate, the optimizer
chooses index and the query runs "fast".  Then after 10pm
(MAINTENANCE WINDOW) gather stats job runs,
the query goes back to FTS, and "slow".  I have to assume
the auto-estimate is not 100% for the histogram.  So with
the info you provided me, I want to find a good way to
compute histograms at 100%, but leave the tables and
indexes to the mercy of AUTO_SAMPLE_SIZE.  In summary, I
think what I've proven is that auto sample size for
histograms can be less than adequate.