Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Copying statistics : used a lot ????

RE: Copying statistics : used a lot ????

From: Boris Dali <>
Date: Sun, 21 Sep 2003 11:34:36 -0800
Message-ID: <>

What a timely thread! We've been discussing this very issue back and forth for some time now in our organization.

We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a "close mirror" of the production one?

The argument of those against a "similar to a Prod box" is simple: "Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources?"

So if we "can't afford" what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their "can't afford" is going to cost them more in the long run?
(easlier said than done)

As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states:
"Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call..."

I don't follow. Does this imply that with importing stats we can't get 100% identical CBO
decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we "replicated" all the stats from Prod and it is not a simple task? Or something else?

"... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution ..."

This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS?
Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range?
Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a "general" statement?

"... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for..."

The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me?

Boris Dali.

Post your free ad now!
Please see the official ORACLE-L FAQ:
Author: Boris Dali

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun Sep 21 2003 - 14:34:36 CDT

Original text of this message