1. -- awr_cpuwl.sql
  2. -- AWR CPU Workload Report
  3. -- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
  4. -- http://karlarao.wordpress.com
  5. --
  6. --
  7. 
  8. set feedback off pages 50000 term off head on und off trimspool on echo off lines 4000 colsep ',' arraysize 5000 verify off
  9. 
  10. COL name NEW_V _instname NOPRINT
  11. select lower(instance_name) name from v$instance;
  12. COL name NEW_V _hostname NOPRINT
  13. select lower(host_name) name from v$instance;
  14. COL ecr_dbid NEW_V ecr_dbid;
  15. SELECT 'get_dbid', TO_CHAR(dbid) ecr_dbid FROM v$database;
  16. COL ecr_min_snap_id NEW_V ecr_min_snap_id;
  17. SELECT 'get_min_snap_id', TO_CHAR(MIN(snap_id)) ecr_min_snap_id
  18. FROM dba_hist_snapshot WHERE dbid = &&ecr_dbid.
  19. and to_date(to_char(END_INTERVAL_TIME,'MM/DD/YY HH24:MI:SS'),'MM/DD/YY HH24:MI:SS') > sysdate - 100;
  20. 
  21. spool awr_cpuwl-tableau-&_instname-&_hostname..csv
  22. WITH
  23. cpuwl AS (
  24. SELECT /*+ MATERIALIZE NO_MERGE */
  25. instance_number,
  26. snap_id,
  27. SUM(CASE WHEN stat_name = 'BUSY_TIME' THEN value ELSE 0 END) busy_time,
  28. SUM(CASE WHEN stat_name = 'SYS_TIME' THEN value ELSE 0 END) sys_time,
  29. SUM(CASE WHEN stat_name = 'IOWAIT_TIME' THEN value ELSE 0 END) io_wait,
  30. SUM(CASE WHEN stat_name = 'RSRC_MGR_CPU_WAIT_TIME' THEN value ELSE 0 END) rsrcmgr,
  31. SUM(CASE WHEN stat_name = 'LOAD' THEN value ELSE 0 END) loadavg,
  32. SUM(CASE WHEN stat_name = 'NUM_CPUS' THEN value ELSE 0 END) cpu
  33. FROM dba_hist_osstat
  34. WHERE snap_id >= &&ecr_min_snap_id.
  35. AND dbid = &&ecr_dbid.
  36. AND stat_name IN
  37. ('BUSY_TIME','SYS_TIME','IOWAIT_TIME','RSRC_MGR_CPU_WAIT_TIME','LOAD','NUM_CPUS')
  38. GROUP BY
  39. instance_number,
  40. snap_id
  41. )
  42. SELECT /*+ MATERIALIZE NO_MERGE */
  43. trim('&_instname') instname,
  44. trim('&&ecr_dbid.') db_id,
  45. trim('&_hostname') hostname,
  46. s0.snap_id id,
  47. TO_CHAR(s0.END_INTERVAL_TIME,'MM/DD/YY HH24:MI:SS') tm,
  48. s0.instance_number inst,
  49. round(((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)/60,2) dur,
  50. h1.cpu AS cpu,
  51. round(h1.loadavg,2) AS loadavg,
  52. ((((h1.busy_time - h0.busy_time)+(h1.rsrcmgr - h0.rsrcmgr))/100) / (((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)*h1.cpu) )*h1.cpu as aas_cpu,
  53. (((h1.rsrcmgr - h0.rsrcmgr)/100) / (((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)*h1.cpu) )*100 as rsrcmgrpct,
  54. (((h1.busy_time - h0.busy_time)/100) / (((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)*h1.cpu) )*100 as oscpupct,
  55. (((h1.sys_time - h0.sys_time)/100) / (((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)*h1.cpu) )*100 as oscpusys,
  56. (((h1.io_wait - h0.io_wait)/100) / (((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 86400)*h1.cpu) )*100 as oscpuio
  57. FROM cpuwl h0,
  58. dba_hist_snapshot s0,
  59. cpuwl h1,
  60. dba_hist_snapshot s1
  61. WHERE s0.snap_id >= &&ecr_min_snap_id.
  62. AND s0.dbid = &&ecr_dbid.
  63. AND s0.snap_id = h0.snap_id
  64. AND s0.instance_number = h0.instance_number
  65. AND h1.instance_number = h0.instance_number
  66. AND h1.snap_id = h0.snap_id + 1
  67. AND s1.snap_id >= &&ecr_min_snap_id.
  68. AND s1.dbid = &&ecr_dbid.
  69. AND s1.snap_id = h1.snap_id
  70. AND s1.instance_number = h1.instance_number
  71. AND s1.snap_id = s0.snap_id + 1
  72. AND s1.startup_time = s0.startup_time
  73. /
  74. spool off
  75. host sed -n -i '2,$ p' awr_cpuwl-tableau-&_instname-&_hostname..csv
  76. host gzip -v awr_cpuwl-tableau-&_instname-&_hostname..csv
  77. host tar -cvf awr_cpuwl-tableau-&_instname-&_hostname..tar awr_cpuwl-tableau-&_instname-&_hostname..csv.gz
  78. host rm awr_cpuwl-tableau-&_instname-&_hostname..csv.gz
  79.