Database Reference
In-Depth Information
cascade
specifies whether indexes are processed. This parameter accepts the values
TRUE
,
FALSE
, and
dbms_stats.auto_cascade
. The latter, which is a constant evaluating to
NULL
,
lets the database engine decide whether to gather the index statistics. The default value is
dbms_stats.auto_cascade
(this default value can be changed—see the section “Configuring
the dbms_stats Package” later in this chapter).
•
•
gather_fixed
specifies whether object statistics for fixed tables are gathered. This parameter
accepts the values
TRUE
and
FALSE
. The default value is
FALSE
.
•
gather_sys
specifies whether the
sys
schema is processed. This parameter accepts the values
TRUE
and
FALSE
. The default value is
FALSE
.
•
gather_temp
specifies whether temporary tables are processed. This parameter accepts
the values
TRUE
and
FALSE
. The default value is
FALSE
. Refer to the “Working with Global
Temporary Tables” section for additional information.
•
options
specifies which, and whether, objects are processed. This parameter accepts the value
listed in Table
8-5
. However, when used with the
gather_table_stats
procedure, only
gather
and
gather auto
are supported. The default value is
gather
.
Table 8-5.
Values Accepted by the
options
Parameter
Value
Meaning
gather
All objects are processed.
gather auto
Let the procedure determine not only which objects are to be processed but also how
they're processed. When this value is
not
used with the
gather_table_stats
procedure,
all parameters except
ownname
,
objlist
,
stattab
,
statid
, and
statown
are ignored.
gather stale
Only objects having stale object statistics are processed. Be careful: objects without object
statistics aren't considered stale.
gather empty
Only objects without object statistics are processed.
list auto
Lists objects that would be processed with the
gather auto
option.
list stale
Lists objects that would be processed with the
gather stale
option.
list empty
Lists objects that would be processed with the
gather empty
option.
StaLeNeSS OF OBJeCt StatIStICS
To recognize whether object statistics are stale, the database engine counts (approximately), for each table, the
number of rows modified through SQL statements. The result of that counting is externalized through the data
dictionary views
all_tab_modifications
,
dba_tab_modifications
(this view exists as of version 11.2 only),
user_tab_modifications
, and, in a 12.1 multitenant environment,
cdb_tab_modifications
. The following
query is an example:
SQL> SELECT inserts, updates, deletes, truncated
2 FROM user_tab_modifications
3 WHERE table_name = 'T';
