Database Reference
In-Depth Information
automatically increased if the gathering of statistics is performed at the database or schema
level, the estimate percentage should be chosen for the biggest tables. In passing, note that
sampling on external tables isn't supported:
SQL> BEGIN
2 dbms_stats.gather_schema_stats(ownname => user,
3 estimate_percent => 0.5);
4 END;
5 /
SQL> SELECT table_name, sample_size, num_rows,
2 round(sample_size/num_rows*100,1) AS "%"
3 FROM user_tables
4 WHERE num_rows > 0
5 ORDER BY table_name;
TABLE_NAME SAMPLE_SIZE NUM_ROWS %
---------------------- ----------- -------- ------
CAL_MONTH_SALES_MV 48 48 100.0
CHANNELS 5 5 100.0
COSTS 4975 81391 6.1
COUNTRIES 23 23 100.0
CUSTOMERS 5435 55002 9.9
FWEEK_PSCAT_SALES_MV 4742 11001 43.1
PRODUCTS 72 72 100.0
PROMOTIONS 503 503 100.0
SALES 4639 927800 0.5
SALES_TRANSACTIONS_EXT 916039 916039 100.0
TIMES 1826 1826 100.0
block_sample
specifies whether row sampling or block sampling is used for the gathering of
statistics. Although row sampling is more accurate, block sampling is faster. Therefore, block
sampling should be used only when it's sure that data is randomly distributed. This parameter
accepts the values
TRUE
and
FALSE
. The default value is
FALSE
. As of version 11.1, this parameter is
only pertinent when the
estimate_percent
parameter isn't set to
dbms_stats.auto_sample_size
.
•
method_opt
specifies whether and how column statistics and histograms are gathered. There
are three typical use cases:
2
•
•
Gathering column statistics and histograms for all
3
columns. All histograms are created
with the very same value for the
size_clause
parameter. If
size 1
is specified, no
histograms are created. The syntax is shown in Figure
8-7
. For example, with the
for all
columns size 254
value, a histogram with up to 254 buckets is created for every column.
2
For simplicity, I'm not describing all the possibilities because many of them are redundant or of limited use in practice.
3
Actually, with the options
indexed
and
hidden
, it's possible to restrict the statistics gathering to indexed and hidden columns only.
As a rule, object statistics should be available for all columns. For this reason, both these options should be avoided (hence they're
shown in Figure
8-7
in gray). If for some columns there's no need to have object statistics, you should use the syntax described in
Figure
8-8
instead. One sensible reason for using
hidden
is to gather statistics on a virtual column used for an extension that was
