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
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
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?
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
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
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.
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.
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
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!
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!!!
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.
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?
May 14, 2012 - 12:25 pm UTC
please don't post a snippet of something out of context like that.
provide everything.