Statistics

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Fri, 11 Jan 2002 16:20:59 GMT
Message-ID: <3c3f0e51.290467204@news.alt.net>

I'd like to preserve old statistics when GATHERing STALE.

1. The DBMS_STATS.GATHER_SCHEMA_STATS procedure has two parameters, STATTAB and STATID. If I specify these when doing a GATHER STALE to the new statistics go there, or the old ones that were just replaced by the new ones?

I would assume that the new ones go there, and that the appropriate way of doing ti would be to use EXPORT_SCHEMA_STATS to archive the old statistics before GATHERing new ones.

My confusion comes from

Oracle8i Designing and Tuning for Performance Release 2 (8.1.6)
Part Number A76992-01
Chapter: 8, "Gathering Statistics"
Section: "Generating Statistics"
Sub-Section: Gathering New Optimizer Statistics

It says

<<

Before gathering new statistics for a particular schema, use the

DBMS_STATS.EXPORT_SCHEMA_STATS procedure to extract and save existing
statistics. Then, use DBMS_STATS.GATHER_SCHEMA_STATS to gather new
statistics. You can implement both of these with a single call to the

GATHER_SCHEMA_STATS procedure.
<<

The first line makes sense. The second line, however, mentions it being done with a single call. How can I GATHER new statistics and archive the old statistics in one call?

2) If I EXPORT_SCHEMA_STATS is there anyway to export only what changed, and not everything? Or would I have to export each object separately by first doing a LIST STALE?

Brian Received on Fri Jan 11 2002 - 10:20:59 CST