wait a second. the CBO takes into consideration your system statistics when you analyze? Is that new in 9i? I thought the export stats and import stats were used if you wanted a 'smaller subset' of data. so you mimic the data stats? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, September 21, 2003 3:34 PM
> 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).