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′;
Let me clean buffer cache and check the auto trace report.
SQL>set autotrace traceonly;
SQL> select * from sh.t1;
415584 rows selected.
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 |
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 :
- 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.
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
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
SQL> alter session set tracefile_identifier=’ABC’;
SQL> alter system set events ‘10046 trace name context forever, level 12’;
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
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 the below part it is evident we have 46 IOPS.
Instance Activity Stats
|physical read total IO requests
|physical read total bytes
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
||The number of read requests merged per second queued to the device.
||The number of write requests merged per second queued to the device.
||The number of read requests issued to the device per second.
||The number of write requests issued to the device per second.
||The number of megabytes read from the device per second. (I chose to used MB/s for the output.)
||The number of megabytes written to the device per second. (I chose to use MB/s for the output.)
||The average size (in sectors) of the requests issued to the device.
||The average queue length of the requests issued to the device.
||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.
||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.
||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.
||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.
||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%.