1. -- awr_services.sql
  2. -- AWR Services Statistics Report
  3. -- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
  4. -- http://karlarao.wordpress.com
  5. --
  6. --
  7. -- Changes:
  8. 
  9. set echo off verify off
  10. 
  11. COLUMN blocksize NEW_VALUE _blocksize NOPRINT
  12. select distinct block_size blocksize from v$datafile;
  13. 
  14. set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
  15. 
  16. set arraysize 5000
  17. set termout off
  18. set echo off verify off
  19. 
  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 Services Statistics Report' skip 2
  34. set pagesize 50000
  35. set linesize 550
  36. 
  37. col instname format a15
  38. col hostname format a30
  39. col tm format a15 heading tm --"Snap|Start|Time"
  40. col id format 99999 heading id --"Snap|ID"
  41. col inst format 90 heading inst --"i|n|s|t|#"
  42. col dur format 999990.00 heading dur --"Snap|Dur|(m)"
  43. col cpu format 90 heading cpu --"C|P|U"
  44. col cap format 9999990.00 heading cap --"***|Total|CPU|Time|(s)"
  45. col dbt format 999990.00 heading dbt --"DB|Time"
  46. col dbc format 99990.00 heading dbc --"DB|CPU"
  47. col bgc format 99990.00 heading bgc --"Bg|CPU"
  48. col rman format 9990.00 heading rman --"RMAN|CPU"
  49. col aas format 990.0 heading aas --"A|A|S"
  50. col totora format 9999990.00 heading totora --"***|Total|Oracle|CPU|(s)"
  51. col busy format 9999990.00 heading busy --"Busy|Time"
  52. col load format 990.00 heading load --"OS|Load"
  53. col totos format 9999990.00 heading totos --"***|Total|OS|CPU|(s)"
  54. col mem format 999990.00 heading mem --"Physical|Memory|(mb)"
  55. col IORs format 9990.000 heading IORs --"IOPs|r"
  56. col IOWs format 9990.000 heading IOWs --"IOPs|w"
  57. col IORedo format 9990.000 heading IORedo --"IOPs|redo"
  58. col IORmbs format 9990.000 heading IORmbs --"IO r|(mb)/s"
  59. col IOWmbs format 9990.000 heading IOWmbs --"IO w|(mb)/s"
  60. col redosizesec format 9990.000 heading redosizesec --"Redo|(mb)/s"
  61. col logons format 990 heading logons --"Sess"
  62. col logone format 990 heading logone --"Sess|End"
  63. col exsraw format 99990.000 heading exsraw --"Exec|raw|delta"
  64. col exs format 9990.000 heading exs --"Exec|/s"
  65. col oracpupct format 990 heading oracpupct --"Oracle|CPU|%"
  66. col rmancpupct format 990 heading rmancpupct --"RMAN|CPU|%"
  67. col oscpupct format 990 heading oscpupct --"OS|CPU|%"
  68. col oscpuusr format 990 heading oscpuusr --"U|S|R|%"
  69. col oscpusys format 990 heading oscpusys --"S|Y|S|%"
  70. col oscpuio format 990 heading oscpuio --"I|O|%"
  71. col phy_reads format 99999990.00 heading phy_reads --"physical|reads"
  72. col log_reads format 99999990.00 heading log_reads --"logical|reads"
  73. 
  74. VARIABLE g_retention NUMBER
  75. DEFINE p_default = 8
  76. DEFINE p_max = 100
  77. SET VERIFY OFF
  78. DECLARE
  79. v_default NUMBER(3) := &p_default;
  80. v_max NUMBER(3) := &p_max;
  81. BEGIN
  82. select
  83. ((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
  84. into :g_retention
  85. from dba_hist_wr_control
  86. where dbid in (select dbid from v$database);
  87. 
  88. if :g_retention > v_default then
  89. :g_retention := v_max;
  90. else
  91. :g_retention := v_default;
  92. end if;
  93. END;
  94. /
  95. 
  96. spool awr_services-tableau-&_instname-&_hostname..csv
  97. select * from
  98. (
  99. select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id,
  100. TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm,
  101. inst,
  102. dur,
  103. service_name,
  104. round(db_time / 1000000, 1) as dbt,
  105. round(db_cpu / 1000000, 1) as dbc,
  106. phy_reads,
  107. log_reads,
  108. aas
  109. from (select
  110. s1.snap_id,
  111. s1.tm,
  112. s1.inst,
  113. s1.dur,
  114. s1.service_name,
  115. sum(decode(s1.stat_name, 'DB time', s1.diff, 0)) db_time,
  116. sum(decode(s1.stat_name, 'DB CPU', s1.diff, 0)) db_cpu,
  117. sum(decode(s1.stat_name, 'physical reads', s1.diff, 0)) phy_reads,
  118. sum(decode(s1.stat_name, 'session logical reads', s1.diff, 0)) log_reads,
  119. round(sum(decode(s1.stat_name, 'DB time', s1.diff, 0))/1000000,1)/60 / s1.dur as aas
  120. from
  121. (select s0.snap_id snap_id,
  122. s0.END_INTERVAL_TIME tm,
  123. s0.instance_number inst,
  124. round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
  125. + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
  126. + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
  127. + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
  128. e.service_name service_name,
  129. e.stat_name stat_name,
  130. e.value - b.value diff
  131. from dba_hist_snapshot s0,
  132. dba_hist_snapshot s1,
  133. dba_hist_service_stat b,
  134. dba_hist_service_stat e
  135. where
  136. s0.dbid = &_dbid -- CHANGE THE DBID HERE!
  137. and s1.dbid = s0.dbid
  138. and b.dbid = s0.dbid
  139. and e.dbid = s0.dbid
  140. --and s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
  141. and s1.instance_number = s0.instance_number
  142. and b.instance_number = s0.instance_number
  143. and e.instance_number = s0.instance_number
  144. and s1.snap_id = s0.snap_id + 1
  145. and b.snap_id = s0.snap_id
  146. and e.snap_id = s0.snap_id + 1
  147. and b.stat_id = e.stat_id
  148. and b.service_name_hash = e.service_name_hash) s1
  149. group by
  150. s1.snap_id, s1.tm, s1.inst, s1.dur, s1.service_name
  151. order by
  152. snap_id asc, aas desc, service_name)
  153. )
  154. WHERE
  155. to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
  156. -- where
  157. -- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
  158. -- AND TO_CHAR(tm,'D') <= 7
  159. -- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
  160. -- AND TO_CHAR(tm,'HH24MI') <= 1800
  161. -- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
  162. -- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
  163. -- snap_id = 338
  164. -- and snap_id >= 335 and snap_id <= 339
  165. -- aas > .5
  166. ;
  167. spool off
  168. host sed -n -i '2,$ p' awr_services-tableau-&_instname-&_hostname..csv
  169. host gzip -v awr_services-tableau-&_instname-&_hostname..csv
  170. host tar -cvf awr_services-tableau-&_instname-&_hostname..tar awr_services-tableau-&_instname-&_hostname..csv.gz
  171. host rm awr_services-tableau-&_instname-&_hostname..csv.gz
  172.