Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Alex.

Asked: June 26, 2001 - 3:08 pm UTC

Last updated: May 14, 2012 - 12:25 pm UTC

Version: 8.1.6.3

Viewed 10K+ times! This question is

You Asked

Hi

I have been wondering what are consistent gets and db block gets, according to the docs consistent gets are reads from rollback segments. Now this puzzles me, why everytime I excute any query consistent gets are never 0, even there arent any DML activities? I have just did a test

1. I bounced the database
2. set autotrace on in sql*plus
3.
select * from dept;
Statistics
----------------------------------------------------------
233 recursive calls
5 db block gets
42 consistent gets
6 physical reads
0 redo size
727 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed

4. repeated query
select * from dept
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
727 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

Why consistent gets is 2 when I am the only one using the database (I am testing in my PC ok) and there isnt absolutely any DML going on (well since I am the only one and I am not modifying anything...) the query reads from rollback segments?

Cheers

(By the way I just asked you a question about session_cached_cursor and I won a T-Shirt in Oracle Openworld because the support couldnt answered it ;D)

and Tom said...

consistent gets are simply blocks read in consistent mode. This will include read asides to the rollback segment when necessary to rollback a block but is not EXCLUSIVELY reads to the rollback segments.

Your query above isn't reading the rollback segments -- its reading the dept table itself in this case.

followup to comment one below

in the above reports db block gets and consistent gets are counts of the number of times a block was accessed from the buffer cache. physical reads would be the number of physical IO's (none in the above since there were all in the buffer cache to begin with)

Rating

  (16 ratings)

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

Comments

Consistent Reads

Sonia, July 06, 2001 - 8:16 am UTC

Y will the table dept be read when it is in the buffer cache? There is no need for block reads after the query was executed for one time

Consistent Gets...

A reader, May 01, 2002 - 9:33 am UTC

Tom,

In Your explanation you have stated that Consistent gets do not necessary mean that theu are RBS gets, then why this discrimination. Is'nt tis misleading.

Thanks.

Regards,
Ganesh R

Tom Kyte
May 01, 2002 - 9:47 am UTC

consistent gets are blocks gotten in consistent read mode. They are gotten AS OF the point in time the query begin.

db block gets are CURRENT mode block gets. They are gotten as the blocks exist right now.


consistent gets MIGHT entail reading RBS to reconstruct the data.
current gets MIGHT entail waiting for some data.


That is the difference between them. The distinction is very important.

Sorry !!!

A reader, May 01, 2002 - 10:12 am UTC

Tom,

Really Sorry but i am not able to place it...

<quote>
consistent gets MIGHT entail reading RBS to reconstruct the data.
current gets MIGHT entail waiting for some data.
</quote>

What do u mean by that capital MIGHT... If It might read the RBS what other reason is there to increase the count of consistent gets in the trace output.

Sorry tom... I think i have got my brains switched off today.

Thanks.

Regards,
Ganesh R

Tom Kyte
May 01, 2002 - 10:45 am UTC

We go for a block in the buffer cache.

If the block in the buffer cache is "good enough" (current enough and not too current), we are done.

If the block is too current, we need to go to the RBS and roll it back. This will involve more READS (more logical/physical io). This will increase the consistent gets.

Read also:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:880343948514 <code>
which shows the direct correlation between the array fetch size and consistent gets as well.

Why are "db block gets" and "consistent gets" all exist?

Richard he, July 26, 2002 - 3:07 am UTC

When I repeat a same query on simple table,no other dml.I think only "consistent get" occurs,but in fact, "db block gets" and "consistent gets" all occurs,why?

Tom Kyte
July 26, 2002 - 6:47 am UTC

full scans, we get the segment header in current mode in order to figure out what to scan.

Reader

A reader, August 06, 2002 - 2:54 am UTC

Tom,

If a block is in the Buffer Cache and the version of the
block (SCN and Seq# in Oracle 8i) is equal to the
logical time of the start of a query, this block is read
in "consistent mode" as shown by statistics. Is this
correct

Thanks

Consistent gets/ db block gets

Parag J Patankar, April 15, 2005 - 11:59 am UTC

Hi Tom,

Sorry with a lot of discussion on your site regarding "consistent gets" I have still not understood that I am the only user on my system and if I do "select * from emp" ( same command executed 3/4 times after restaring the system / after system is running for few hours ) still it shows me few consistent reads every time and db block gets 0

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

According to me

db_block_gets : block gets from buffer pool in current state
consistent gets : no of times consistent read requested

my understanding is, if I am running this command few times, then my db block gets should show me some blocks and consistent gets should not show me any blocks ( as I do not require any consistent / undo read as I am the only user in the database and doing select command only and ensured that after database is up no update command executed" ) and all my required blocks in current state in buffer pool.

Kindly explain.

regards & thanks
pjp

Tom Kyte
April 15, 2005 - 1:07 pm UTC

they are modes.

when you read a table, we get blocks in consistent mode, as of the point in time your query began. It does not mean "we had to undo" the changes, it means "we will undo changes if we have to". It is a mode we read in, it is not an indication that any extra work was done.

Do this:

select * from emp;
measure your LIO a couple of times.
IN ANOTHER SESSION issue update emp set ename = ename; and do not commit.
select * from emp; in that first session
measure your LIO

it'll have gone up, you really did to an 'undo' at that point.


db block gets are blocks read in 'current' mode. meaning, get me the block as it exists right now, no read consistency. do NOT undo changes. Again, it is a mode, not an indication of how many blocks didn't need changes -- rather how many blocks we asked for in that MODE.






confusion.

Kapil Goyal, April 26, 2005 - 8:13 am UTC

Hi Tom,

I tried the same example (9204 on linux RHEL4/Dell 4600 Box ), and every time it does FTS then how come "db block gets" can be 0 ? also why "consistent gets" got decreased after first execution ? why recursive call got decreased from 173 to 0 ?

 
SQL> set autotrace on
SQL> select * from t1;

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T1'




Statistics
----------------------------------------------------------
        173  recursive calls
          0  db block gets
         22  consistent gets
          2  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T1'




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

 

Tom Kyte
April 26, 2005 - 8:20 am UTC

the recursive sql was from the first "hard parse", brand new query -- never seen before, we ran some sql in order to figure out how to run your sql.

That accounts for the extra consistent gets too -- that sql that was run for you by us, it counted in the consistent gets.


The number of db block gets and consistent gets varies by release.


scott.dept on 817:
4 db block gets
2 consistent gets

scott.dept on 9ir1
2 db block gets
2 consistent gets


scott.dept on 9ir2
0 db block gets
4 consistent gets

scott.dept on 10gr1
0 db block gets
4 consistent gets



things change over time -- one of the reasons I show how to measure things here.

Kapil Goyal, April 26, 2005 - 8:57 am UTC

<quote>
That indicates that we did 4 current mode reads to determine how to FULL SCAN dual.
</quote>
so in 9iR2, 10g what mechanism Oracle follows ? NO "current mode reads" kind of concept ? or some great enhancement ?? i am sure you must have digged upto that much depth :)
Can you elaborate a bit more why it is ZERO in 9iR2 and 10g.

Tom Kyte
April 26, 2005 - 9:16 am UTC

that is a good enhancement since current mode reads are "more expensive" than the other kinds yes.

This was simply an incremental improvement over time.

It is zero because they optimized away the need to do it.

A reader, September 02, 2005 - 9:31 am UTC

Tom,

Is it that Oracle takes a Read Consistent View of the Blocks JUST BEFORE it starts updating NOT causing this:

-> Oracle updates ID 2 after it has done ID 1 as first record taken up for an update, in that case ID 1 would have had 6 and the sum(val) would be 10 which ID2's VAL would have become?


2)So is Oracle internally Rolling Back ID1 (if its doing that first) to get a consistent view of data?

Thanks

Drop table tx;

Create table tx as select rownum id, 1 val from all_objects where rownum < 6;

Update tx set val = val +1 where rownum < 4;

select * from tx;
ID VAL
---------- ----------
1 2
2 2
3 2
4 1
5 1

5 rows selected.


Update tx a set val = (Select sum(val) from tx b where a.val=b.val);


Select * from tx;

ID VAL
---------- ----------
1 6
2 6
3 6
4 2
5 2

5 rows selected.

Tom Kyte
September 03, 2005 - 7:17 am UTC

the read component of the DML is done with read consistency, the modification in current mode.

So all of the reads are done with read consistency, yes.


Else that sql statement would be very much "non-deterministic"

consistent gets

Ani, May 15, 2008 - 9:18 am UTC

I have run select * from emp twice.My emp table contains no data.When first time I am running the query it gives me 36 consistent gets , but when I am running the query for the second time it gives me 3 consistent gets.
My question is, if my table is empty then why there are 36 consistent gets?I can understand that even thoug the table is empty nevertheless it can read 3 blocks,
(consistent gets) even though these blocks are empty.

I am using oracle 9i

SQL> select * From emp;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
        178  recursive calls
          0  db block gets
         23  consistent gets
          9  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        456  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




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

Tom Kyte
May 19, 2008 - 12:05 pm UTC

see the 178 recursive calls the first time?

that is the sql we did to execute (hard parse) your sql the first time. Your IO's include our IO's.


the first time we executed your query right after you created the table, we had to hard parse it, we ran sql to figure out how to run your sql.

Karteek, May 20, 2008 - 10:20 am UTC

In the latest review just above here, when query was ran second time on an empty table there were 3 consistent gets and 2 physical reads.

what is the nature of data that would have been present in those 2 or 3 or 5(2+3) reads, eventhough the table has no data? could it be metadata due to recursive runs?

Thanks!
Tom Kyte
May 20, 2008 - 11:42 am UTC

we have to read the segment header to figure out what to full scan. That accounted for the IO's


there were 3 reads - one from the buffer cache, 2 from disk. There were not five reads.

db block gets and consistent gets

DB user, June 16, 2011 - 1:16 am UTC

db block gets and consistent gets
I dont think anyone provided clear distincation on what exactly db block gets and consistent gets is all about!!!

Tom Kyte
June 17, 2011 - 1:30 pm UTC

I don't think someone read this page then.


quote:

consistent gets are blocks gotten in consistent read mode. They are gotten AS OF the point in time the query begin.


db block gets are CURRENT mode block gets. They are gotten as the blocks exist right now.


db block gets are blocks read in 'current' mode. meaning, get me the block as it exists right now,
no read consistency. do NOT undo changes. Again, it is a mode, not an indication of how many
blocks didn't need changes -- rather how many blocks we asked for in that MODE.




Do you have a specific question that was not already answered?

db block gets and consistent gets

DB user, June 16, 2011 - 1:16 am UTC

db block gets and consistent gets
I dont think anyone provided clear distincation on what exactly db block gets and consistent gets is all about!!!

db block gets and consistent gets

DB user, June 16, 2011 - 1:17 am UTC

db block gets and consistent gets
I dont think anyone provided clear distincation on what exactly db block gets and consistent gets is all about!!!

How to reduce consistent/physical reads

Reader, July 12, 2011 - 4:06 pm UTC

tkprof reveals that Fetch step of a sql SELECT statement takes about 7 minutes to complete as disk and query values are about 74555 and 84323 respectively to retrieve 3 rows. This particular sql runs every night -- most of the nights they are fine but only on a certain night it spends a lot of time in fetching. From tkprof description:

disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read

is there a way to find the cause for the "high" values for disk and query and how to reduce the fetch time? Thanks.
Tom Kyte
July 13, 2011 - 7:59 pm UTC

given that a physical IO could be averageing in at 5 milliseconds:

(74 555 * (5 / 1 000)) / 60 = 6.21291667

sure, that could be 7ish minutes.


The only way to make that go faster would be to do something to either

a) reduce your IO's (alternate indexing strategy or maybe skip indexes altogether)

b) make your IO faster (by magic usually...)



On that certain night, the data that query needs just isn't in the buffer cache when it needs it .

cr

A Reader, May 14, 2012 - 5:28 am UTC

Hi Tom

from tkprof
------------
1113041 NESTED LOOPS SEMI (cr=10652584 pr=2619 pw=0 time=446359 us cost=97 size=510 card=1)
1113041 NESTED LOOPS (cr=10652571 pr=2619 pw=0 time=429616 us cost=95 size=504 card=1)
....
snip
....
1113041 INDEX RANGE SCAN T_IDX (cr=13 pr=0 pw=0 time=0 us cost=2 size=19687572 card=3281262)(object id 63516)



from explan plan
-----------------
|* 50 | INDEX RANGE SCAN | t_IDX | 3281K| 18M| 2 (0)| 00:00:01 | | |

50 - access("t1"."c1"="t"."c1")


index t_IDX is on table (t)


question/s
a) how the index range scan resulted in just 13 consistent read blocks to read 1113041 rows?


Tom Kyte
May 14, 2012 - 12:25 pm UTC

please don't post a snippet of something out of context like that.

provide everything.