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).
Received on Sun Sep 21 2003 - 14:34:36 CDT
