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? TIA, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Our production and test systems are same ... test > lags 24 hours behind > production that's all. But I have successfully used > dbms_stats to copy over > stats from production to test on a table by table > basis to verify explain > plans. > > My opinion WAD - Works as designed ... remember to > take a backup of existing > stats on test in a separate table so you can reload > them when needed > quickly. > > My experience is on 9ir2 only for this feature. > Raj > ---------------------------------------------------------------------------- > ---- > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -----Original Message----- > Sent: Friday, September 12, 2003 10:29 AM > To: Multiple recipients of list ORACLE-L > > > Well no, I have bought the Expert one on one book. > I check on his web site and I found one reference > where he addresses the use > of changing the stats. > > Usually you can find what is its opinion just by the > tone, but this time I > was not able to see if he's against or not on this. > > Can you share more of what's in the book ? > > > Stephane Paquette > Administrateur de bases de donnees > Database Administrator > Standard Life > www.standardlife.ca > Tel. (514) 499-7999 7470 and (514) 925-7187 > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > > -----Original Message----- > Grabowy, Chris > Sent: Thursday, September 11, 2003 5:44 PM > To: Multiple recipients of list ORACLE-L > > > By chance, do you have Tom Kyte's latest book? > Effective Oracle by Design?? > > He states his opinion on this approach on page 30, > section entitled "Test > Against Representative Data". > > -----Original Message----- > Stephane Paquette > Sent: Thursday, September 11, 2003 4:38 PM > To: Multiple recipients of list ORACLE-L > > > HI, > > I was wandering if a lot of people are copying > statistics using dbms_stats > from production to test environment to see what will > be the access plan. > > If not used, why ? no time to look at it, bugged, > not usefull ,... ? > > > Stephane Paquette > Administrateur de bases de donnees > Database Administrator > Standard Life > www.standardlife.ca > Tel. (514) 499-7999 7470 and (514) 925-7187 > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Stephane Paquette > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Grabowy, Chris > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Stephane Paquette > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). > > ********************************************************************This > e-mail message is confidential, intended only for > the named recipient(s) above and may contain > information that is privileged, attorney work > product or exempt from disclosure under applicable > law. If you have received this message in error, or > are not the named recipient(s), please immediately > notify corporate MIS at (860) 766-2000 and delete > this e-mail message from your computer, Thank > you.*********************************************************************2 > ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
