Database Reference
In-Depth Information
Comparing Object Statistics
In the following three common situations, you end up with several sets of object statistics for the very same object:
dbms_stats
package (through the parameters
statown
,
stattab
, and
statid
) to save the current statistics in a backup table.
•
When you instruct the
dbms_stats
package is used to gather object statistics. In fact, as already
described in the “Restoring Object Statistics” section, the package automatically keeps a
history of the object statistics instead of simply overwriting them when a new set is gathered.
•
Whenever the
•
As of version 11.1, when you gather pending statistics.
It's not unusual to want to know the differences between two sets of object statistics. As of version 10.2.0.4,
you're no longer required to write queries yourself to make such a comparison. You can simply take advantage of new
functions in the
dbms_stats
package.
The following example, an excerpt of the output generated by the
comparing_object_statistics.sql
script,
shows the kind of report you can get:
###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : T
OWNER : CHRIS
SOURCE A : Statistics as of 10-APR-13 20.05.07.106712 +02:00
SOURCE B : Current Statistics in dictionary
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
T T A 10088 110 37 5865
B 12691 253 37 5036
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
ID A 9862 .000101399 NO 0 4 C103 C2646 5734
B 12645 .000079082 NO 0 5 C108 C3026 5018
VAL1 A 3203 .000454959 YES 0 5 3D382 C2240 5779
B 2990 .000489236 YES 0 5 3D421 C2251 4926
VAL2 A 9 .000049759 YES 0 3 C10C C114 5842
B 9 .000039438 YES 0 3 C10C C114 5031
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
