1. -- awr_topsqlx-exa.sql
  2. -- AWR Top SQL Report, a version of "Top SQL" but across SNAP_IDs with AAS metric and more details
  3. -- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
  4. -- http://karlarao.wordpress.com
  5. --
  6. -- NOTES: SEE COMMENTS ON THE SCRIPT..ESPECIALLY ON SQL_TEXT, TIME_RANK, AND ORDER BY SECTIONS
  7. --
  8. -- Changes:
  9. -- 20100512 added timestamp to filter specific workload periods, must uncomment to use
  10. -- 20120825 added the join of dba_hist_sqltext to audit_actions to show the short name of command_type
  11. 
  12. set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
  13. 
  14. set arraysize 5000
  15. set termout off
  16. set echo off verify off
  17. 
  18. COLUMN blocksize NEW_VALUE _blocksize NOPRINT
  19. select distinct block_size blocksize from v$datafile;
  20. 
  21. COLUMN dbid NEW_VALUE _dbid NOPRINT
  22. select dbid from v$database;
  23. 
  24. COLUMN name NEW_VALUE _instname NOPRINT
  25. select lower(instance_name) name from v$instance;
  26. 
  27. COLUMN name NEW_VALUE _hostname NOPRINT
  28. select lower(host_name) name from v$instance;
  29. 
  30. COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
  31. select instance_number instancenumber from v$instance;
  32. 
  33. -- ttitle center 'AWR Top SQL Report' skip 2
  34. set pagesize 50000
  35. set linesize 700
  36. 
  37. col snap_id format 99999 heading -- "Snap|ID"
  38. col tm format a15 heading -- "Snap|Start|Time"
  39. col inst format 90 heading -- "i|n|s|t|#"
  40. col dur format 990.00 heading -- "Snap|Dur|(m)"
  41. col sql_id format a15 heading -- "SQL|ID"
  42. col phv format 99999999999 heading -- "Plan|Hash|Value"
  43. col module format a50
  44. col elap format 999990.00 heading -- "Ela|Time|(s)"
  45. col elapexec format 999990.00 heading -- "Ela|Time|per|exec|(s)"
  46. col cput format 999990.00 heading -- "CPU|Time|(s)"
  47. col iowait format 999990.00 heading -- "IO|Wait|(s)"
  48. col appwait format 999990.00 heading -- "App|Wait|(s)"
  49. col concurwait format 999990.00 heading -- "Ccr|Wait|(s)"
  50. col clwait format 999990.00 heading -- "Cluster|Wait|(s)"
  51. col bget format 99999999990 heading -- "LIO"
  52. col dskr format 99999999990 heading -- "PIO"
  53. col dpath format 99999999990 heading -- "Direct|Writes"
  54. col rowp format 99999999990 heading -- "Rows"
  55. col exec format 9999990 heading -- "Exec"
  56. col prsc format 999999990 heading -- "Parse|Count"
  57. col pxexec format 9999990 heading -- "PX|Server|Exec"
  58. col icbytes format 99999990 heading -- "IC|MB"
  59. col offloadbytes format 99999990 heading -- "Offload|MB"
  60. col offloadreturnbytes format 99999990 heading -- "Offload|return|MB"
  61. col flashcachereads format 99999990 heading -- "Flash|Cache|MB"
  62. col uncompbytes format 99999990 heading -- "Uncomp|MB"
  63. col pctdbt format 990 heading -- "DB Time|%"
  64. col aas format 990.00 heading -- "A|A|S"
  65. col time_rank format 90 heading -- "Time|Rank"
  66. col sql_text format a6 heading -- "SQL|Text"
  67. 
  68. VARIABLE g_retention NUMBER
  69. DEFINE p_default = 8
  70. DEFINE p_max = 100
  71. SET VERIFY OFF
  72. DECLARE
  73. v_default NUMBER(3) := &p_default;
  74. v_max NUMBER(3) := &p_max;
  75. BEGIN
  76. select
  77. ((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
  78. into :g_retention
  79. from dba_hist_wr_control
  80. where dbid in (select dbid from v$database);
  81. 
  82. if :g_retention > v_default then
  83. :g_retention := v_max;
  84. else
  85. :g_retention := v_default;
  86. end if;
  87. END;
  88. /
  89. 
  90. spool awr_topsqlx-tableau-exa-&_instname-&_hostname..csv
  91. select *
  92. from (
  93. select
  94. trim('&_instname') instname,
  95. trim('&_dbid') db_id,
  96. trim('&_hostname') hostname,
  97. sqt.snap_id snap_id,
  98. TO_CHAR(sqt.tm,'MM/DD/YY HH24:MI:SS') tm,
  99. sqt.inst inst,
  100. sqt.dur dur,
  101. sqt.aas aas,
  102. nvl((sqt.elap), to_number(null)) elap,
  103. nvl((sqt.elapexec), 0) elapexec,
  104. nvl((sqt.cput), to_number(null)) cput,
  105. sqt.iowait iowait,
  106. sqt.appwait appwait,
  107. sqt.concurwait concurwait,
  108. sqt.clwait clwait,
  109. sqt.bget bget,
  110. sqt.dskr dskr,
  111. sqt.dpath dpath,
  112. sqt.rowp rowp,
  113. sqt.exec exec,
  114. sqt.prsc prsc,
  115. sqt.pxexec pxexec,
  116. sqt.icbytes,
  117. sqt.offloadbytes,
  118. sqt.offloadreturnbytes,
  119. sqt.flashcachereads,
  120. sqt.uncompbytes,
  121. sqt.time_rank time_rank,
  122. sqt.sql_id sql_id,
  123. sqt.phv phv,
  124. sqt.parse_schema parse_schema,
  125. substr(to_clob(decode(sqt.module, null, null, sqt.module)),1,50) module,
  126. st.sql_text sql_text -- PUT/REMOVE COMMENT TO HIDE/SHOW THE SQL_TEXT
  127. from (
  128. select snap_id, tm, inst, dur, sql_id, phv, parse_schema, module, elap, elapexec, cput, iowait, appwait, concurwait, clwait, bget, dskr, dpath, rowp, exec, prsc, pxexec, icbytes, offloadbytes, offloadreturnbytes, flashcachereads, uncompbytes, aas, time_rank
  129. from
  130. (
  131. select
  132. s0.snap_id snap_id,
  133. s0.END_INTERVAL_TIME tm,
  134. s0.instance_number inst,
  135. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  136. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  137. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  138. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  139. e.sql_id sql_id,
  140. e.plan_hash_value phv,
  141. e.parsing_schema_name parse_schema,
  142. max(e.module) module,
  143. sum(e.elapsed_time_delta)/1000000 elap,
  144. decode((sum(e.executions_delta)), 0, to_number(null), ((sum(e.elapsed_time_delta)) / (sum(e.executions_delta)) / 1000000)) elapexec,
  145. sum(e.cpu_time_delta)/1000000 cput,
  146. sum(e.iowait_delta)/1000000 iowait,
  147. sum(e.apwait_delta)/1000000 appwait,
  148. sum(e.ccwait_delta)/1000000 concurwait,
  149. sum(e.clwait_delta)/1000000 clwait,
  150. sum(e.buffer_gets_delta) bget,
  151. sum(e.disk_reads_delta) dskr,
  152. sum(e.direct_writes_delta) dpath,
  153. sum(e.rows_processed_delta) rowp,
  154. sum(e.executions_delta) exec,
  155. sum(e.parse_calls_delta) prsc,
  156. sum(e.px_servers_execs_delta) pxexec,
  157. sum(e.io_interconnect_bytes_delta)/1024/1024 icbytes,
  158. sum(e.io_offload_elig_bytes_delta)/1024/1024 offloadbytes,
  159. sum(e.io_offload_return_bytes_delta)/1024/1024 offloadreturnbytes,
  160. (sum(e.optimized_physical_reads_delta)* &_blocksize)/1024/1024 flashcachereads,
  161. sum(e.cell_uncompressed_bytes_delta)/1024/1024 uncompbytes,
  162. (sum(e.elapsed_time_delta)/1000000) / ((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  163. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  164. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  165. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2))*60) aas,
  166. DENSE_RANK() OVER (
  167. PARTITION BY s0.snap_id ORDER BY e.elapsed_time_delta DESC) time_rank
  168. from
  169. dba_hist_snapshot s0,
  170. dba_hist_snapshot s1,
  171. dba_hist_sqlstat e
  172. where
  173. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  174. AND s1.dbid = s0.dbid
  175. and e.dbid = s0.dbid
  176. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  177. AND s1.instance_number = s0.instance_number
  178. and e.instance_number = s0.instance_number
  179. AND s1.snap_id = s0.snap_id + 1
  180. and e.snap_id = s0.snap_id + 1
  181. group by
  182. s0.snap_id, s0.END_INTERVAL_TIME, s0.instance_number, e.sql_id, e.plan_hash_value, e.parsing_schema_name, e.elapsed_time_delta, s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME
  183. )
  184. where
  185. time_rank <= 5 -- GET TOP 5 SQL ACROSS SNAP_IDs... YOU CAN ALTER THIS TO HAVE MORE DATA POINTS
  186. )
  187. sqt,
  188. (select sql_id, dbid, nvl(b.name, a.command_type) sql_text from dba_hist_sqltext a, audit_actions b where a.command_type = b.action(+)) st
  189. where st.sql_id(+) = sqt.sql_id
  190. and st.dbid(+) = &_dbid
  191. -- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
  192. -- AND TO_CHAR(tm,'D') <= 7
  193. -- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
  194. -- AND TO_CHAR(tm,'HH24MI') <= 1800
  195. -- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
  196. -- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
  197. -- AND snap_id in (338,339)
  198. -- AND snap_id = 338
  199. -- AND snap_id >= 335 and snap_id <= 339
  200. -- AND lower(st.sql_text) like 'select%'
  201. -- AND lower(st.sql_text) like 'insert%'
  202. -- AND lower(st.sql_text) like 'update%'
  203. -- AND lower(st.sql_text) like 'merge%'
  204. -- AND pxexec > 0
  205. -- AND aas > .5
  206. order by
  207. snap_id -- TO GET SQL OUTPUT ACROSS SNAP_IDs SEQUENTIALLY AND ASC
  208. -- nvl(sqt.elap, -1) desc, sqt.sql_id -- TO GET SQL OUTPUT BY ELAPSED TIME
  209. )
  210. -- where rownum <= 20
  211. WHERE
  212. to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
  213. ;
  214. spool off
  215. host sed -n -i '2,$ p' awr_topsqlx-tableau-exa-&_instname-&_hostname..csv
  216. host gzip -v awr_topsqlx-tableau-exa-&_instname-&_hostname..csv
  217. host tar -cvf awr_topsqlx-tableau-exa-&_instname-&_hostname..tar awr_topsqlx-tableau-exa-&_instname-&_hostname..csv.gz
  218. host rm awr_topsqlx-tableau-exa-&_instname-&_hostname..csv.gz
  219.