It did however contain a (in your case minor) error, logical IO is better
defined as (consistent gets + db block gets)
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Shyamala" <o_shyam_at_yahoo.com> wrote in message
news:9e3c6eb3.0304072041.60f8ae22_at_posting.google.com...
> Thanks a lot Niall !
> That was a helpful piece of info for me..
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:<3e918826$0$4850$ed9e5944_at_reading.news.pipex.net>...
> > 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...
> > > 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.
Received on Tue Apr 08 2003 - 03:06:17 CDT
