Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: gather stats slows performance?

Re: gather stats slows performance?

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 07 Aug 2004 20:30:33 GMT
Message-ID: <Xns953E896C53FFFSunnySD@68.6.19.6>


streib_at_cs.indiana.edu (Allan Streib) wrote in news:e334e4a7.0408071154.5171332a_at_posting.google.com:

> Oracle 9i R2.
>
> Application is an ASP app that has a fairly long history. There is
> virtually no use of stored procs, and most of the queries are simply
> static sql passed to Oracle via ODBC (i.e. no bind variables). I know
> this is not ideal but we can't fix Rome in a day. Given this is the
> case, I noticed that every time I look at a query plan it is using the
> RBO. So I think that we probably don't have statistics in our schema.
> (I now know how to check for that, but did not before I tried the
> following)
>
> dbms_stats.delete_schema_stats
> dbms_stats.gather_schema_stats (using defaults for all the options)
>
> After stats were gathered, queries now seem to be using the CBO. A
> few particularly slow queries are now much faster, but overall,
> application responsiveness has slowed. To confirm this, I deleted
> stats and the overall performance picked back up while the few
> troublesome queries are slow again.
>
> My guess as to why this is, is that the CBO is slower than the RBO,
> even though it may come up with a better plan. And since we are not
> using bind variables, virtually every query has to be parsed from
> scratch. Is this a reasonable conjecture?
>
> Until we can get our applictation refactored to use more stored procs
> and bind variables, are we likely to be better off without using
> statistics and the CBO?
>
> Many thanks,
>
> Allan
>

Which tasts better, an apple or an orange?

WRT the CBO vs. the RBO, which is "better"? It depends.
It depends upon many factors.

Maybe by using OUTLINEs you can have the best of both. Received on Sat Aug 07 2004 - 15:30:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US