Consistent gets – How Many?
When you execute an SQL – why there is a difference in Consistent gets on the same set of data for same SQL. For any SELECT query, oracle need to prepare the consistent data in the buffer cache using undo records then forward the data the requesting session as of a specific SCN. Touching any block in buffer cache to prepare the blocks for consistent data is known as Consistent Reads.
In an ideal situation, to read a row from a block just need one consistent get.
SQL> select * from test where rowid='AAAPaVAABAAAzfJAAA'; C1 C2 ---------- ---------- 1 CON$ Execution Plan ---------------------------------------------------------- Plan hash value: 2153624467 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 589 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
But when I run the same SQL again after a transaction from different session, the number of consistent gets gone up to 3, just to read a single row.
SQL> select * from test where rowid='AAAPaVAABAAAzfJAAA'; C1 C2 ---------- ---------- 1 ABC Execution Plan ---------------------------------------------------------- Plan hash value: 2153624467 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 108 redo size 588 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
The 3 consistent gets may be (but later found to be incorrect!!)
- Access the current block
- Create a new CONSISTENT block and copy the CURRENT block contents
- Access the undo block and rollback the uncommitted transaction
So, what if I have one more transaction going on a different row on the same block? Need to access one more undo block to rollback the second uncommitted transaction, so that you will get 4 consistent gets.
SQL> select * from test where rowid='AAAPaVAABAAAzfJAAA'; C1 C2 ---------- ---------- 1 ABC Execution Plan ---------------------------------------------------------- Plan hash value: 2153624467 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 108 redo size 588 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Contrary to my expectation of one more consistent get, the number of consistent gets grown by 2 reaching to 5 gets. I am sure, that I need to read one extra undo block for the uncommitted transaction, but what is the 5th block?
10046 trace show me that I was totally wrong – even my earlier assumption. There were only one block read from the table and rest of the 4 reads from the UNDO file.
WAIT #2: nam='db file sequential read' ela= 34 file#=1 block#=210889 blocks=1 obj#=63125 tim=39307229867247 WAIT #2: nam='db file sequential read' ela= 25 file#=3 block#=208 blocks=1 obj#=0 tim=39307229867390 WAIT #2: nam='db file sequential read' ela= 22 file#=3 block#=224 blocks=1 obj#=0 tim=39307229867454 WAIT #2: nam='db file sequential read' ela= 22 file#=3 block#=439 blocks=1 obj#=0 tim=39307229867585 WAIT #2: nam='db file sequential read' ela= 21 file#=3 block#=157 blocks=1 obj#=0 tim=39307229867696
First undo block
Block dump from disk: buffer tsn: 2 rdba: 0x00c000d0 (3/208) scn: 0x01a7.37e301cf seq: 0x01 flg: 0x04 tail: 0x01cf2601 frmt: 0x02 chkval: 0xd8b1 type: 0x26=KTU SMU HEADER BLOCK
Second undo block
buffer tsn: 2 rdba: 0x00c000e0 (3/224) scn: 0x01a7.37e300bb seq: 0x01 flg: 0x04 tail: 0x00bb2601 frmt: 0x02 chkval: 0xa4d2 type: 0x26=KTU SMU HEADER BLOCK
3rd undo block
buffer tsn: 2 rdba: 0x00c001b7 (3/439) scn: 0x01a7.37e301cf seq: 0x01 flg: 0x04 tail: 0x01cf0201 frmt: 0x02 chkval: 0xc91c type: 0x02=KTU UNDO BLOCK
4th undo block
buffer tsn: 2 rdba: 0x00c0009d (3/157) scn: 0x01a7.37e300bb seq: 0x01 flg: 0x04 tail: 0x00bb0201 frmt: 0x02 chkval: 0x5f27 type: 0x02=KTU UNDO BLOCK
If you look closely, a pair of blocks under same SCN and the first block type is “KTU SMU HEADER BLOCK” is the undo header block and the second one is the undo block of type “KTU UNDO BLOCK”. So, oracle is reading 1 consistent get to copy the current block, and for each transaction 1 undo block header and 1 undo block. X$bh was also showing one more block in STATE 3, which I felt, is not reported in the consistent gets. That is the block read from the data file with the uncommitted transaction. Repeated the test with one more transaction, and got 7 consistent gets.
SQL> select * from test where rowid='AAAPaVAABAAAzfJAAA'; C1 C2 ---------- ---------- 1 ABC Execution Plan ---------------------------------------------------------- Plan hash value: 2153624467 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 108 redo size 588 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
In nutshell, how many maximum consistent gets can happen just to read one row from a table got 1 block with 50 different rows in the block? In theory, there can be 50 different transactions can happen to the block concurrently. So, the maximum number consistent get can be 2n+1 where n is the number of rows in the block. So, there can be up to 101 consistent gets to read one row!
Thomas Saviour's Blog 