Re: Stats gathering (8i) to non-data dictionary tables

Sybrand Bakker wrote:
> On 2 Aug 2005 08:00:45 -0700, "Mike Collier" <mcollier_at_btinternet.com>
> wrote:
>
>

>>Anyway, my understanding here is that stattab (if used) will be filled
>>with a backup of the current stats PRIOR to them being gathered (so
>>they can be restored if all goes wrong).

I don't know why I have to follow-up you so often lately, but even the 10g documentation states this (in the examples section):

<quote>
Saving Original Statistics and Gathering New Statistics

Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:

BEGIN
    DBMS_STATS.CREATE_STAT_TABLE ('hr', 'savestats');     DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); END; This operation gathers new statistics on the employees table, but first saves the original statistics in a user statistics table: hr.savestats.

If the user believes that the new statistics are causing the optimizer to generate poor plans, then the original statistics can be restored as follows:

BEGIN
    DBMS_STATS.DELETE_TABLE_STATS ('hr', 'employees');     DBMS_STATS.IMPORT_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); END;
</quote>

And the description of stattab in the summary of DBMS_STATS Subprograms is as follows: <quote>
stattab         

User statistics table identifier describing where to save the *current* statistics. </quote>

[Note: the emphasis of current is mine and not in the original text]

Now your expirience might differ, (and I haven't used the stattab option in my life so I'm not qualified to comment based on experience), but would you care to share what your understanding of the stattab option is, and also if possible support them by a short demonstration or a link to the relevant documentation?

Thanks

Holger Received on Wed Aug 03 2005 - 08:44:28 CDT