Yuan Yao, An Oracle ACE's Blog

In the “SQL Statistics” section of an Oracle AWR report, the list titled “SQL ordered by Physical Reads (UnOptimized)” presents the top SQLs in term of unoptimzied read request. This list can be used to evaluate how effectively Smart Flash Cache or Exadata Smart Scan are being utilized from SQL workload perspective.

In an Oracle database equipped with Smart Flash Cache, each physical read request can be categorized as either optimized or unoptimized

• Optimized read requests are those satisfied directly from the Smart Flash Cache (or Smart Flash Cache in Exadata V2), meaning they don’t require disk access.
• Unoptimized reads are traditional physical reads that bypass the Smart Flash Cache and go directly to disk I/O.

Here’s an example:

For this SQL, we can see that this SQL performed 240 physical read requests, out of which only 30 were traditional physical reads. The remaining 210 (240 – 30) were optimized by Smart Flash Cache, resulting in an optimization rate of 210 ÷ 240 = 87.5%. This means the Smart Flash Cache successfully handled most of the I/O requests for this SQL.

If Smart Flash Cache is not enabled in the database, all read requests are unoptimized. In this case, the values of “UnOptimized Read Reqs” and “Physical Read Reqs” are identical, and the “%Opt” is zero. Consequently, the “SQL ordered by Physical Reads (UnOptimized)” list effectively mirrors “SQL ordered by Reads” list, both showing the top SQL statements by physical reads. Even so, this list remains useful for identifying the SQLs responsible for the highest I/O load.

Here’s an example showing the same SQL as it appears in both lists:

These two lists look very similar, but they measure slightly different things:

• In “SQL ordered by Reads”, Physical Reads refers to the number of data blocks read.
• In “SQL ordered by Physical Reads (UnOptimized)”, Physical Read Reqs refers to the number of I/O requests, not blocks.

By comparing the two, you can estimate how many blocks are read per I/O request. In this case, the numbers are nearly identical, indicating that Oracle retrieves roughly one block per I/O