Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, gaurang.

Asked: June 28, 2001 - 1:45 pm UTC

Last updated: April 30, 2012 - 8:05 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

hi tom

i your discussion regarding consistant gets and db dblock gets
you mention about block read in CURRENT MODE AND consistent mode

Could you please explain exact meaning of this words by giving
some examples?

Thanks

and Tom said...

Ok, when you turn on autotrace in sqlplus you can see these stats.

Lets run a query:

ops$tkyte@ORA817.US.ORACLE.COM> set autotrace traceonly statistics
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from emp;

14 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
1979 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

Here we had 4 db block gets. Those were blocks read in CURRENT MODE. The blocks that were read are actually the blocks that tell us how to FULL SCAN the dept table (data dictionary type of information). We need to get that in CURRENT MODE (as of RIGHT NOW) to get an accurate picture of what the table looks like.

We also had 2 consistent gets -- these are blocks we read in "consistent read" mode -- also known as query mode. This means we were reading them as of the POINT IN TIME the query began. See
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#17882 <code>
for a great discussion of this.


Now, if we do a delete:

ops$tkyte@ORA817.US.ORACLE.COM> delete from emp;

14 rows deleted.


Statistics
----------------------------------------------------------
0 recursive calls
20 db block gets
1 consistent gets
0 physical reads
4220 redo size
1009 bytes sent via SQL*Net to client
796 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

we do lots more db block gets -- why? because in order to delete the data we need to get the block in CURRENT MODE, as it exists right then.

why did we do a consistent get? because the "read" part of the delete uses the consistent read mechanism -- we only delete data that existed in the table as of the point in time the delete began. Consider if DEPT was a 1,000,000 row table instead. It'll take a while to delete all of those rows. As you are deleting however, other sessions are inserting and committing data. This consistent read mechanism makes it so that we only delete the rows that existed WHEN WE BEGAN the delete. We will not delete this new data being inserted.

followup to comment one below

Think of the delete being processed like this:


for x in ( select rowid from emp ) --- CONSISTENT GETS
loop
delete from emp where rowid = x.rowid; --- CURRENT MODE GETS
end loop;

that is in effect what is happening. the READ portion of the delete, the portion of the delete that finds rows to actually remove reads the table in consistent read mode. For each row that it finds that it wants to delete, it does a CURRENT MODE get on that block to get the row as it exists now (so as to modify that row).

Consider what happens when we delete 1, 5, 10, and all rows from emp:


scott@ORA817.US.ORACLE.COM> set autotrace traceonly statistics;
scott@ORA817.US.ORACLE.COM> select * from emp;

14 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
1979 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=1;

1 row deleted.


Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
516 redo size
850 bytes sent via SQL*Net to client
564 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=5;

5 rows deleted.


Statistics
----------------------------------------------------------
0 recursive calls
11 db block gets
1 consistent gets
0 physical reads
1660 redo size
850 bytes sent via SQL*Net to client
564 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=10;

10 rows deleted.


Statistics
----------------------------------------------------------
0 recursive calls
16 db block gets
1 consistent gets
0 physical reads
3080 redo size
850 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=15;

14 rows deleted.


Statistics
----------------------------------------------------------
0 recursive calls
22 db block gets
1 consistent gets
0 physical reads
4272 redo size
850 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.


Each one reads blocks in current mode with a cardinality that relates to the number of rows deleted.

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 28, 2001 - 5:06 pm UTC

I still don't understand why delete takes so many db block gets,
In this case, there are only 14 rows in emp table, if we have 8k db block, all those rows should stay at ONLY one block, what you mean get the block in CURRENT MODE?
if no body else touch the database, it is in CURRENT MODE,
you simply get that block and delete it, ONE db block gets is enough.
if there are some concurrent user modifying the database, i can understand that db block gets goes high, but not in this case. can you explain to me more about it?

A reader, June 29, 2001 - 2:24 pm UTC

Tom:
I create table empback as select * from emp;
set autotrace on;
then select * from empback.
why consistent get and db block gets is different from
select * from emp?
for emp it is 4 and 2;
for empback it is 12 and 6;



current mode AND consistent mode

A reader, December 04, 2003 - 6:42 am UTC

Tom,
I am trying to understand the difference between current mode AND consistent mode.
I was looking at the examples you have given above for 'CURRENT MODE AND consistent mode'. The URL you have pointed to
>></code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#17882 <code>
>>for a great discussion of this.

Is this related to statement-level read consistency and transaction-level read consistency?
Can you please give the book and chapter for the above URL(since OTN moved all docs....)
Book: Concepts guide ....Chapter ....

Thank you

Tom Kyte
December 04, 2003 - 9:47 am UTC

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#2570 <code>



Current mode and consistent mode

Dumitru Iustin Mircea, March 18, 2004 - 6:20 am UTC

Thanks Tom, I finally understood what this means.

still doubts

siva, June 07, 2004 - 2:04 am UTC

<quote>
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace traceonly statistics
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from emp;

14 rows selected.

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
1979 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

Here we had 4 db block gets. Those were blocks read in CURRENT MODE. The blocks that were read are actually the blocks that tell us how to FULL SCAN the dept table (data dictionary type of information). We need to get that in CURRENT MODE (as of RIGHT NOW) to get an accurate picture of what the table looks like.

We also had 2 consistent gets -- these are blocks we read in "consistent read" mode -- also known as query mode. This means we were reading them as of the POINT IN TIME the query began.
<quote>
hi tom,

But i could not still understand the difference you tell in
"POINT IN TIME" for "consistent read" and "AS OF NOW" for "db block gets".

kindly solve this mystery.


Tom Kyte
June 07, 2004 - 8:23 am UTC

see the concepts guide and read about multi-versioning......

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#17882 <code>

multi-versioning is the art of "consistent reads", reads as of a fixed point in time.

Current mode??

Vinnie, June 25, 2004 - 11:19 am UTC

Tom,

I am wondering what would cause my current mode to be so high. Could this be causing the large elapsed time?

UPDATE event SET STATUS = :1
WHERE
ROWID = :2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 221 0.02 0.03 0 0 0 0
Execute 221 37.73 512.03 7101 83011 83749 79839
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 442 37.75 512.07 7101 83011 83749 79839

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41

Rows Row Source Operation
------- ---------------------------------------------------
100 UPDATE
100 TABLE ACCESS BY USER ROWID event


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7097 8.15 223.44
SQL*Net more data from client 772 0.00 0.04
SQL*Net message to client 221 0.00 0.00
SQL*Net message from client 221 2.49 3.33
free buffer waits 152 1.00 120.48
log buffer space 8 0.80 3.17
write complete waits 141 1.00 107.17
log file switch completion 1 0.25 0.25
********************************************************************************


Tom Kyte
June 25, 2004 - 3:56 pm UTC

every row you update will cause a current mode get. you updated 80k rows, one would expect at least 80k current mode gets -- and maybe more for migrated/chained rows.

when you do an update -- we

a) find the rows using consistent read
b) verify the row and update the CURRENT MODE version of it.

Larget elapsed time

Vinnie, June 27, 2004 - 4:43 pm UTC

What would your opinion be on the larget elapsed time detailed below:

UPDATE event SET STATUS = :1
WHERE
ROWID = :2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 221 0.02 0.03 0 0 0 0
Execute 221 37.73 512.03 7101 83011 83749 79839
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 442 37.75 512.07 7101 83011 83749 79839

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41

Rows Row Source Operation
------- ---------------------------------------------------
100 UPDATE
100 TABLE ACCESS BY USER ROWID event


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7097 8.15 223.44
SQL*Net more data from client 772 0.00 0.04
SQL*Net message to client 221 0.00 0.00
SQL*Net message from client 221 2.49 3.33
free buffer waits 152 1.00 120.48
log buffer space 8 0.80 3.17
write complete waits 141 1.00 107.17
log file switch completion 1 0.25 0.25
********************************************************************************


Tom Kyte
June 28, 2004 - 7:57 am UTC

no opinions, the waits say it all?

you've got some problem disks there it would appear. 8.15 seconds to wait for a read? 0.03 seconds average per physical io.

Your write IO is just as bad... free buffer waits, most likely waiting on dbwr to clean out a block in the cache so you can use it. write complete waits, same thing.

consistent gets confusion

Sabya, June 03, 2009 - 8:16 am UTC 

Hi Tom

I had one confusion in this context. I have created a table in my db and inserted 3 rows in it and commited it. I have issued manual checkpoint command a number of times to ensure that there is no dirty buffer present. No one else is working in the db. Then when i issue select * from test table i get the following output :-

SQL> select * from test;


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    39 |     2   (0)|
|   1 |  TABLE ACCESS FULL| TEST |     3 |    39 |     2   (0)|
---------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        239  bytes sent via SQL*Net to client
        238  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

Now why there is 4 consistent gets out here? Consistent gets means data being read from rollback . But here there is no transaction occurring in the system on that table.

Pls explain. Thanks in advance

Tom Kyte
June 04, 2009 - 9:20 am UTC

consistent gets do not mean that.

You are either going to get a block in read consistent mode (as of a point in time) - that is a consistent get, or you will get a block in current mode (as it exists right now - typically for an UPDATE) - that is a db block get.

You are not reading undo in this case, you are reading the segment header information and then the block in question.


In this case, all 4 IO's are logical IO's done in read consistent mode, IF UNDO HAD TO BE APPLIED to make them consistent - it would have been (since it is a consistent get), but it wasn't necessary.

Consistent gets and UNDO

A reader, June 04, 2009 - 3:47 pm UTC

"In this case, all 4 IO's are logical IO's done in read consistent mode, IF UNDO HAD TO BE APPLIED to make them consistent - it would have been (since it is a consistent get), but it wasn't necessary."

So just because it says "consistent" doesn't mean we read from undo. All those consistent reads were the same as current reads because there was no need to read from undo, we read current data. Is that correct?
Tom Kyte
June 08, 2009 - 11:34 am UTC

they are not the same as current reads - NO.

they are blocks gotten in consistent read mode, as of a point in time.

Remember - we are "two" things

a) MULTI-VERSIONING (there are many times more than one version of a given block in the cache - old ones and newer ones - maybe just all old ones and not a current mode one - maybe a current mode one and some older ones)

b) and READ CONSISTENT


a block gotten in consistent read mode is not anything like a block gotten in current mode.

Confused about Consistent gets

Rohit, December 14, 2010 - 5:36 am UTC

Tom, not sure i understood your last few statements.

You said - "consistent gets do not mean that"

Doesn't Consistent gets mean that the data block has been read from the UNDO segments? I know you tried to answered the same question here, but still i do not understand this. Can you please provide some more information on the "consistent gets" statistic????

As per your last explanation on the 4 consistent gets example, i understood that here only the header is being read and there is no UNDO being applied to make them consistent, right? So that also means that consistent gets statistic has 2 parts - first, reading the header and second, actually reading the block after applying the UNDO data. Is this correct?
But how do we know, from seeing the autotrace or tkprof output, that the shown consistent gets are not depicting the case of using UNDO data to provide a read consistent view? Instead it could be just reading the header????

Thanks
Rohit
Tom Kyte
December 14, 2010 - 7:27 am UTC

to put it into context:

...
Now why there is 4 consistent gets out here? Consistent gets means data being read from rollback .
But here there is no transaction occurring in the system on that table.

Pls explain. Thanks in advance


Followup June 4, 2009 - 9am Central time zone:


consistent gets do not mean that.
......


consistent gets are blocks retrieved in a read consistent mode. We need block "x" as of time "y"

we will go to the buffer cache and look for block "x". if it is as of time "y" in the cache - we are DONE. No undo is touched. This is normal.

If it is not as of time "y", then we read undo and roll it back until it is as of time "y"


A consistent get MAY or MAY NOT represent undo reads. Mostly they represent gets from the buffer cache.

Continuing from last post

Rohit, December 16, 2010 - 11:34 am UTC


Tom,

Then that should be CURRENT read, isn't it? It is reading the block from buffer cache as it is, without applying any UNDO.

If not, Can you please explain CURRENT reads by using the same X Y example and how does is it different from CONSISTENT? You have explained the definitions several times but unfortunately this last example has triggered more confusion.

Alright Tom, i have some more questions on the same topic -

Any SQL Statement (DML or Select) will undergo any of these 3 reads - consistent, current or Physical, right?

1. Of these 3, What is the first attempt when a SQL Statement is fired?

2. How does it decides in the first place that which of these 3 is required? Correct me if i am wrong, but it first goes to buffer cache and if block is not available there, it goes to the disk to fetch it. Is it?

3. Then lets assume that the data block is found in buffer cache, then how does it decides if a consistent read is sufficient or a current read is required?

I am basically trying to create a flow chart of the way a block is read and fetched.

I think the partial answer to #2 and #3 is that in buffer cache, it checks for the state of block as of time (T) when the query was fired. The first attempt would be to get the block as of that time (T) and if it is available in buffer cache, it is known as consistent read. If needed, UNDO will be applied to rollback to the block state as of time T. This is also known as consistent read. But then how does CURRENT reads come into picture here?

Please advise

Thanks
Rohit

Tom Kyte
December 16, 2010 - 12:03 pm UTC

... It is reading the block from buffer
cache as it is, without applying any UNDO. ...

if the version of the block in the cache is good enough for the consistent read - it DOESN'T NEED ANY UNDO. Many times, just by chance, the 'current version' and the 'consistent read version' are AS OF THE SAME TIME.

suppose the database was just started. You do a query. the blocks are loaded into the cache. They are all you need - there is no undo to process, or even to consider.

All you need to do is think about "what if the block in the cache is what I needed in the first place, it is CONSISTENT for me". we don't need any undo for that.



Any SQL Statement (DML or Select) will undergo any of these 3 reads -
consistent, current or Physical, right?

sort of - if we include direct reads only.

In general, a statement will either get a block from the cache in current mode or consistent read mode. Those are logical IO's. The logical IO might need to do a physical IO in order to get the block into the cache - but the statement is doing either a consistent read or current mode read.

If we are using direct IO like we typically do in parallel query, then we are bypassing the buffer cache and reading right from disk. Those are read in consistent mode - so we might need to apply undo to them - but we get the data block from disk and the undo from where ever it might happen to be.

More on consistent and current

Rohit, December 17, 2010 - 5:52 am UTC


==> So flow is like (Please correct me if i am wrong):

1. Check the buffer cache for the block

1a. If not available, fetch from disk (physical read occurs)

1b. If available in buffer cache, check if the blocks needed are exactly in a state that we need (i.e SCN matches with the one at the time of starting the query)

1b-1. If yes, a Consistent read occurs because the block is consistent to the requirements. No UNDO data is required. At this point, consistent read is also the current read because the block's current state is what we are fetching

1b-2. If no, Apply UNDO data to rollback up to the point in time (or up to the SCN) we need the block to be. This also falls under the category of consistent reads.


So one inference i can draw from this discussion is that the CURRENT state is always the one where the block suits our need and no UNDO is required. Which means the SCN at the time of firing the sql statement matches the current SCN of data blocks which are being read in current state (as in consistent to our requirement). Basically a consistent read which needs no UNDO is equal to Current read, right?

Then again, this leads me to the point that "current read = consistent read". However, I am sure they are different. Is it possible to provide a scenario/example where they can be properly differentiated? In other words, a scenario where current read is not same as consistent read and we might see both happening?


==> Another important question i wanted to ask is that what are the options to reduce Logical I/O. To fetch the data blocks, either a physical or a logical I/O has to take place. It is preferrable to have logical for sure. But We know excess of both Physical as well as Logical is not good. What are the options to reduce logical I/O? Also if you can help me understand that Why and When does the logical I/O becomes bad for my database?
Tom Kyte
December 20, 2010 - 7:31 am UTC

1b-1 - a consistent read is going to occur regardless - you are DOING a consistent read. The consistent read is the action you are taking, not a side effect of some system state.

the consistent read is NOT a current read - a current read implies locking the block for your exclusive use. a CURRENT READ is an action - a CONSISTENT READ is an action - they are something you "do", they are NOT side effects of something.

It would never be proper to say " At this point, consistent read
is also the current read" - never.

... So one inference i can draw from this discussion is that the CURRENT state is
always the one where the block suits our need and no UNDO is required ...

false - you are forgetting about MULTI-VERSIONING. There can be more than one version of a block in the cache at any point in time. Say I started a query against table T as 12:00. You started a query against table T at 12:15. An update began at 12:30. We all need to get block #10245 from table T at 12:45.

We all need three different versions and two of us need a consistent read and one of us needs a consistent read followed by a current mode read (the update)

Now, in the buffer cache - there happens to be five versions of the block (assume this, it can and will happen). It could well be that you and I both do a single logical IO to get our block - because the specific versions WE NEEDED are in the cache.

The update however - it might have to do a physical IO to get the current mode version of the block - or additional IO's to get it's consistent read version of the block.

Just because you find your consistent read version in the cache does not imply "it is the most current version".



It is preferrable to have logical for sure.

Again, that is not true. Parallel query in general for example starts with

a) checkpoint the segments being queried to disk - get them OUT of the cache.
b) use direct IO to read from disk - bypassing the hugely inefficient cache (note: this changes in 11gr2 due to a pretty major restructuring of things - but still takes place in many cases)


To get a lot of blocks out of the buffer cache - where they are spread all over the place and have to be individually looked up - can be much less efficient than just performing the physical IO's against disk using big multi-blocks IO's directly into your PGA.

Logical IO's require hundreds of cpu instructions. They require a cache buffers chains latch (latches are a locking device - a serialization device - they inhibit scalability). They should be avoided as much as possible. And in general - if you tune done the logical IO's, the physical IO's tend to take care of themselves - not in EVERY case - but in general.



A reader, December 20, 2010 - 11:33 am UTC

>>note: this changes in 11gr2 due to a pretty major restructuring of things

Should we be learning this or its something internal we need not be concerned ? If yes then can you please point to some link.

Thanks.
Tom Kyte
December 20, 2010 - 11:42 am UTC

read about in memory parallel execution

http://www.oracle.com/pls/db112/search?word=In-Memory+Parallel+Execution&partno=

Rohit, December 21, 2010 - 2:17 am UTC


Thanks Tom for this nice explanation.

However, taking the same example with a slight variation, i and you request the same block at 12:45 and our queries began at different times (12 and 12:15). If there are no changes to this block till 12.45 (i am not taking into account the update statement), no UNDO is applied. This means we both get the block in consistent state. So far so good. But here my question is that isn't the same block in the cache also current because there are no changes to the block. Which means consistent read = current read in this case.

Of course, if Oracle finds that there are interim changes to the block, it will go for a consistent read (and apply UNDO) to give me and you the consistent block as of the time our queries began. And obviously, the current version and my (and yours) consistent version of the block will differ at 12.45 in this case.

So my point is that in some cases, current=consistent (if there are no changes between the times of query start and block request) and in some not (when there are changes by the time a block is requested). Isn't it an extension of what you said in one of your earlier replies - "Many times, just by chance, the 'current version' and the 'consistent read version' are AS OF THE SAME TIME."
Tom Kyte
December 21, 2010 - 7:09 am UTC

I didn't say no changes to this block until 12:45.

Ok, add another assumption: this block is updated and committed against constantly.

Example does not change at all.





... But here my question is that isn't the same
block in the cache also current ...


Stop calling a block "current". A block isn't current - you get a block in a certain MODE - it is an ACTION, not a state of being. Start thinking about it that way.

You either look for a block in the cache in READ CONSISTENT MODE
Or
You look for a block in the cache in CURRENT MODE


I frankly don't care if the data on a block read in consistent mode matches what would have been on the block if we read in current mode - it is not relevant, it is not interesting, it is not useful - it doesn't make sense to think of it that way.

... So my point is that in some cases, current=consistent ...

NOT TRUE and just stop saying it. You'll only confuse people by saying something like that. It is *not true*.

current read

Bhaskar, December 23, 2010 - 4:47 am UTC

Hi Tom,

I'm going through your discussion here. I have the below doubt.

"There can be more than one version of a block in the cache at any point in time. Say I started a query against table T as 12:00. You started a query against table T at 12:15. An update began at 12:30. We all need to get block #10245 from table T at 12:45.

We all need three different versions and two of us need a consistent read and one of us needs a consistent read followed by a current mode read (the update)
"
1.
Say the user u1 starts the first query at 12:00 and the block is not present in buffer cache then that particular block will be read from disk and will be put in buffer and then u1 will be able to view the data related to the query.
In this case do we need 1 PIO to get the block from disk and 1 LIO so that user can see the data from buffer and in this case u1 will read data in cosistent mode.

2.
As the user u2 starts the query at 12:30 and as the block is in buffer cache u2 needs only one LIO in consistent mode to get the data? will U2 read

3.
As U3 starts update at 12:45 he needs data in current mode so the existing block in the buffer cache will not be used.in this case how many PIO and LIO he needs? say he needs to update 10 rows.


Tom Kyte
December 23, 2010 - 11:04 am UTC

1) well, we'd actually need 1 physical IO (pio) to get the block into the cache, that copy of the block would be the one with all of the latest changes on it. Then we'd do a logical IO (lio) to read that block out of the cache - that would be the image of the block "as of 12:45" (remember - we are constantly updating this block). We would then perform hundreds or thousands of LIO's/PIO's against the undo segments to roll back this block to 12:00.

2) u2 would probably have to do a LIO to get the block - but it would discover that the block it got was not as of the right time (we only keep like 4 or 5 versions of a block in the cache at most, not hundreds or thousands). So it too would have to do some more LIO's to rollback that block.

3) u3 would get the consistent read version it needs of the block right out of the cache - why wouldn't it be there? u3 probably has to do 1 LIO to get the block (remember, u1 just read it into the cache from disk - it was the latest version of that block) and maybe (maybe) a few LIO's/PIO's against the undo segment to roll back the block just a little (for his consistent read of the block to see if he wants to update it) and then another LIO against the cache to get the block in current mode.

current and consistent mode

Bhaskar, December 24, 2010 - 4:33 am UTC

Hi Tom,

Many thanks. Still i have some doubts.

"...that would be the image of the block "as of 12:45" (remember - we are constantly updating this block). We would then perform hundreds or thousands of LIO's/PIO's against the undo segments to roll back this block to 12:00."

As you told after doing 1 PIO and 1 LIO u1 will be able to read the data of that block.
1.
So as u3 starts updating the block at 12:45 so at 12:45 we will have different image of same data block in cache?
2.
In this above case why u1 needs to read data from undo block because u1 is already reading the image of the block at 12:00 which is already present in cache.

3. Same doubt i have for u2 also. As u2 started reading he will get the image of that block at 12:30 which is already present. Then why u2 needs to do PIO/LIO to get the block?

Is this because the image of the block at 12:00 and 12:30 will be replaced by the updated image at 12:45?
If it is so then what parameter will decide how many image will be there [as you told -->"(we only keep like 4 or 5 versions of a block in the cache at most, not hundreds or thousands)"] in cache?

4. when u3 will start updating the data in the same block does he require PIO for first time? because we have image of the data block in 12:300 at consistent mode.

It would be great sir if you can explain how bulk collect and sigle update statement will affect the LIO/PIO operations.

Thanks in advance
Tom Kyte
December 24, 2010 - 12:20 pm UTC

1) actually, we'd have them before u3 did that - since u1, u2 and u3 all need different versions. the update done by u3 might just cause more to appear.

2) stop - you said "and the block is not present in buffer cache", so on disk is some version of the block and DEFINITELY NOT the 12:00 version (remember, this block is updated often in this period of time). The block image on disk MUST be a version well after 12:00 - it was checkpointed to disk - it was the latest version of the block after updates.

3) same reason as #2

4) we may or may not have the 12:30 image - you cannot tell until you go to look. We might need to read, might not. It depends.



bulk would not affect this really - we still need "blocks" and the IO's we perform to get blocks are a function of what is in the cache.

Need little help to understand

Prashant, April 27, 2012 - 12:20 am UTC 

INSERT INTO GL_BALANCE(PERIOD_NAME,GL_ACCOUNT_NO,LEDGER,PERIOD_NET_DR,
  PERIOD_NET_CR,BALANCE_TYPE,CURRENCY,LA,QTD_CR,BEGIN_BALANCE_DR,
  PROJECT_TO_DATE_DR,QTD_DR,PROJECT_TO_DATE_CR,BEGIN_BALANCE_CR,SEGMENT7,
  PRODUCT_ACCOUNT,PROJECT_ACCOUNT,RC,NA,TD,BC,SUMMARY_FLAG,SET_OF_BOOKS_ID,
  EDW_INSERT_DATE,EDW_INSERT_BY,EDW_UPDATE_DATE,EDW_UPDATE_BY,
  EDW_ERROR_STATUS,TRANSLATED_FLAG,PERIOD_END_DT,STD_ACCOUNTING_PERIOD)  
VALUES
 ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17,
   :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    457     29.59      34.69        772     351953     585016       85722
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      457     29.59      34.69        772     351953     585016       85722

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 177  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=1208 pr=7 pw=0 time=211039 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net more data from client                1973        0.00          0.04
  Disk file operations I/O                       10        0.00          0.00
  db file sequential read                       772        0.17          4.87
  SQL*Net message to client                      32        0.00          0.00
  SQL*Net message from client                    32        0.00          0.17
  log file switch (private strand flush incomplete)
                                                  1        0.16          0.16
  log buffer space                                1        0.04          0.04
********************************************************************************


The application (Informatica) issues row by row inserts. We have traced the sql from Oracle.
The table in question has 7 local indexes and list partitioned on STD_ACCOUNTING_PERIOD. All the rows above are inserted into a single partition of APR-2012.
Number of rows in the partition: 2.4 million
Number of blocks in the partition: 36864
Avg row len = 192
ASSM managed. Oracle version 11.2.0.2

Questions
1. Why do I see current gets for Insert operation?
2. Are the waits normal? [DBAs have increased the redo log files and size after seeing this report to reduce log switches)
3. Is there anyway to tune this because the throughput is very low (direct path and bulk are not options for us because of Informatica, also we cannot disable or drop indexes because we will be running this batch ever 10 mins)?
4. I would expect to see waits on sequential reads for each of 7 indexes (on the partition) for the first row inserted and would expect them to be cached in buffer. However I do see waits in the detailed trace file for these objects even further down the trace file plus a sequential read wait on the APR-12 partition for each row inserted. Is this ok?
Tom Kyte
April 27, 2012 - 8:06 am UTC

It is actually in this case doing bulk inserts - 85722 rows, 457 executes - looks like about 187 rows per insert call. Not excellent, but not bad.


1) index maintenance, block updating. You have to get a block in current mode in order to modify it - before you can actually put the row on a table block you get it in current mode. In order to put an entry on an index leaf block - you have to get it in current mode.

2) there were hardly any waits to speak of here. Your average IO time was 0.006 - not phenomenal - but not horrible either. Nothing pops out as beyond normal, no.

3) try to get them to do a larger array insert. Make sure every index you have is a necessary to have index.

4) why just the first row? why the first row in particular? If the index is on a randomly arriving field (think of an index on last_name for example) - you should expect IO's all of the time, randomly. In order to maintain an index on last_name where the rows are coming in like "smith, adams, zebrowski, miller, ..." - you need the S block of the index, then the A block, then the Z block, then the M block and so on - in short, you need the entire index in cache. What are the odds all seven indexes are fully cached?

Thanks

Prashant, April 28, 2012 - 8:07 am UTC

Yes I too noticed the executes but wasn't sure about it. Thanks for your clarifications.

I don't understand Informatica very well and was told by some Informatica guys on the team that they cannot do bulk (because it gives an error when there are indexes on the target table) or direct path.

I have read some articles since your response and have understood in normal mode Informatica can do array inserts but not understood on how the array insert settings can be altered. Do you know?

Can they use direct path? Do you know?
Tom Kyte
April 30, 2012 - 8:05 am UTC

but it IS doing bulk already. It is array inserting about 187 rows per execute. They are *in bulk*.

You might want to ask informatica how to set up and configure informatica?

yes, they have been reported to be able to use direct path - but again, you'd really want to ask them how their product can be used.

what happened to the bug

A reader, May 16, 2017 - 8:41 pm UTC

5 years later....

select * from X where rownum in (select 1 from dual)
still returns all the rows.



what happened to the bug

A reader, May 16, 2017 - 8:42 pm UTC




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.