Thanks for your response Riyaj.
I initially had the same thought so I had re-run
the queries a no. of times, all giving the same
(inconsistent) results. I also confirmed from
v$mystat that "consistent gets" as reported by
autotrace were being reported proportionately to
"no work - consistent read gets" - an indication
to me anyway that it was not having to re-create
the blocks for the consistent view.
Any other ideas?
Paul
- Riyaj_Shamsudeen_at_i2.com wrote:
>
> Hi
> 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).
>
>
>
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 - 13:22:32 CST
