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 <[EMAIL PROTECTED]>
yahoo.com> cc:
Sent by: Subject: Consistent Gets?
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
