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 <steve.adams_at_ixora.com.au> 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: steve.adams_at_ixora.com.au
>
> 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: ListGuru_at_fatcity.com (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: paul_g_parker_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Feb 12 2001 - 16:51:23 CST
