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" <ORACLE-L_at_fatcity.com>
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"
> <Rajendra.Jamadagni_at_ESPN.COM> 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
> > stephane.paquette_at_standardlife.ca
> > <mailto:stephane.paquette_at_standardlife.ca>
> >
> >
> >
> > -----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
> > stephane.paquette_at_standardlife.ca
> > <mailto:stephane.paquette_at_standardlife.ca>
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Stephane Paquette
> > INET: stephane.paquette_at_standardlife.ca
> >
> > 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: ListGuru_at_fatcity.com (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: chris.grabowy_at_lmco.com
> >
> > 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: ListGuru_at_fatcity.com (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: stephane.paquette_at_standardlife.ca
> >
> > 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: ListGuru_at_fatcity.com (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: boris_dali_at_yahoo.ca
>
> 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: ListGuru_at_fatcity.com (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: rgaffuri_at_cox.net
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: ListGuru_at_fatcity.com (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:54:37 CDT
