| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Rule vs. Cost
Karl M. Nordquist wrote:
>
> Jim,
>
> We have Oracle v7.3.4.x here and everytime we try and go to cost base,
> everything just gets dragged down the tube, and we have indexes, primary keys,
> foreign keys with child indexes. Now, with that said, I do believe that we need
> more education on the matter before we can say that there is something wrong
> with v7's optimizer, but we have had no luck with it so far.
>
> HTH
>
> Karl Nordquist
> Application Development Supervisor
> Bright Wood Corporation
> Madras, OR
> karln_at_brightwood.com
>
> jawa wrote:
>
> > Rule-based optimization runs more efficiently than cost on our application.
> > The supplier told us that the version 7 optimizer has problems. Is this
> > true, or is it that their application was not written by folks knowledgeable
> > of the hints and tricks designed to take full advantage of cost-based
> > optimization? For instance, I ran a small benchmark report on the schema
> > and found: (1) >40 tables with no index on them at all (even small tables
> > without an index become the driving table), and (2) >100 foreign keys
> > missing child index references (all having the potential to put locks on the
> > parent table). Could these contribute to a cost-based scenario that would
> > perform poorly, or is it true that version 7's optimizer has problems like
> > the supplier alleges? Has anyone else experienced a similar case study?
> >
> > --
> > Jim Wadas
> > Information Technology Solutions and Services (ITSS)
> > Motorola Systems Solutions Group (SSG)
> > Scottsdale, AZ 85257
> > (480) 441-8196
> > Jim.Wadas_at_motorola.com
My subjective experience on the optimisers is:
RBO - obsession with indexes which is fine for OLTP but causes grief when querying larger result sets.
CBO v7.1 - didn't work at all well
CBO v7.2, 7.3 - works a lot better but tends toward to full scans and hash joins too often
CBO 8.0 - fixed the hash join 'problem' but still tends too much toward scans
CBO 8.1 (8i) - so far seems to be better balanced from what I've used of
it.
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Nov 09 1999 - 03:42:14 CST
 |
 |