Oracle AWR報告分析之–SQL ordered by

yy418408247發表於2016-06-23
OracleSQL

 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/29494175/viewspace-2120787/，如需轉載，請註明出處，否則將追究法律責任。

相關文章