11G新特性，比較統計資訊

In the following three common situations, you end up with several sets of object statistics for
the very same object:
• When you instruct the package dbms_stats (through the parameters statown, stattab,
and statid) to back up current statistics in a user-defined table.
• As of Oracle Database 10g, whenever the package dbms_stats is used to gather statistics.
In this case, the package automatically keeps a history of the object statistics instead of
simply overwriting them when a new set is gathered. You can find more information
about how to manage that history in the section “Statistics History” later in this chapter.
• As of Oracle Database 11g, when you gather pending statistics.
It is not unusual to want to know what the differences between two sets of object statistics are.
As of Oracle Database 10g patchset 10.2.0.4, you are no longer required to write queries yourself
to make such a comparison. You can simply take advantage of the new functions in the package
dbms_stats.
The following example, which is an excerpt of the output generated by the script. comparing_
object_statistics.sql, shows the kind of report you get. Notice how in the first part, you can
see the parameters used for the comparison: the schema and the table name, the definition of
two sources (A and B), and a threshold. This last parameter specifies whether to display only
the object statistics for which the difference (in percent) between the two sets of statistics exceeds
the specified threshold. For example, if you have the two values, 100 and 115, they are recognized
as different only if the threshold is set to 15 or less. The default value is 10. To display all
statistics, the value 0 can be used.

The following are the functions available in the package dbms_stats:
• diff_table_stats_in_stattab compares the object statistics found in a backup table
(specified with the parameters ownname and tabname) with the current object statistics
or another set found in another backup table. The parameters stattab1, statid1, and
stattab1own are provided to specify the first backup table. The second backup table
(which is optional) is specified with the parameters stattab2, statid2, and stattab2own.
If the second backup table is not specified, or set to NULL, the current object statistics
are compared with the object statistics in the first backup table. The following example
compares the current object statistics of the table t with a set of object statistics named
set1 and stored in the backup table mystats:
dbms_stats.diff_table_stats_in_stattab(ownname => user,
tabname => 'T',
stattab1 => 'MYSTATS',
statid1 => 'SET1',
stattab1own => user,
pctthreshold => 10)
• diff_table_stats_in_history compares the current object statistics for one table, or a
set from the history, with other object statistics from the history. The parameters time1
and time2 are provided to specify which statistics are used. If the parameter time2 is not
specified, or set to NULL, the current object statistics are compared to another set from
the history. The following example compares the current object statistics of the table t
with the object statistics of one day ago (for example, prior to a gathering of statistics
that was executed during the night):
dbms_stats.diff_table_stats_in_history(ownname => user,
tabname => 'T',
time1 => systimestamp – 1,
time2 => NULL,
pctthreshold => 10)
• diff_table_stats_in_pending compares the current object statistics for one table, or a
set from the history, with the pending statistics. To specify object statistics stored in the
history, the parameter time_stamp is provided. If this parameter is set to NULL (default),
current object statistics are compared to pending statistics. The following example
compares the current statistics of the table t with the pending statistics:
dbms_stats.diff_table_stats_in_pending(ownname => user,
tabname => 'T',
time_stamp => NULL,
pctthreshold => 10)