1. -- awr_topevents.sql
  2. -- AWR Top Events Report, a version of "Top 5 Timed Events" but across SNAP_IDs with AAS metric
  3. -- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
  4. -- http://karlarao.wordpress.com
  5. --
  6. -- Changes:
  7. -- 20100427 included the columns "tm, inst, dur" and "event_rank"
  8. -- 20100511 added timestamp to filter specific workload periods, must uncomment to use
  9. -- 20120120 added a section to compute for "CPU wait" (new metric in 11g) to include the
  10. -- "unaccounted for DB Time" on high run queue or CPU intensive workloads
  11. -- 20120420 added NULLIF on pctdbt for very idle databases
  12. 
  13. set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
  14. 
  15. set arraysize 5000
  16. set termout off
  17. set echo off verify off
  18. 
  19. COLUMN dbid NEW_VALUE _dbid NOPRINT
  20. select dbid from v$database;
  21. 
  22. COLUMN name NEW_VALUE _instname NOPRINT
  23. select lower(instance_name) name from v$instance;
  24. 
  25. COLUMN name NEW_VALUE _hostname NOPRINT
  26. select lower(host_name) name from v$instance;
  27. 
  28. COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
  29. select instance_number instancenumber from v$instance;
  30. 
  31. -- ttitle center 'AWR Top Events Report' skip 2
  32. set pagesize 50000
  33. set linesize 550
  34. 
  35. col instname format a15
  36. col hostname format a30
  37. col snap_id format 99999 heading snap_id -- "snapid"
  38. col tm format a17 heading tm -- "tm"
  39. col inst format 90 heading inst -- "inst"
  40. col dur format 999990.00 heading dur -- "dur"
  41. col event format a55 heading event -- "Event"
  42. col event_rank format 90 heading event_rank -- "EventRank"
  43. col waits format 9999999990.00 heading waits -- "Waits"
  44. col time format 9999999990.00 heading time -- "Timesec"
  45. col avgwt format 99990.00 heading avgwt -- "Avgwtms"
  46. col pctdbt format 9990.0 heading pctdbt -- "DBTimepct"
  47. col aas format 990.0 heading aas -- "Aas"
  48. col wait_class format a15 heading wait_class -- "WaitClass"
  49. 
  50. VARIABLE g_retention NUMBER
  51. DEFINE p_default = 8
  52. DEFINE p_max = 100
  53. SET VERIFY OFF
  54. DECLARE
  55. v_default NUMBER(3) := &p_default;
  56. v_max NUMBER(3) := &p_max;
  57. BEGIN
  58. select
  59. ((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
  60. into :g_retention
  61. from dba_hist_wr_control
  62. where dbid in (select dbid from v$database);
  63. 
  64. if :g_retention > v_default then
  65. :g_retention := v_max;
  66. else
  67. :g_retention := v_default;
  68. end if;
  69. END;
  70. /
  71. 
  72. spool awr_topevents-tableau-&_instname-&_hostname..csv
  73. select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class
  74. from
  75. (select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
  76. DENSE_RANK() OVER (
  77. PARTITION BY snap_id ORDER BY time DESC) event_rank
  78. from
  79. (
  80. select * from
  81. (select * from
  82. (select
  83. s0.snap_id snap_id,
  84. s0.END_INTERVAL_TIME tm,
  85. s0.instance_number inst,
  86. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  87. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  88. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  89. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  90. e.event_name event,
  91. e.total_waits - nvl(b.total_waits,0) waits,
  92. round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
  93. round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
  94. ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
  95. (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  96. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  97. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  98. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
  99. e.wait_class wait_class
  100. from
  101. dba_hist_snapshot s0,
  102. dba_hist_snapshot s1,
  103. dba_hist_system_event b,
  104. dba_hist_system_event e,
  105. dba_hist_sys_time_model s5t0,
  106. dba_hist_sys_time_model s5t1
  107. where
  108. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  109. AND s1.dbid = s0.dbid
  110. and b.dbid(+) = s0.dbid
  111. and e.dbid = s0.dbid
  112. AND s5t0.dbid = s0.dbid
  113. AND s5t1.dbid = s0.dbid
  114. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  115. AND s1.instance_number = s0.instance_number
  116. and b.instance_number(+) = s0.instance_number
  117. and e.instance_number = s0.instance_number
  118. AND s5t0.instance_number = s0.instance_number
  119. AND s5t1.instance_number = s0.instance_number
  120. AND s1.snap_id = s0.snap_id + 1
  121. AND b.snap_id(+) = s0.snap_id
  122. and e.snap_id = s0.snap_id + 1
  123. AND s5t0.snap_id = s0.snap_id
  124. AND s5t1.snap_id = s0.snap_id + 1
  125. AND s5t0.stat_name = 'DB time'
  126. AND s5t1.stat_name = s5t0.stat_name
  127. and b.event_id = e.event_id
  128. and e.wait_class != 'Idle'
  129. and e.total_waits > nvl(b.total_waits,0)
  130. and e.event_name not in ('smon timer',
  131. 'pmon timer',
  132. 'dispatcher timer',
  133. 'dispatcher listen timer',
  134. 'rdbms ipc message')
  135. order by snap_id, time desc, waits desc, event)
  136. union all
  137. select
  138. s0.snap_id snap_id,
  139. s0.END_INTERVAL_TIME tm,
  140. s0.instance_number inst,
  141. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  142. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  143. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  144. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  145. 'CPU time',
  146. 0,
  147. round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
  148. 0,
  149. ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
  150. (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  151. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  152. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  153. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
  154. 'CPU'
  155. from
  156. dba_hist_snapshot s0,
  157. dba_hist_snapshot s1,
  158. dba_hist_sys_time_model s6t0,
  159. dba_hist_sys_time_model s6t1,
  160. dba_hist_sys_time_model s5t0,
  161. dba_hist_sys_time_model s5t1
  162. WHERE
  163. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  164. AND s1.dbid = s0.dbid
  165. AND s6t0.dbid = s0.dbid
  166. AND s6t1.dbid = s0.dbid
  167. AND s5t0.dbid = s0.dbid
  168. AND s5t1.dbid = s0.dbid
  169. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  170. AND s1.instance_number = s0.instance_number
  171. AND s6t0.instance_number = s0.instance_number
  172. AND s6t1.instance_number = s0.instance_number
  173. AND s5t0.instance_number = s0.instance_number
  174. AND s5t1.instance_number = s0.instance_number
  175. AND s1.snap_id = s0.snap_id + 1
  176. AND s6t0.snap_id = s0.snap_id
  177. AND s6t1.snap_id = s0.snap_id + 1
  178. AND s5t0.snap_id = s0.snap_id
  179. AND s5t1.snap_id = s0.snap_id + 1
  180. AND s6t0.stat_name = 'DB CPU'
  181. AND s6t1.stat_name = s6t0.stat_name
  182. AND s5t0.stat_name = 'DB time'
  183. AND s5t1.stat_name = s5t0.stat_name
  184. union all
  185. (select
  186. dbtime.snap_id,
  187. dbtime.tm,
  188. dbtime.inst,
  189. dbtime.dur,
  190. 'CPU wait',
  191. 0,
  192. round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec)
  193. 0,
  194. ((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec)
  195. round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME
  196. 'CPU wait'
  197. from
  198. (select
  199. s0.snap_id,
  200. s0.END_INTERVAL_TIME tm,
  201. s0.instance_number inst,
  202. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  203. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  204. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  205. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  206. 'DB time',
  207. 0,
  208. round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec)
  209. 0,
  210. 0,
  211. (round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  212. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  213. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  214. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas,
  215. 'DB time'
  216. from
  217. dba_hist_snapshot s0,
  218. dba_hist_snapshot s1,
  219. dba_hist_sys_time_model s5t0,
  220. dba_hist_sys_time_model s5t1
  221. WHERE
  222. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  223. AND s1.dbid = s0.dbid
  224. AND s5t0.dbid = s0.dbid
  225. AND s5t1.dbid = s0.dbid
  226. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  227. AND s1.instance_number = s0.instance_number
  228. AND s5t0.instance_number = s0.instance_number
  229. AND s5t1.instance_number = s0.instance_number
  230. AND s1.snap_id = s0.snap_id + 1
  231. AND s5t0.snap_id = s0.snap_id
  232. AND s5t1.snap_id = s0.snap_id + 1
  233. AND s5t0.stat_name = 'DB time'
  234. AND s5t1.stat_name = s5t0.stat_name) dbtime,
  235. (select snap_id, inst, sum(time) time, sum(AAS) aas from
  236. (select * from (select
  237. s0.snap_id snap_id,
  238. s0.END_INTERVAL_TIME tm,
  239. s0.instance_number inst,
  240. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  241. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  242. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  243. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  244. e.event_name event,
  245. e.total_waits - nvl(b.total_waits,0) waits,
  246. round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
  247. round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
  248. ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
  249. (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  250. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  251. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  252. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
  253. e.wait_class wait_class
  254. from
  255. dba_hist_snapshot s0,
  256. dba_hist_snapshot s1,
  257. dba_hist_system_event b,
  258. dba_hist_system_event e,
  259. dba_hist_sys_time_model s5t0,
  260. dba_hist_sys_time_model s5t1
  261. where
  262. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  263. AND s1.dbid = s0.dbid
  264. and b.dbid(+) = s0.dbid
  265. and e.dbid = s0.dbid
  266. AND s5t0.dbid = s0.dbid
  267. AND s5t1.dbid = s0.dbid
  268. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  269. AND s1.instance_number = s0.instance_number
  270. and b.instance_number(+) = s0.instance_number
  271. and e.instance_number = s0.instance_number
  272. AND s5t0.instance_number = s0.instance_number
  273. AND s5t1.instance_number = s0.instance_number
  274. AND s1.snap_id = s0.snap_id + 1
  275. AND b.snap_id(+) = s0.snap_id
  276. and e.snap_id = s0.snap_id + 1
  277. AND s5t0.snap_id = s0.snap_id
  278. AND s5t1.snap_id = s0.snap_id + 1
  279. AND s5t0.stat_name = 'DB time'
  280. AND s5t1.stat_name = s5t0.stat_name
  281. and b.event_id = e.event_id
  282. and e.wait_class != 'Idle'
  283. and e.total_waits > nvl(b.total_waits,0)
  284. and e.event_name not in ('smon timer',
  285. 'pmon timer',
  286. 'dispatcher timer',
  287. 'dispatcher listen timer',
  288. 'rdbms ipc message')
  289. order by snap_id, time desc, waits desc, event)
  290. union all
  291. select
  292. s0.snap_id snap_id,
  293. s0.END_INTERVAL_TIME tm,
  294. s0.instance_number inst,
  295. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  296. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  297. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  298. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  299. 'CPU time',
  300. 0,
  301. round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
  302. 0,
  303. ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
  304. (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  305. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  306. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  307. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
  308. 'CPU'
  309. from
  310. dba_hist_snapshot s0,
  311. dba_hist_snapshot s1,
  312. dba_hist_sys_time_model s6t0,
  313. dba_hist_sys_time_model s6t1,
  314. dba_hist_sys_time_model s5t0,
  315. dba_hist_sys_time_model s5t1
  316. WHERE
  317. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  318. AND s1.dbid = s0.dbid
  319. AND s6t0.dbid = s0.dbid
  320. AND s6t1.dbid = s0.dbid
  321. AND s5t0.dbid = s0.dbid
  322. AND s5t1.dbid = s0.dbid
  323. --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  324. AND s1.instance_number = s0.instance_number
  325. AND s6t0.instance_number = s0.instance_number
  326. AND s6t1.instance_number = s0.instance_number
  327. AND s5t0.instance_number = s0.instance_number
  328. AND s5t1.instance_number = s0.instance_number
  329. AND s1.snap_id = s0.snap_id + 1
  330. AND s6t0.snap_id = s0.snap_id
  331. AND s6t1.snap_id = s0.snap_id + 1
  332. AND s5t0.snap_id = s0.snap_id
  333. AND s5t1.snap_id = s0.snap_id + 1
  334. AND s6t0.stat_name = 'DB CPU'
  335. AND s6t1.stat_name = s6t0.stat_name
  336. AND s5t0.stat_name = 'DB time'
  337. AND s5t1.stat_name = s5t0.stat_name
  338. ) group by snap_id, inst) accounted_dbtime
  339. where dbtime.snap_id = accounted_dbtime.snap_id
  340. and dbtime.inst = accounted_dbtime.inst
  341. )
  342. )
  343. )
  344. )
  345. WHERE event_rank <= 5
  346. AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
  347. -- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
  348. -- AND TO_CHAR(tm,'D') <= 7
  349. -- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
  350. -- AND TO_CHAR(tm,'HH24MI') <= 1800
  351. -- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
  352. -- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
  353. -- and snap_id = 495
  354. -- and snap_id >= 495 and snap_id <= 496
  355. -- and event = 'db file sequential read'
  356. -- and event like 'CPU%'
  357. -- and avgwt > 5
  358. -- and aas > .5
  359. -- and wait_class = 'CPU'
  360. -- and wait_class like '%I/O%'
  361. -- and event_rank in (1,2,3)
  362. ORDER BY snap_id;
  363. spool off
  364. host sed -n -i '2,$ p' awr_topevents-tableau-&_instname-&_hostname..csv
  365. host gzip -v awr_topevents-tableau-&_instname-&_hostname..csv
  366. host tar -cvf awr_topevents-tableau-&_instname-&_hostname..tar awr_topevents-tableau-&_instname-&_hostname..csv.gz
  367. host rm awr_topevents-tableau-&_instname-&_hostname..csv.gz
  368.