Database Reference
In-Depth Information
COLUMN USAGE REPORT FOR CHRIS.T
...............................
1. ID : EQ EQ_JOIN
2. PAD : RANGE
3. VAL1 : EQ
4. VAL3 : EQ EQ_JOIN
in addition, as of version 11.2.0.2, the
dbms_stats
package also provides a way to reset the content of
col_usage$
. You can do this through the
reset_col_usage
procedure.
•
degree
specifies the degree of parallelism used while gathering statistics for a single
object. To use the degree of parallelism defined at the table/index level, specify the
value
NULL
. To let the procedure determine the degree of parallelism, specify the
dbms_
stats.default_degree
constant. The default value is
NULL
(this default value can be
changed—see the section “Configuring the dbms_stats Package” later in this chapter).
Note that the processing of several objects is serialized except when concurrent
statistics gathering is used. This means parallelization is useful only for speeding up
the gathering of statistics on large objects. To parallelize the processing of several
objects at the same time, manual parallelization (that is, starting several jobs) is
necessary. Refer to Chapter 15 for detailed information about parallel processing.
Parallel gathering of object statistics is available only with the Enterprise Edition.
CONCUrreNt StatIStICS GatherING
by default, the
dbms_stats
package only parallelizes the gathering at the table or partition level (based on the
degree
parameter). in other words, at any given time, only a single table or partition is processed. if the database
server has plenty of free resources and a number of tables or partitions have to be processed, it may be sensible
to simultaneously process them. For that purpose, as of version 11.2.0.2, Oracle Database provides a new
gathering mode called
concurrent statistics gathering
.
Concurrent statistics gathering is implemented in the
gather_*_stats
procedures. To control it, the
concurrent
preference is available. Depending on the version you're running, it can be set to the following values:
•
11.2:
FALSE
to disable the feature (this is the default value), or
TRUE
to enable the feature.
•
12.1:
OFF
to disable the feature (this is default value),
MANUAL
to enable the feature only for
manual statistics gathering,
AUTOMATIC
to enable the feature only for automatic statistics
gathering, or
ALL
to enable the feature for all statistics gathering.
To take advantage of concurrent statistics gathering, the following requirements should be fulfilled:
•
The
job_queue_processes
initialization parameter should be set to at least 4. This is necessary
because to simultaneously process several tables or partitions, the
dbms_stats
package submits
a number of jobs to the Scheduler.
•
The resource Manager should be enabled. Without it, because the
dbms_stats
package doesn't
control how many concurrent jobs are run simulteneously, system load could go out of control.
in fact, concurrent statistics gathering relies on the Scheduler and the resource Manager to
