APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterInformation in this document applies to any platform.
GOAL
This document describes the 'SQL ordered by Physical Reads (UnOptimized)' section in AWR reports from Oracle 11.2 onwards and explains how to interpret certain sections.
SOLUTION
An example of the 'SQL ordered by Physical Reads (UnOptimized)' section would look similar to the following:
SQL ordered by Physical Reads (UnOptimized)
- UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
- %Opt - Optimized Reads as percentage of SQL Read Requests
- %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
- Total Physical Read Requests: 3,438,206
- Captured SQL account for 88.8% of Total
- Total UnOptimized Read Requests: 3,438,206
- Captured SQL account for 88.8% of Total
- Total Optimized Read Requests: 1
- Captured SQL account for 0.0% of Total
UnOptimized Read Reqs | Physical Read Reqs | Executions | UnOptimized Reqs per Exec | %Opt | %Total | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|
1,072,693 | 1,072,693 | 4 | 268,173.25 | 0.00 | 31.20 | a94vs7s7k2xx0 | SQL*Plus | DECLARE OUTPUT NUMBER; V_ERROR... |
1,064,970 | 1,064,970 | 3 | 354,990.00 | 0.00 | 30.97 | 6bsa26gr5mp2k | SQL*Plus | INSERT /*+ append */ INTO FIRE... |
771,242 | 771,242 | 1 | 771,242.00 | 0.00 | 22.43 | ftja941kjtrxj | SQL*Plus | DECLARE OUTPUT NUMBER; V_ERROR... |
.... | ... | ... | ... | ... | ... | ... | ... | ... |
63,381 | 63,381 | 2 | 31,690.50 | 0.00 | 1.84 | bha3g79j51261 | SQL*Plus | INSERT INTO FIREWALL_XFORM_TEM... |
61,362 | 61,362 | 2 | 30,681.00 | 0.00 | 1.78 | f0tqq0h3aqsn0 | SQL*Plus | SELECT TRUNC(EVENT_DT, 'hh') E... |
59,921 | 59,921 | 11 | 5,447.36 | 0.00 | 1.74 | 6zbw6ctdmd9d8 | DBMS_SCHEDULER | /* SQL Analyze(8, 1) */ MERGE ... |
37,168 | 37,168 | 1 | 37,168.00 | 0.00 | 1.08 | frurkbv3y3acd | SQL*Plus | INSERT INTO FIREWALL_NODE (ID,... |
What are 'Optimized Read Reqs'?
Optimized Read Requests are read requests that are satisfied from the Smart Flash Cache ( or the Smart Flash Cache in OracleExadata V2).
Read requests that are satisfied from the Smart Flash Cache are termed 'optimized' since they are returned much faster than requests from disk (the implemention uses solid state device (SSD) technology). Additionally, read requests accessing Storage Indexes using smart scans in Oracle Exadata V2 (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 reportabove).
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:
SQL ordered by Reads
- %Total - Physical Reads as a percentage of Total Disk Reads
- %CPU - CPU Time as a percentage of Elapsed Time
- %IO - User I/O Time as a percentage of Elapsed Time
- Total Disk Reads: 8,414,054
- Captured SQL account for 98.3% of Total
Physical Reads | Executions | Reads per Exec | %Total | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|---|
3,308,102 | 1 | 3,308,102.00 | 39.32 | 2,250.65 | 15.30 | 85.62 | 59v4zh1ac3v2a | DBMS_SCHEDULER | DECLARE job BINARY_INTEGER := ... |
... | ... | ... | .... | ... | ... | ... | ... | ... | ... |
910,228 | 2 | 455,114.00 | 10.82 | 116.02 | 66.64 | 12.31 | f0tqq0h3aqsn0 | SQL*Plus | SELECT TRUNC(EVENT_DT, 'hh') E... |
772,465 | 1 | 772,465.00 | 9.18 | 3,536.45 | 25.41 | 70.20 | ftja941kjtrxj | SQL*Plus | DECLARE OUTPUT NUMBER; V_ERROR... |
681,649 | 0 | 8.10 | 3,598.62 | 1.29 | 99.21 | 6mcpb06rctk0x | DBMS_SCHEDULER | call dbms_space.auto_space_adv... | |
654,542 | 1 | 654,542.00 | 7.78 | 3,166.37 | 26.57 | 68.47 | 9cdjzp9sv9tqh | SQL*Plus | INSERT INTO FIREWALL_XFORM(REC... |
391,516 | 317 | 1,235.07 | 4.65 | 3,564.33 | 0.89 | 99.56 | 8szmwam7fysa3 | DBMS_SCHEDULER | insert into wri$_adv_objspace_... |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
To tune high physical reads for non smart cache sql,please review following:
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=f0tqq0h3aqsn0" from same AWR report in the comparison below:
SQL ordered by Physical Reads (UnOptimized)
SQL ordered by Reads
For more details on configuring the Smart Flash Cache see:
11g Release 2 (11.2)
Part Number E25494-02
Configuring Database Smart Flash Cache
http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#BABHEDBH
![](https://i-blog.csdnimg.cn/blog_migrate/200491d1ae6203cbdf7e02dca39a75b1.gif)