Re: Consistent Gets?

From: Tim Sawmiller <sawmillert_at_state.mi.us>
Date: Mon, 12 Feb 2001 11:26:53 -0800
Message-ID: <F001.002B1DB7.20010212102525@fatcity.com>

I believe it's because the query that returned rows had to assemble a result set, hence the extra consistent gets. The second query had no result set, so much less work was done (less consistent gets).

>>> paul_g_parker_at_yahoo.com 02/12/01 12:30PM >>>
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

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: sawmillert_at_state.mi.us

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 - 13:26:53 CST