Hi Steve,
Spot on! Thanx for your help.
Up until now, I was unaware that ARRAYSIZE
impacts the no. of consistent gets. Well, one
learns every day ...
Thanx once again for your assistance
Paul
--- Steve Adams <[EMAIL PROTECTED]> wrote:
> Hi Paul,
>
> Thanks for sending the V$MYSTAT data offline.
>
> Try this explanation ...
> The first query is fetching all the rows using
> an array size of 15.
> The V$MYSTAT data showed a difference of about
> 25000 'user calls',
> and the 'SQL*Net roundtrips to/from client'
> data below matches that.
> The number of additional 'consistent gets' is
> not much less than that.
> This suggests that there is an extra consistent
> get for each fetch,
> unless the previous fetch finished at a
> database block boundary.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> From: Paul Parker
> [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 13 February 2001 8:41
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Consistent Gets?
>
>
> Hi Steve, thanks for your response.
>
> I had analyzed the tables - chain_cnt = 0.
> Just
> to verify, I also checked "table fetch
> continued
> row" which was 0 on all my tests.
>
> Any other ideas?
>
> Paul
>
> --- Steve Adams <[EMAIL PROTECTED]>
> wrote:
> > Hi Paul,
> >
> > Analyze the table and see if you have any
> > chained rows. If there are chained
> > rows and if the STATE_CODE field is not
> always
> > in the last row piece, then a
> > extra consistent gets will be needed to get
> the
> > column values from the trailing
> > row pieces of chained rows that are not
> > excluded by the where clause predicates.
> >
> > @ Regards,
> > @ Steve Adams
> > @ http://www.ixora.com.au/
> > @ http://www.christianity.net.au/
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, 13 February 2001 3:31
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi all,
> >
> > Could someone attempt to explain the
> difference
> > in the no. of "consistent gets" reported for
> > these 2 queries?
> >
> > I have a table (TEST1) made up of 11,333
> > blocks.
> > No indexes on this table. I run two queries,
> > both reported to do full table scans (as
> > expected), one returning all the rows from
> the
> > table and one with a bogus condition
> resulting
> > in
> > no rows returned. I expected, that since
> both
> > queries did full table scans, that the amount
> > of
> > IO would be the same. Yet the query which
> > returned data did 3 times as much IO as the
> one
> > which did not. Output follows :
> >
> >
> > 12:08:16 T10-SERVCBO-CH> @p2
> > 12:08:22 T10-SERVCBO-CH> set autotrace
> > traceonly
> > exp stat
> > 12:08:22 T10-SERVCBO-CH> select
> > 12:08:22 2 *
> > 12:08:22 3 from
> > 12:08:22 4 test1
> > 12:08:22 5 where
> > 12:08:22 6 pay_dealer_date >= '01/01/2000'
> > 12:08:22 7 -- and state_code = 'AB'
> ----
> > BOGUS CONDITION
> > 12:08:22 8 ;
> >
> > 375043 rows selected.
> >
> > Elapsed: 00:00:55.46
> >
> > Execution Plan
> >
>
----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 TABLE ACCESS (FULL) OF 'TEST1'
> >
> > Statistics
> >
>
----------------------------------------------------------
> > 0 recursive calls
> > 15 db block gets
> > 35581 consistent gets
> > 10575 physical reads
> > 0 redo size
> > 66817080 bytes sent via SQL*Net to client
> > 2775646 bytes received via SQL*Net from
> > client
> > 25004 SQL*Net roundtrips to/from
> client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 375043 rows processed
> >
> >
> > 12:09:18 T10-SERVCBO-CH> ed p2
> >
> > 12:09:33 T10-SERVCBO-CH> @p2
> > 12:09:35 T10-SERVCBO-CH> set autotrace
> > traceonly
> > exp stat
> > 12:09:35 T10-SERVCBO-CH> select
> > 12:09:35 2 *
> > 12:09:35 3 from
> > 12:09:35 4 test1
> > 12:09:35 5 where
> > 12:09:35 6 pay_dealer_date >= '01/01/2000'
> > 12:09:35 7 and state_code = 'AB' ----
> > BOGUS
> > CONDITION
> > 12:09:35 8 ;
> >
> > no rows selected
> >
> > Elapsed: 00:00:03.43
> >
> > Execution Plan
> >
>
----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 TABLE ACCESS (FULL) OF 'TEST1'
> >
> >
> >
> > Statistics
> >
>
----------------------------------------------------------
> > 0 recursive calls
> > 15 db block gets
> > 11337 consistent gets
> > 10573 physical reads
> > 0 redo size
> > 1860 bytes sent via SQL*Net to client
> > 313 bytes received via SQL*Net from
> > client
> > 1 SQL*Net roundtrips to/from
> client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 0 rows processed
> >
> > 12:09:38 T10-SERVCBO-CH> spool off
> >
> >
> > What am I missing here? Any help
> appreciated.
> >
> > Thanx
> > Paul
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Steve Adams
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- (858)
> 538-5051
> > FAX: (858) 538-5051
> > San Diego, California -- Public
> Internet
> > access / Mailing Lists
> >
>
--------------------------------------------------------------------
> > To REMOVE yourself from this mailing list,
> send
> > an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling
> > of 'ListGuru') and in
> > the message BODY, include a line containing:
> > UNSUB ORACLE-L
> > (or the name of mailing list you want to be
> > removed from). You may
> > also send the HELP command for other
> > information (like subscribing).
>
>
>
__________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo!
> Mail - only $35
> a year! http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Paul Parker
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send
> an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling
> of 'ListGuru') and in
> the message BODY, include a line containing:
> UNSUB ORACLE-L
> (or the name of mailing list you want to be
> removed from). You may
> also send the HELP command for other
> information (like subscribing).
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Parker
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
