« [招聘信息]-飞信招聘高级MySQL dba | Blog首页 | 盖小咪的第一次表演 »
AWR之:SQL ordered by User I/O Wait Time
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/01/awrsql_ordered_userio.html
这几天在做AWR报告分析时,忽然发现在报告中SQL部分增加了几个新的内容,现在的列表包括:链接:https://www.eygle.com/archives/2011/01/awrsql_ordered_userio.html
- SQL ordered by Elapsed Time
- SQL ordered by CPU Time
- SQL ordered by User I/O Wait Time
- SQL ordered by Gets
- SQL ordered by Reads
- SQL ordered by Physical Reads (UnOptimized)
- SQL ordered by Executions
- SQL ordered by Parse Calls
- SQL ordered by Sharable Memory
- SQL ordered by Version Count
- Complete List of SQL Text
SQL ordered by User I/O Wait Time帮助我们找到那些消耗最多I/O等待时间的SQL查询,以下是一个示范输出:
SQL ordered by User I/O Wait Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- %Total - User I/O Time as a percentage of Total User I/O Wait time
- %CPU - CPU Time as a percentage of Elapsed Time
- %IO - User I/O Time as a percentage of Elapsed Time
- Captured SQL account for 78.2% of Total User I/O Wait Time (s): 167,727
- Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 167,727
User I/O Time (s) | Executions | UIO per Exec (s) | %Total | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|---|
22,780.47 | 12 | 1,898.37 | 13.58 | 25,162.65 | 2.24 | 90.53 | 3mdwt0vbm1h7v | SELECT SDSO15, SDFRGD, SDFUC, ... | |
15,310.87 | 8 | 1,913.86 | 9.13 | 16,537.85 | 2.67 | 92.58 | 83za26ynm7sn8 | SELECT ILKCO, ILUKID, ILLOTN, ... | |
10,787.33 | 5 | 2,157.47 | 6.43 | 11,602.32 | 2.66 | 92.98 | f5s0wxsx32jz3 | ORACLE.EXE | SELECT "A1"."SDTRDJ", "A1"."SD... |
10,515.02 | 11 | 955.91 | 6.27 | 10,686.33 | 2.01 | 98.40 | b26uyypzm63ps | INSERT INTO PRODDTA.F554111L(T... | |
9,437.84 | 6 | 1,572.97 | 5.63 | 10,016.45 | 3.89 | 94.22 | 2a4x9zud41btv | SELECT ILKCO, ILUKID, ILLOTN, ... | |
8,720.13 | 4 | 2,180.03 | 5.20 | 9,291.57 | 3.29 | 93.85 | 0cry9wsbj3yjp | jdenet_k.exe | SELECT SDPRAN8 FROM PRODDTA.F4... |
7,880.64 | 1 | 7,880.64 | 4.70 | 7,960.22 | 1.86 | 99.00 | 4gyhv1tu7cqd3 | INSERT INTO PRODDTA.F554111X (... | |
5,790.45 | 1 | 5,790.45 | 3.45 | 5,832.77 | 2.04 | 99.27 | 1cqws9u14ayd5 | SELECT SLDGL, SLLNTY, SLNXTR, ... | |
4,224.36 | 1 | 4,224.36 | 2.52 | 4,249.62 | 2.27 | 99.41 | 1smht09f1q2pt | SELECT SLDGL, SLLNTY, SLNXTR, ... | |
3,081.17 | 0 | 1.84 | 3,358.95 | 1.96 | 91.73 | 4nmku6h62j2p1 | SELECT GLJBCD, GLJBST, GLUSER,... | ||
2,588.41 | 3 | 862.80 | 1.54 | 2,614.65 | 1.39 | 99.00 | 1q3svk26r7ta4 | SELECT SDSO15, SDFRGD, SDFUC, ... | |
2,124.71 | 1 | 2,124.71 | 1.27 | 2,776.07 | 2.54 | 76.54 | ahcm9d23yvvpg | SELECT T0.SDDCTO, T0.SDUOPN, T... | |
2,107.62 | 2 | 1,053.81 | 1.26 | 2,110.95 | 3.82 | 99.84 | f2xft4f1fbbvf | SELECT ILKCO, ILUKID, ILLOTN, ... | |
2,035.91 | 1 | 2,035.91 | 1.21 | 2,650.37 | 2.37 | 76.82 | drhb7uvyqgcyx | SELECT ILKCO, ILUKID, ILLOTN, ... | |
2,014.26 | 2 | 1,007.13 | 1.20 | 2,033.23 | 1.54 | 99.07 | a6zj1yjc05bg7 | ORACLE.EXE | SELECT SUM("A1"."RPAG")/100 FR... |
1,752.87 | 1 | 1,752.87 | 1.05 | 1,757.86 | 2.75 | 99.72 | 0cr0zm356mjzb | jdenet_k.exe | SELECT * FROM PRODDTA.F4111 WH... |
1,749.27 | 73,862 | 0.02 | 1.04 | 1,772.38 | 3.96 | 98.70 | 8m6prp6zgr0dg | runbatch.exe | SELECT T0.PRMATC, T0.PRAN8, T0... |
1,686.59 | 1 | 1,686.59 | 1.01 | 2,152.18 | 2.36 | 78.37 | gpnxfz34ubzbk | SELECT ILKCO, ILUKID, ILLOTN, ... |
这些细致入微的微小变更,都使得Oracle更接近用户。
历史上的今天...
>> 2013-01-14文章:
>> 2010-01-14文章:
>> 2009-01-14文章:
>> 2008-01-14文章:
>> 2007-01-14文章:
>> 2006-01-14文章:
>> 2005-01-14文章:
By eygle on 2011-01-14 11:36 | Comments (1) | FAQ | 2709 |
这样的话,,我觉得还应该加入两块信息,对于大规模的网站可能也非常适用..
order by application_wait_time
order by concurrency_wait_time
分别表示发生这条SQL语句上的application_wait_time的时间与concurrency_wait_time的时间,这两个指标都是oracle 10g中引入的..
至于这两个指标都是什么含义,,可以参考v$event_name 中对应的wait_class来确定.