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
**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
Question:
if our segment
header block is 130, what are the 128th and 129th blocks?
Answer:
They are segment space management 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
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=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;
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
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 1st 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:
