Re: 7.3.4 Statistics Problem

> Does anyone have tips or tricks for copying stats
> from a 7.3.4 production volume database into
> another schema which has smaller tables, for
> performance testing using the cost-based
> optimiser?
>

I don't think this is advisable, but it does raise in interesting question in relation to tuning queries on large production databases. I'm curious as to whether anyone has a good solution to this. In my experience it has been possible to beta-test batch programs directly on prod, through precautions such as

• testing logic on test DB then tuning performance on prod (usually not required)
• restricting the rows accessible (through multiple/redundant means)
• backing up (withing the DB) all rows which could be potentially updated (both prior to test and prior to prod). Storage requirements for backing up a single col with it's PK have not been an issue to date, and I like the rollback potential it gives me
• creating a "test" mode - in test mode the program doesn't update the prod tables, but instead dumps results to a special table, usually only holding the col to be updated and the PK of the target table. Reporst linking this table to prod tables can be given to users for signoff.

Tuning batch programs seems easy though - it's usually obvious what can be done to speed them up, just a question of whether it ís/isn't required or gives value to the customer (given the other tasks requiring time). End-user queries are another issue. Changing stats on a data warehouse can potentially cause large changes to the runtime of reports they've built. If runtime increases 100% they're justifiably annoyed. If the report now fails .... well there's another PL/SQL task that'll have to be done today.

Thoughts anyone?

Richard Gowan Received on Sun Aug 27 2000 - 04:03:12 CDT