Many of you who have been reading statspack or AWR reports might be wondering what is the new category of “SQL Statistics; SQL ordered by Physical Reads (UnOptimized)”. What is an UnOptimized read?
To begin, we need to clarify the meaning of the statistic. For systems not using Exadata Hardware and the Oracle Database 11g new feature, Database Smart Flash Cache, this statistic does not apply. The UnOptimized read is one that is not serviced by the Database Smart Flash Cache or the Cell Smart Flash Cache within Exadata. Thus, if you do not have Exadata or do not use the Database Smart Flash Cache, all reads will be default fall into the category of “…UnOptimized”.
So, what are UnOptimized reads? In Oracle 11g there is now the ability to create a second layer of buffer cache called the Database Smart Flash Cache, which is used as an overflow for the buffer cache. The Database Smart Flash Cache resides on SSD disks that are available to the OS. As data gets aged off of the buffer cache, it is written into the Database Smart Flash Cache. The Database Smart Flash Cache is set up as a data file that resides on SSD. Although this is not nearly as fast as RAM, it is much faster than going to disk. When retrieving data, the SGA is checked first, then the Database Smart Flash Cache, and then disk.
In addition to the Database Smart Flash Cache which is used for the entire instance, within Exadata each storage cell has its own large amount of flash memory (SSD), called Cell Smart Flash Cache, which is used somewhat like a disk cache. Depending on the setting of the database object cell_flash_cache option, data in the cell flash cache will be handled differently. Using the keep option the storage cell will cache data from objects with that setting in the cell flash cache as long as possible. Objects with cell_flash_cache set to default will cache random reads from the object, but not table scans. An object set to none will never be cached.
Note: This is similar to the Direct Path Read, where in the case of a table scan; the data is neither read into the Database Buffer Cache nor kept in the Cell Smart Flash Cache, unless the cell_flash_cache option for that object is set to KEEP.
So to answer the question, an UnOptimized read in Oracle 11g is a read that is not found in the Database Smart Flash Cache. On Exadata an UnOptimized read is not found in the Database Smart Flash Cache or in the Exadata Cell Smart Flash Cache.
An “Optimized” read is one that finds its data in either one of those two caches with Exadata or in the database smart flash cache in a non-Exadata system. A read that finds its data in the Database Buffer Cache is not counted as an Optimized or UnOptimized read. The cache reads are categorized as gets in AWR reports. A read is a physical I/O operation, either to the database smart flash cache (SSD disk) or the disk, or to the Exadata storage cell.
So, next time you read an 11g AWR report, don’t worry if all of your reads are UnOptimized.