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: there are still many cases where the rule-based optimizer will result in faster Oracle queries.

Re: there are still many cases where the rule-based optimizer will result in faster Oracle queries.

From: <fitzjarrell_at_cox.net>
Date: Mon, 15 Oct 2007 05:45:50 -0700
Message-ID: <1192452350.294135.10400@y27g2000pre.googlegroups.com>


On Oct 15, 6:40 am, "Jack" <n..._at_INVALIDmail.com> wrote:
> The very same query can be run 1175 times faster ;)
> Server 9.2.0.8
>
> real: 385635
> Statistics
> ----------------------------------------------------------
> 7344195 recursive calls
> 0 db block gets
> 26410200 consistent gets
> 0 physical reads
> 0 redo size
> 1497 bytes sent via SQL*Net to client
> 422 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 15 rows processed
>
> -------------------------------------------------------------
> After command: alter session set optimizer_goal=ALL_ROWS;
>
> real: 328
> Statistics
> ----------------------------------------------------------
> 108 recursive calls
> 0 db block gets
> 255 consistent gets
> 0 physical reads
> 0 redo size
> 1497 bytes sent via SQL*Net to client
> 422 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 15 rows processed
>
> Well, it was hole time using RBO, so not comparing to CBO

Interesting that you post nothing of substantial proof of your claim: no query, no explain plan, no optimizer_goal setting for the first set of statistics. And it is extremely funny that you think ALL_ROWS is an RBO option; it's a cost-based option optimized for minimum resource consumption.

I'm still waiting for your evidence that the RBO is 1175 times faster than the CBO with correctly computed statistics.

David Fitzjarrell Received on Mon Oct 15 2007 - 07:45:50 CDT

Original text of this message

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