Based on the request from some of my regular readers, this is a reposting of my previous blog on viveklsharma.blogspot.com (at present unaccessible).
Any Query executed against a database gets the required rows, which are then fetched to the end user. Everyone is aware with the fact that a table row(s) is/are stored in Oracle Database block(s), hence, when oracle has to get even a single row, a block containing that row has to be read into the buffer cache. From this, it is quite evident that Oracle reads a block at a minimum. Also, as an Optimization Feature, for every query, blocks are fetched to a user from the buffer cache (Parallel Queries are exception). Hence, if a block is not found in the buffer cache, these are reads from the disk into the cache and then given to the user.
In Oracle terminology, a read from disk is termed as a Physical Read or a Disk Reads and, a read from Cache is termed as a Consistent Get or a Logical I/O. As mentioned in my previous paragraph, if a block is not found in the Cache, it is first read from the disk into the cache and fetched to the user. This also means that every logical read includes the count of disk reads.
In this blog, I am going to write on a myth about the calculation assumed and derived by the DBA’s from the value of Consistent Gets.
At one customer site, we were discussing about Application tuning wherein a dba gave a list of Top Queries to the Vendor. The list contained the queries alongwith the statistics called Total I/O in GB and the values in this column was in the range of hundreds of GB to tens of TB. I was surprised to see these values and after the discussion, out of the curiosity, asked the dba about the values calculated in this column. The dba immediately gave me the access to his query that generated the output. The calculation in this column was (buffer_gets*db_block_size)/(1024*1024*1024). It seems that the dba’s assumed that each logical I/O to be a read to a new block and hence, these high values. Hence, if a query does a logical I/O of 1549087, then based on the calculation, the dba’s assumed that a query has read 11 gb (assuming 8k block size) worth of data into the cache. This is not true.
BLOCKS READ ALGORITHM
A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :
- User Issues a Query.
- Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.
- Based on the Execution Path, required Index or Table block is searched in the the Cache.
- If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.
- The block, from the Cache is then read into a private memory area (UGA) of the User.
- Once the Block is read into the private memory of the User, the required row is fetched.
The value of Consistent Read is incremented each time a new block is read into a private memory area of the User, which also means that, a single block, if read multiple times, affect the value of the Consistent Read. While an in-efficient SQL contributes largely to Consistent Read, but one factor that also have significant impact is ARRAY SIZE. I am not going to discuss about In-efficient Queries, but will write on the calculation that undergoes and impact Consistent Gets.
A better way to understand the calculation is by way of an Example and as always, I will run through some of the simple queries and see the impact of the change in ARRAYSIZE to check its impact on Consistent Gets.
I will create a table with 3000 rows and will run a simple query in SQLPLUS prompt. Without an Index on this table, it will be a full table scan.
SQL> create table test_cg as select * from all_objects where rownum between 1 and 3000;
Table created.
Elapsed: 00:00:01.82
SQL> exec dbms_stats.gather_table_stats(user,'TEST_CG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
SQL> @table_stats TEST_CG
old 2: where table_name='&1'
new 2: where table_name='TEST_CG'
OWNER PAR NUM_ROWS BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK NO 3000 38 02-MAR-10 YES
1 row selected.
SQL> set autot trace
SQL> select * from test_cg;
3000 rows selected.
Elapsed: 00:00:00.17
Execution Plan
----------------------------------------------------------
Plan hash value: 2626677675
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 249K| 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_CG | 3000 | 249K| 12 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
238 consistent gets
0 physical reads
0 redo size
300791 bytes sent via SQL*Net to client
2608 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3000 rows processed
A simple query with 38 blocks accounted for 238 consistent gets. Does it mean 238*8192 i.e.1.8 MB of data read into the cache ? The answer is plain NO. As mentioned earlier, the blocks are read only once into the cache and is sent to the PGA of the user that require this block. Internally, the calculation for consistent gets is (NUM_ROWS / ARRAYSIZE)+NUM_BLOCKS. In my case, when I executed this query, the arraysize was unchanged and therefore was default, which is 15.
Rows in my Table : 3000
Blks in my Table : 38
Default Arraysize : 15
(3000/15)+38 = 200 + 38 = 238
The calculation matches the Consistent Gets. One value worth discussing here is the number 200 derived from (3000/15). This value means that each of the blocks will be touched multiple times. There are 38 blocks in the table, each of these 38 blocks will be read only once in the cache, but will be touched more than once and therefore, the consistent gets for a single block access will based on the number of times, it will be visited to read all the required rows. Incidently, another statistics that matches our calculation is the “SQL*Net roundtrips to/from client” from the Autotrace output above. The value, with arrays 15 is 201, which means, 200 visits or touch to the block to fetch 3000 rows, and last visit required to check and confirm whether more rows are to be fetched.
Back to our example, let us get into more details of the calculation of Consistent Gets. The table has 3000 rows and 38 blocks. The count of rows in each of these 38 blocks is shown below.
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt
from test_cg
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1;
SQL> /
BLKNO CNT
---------- ----------
641 88
642 84
643 81
644 76
645 81
646 80
647 82
648 77
.... some lines deleted
.... some lines deleted
677 78
678 82
----------
sum 3000
The Sequence of Consistent Gets calculation is as under (with default arraysize) :
- Get Block 641 in the PGA, fetch 15 Rows – Consistent Gets = 1
- Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 2
- Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 3
- Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 4
- Get Block 641 again in the PGA, fetch another 15 rows – Consistent Gets = 5
- Get Block 641 again in the PGA, fetch another 13 rows – Consistent Gets = 6
- Get Block 642 in the PGA, fetch 2 rows – Consistent Gets = 7
- Get Block 642 again in the PGA, fetch another 15 rows – Consistent Gets = 8
and so on….
It is clear from this explanation that a single block is read is multiple times, in our case at at average 6 times and therefore, consistent gets for each of these blocks was around 6 to 7. Run this test case with different arraysize and the calculation should match that shown in this blog. Let us run the query with different values of Arraysize and validate the calculation.
ArraySize = 35 Consistent Gets will be (3000/35)+38 = 86+38 = 124
ArraySize = 40 Consistent Gets will be (3000/40)+38 = 75+38 = 113
ArraySize = 50 Consistent Gets will be (3000/50)+38 = 60+38 = 98
SQL> set arrays 35
SQL>select * from test_cg;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
124 consistent gets
87 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3000 rows processed
SQL> set arrays 40
SQL> select * from test_cg;
3000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
113 consistent gets
76 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3000 rows processed
SQL> set arrays 50
SQL> select * from test_cg;
3000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
98 consistent gets
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3000 rows processed
Below is the query, that can be executed to check for the approximate value of the Consistent Gets and should help you understand the way this value is calculated. The value of bind variable is to be set to the ARRAYSIZE. The Query and the details of the columns used is as under :
variable b1 number;
exec :b1:=15;
SQL> compute sum of total_cnt on report
SQL> break on report
select blkno, total_cnt, final_cnt, rows_remaining,
case when rows_remaining=0 then touch_cnt+1 else touch_cnt end touch_cnt
from (
select blkno, total_cnt, final_cnt, rows_remaining,
case when total_cnt = final_cnt then ceil(final_cnt/:b1) else ceil(final_cnt/:b1)+1 end touch_cnt
from (
select blkno, cnt total_cnt,
case when rownum=1 or lag(rows_remaining) over (order by blkno)=0
then cnt else (cnt-(:b1-lag(rows_remaining) over (order by blkno))) end final_cnt,
rows_remaining
from (
select blkno, cnt, rr,
lead(rr) over(order by blkno) next_rr,
lead(blkno) over(order by blkno) next_blk,
ceil(rr/:b1) touch_cnt,
mod(rr,:b1) rows_remaining
from (
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt,
sum(count(*)) over(order by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) rr
from test_cg
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1))));
BLKNO TOTAL_CNT FINAL_CNT ROWS_REMAINING TOUCH_CNT
---------- ---------- ---------- -------------- ----------
641 88 88 13 6
642 84 82 7 7
643 81 73 13 6
644 76 74 14 6
645 81 80 5 7
646 80 70 10 6
647 82 77 2 7
648 77 64 4 6
649 73 62 2 6
650 79 66 6 6
651 79 70 10 6
652 79 74 14 6
653 81 80 5 7
654 82 72 12 6
655 77 74 14 6
656 81 80 5 7
657 80 70 10 6
658 81 76 1 7
659 78 64 4 6
660 78 67 7 6
661 76 68 8 6
662 78 71 11 6
663 78 74 14 6
664 77 76 1 7
665 81 67 7 6
666 79 71 11 6
667 79 75 0 7
668 77 77 2 6
669 77 64 4 6
670 76 65 5 6
671 80 70 10 6
672 77 72 12 6
673 76 73 13 6
674 75 73 13 6
675 79 77 2 7
676 78 65 5 6
677 78 68 8 6
678 82 75 0 7
---------- ---------- -------------- ----------
sum 3000 2744 284 238
BLKNO : Block Number
TOTAL_CNT : Total Rows in the Block
FINAL_CNT : Final Number of Rows
(Example Block 642 has total 84 Rows, but final row count is 82 as 13 rows were read from 641 and balance
2 Rows were fetched from this block, this gives 84-2=82)
ROWS_REMAINING : Incomplete Arraysize Rows from the current block
TOUCH_CNT : Touch Count for current block and is our Consistent Gets per Block. Final total at the end.
Consistent Gets are not a measure of number of blocks that are read into the cache but number of times, a block was read into the PGA. A block is read only once in the cache and touched multiple times. Our example above showed that with arraysize of 15, an 8k block was read once but was touched 6-7 times and hence had a 6 or 7 consistent gets per block. This does not mean 6*8192 worth of data. The data was read only once.