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

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