Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Understanding the statistics
consistent gets measures logical IO,
physical reads measures physical IO
your goal should be to minimize response time which usually equates to minimising logical IO. In this case the same mode minimises both logical and physical IO and so should be way faster.
-- Niall Litchfield Oracle DBA Audit Commission UK "Shyamala" <o_shyam_at_yahoo.com> wrote in message news:9e3c6eb3.0304070541.726c4827_at_posting.google.com...Received on Mon Apr 07 2003 - 09:16:06 CDT
> Can anyone please help me out in understanding the statistics given
> below for the same query executed under COST-BASED and RULE-BASED
> optimizer modes :
>
> Statistics captured when executed using Cost-based optimizer mode :
> ===================================================================
>
> Statistics
> ----------------------------------------------------------
> 506 recursive calls
> 4 db block gets
> 134 consistent gets
> 7 physical reads
> 0 redo size
> 2108 bytes sent via SQL*Net to client
> 420 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 10 sorts (memory)
> 0 sorts (disk)
> 4 rows processed
>
> Statistics captured when executed using Rule-based optimizer mode :
> ===================================================================
>
> Statistics
> -------------------------------------------------------
> 475 recursive calls
> 0 db block gets
> 15293 consistent gets
> 273 physical reads
> 0 redo size
> 1946 bytes sent via SQL*Net to client
> 420 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 4 rows processed
>
> Which optimizer mode is better for the query under consideration,
> given the above listed statistics ? Please revert with answers.