ITPub博客

首页 > 数据库 > Oracle > Oracle AWR报告分析之–SQL ordered by

Oracle AWR报告分析之–SQL ordered by

Oracle 作者:zhang_1202 时间:2015-11-12 15:07:32 0 删除 编辑

 1、SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
  • %Total - Elapsed Time as a percentage of Total DB 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 127.4% of Total DB Time (s): 1,908
  • Captured PL/SQL account for 39.1% of Total DB Time (s): 1,908
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和,而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。
Elapsed Time(S): SQL语句执行用总时长,此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间,而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time
CPU Time(s): 为SQL语句执行时CPU占用时间总时长,此时间会小于等于Elapsed Time时间。单位时间为秒。
Executions: SQL语句在监控范围内的执行次数总计。
Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。
% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。
SQL ID: SQL语句的ID编号,点击之后就能导航到下边的SQL详细列表中,点击IE的返回可以回到当前SQL ID的地方。
SQL Module: 显示该SQL是用什么方式连接到数据库执行的,如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
SQL Text: 简单的sql提示,详细的需要点击SQL ID。


2、SQL ordered by CPU Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • %Total - CPU Time as a percentage of Total DB CPU
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 125.6% of Total CPU Time (s): 1,339
  • Captured PL/SQL account for 38.0% of Total CPU Time (s): 1,339
CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和,而不是单次SQL执行时间)。




3、SQL ordered by Gets

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • %Total - Buffer Gets as a percentage of Total Buffer Gets
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Total Buffer Gets: 359,945,491
  • Captured SQL account for 174.8% of Total
Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和,而不是单次SQL执行所占的Gets)。



4、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: 459
  • Captured SQL account for 0.2% of Total
Physical Reads Executions Reads per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text

5、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: 459
  • Captured SQL account for 0.2% of Total
  • Total UnOptimized Read Requests: 459
  • Captured SQL account for 0.2% 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
记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和,而不是单次SQL执行所占的磁盘物理读)。



6、SQL ordered by Executions

  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Total Executions: 2,863,782
  • Captured SQL account for 77.9% of Total
Executions Rows Processed Rows per Exec Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。



7、SQL ordered by Parse Calls

  • Total Parse Calls: 13,587
  • Captured SQL account for 14.0% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oracle对sql的处理过程。



8、SQL ordered by Sharable Memory

  • Only Statements with Sharable Memory greater than 1048576 are displayed
Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text
记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b):占用library cache的大小,单位是byte。



9、SQL ordered by Version Count

  • Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
记录了SQL的打开子游标的TOP SQL。


10、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 1.0% of Total User I/O Wait Time (s): 11
  • Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 11
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
记录了收集间的等待时间的TOP SQL


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30557618/viewspace-1833485/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-11-09

  • 博文量
    40
  • 访问量
    90321