Object Statistics - Troubleshooting Oracle Performance - page 234

obj_filter_list specifies to gather statistics only for objects fulfilling at least one of the filters
passed as a parameter. It's based on the objecttab type defined in the dbms_stats package
itself and is available as of version 11.1 only. The following PL/SQL block shows how to gather
statistics for all tables of the HR schema and all tables of the SH schema that have a name
starting with the letter C:

•

DECLARE
l_filter dbms_stats.objecttab := dbms_stats.objecttab();
BEGIN
l_filter.extend(2);
l_filter(1).ownname := 'HR';
l_filter(2).ownname := 'SH';
l_filter(2).objname := 'C%';
dbms_stats.gather_database_stats(obj_filter_list => l_filter,
options => 'gather');
END;

Gathering Options
The gathering option parameters listed in Table 8-2 specify how the gathering of statistics takes place, which kinds of
column statistics are gathered, and whether dependent SQL cursors are invalidated. The options are as follows:

estimate_percent specifies whether sampling is used for gathering statistics. Valid values
are decimal numbers between 0.000001 and 100. The value 100, as well as the value NULL ,
means no sampling. The dbms_stats.auto_sample_size constant, which is the default value
(this default value can be changed—see the section “Configuring the dbms_stats Package”
later in this chapter), lets the procedure determine the sample size. As of version 11.1, it's
recommended to use this value. In fact, in most cases, using the default value not only
gathers statistics that are more accurate than a sampling of, say, 10%, but it results in their
being gathered more quickly. This behavior is possible because version 11.1 introduces a
completely new algorithm used only when dbms_stats.auto_sample_size is specified. Also
note that, because this new algorithm requires a full scan of the table on which the statistics
are gathered, on a system with a relatively slow disk I/O subsystem that might take too long.
Also note that some features (top frequency histograms, hybrid histograms, and incremental
statistics) only work when dbms_stats.auto_sample_size is specified. It's important to
understand that when a decimal number is passed as parameter, the value specified by the
estimate_percent parameter is only the minimum percentage used for gathering statistics.
In fact, as shown in the following example, the dbms_stats package may automatically
increase the specified estimate_percent value if the package considers the parameter's value
to be too small. Provided you don't work with dbms_stats.auto_sample_size , to speed up
the gathering of object statistics use small estimate percentages; values less than 10 percent
are usually good. For large tables, even 0.5 percent, 0.1 percent, or less could be fine. The
actual optimal value depends on data distribution. If you're uncertain about your choice,
simply try different estimate percentages and compare the gathered statistics. In that way, you
may find the best compromise between performance and accuracy. Note that small estimate
percentages might not generate deterministic statistics. Because values that are too small are

•