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

Home -> Community -> Usenet -> c.d.o.misc -> Re: "a tale of two optimizers"

Re: "a tale of two optimizers"

From: -=< a q u a b u b b l e >=- <aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com>
Date: Fri, 5 Nov 1999 01:44:14 -0000
Message-ID: <7vtcpf$9b$1@news5.svr.pol.co.uk>


attwoody <attwoody_at_my-deja.com> wrote in message news:7vsuji$dt2$1_at_nnrp1.deja.com...
> Greetings, all,
>
> I'm supporting a couple of databases on a IBM RS/6000 S70, with
> eight (8) 125MHz processors, 2GB RAM and about 72GB of SSA disks.
> The application is PeopleSoft 6.10.20, Fixed Assets and AP/GL, with
> AIX 4.3.1 and Oracle 7.3.4.4.
>
> I recently reorganized the databases, and changed the blocksize
> from 4K to 8K. I also changed the OPTIMIZER_MODE from CHOOSE to
> RULE, and adjusted parameters such as LOG_BUFFER, SORT_AREA_SIZE,
> etc.
>
> The first week after the reorganization, Crystal Reports that had
> been running for an hour went down to two minutes; some of the
> nVision reports improved as well. Others still ran over an hour.
> I attributed this to the defragmentation that the reorg took care of
> the more efficient blocksize, and the rebuilding of indexes.
>
> The second week, which began our month-end close, the system went
> to pieces - all eight (8) of the processors on the S70 were _pegged_
> at 85-90%, and complaints rolled in about s-l-o-w response time.
> I kept getting pulled into meetings and asked what the problem was.
>
> I changed the OPTIMIZER_MODE back to CHOOSE, ran an ANALYZE TABLE...
> COMPUTE STATISTICS, and the next day, everything was great!
> The users were happy, they were able to get most of their reports
> done - the nVision reports were still running in about an hour,
> and the Crystal Reports were taking about 2-10 minutes (there were
> a lot more people on the system).
>
> The other thing I noticed was that when the OPTIMIZER_MODE was
> set to RULE, the individual buffer hit ratios were about 99-100%,
> overall about 98%. CONSISTENT_GETS were in the hundreds of millions.
> When I changed it back to CHOOSE, individual buffer hit ratios
> went down a bit, with most being between 97-100%, but the overall
> hit ratio went down to about 80% (it had never gone below 90% when
> the mode was RULE). CONSISTENT_GETS went down to the hundreds of
> thousands.
>
> It seems that RULE caused everything to be in memory, which
> inflated the buffer hit ratio (making me thing everything was
> great).

Rule based chooses to use indexes if one is available... even if it may be more efficient to do a full table scan. This is what the CBO is for... if you have analysed your tables, then it will (hopefully) come up with a more efficient execution plan than the RBO: it knows more about your data. Creating histograms helps even more.

Your consistent gets would be because it has the indexes cached in buffer. Full table scans are last in, first out (I think), which is why your hit ratio goes down. It can be misleading to only look at the hit ratio... it all depends on what kind of query you are executing. Something like a report will not have as high a ratio... you would not want it to since a full table scan is what you are after.

Some DBAs I've spoken to prefer RBO (even in Ora8 systems), but I've always found that that is because they don't know how to make the most of CBO (hell, some didn't even know how to use histograms... I do and I'm just a certified developer).

HTH. It's late so I may not be explaining myself so well, let me know if you need any further info. Received on Thu Nov 04 1999 - 19:44:14 CST

Original text of this message

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