| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: query slow in 9i, but not slow in 8i
We have been stress testing our system with as many as 3,000 concurrent
users(its simulated) and it has scaled well. We have histograms on all
tables. Our data set is approximately 500 GBs and we often have multi-table
joins. Most of the queries we are testing are OLTP type result sets. We have
not tested the enterprise reporting and the 'calculations' we have to do to
see how that scales. At this point most all of our queries use bind
variables so they are not using the histograms(they are for the reporting
to come in the future).
We are testing on a 2 Node RAC each node has a 4 CPU Solaris system.
Anyone have any hard data to back this up?
> I don't have access to the data to back this up, but I have seen PARSE
> call response time move from over a minute to less than a second by
> eliminating histograms from the equation. I think when I last saw this,
> it was an Oracle8 system.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
> Sent: Monday, March 01, 2004 10:30 PM
> To: oracle-l_at_freelists.org
> Subject: Re: RE: query slow in 9i, but not slow in 8i
>
> There surely is some "cost" involved in evaluating a histogram during
> the
> parse phase, but I doubt that it will be significant enough to be
> measurable. But histograms change the estimates of the CBO and it is
> quite
> possible that the presence of a histogram on a column where the content
> is
> not skewed enough to really warrant a histogram can change the estimates
>
> enough to cause the optimizer to use a different, slower access path
> than
> without the histogram.
>
> At 09:14 PM 3/1/2004, you wrote:
>
> >I am unfortunately speaking from heresay, however I heard once that
> having
> >histograms all over the place can slow down parsing as they have to be
> >inspected. I was left with the impression that unless the data is
> skewed
> >enough to warrant a histogram then there actually is a negative cost
> >associated with having too many histograms.
> >
> >Surely someone on this list can either support me or shoot me down on
> this
> >one?
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 02 2004 - 21:11:40 CST
 |
 |