Database Reference
In-Depth Information
Table 8-7.
Values Accepted by the
autostats_target
Parameter
Value
Meaning
all
All objects are processed. Up to and including version 11.2, fixed tables are excluded.
However, from version 12.1 onward, fixed tables are included.
auto
The job determines which objects are processed.
oracle
Only objects belonging to the data dictionary, except fixed tables, are processed.
To get the default value of all parameters without executing the
get_param
function several times, you can use the
following query:
5
SQL> SELECT sname AS parameter, nvl(spare4,sval1) AS default_value
2 FROM sys.optstat_hist_control$
3 WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE','METHOD_OPT',
4 'NO_INVALIDATE','GRANULARITY','AUTOSTATS_TARGET');
PARAMETER DEFAULT_VALUE
---------------- ---------------------------
CASCADE DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE NULL
METHOD_OPT FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
GRANULARITY AUTO
AUTOSTATS_TARGET AUTO
To restore the default values to the original setting, the
dbms_stats
package provides the
reset_param_defaults
procedure.
The Contemporary Way
As of version 11.1, the concept of setting default values for parameters, which are called
preferences
, is strongly
enhanced compared to version 10.2. In fact, not only can you set the global defaults, you can also set defaults at
the table level. One consequence of these enhancements is that the
get_param
function and the
set_param
and
reset_param_defaults
procedures described in the preceding section are obsolete.
You can change the default values of the parameters
autostats_target
,
cascade
,
concurrent
,
estimate_percent
,
degree
,
method_opt
,
no_invalidate
,
granularity
,
publish
,
incremental
,
stale_percent
,
table_cached_blocks
(as of version 11.2.0.4), and, as of version 12.1,
global_temp_table_stats
,
incremental_staleness
, and
incremental_level
. To change them, the following procedures are available in the
dbms_stats
package:
set_global_prefs
sets the global preferences. It replaces the
set_param
procedure.
•
set_database_prefs
sets the database preferences. The difference between global and
database preferences is that the latter aren't used for the data dictionary objects. In other
words, database preferences are used only for user-defined objects.
•
