Object Statistics - Troubleshooting Oracle Performance - page 261

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................

INDEX: T_PK
...........

T_PK I A 10000 20 10000 1 1 9901 1 10000
B 12500 27 12500 1 1 12300 1 12500
###############################################################################

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're recognized as different only if the threshold is set to 15 or
less. The default value is 10. To display all object statistics, the value 0 can be used.
The following are the functions available in the dbms_stats package to generate such a report:
•

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
parameters of the second backup table aren't specified, or if they're 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 with a set of object statistics named set1 and
stored in the mystats backup table:

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, for one table, either the current object statistics with
object statistics from the history or two sets of object statistics from the history. The parameters
time1 and time2 are provided to specify which statistics are used. If the parameter time2
isn't 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 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));