Re: Consistent Gets?

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Mon, 12 Feb 2001 10:41:58 -0800
Message-ID: <F001.002B1D41.20010212100522@fatcity.com>

This may be due to commit cleanout mechanism. After populating the table, your commit simply marks the transaction as completed in the rollback segment header and does not clean the rows in the block. So the flags in the row header portion of the block indicates that the transaction is open and active. When you do a select on those rows Oracle sees that the transaction is open and goes to the rollback segment header to check the status of the transaction, and then marks the row headers to committed state.

When you do the select second time, since the row headers indicates the commit status, the session doesn't need to do that much work to get the consistent data.

To verify this behavior, do the first select again and you could see comparable consistent gets.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my employer. Use at your own risk"

                                                                                       
                         
                    Paul Parker                                                        
                         
                    <paul_g_parker@        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>  
                    yahoo.com>             cc:                                         
                         
                    Sent by:               Subject:     Consistent Gets?               
                         
                    root_at_fatcity.co                                                    
                         
                    m                                                                  
                         
                                                                                       
                         
                                                                                       
                         
                    02/12/01 11:30                                                     
                         
                    AM                                                                 
                         
                    Please respond                                                     
                         
                    to 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

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:
INET: Riyaj_Shamsudeen_at_i2.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