Database Reference
In-Depth Information
It's important to point out that the copy_table_stats procedure doesn't perform a simple, one-to-one copy.
Instead, it's able to change the minimum and maximum values according to the way the partitions are defined. For
example, and given a range partitioned table, the package can derive the minimum and maximum value from the
partition bounds of the destination partition and of the partition preceding it. In addition, as of version 10.2.0.5, you
can scale up or down the number of rows and blocks by setting a scale_factor parameter to a value different than 1
(the default).
If the table on which the copy is performed has derived statistics at the table/index level, then the table/
index level statistics are also amended during the copy. For example, the number of rows is increased, and the
maximum value is set accordingly. A similar thing happens at the partition level when statistics are copied between
subpartitions.
All versions up to 11.2.0.3 contain some bugs in the copy_table_stats procedure. Some of those bugs are
corner cases that you might never hit, but others, depending on the version you're using, impact core functionality. Search
for “copy table stats” on the Oracle Support website to learn whether the version you are using requires particular attention.
Tip
Scheduling Object Statistics Gathering
The query optimizer requires object statistics to correctly carry out its duties. Thus, when a new database is created,
a job calling the gather_database_stats_job_proc procedure in the dbms_stats package is set up by default.
The gather_database_stats_job_proc procedure performs essentially the same thing that would happen were you
to invoke the gather_database_stats procedure of the dbms_stats package with the options parameter set
to gather_stale and gather_empty . Note that although in version 10.2 a regular job is used, as of version 11.1
the gathering is integrated in the automated maintenance tasks. In both cases, the job is scheduled using the
dbms_scheduler package, not with the dbms_job package.
Up to and including version 11.2, by default the job targets all objects except for fixed tables. As a result,
you have to take care of fixed tables yourself by gathering object statistics for them when the database engine is at peak
load. i advise you to do it at peak load because their content is strongly dependent on the load. Think, for example, about
x$ksuse , which contains one row for each session.
Caution
The aim of the next two sections is to provide detailed information about the configuration used for scheduling
the default job. The first section covers 10g. The next section covers later versions.
The 10g Way
The gather_stats_job job is automatically setup in 10g. The current configuration, which in the following example is
the default configuration of version 10.2, can be displayed with the following queries. The output was generated with
the dbms_stats_job_10g.sql script:
SQL> SELECT program_name, schedule_name, enabled, state
2 FROM dba_scheduler_jobs
3 WHERE owner = 'SYS'
 
 
Search WWH ::




Custom Search