Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Consistent Gets?

RE: Consistent Gets?

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Mon, 12 Feb 2001 13:39:07 -0800
Message-ID: <F001.002B1FC3.20010212134119@fatcity.com>

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).
Received on Mon Feb 12 2001 - 15:39:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US