Consistent gets oracle

Logical IO vs Physical IO vs Consistent gets oracle

Posted on by debasis maity

Logical IO vs Physical IO vs Consistent gets

I have created 1 GB table T1.
 
Let me check how many blocks are there in the table after I have analyzed it.
 
SQL> select BLOCKS from user_tables where table_name=’T1′;
   BLOCKS
———-
   139153
 

Autotrace report

Let me clean buffer cache and check the auto trace report.
 
SQL>set autotrace traceonly;
 
SQL> select * from sh.t1;
 
415584 rows selected.
 
Execution Plan
———————————————————-
Plan hash value: 3617692013
 
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |   415K|   797M| 37716   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   |   415K|   797M| 37716   (1)| 00:00:02 |
————————————————————————–
 
Statistics
———————————————————-
         1  recursive calls
         0  db block gets
    166620  consistent gets
    138907  physical reads
         0  redo size
   9193927  bytes sent via SQL*Net to client
    305307  bytes received via SQL*Net from client
     27707  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
    415584  rows processed
 

BLOCKS READ ALGORITHM

 
A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :
  1. User Issues a Query.
  2. Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.
  3. Based on the Execution Path, required Index or Table block is searched in the the Cache.
  4. If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.
  5. The block, from the Cache is then read into a private memory area (UGA) of the User.
  6. 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.
 

How consistent gets ORACLE calculated:-

 
Calculation:-No of physical block+total no of rows fetched/array size
By default array size is 15.
 
So In my case
138907+415584/15=166620
 
When you fetched 15 rows, Oracle paused, gave you the data. When you went back to get the next 15, it got the buffer again to resume your query
 

PHYSICAL IO and LOGICAL IO

 
db_block_gets + consistent_gets = LOGICAL IO=166620
physical_reads( typically no of block in case of full scan) = PHYSICAL IO=138907
 

TKPROF command

 
SQL> alter session set tracefile_identifier=’ABC’;
 
Session altered.
 
SQL> alter system set events ‘10046 trace name context forever, level  12’;
 
System altered.
 
SQL>select * from t1;
 
SQL>alter session set events ‘10046 trace name context off’
 
tkprof ERMAN_ora_26624_ABC.trc abc.txt sys=no sort='(prsela,exeela,fchela)’ explain=test/test
 

TKPROF output

 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse       22      0.00       0.01          1         63          2           0
Execute     20      0.11       0.67         15        417         22           3
Fetch    27727      0.20       0.97     138909     166680          0      415654
——- ——  ——– ———- ———- ———- ———-  ———-
total    27769      0.33       1.65     138925     167160         24      415657
 
Misses in library cache during parse: 13
Misses in library cache during execute: 6
 
Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 —————————————-   Waited  ———-  ————
 Disk file operations I/O                       30        0.00          0.00
 SQL*Net message to client                   27741        0.00          0.01
 SQL*Net message from client                 27741      154.26        345.66
 SGA: allocation forcing component growth      201        0.10         19.90
 SQL*Net break/reset to client                   6        0.00          0.00
 DLM cross inst call completion                  1        0.52          0.52
 db file sequential read                        57        0.00          0.04
 db file scattered read                          1        0.00          0.00
 log file sync                                   1        0.00          0.00
 direct path read                               68        0.00          0.16
 

In AWR

In the below part it is evident we have 46 IOPS.

Instance Activity Stats

physical read total IO requests 2,667 45.47 190.50
physical read total bytes 1,159,954,432 19,777,569.17 82,853,888.00
 
 
 

IOSTAT

SQL> show parameter multi;
NAME TYPE VALUE
———————————— ———– ——————————
db_file_multiblock_read_count integer 128
 
SO 128 Oracle blocks could be written in one sequential I/O (In case of Full table scan) or  individual “random” I/Os (In case of index scan)
 
 

 

 Device Status Output

Units Definition
rrqm/s The number of read requests merged per second queued to the device.
wrqm/s The number of write requests merged per second queued to the device.
r/s The number of read requests issued to the device per second.
w/s The number of write requests issued to the device per second.
rMB/s The number of megabytes read from the device per second. (I chose to used MB/s for the output.)
wMB/s The number of megabytes written to the device per second. (I chose to use MB/s for the output.)
avgrq-sz The average size (in sectors) of the requests issued to the device.
avgqu-sz The average queue length of the requests issued to the device.
await The average time (milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
r_await The average time (in milliseconds) for read requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
w_await The average time (in milliseconds) for write requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
svctm The average service time (in milliseconds) for I/O requests issued to the device. Warning! Do not trust this field; it will be removed in a future version of sysstat.
%util Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this values is close to 100%.