eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« [招聘信息]-飞信招聘高级MySQL dba | Blog首页 | 盖小咪的第一次表演 »

AWR之:SQL ordered by User I/O Wait Time

这几天在做AWR报告分析时,忽然发现在报告中SQL部分增加了几个新的内容,现在的列表包括:
这其中User I/O 和 Physical Reads(UnOptimized)部分是Oracle 11gR2中新增加的,而且幸运的是,当我们通过AEWRLOAD.sql将10g的AWR数据加载到11gR2的数据库中时,这部分信息同样能够被展现出来。

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)%TotalElapsed Time (s)%CPU%IO SQL IdSQL ModuleSQL Text
22,780.47121,898.3713.5825,162.652.2490.533mdwt0vbm1h7v   SELECT SDSO15, SDFRGD, SDFUC, ...
15,310.8781,913.869.1316,537.852.6792.5883za26ynm7sn8   SELECT ILKCO, ILUKID, ILLOTN, ...
10,787.3352,157.476.4311,602.322.6692.98f5s0wxsx32jz3 ORACLE.EXE SELECT "A1"."SDTRDJ", "A1"."SD...
10,515.0211955.916.2710,686.332.0198.40b26uyypzm63ps   INSERT INTO PRODDTA.F554111L(T...
9,437.8461,572.975.6310,016.453.8994.222a4x9zud41btv   SELECT ILKCO, ILUKID, ILLOTN, ...
8,720.1342,180.035.209,291.573.2993.850cry9wsbj3yjp jdenet_k.exe SELECT SDPRAN8 FROM PRODDTA.F4...
7,880.6417,880.644.707,960.221.8699.004gyhv1tu7cqd3   INSERT INTO PRODDTA.F554111X (...
5,790.4515,790.453.455,832.772.0499.271cqws9u14ayd5   SELECT SLDGL, SLLNTY, SLNXTR, ...
4,224.3614,224.362.524,249.622.2799.411smht09f1q2pt   SELECT SLDGL, SLLNTY, SLNXTR, ...
3,081.170 1.843,358.951.9691.734nmku6h62j2p1   SELECT GLJBCD, GLJBST, GLUSER,...
2,588.413862.801.542,614.651.3999.001q3svk26r7ta4   SELECT SDSO15, SDFRGD, SDFUC, ...
2,124.7112,124.711.272,776.072.5476.54ahcm9d23yvvpg   SELECT T0.SDDCTO, T0.SDUOPN, T...
2,107.6221,053.811.262,110.953.8299.84f2xft4f1fbbvf   SELECT ILKCO, ILUKID, ILLOTN, ...
2,035.9112,035.911.212,650.372.3776.82drhb7uvyqgcyx   SELECT ILKCO, ILUKID, ILLOTN, ...
2,014.2621,007.131.202,033.231.5499.07a6zj1yjc05bg7 ORACLE.EXE SELECT SUM("A1"."RPAG")/100 FR...
1,752.8711,752.871.051,757.862.7599.720cr0zm356mjzb jdenet_k.exe SELECT * FROM PRODDTA.F4111 WH...
1,749.2773,8620.021.041,772.383.9698.708m6prp6zgr0dg runbatch.exe SELECT T0.PRMATC, T0.PRAN8, T0...
1,686.5911,686.591.012,152.182.3678.37gpnxfz34ubzbk   SELECT ILKCO, ILUKID, ILLOTN, ...

这些细致入微的微小变更,都使得Oracle更接近用户。



历史上的今天...
    >> 2013-01-14文章:
    >> 2010-01-14文章:
    >> 2009-01-14文章:
    >> 2008-01-14文章:
    >> 2007-01-14文章:
           2006 中国ERP市场谁主沉浮?
    >> 2006-01-14文章:
    >> 2005-01-14文章:
           HRAY纳斯达克的IPO历程

无觅

By eygle on 2011-01-14 11:36 | Comments (1) | FAQ | 2709 |

1 Comment

这样的话,,我觉得还应该加入两块信息,对于大规模的网站可能也非常适用..

order by application_wait_time
order by concurrency_wait_time

分别表示发生这条SQL语句上的application_wait_time的时间与concurrency_wait_time的时间,这两个指标都是oracle 10g中引入的..

至于这两个指标都是什么含义,,可以参考v$event_name 中对应的wait_class来确定.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com