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-----
Sent: Tuesday, 13 February 2001 8:41
To: Multiple recipients of list ORACLE-L
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).
--
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).
