Home > Oracle, Performance > Consistent gets – How Many?

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!

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: