SQL ordered by Physical Reads (UnOptimized) from 11g AWR

This blog describes the 'SQL ordered by Physical Reads (UnOptimized)' section in AWR reports from Oracle 11.2 ownwards and explains how to interpret certain sections. This section has been added keeping in view the features of Smart Flash Cache features in 11g as well as Exadata

The  'SQL ordered by Physical Reads (UnOptimized)' section would look similar to the following

Optimized Read Requests are read requests that are satisfied from the Smart Flash Cache ( or the Smart Flash Cache in OracleExadata  (Note that despite same name, concept and use of  'Smart Flash Cache' in Exadata  is different from  'Smart Flash Cache' in Database Smart Flash Cache)). Read requests that are satisfied from this cache are termed 'optimized' since they are provided much faster than requests from disk (implementation uses solid state device (SSD) technology). Additionally, read requests accessing Storage Indexes using smart scans in Oracle Exadata  (and significantly reducing  I/O operations) also fall under the category 'optimized read requests'  since they avoid reading blocks that do not contain relevant data. 

In database systems where 'Optimized Read Requests' are not present, UnOptimized Read Reqs will be equal to Physical Read Reqs (I/O requests satisfied from disk).  In this case columns 'UnOptimized Read Reqs' and 'Physical Read Reqs' will display the same values and column '%Opt' will display zero (as seen in extract from AWR report above)


Note that the 'Physical Read Reqs' column in the 'SQL ordered by Physical Reads (UnOptimized)' section is the number I/O requests and not the number of blocks returned. Be careful not to confuse these with the Physical Reads statistics from the AWR section 'SQL ordered by Reads', which counts database blocks read from the disk not actual I/Os (a single I/O operation  may return many blocks from disk)

If you look at the example 'SQL ordered by Reads' section below from same AWR report for database not using smart cache,notice the physical reads

Note the difference between 'Physical Reads' in the 'SQL ordered by Reads'  and the 'Physical Read Reqs' in the 'SQL ordered by Physical Reads (UnOptimized)' section above for the SQL with "SQL_ID=8q4akpck0nxgg"  from same AWR report.

Source:My Oracle Support