ORACLE CONSISTENT GET MECHANISM

As you know there are 2 types of IO operations on Oracle Database

           

A.physical reads: these are done by reading from disk (expensive reads). However, physical reads can also occur on IO devices (such as RAW,ASM) or disk cache.

B.logical reads: these are done by reading the data blocks on buffer cache. (performance reads). There are 2 types of logical reads:

                                                                                                                                                                                                               

                                                               

B.1. Consistent Get: this is a read on buffer cache to access the data in the block which is consistent with a given point time or SCN. Consistent gets will be increased if it needs to use undo segments to apply the uncommited changes.

                               

B.2. DB Block Get(current get): this is a read on buffer cache to access the most up-to-date current copy of the data in the blcok. There can be only one current copy of a block. 

**db block gets are generally used while DML operations

                               

This is a very brief explanation of reads, now I will only try to explain consistent get mechanism.

                                 

What is consistent gets?

Actually, a consistent get is the number of accessing blocks in the buffer cache.

Example;

--create a tablespace with uniform extent size;

create tablespace test_tbs datafile '+DATA' size 100m extent management local uniform size 64k;

--create a table;

create table demo_objects tablespace test_tbs as select * from dba_objects where rownum between 1 and 300;

--gather the statistics

begin

  dbms_stats.gather_table_stats('HR', 'DEMO_OBJECTS', granularity => 'ALL');

end;

--check the number of rows and total number of blocks

select num_rows, blocks from dba_tables where table_name='DEMO_OBJECTS';

       NUM_ROWS     BLOCKS

            300          8

  

--check the data block and number of rows for each block

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

  from demo_objects

 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

 order by 1

              BLKNO      ROW_CNT

                131           76

                132           73

                133           72

                134           70

                135            9

  

--there are total 8 blocks but 5 of them are data blocks. So, what is the rest of 3 blocks?

select extent_id, block_id from dba_extents where segment_name='DEMO_OBJECTS'

       EXTENT_ID      BLOCK_ID

               0           128

               

***our first block is at 128th block in the 0th extent

--header block

select header_block from dba_segments s where s.segment_name='DEMO_OBJECTS'

       HEADER_BLOCK

                130

Question:

if our segment header block is 130, what are the 128th and 129th blocks?

Answer:
They are segment space management blocks

AUTOMATIC SEGMENT SPACE MANAGEMENT BLOCKS (BitMapBlocks)

Before the data blocks, every segment has "some segment management blcoks" to hold some information. When we start to read a table, these blocks also cause some consistent gets.

L1 Leaf   BMB---> always starts from first block at first extent, indicates the free space of blocks, can be more than one

L2 Branch BMB---> contains search hint for first L1 BMB

L3 Root   BMB---> segment header and L3 Root reside in same block

For our “demo_objects” table:

Block# 128------------> L1 BMB

Block# 129------------> L2 BMB

Block# 130------------> L3 BMB and segment header

  

CHECK THE STATISTICS AND CONSISTENT GETS

session#1

---------

select * from demo_objects;

Statistics

----------------------------------------------------------

         1  recursive calls

         0  db block gets

        26  consistent gets

         0  physical reads

         0  redo size

      35259  bytes sent via SQL*Net to client

       760  bytes received via SQL*Net from client

        21  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       300  rows processed

We have 8 blocks for “demo_objects” table but consistent gets value is 26. So, it means we accessed the blocks multiple times.

Let's make a DML operation from another session (without commit) and check the consistent gets again; 

session#2

---------

SQL> update demo_objects set object_name='xxx' where rownum < 50;

49 rows updated.

session#1

---------

SQL> select * from demo_objects;

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

        76  consistent gets

         0  physical reads

       116  redo size

35259  bytes sent via SQL*Net to client

       760  bytes received via SQL*Net from client

        21  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       300  rows processed

As you see, consistent gets are increased from 26 to 76. Also there is no physical reads and db_block gets.

So where are these extra consistent gets are coming from? Answer is "undo segments"

After we rollback the session, you will see that consistent gets will be 26 again.

Now, how the consistent gets are happening?

To explain the mechanism, we should understand the array size first;

array size:  When you send a query to the database, the rows are fetched from the block according to the array size. The default value of  array size for SQL*Plus is 15. You can change this value if you want.

SQL> show arraysize

arraysize 15

Now, check how many rows each data block has for ‘demo_objects’ table;

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

  from demo_objects

 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

 order by 1

         BLKNO         ROW_CNT

           131             76

           132             73

           133             72

           134             70

           135              9

we have 5 data blocks between 131 and 135

76+73+72+70+9 = 300 rows

When we send the query, database will start to read 15 rows for each fetch.

For e.g.:

*****

Block# 132 Rows=72

**For the last fetch, there is only 12 rows but as we know fetch wants to read 15 rows.

So, what will be to the excess 3 rows?   These  rows will be read from next block.

**Another point is  Oracle starts the execution of query by finding the 1st row of the table. We can prove this from 10046 trace file:

r=  number of rows

cr= consistent reads

PARSE #139848017540176:c=1000,e=955,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1714943783,tim=793948881469

EXEC  #139848017540176:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1714943783,tim=793948881532

FETCH #139848017540176:c=1000,e=242,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1714943783,tim=793948881828

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882270

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882561

FETCH #139848017540176:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883180

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883455

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883730

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884373

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884658

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884979

FETCH #139848017540176:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885401

FETCH #139848017540176:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885722

FETCH #139848017540176:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886235

FETCH #139848017540176:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886681

FETCH #139848017540176:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886842

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887558

FETCH #139848017540176:c=0,e=35,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887873

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948888158

FETCH #139848017540176:c=0,e=30,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889540

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134

FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

STAT #139848017540176 id=1 cnt=300 pid=0 pos=1 obj=94899 op='TABLE ACCESS FULL DEMO_OBJECTS (cr=26 pr=0 pw=0 time=1284 us cost=4 size=27900 card=300)'

As you see, the first fetch requests only 1 row and it makes 3 consistent reads.

We can say that, the first fetch used the "segment space management blocks" to find the 1st row and it makes 3 consistent gets for this operation.

Demonstration:

select * from demo_objects;

3 (first row) + 5 + 5 + 6 +6 + 1 = 26 consistent gets

We read 9+5=14 rows for the last fetch and we can check this from 10046 trace file

.

.

.

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134
FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

I wrote a little complicated query to calculate the consistent gets. I assumed that it will cost 3 cr to find the 1^st row. This can change according to extent size and number of extents.

CALCULATION QUERY

select 'Finding 1st row' block_no,
       1 rows_1st_count,
       1 rows_2nd_count,
       0 last_fetch_rows,
       3 "block_touch(gets)"
  from dual
union all
select to_char(d.blok_no),
       d.row_count ROWs_1st_COUNT,
       case
         when rownum = 1 then
          d.final_cnt - 1
         else
          d.final_cnt
       end ROWS_2nd_COUNT,
       d.rows_left last_fetch_rows,
       case
         when rownum = 1 and d.rows_left = 0 then
          d.block_acc
         when rownum != 1 and d.rows_left = 0 then
          d.block_acc + 1
         when lead(d.rows_left)
          over(order by d.blok_no) is null and d.rows_left = 0 then
          d.block_acc + 1
         when lag(d.rows_left) over(order by d.blok_no) = 0 then
          d.block_acc + 1
         else
          d.block_acc + 2
       end "block_touch(gets)"
  from (select c.*,
               case
                 when rownum = 1 then
                  (c.final_cnt - c.rows_left - 1) / 15
                 else
                  (c.final_cnt - c.rows_left) / 15
               end block_acc
          from (select b.blok_no,
                       b.row_count,
                       case
                         when rownum = 1 or lag(b.rows_left)
                          over(order by b.blok_no) = 0 then
                          b.row_count
                         else
                          (b.row_count -
                          (15 - lag(b.rows_left) over(order by b.blok_no)))
                       end final_cnt,
                       b.rows_left
                  from (select a.*, mod(rr - 1, 15) rows_left
                          from (select dbms_rowid.rowid_block_number(rowid) blok_no,
                                    count(*) row_count,
                                    sum(count(*)) over(order by dbms_rowid.rowid_block_number(rowid)) rr
                                  from demo_objects t
                                 group by dbms_rowid.rowid_block_number(rowid)
                                 order by 1) a) b) c) d

Query Result: