http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/
define instance_num="1"
define start_date = "2011_1126_0009"
define end_date = "2011_1127_1900"
define start_hour = "9"
define end_hour = "19"
define date_format = "YYYY_MMDD_HH24MI"
set pagesize 1000
col dur format 999990.00 heading "Snap|Dur|(m)"
col id format 99999 heading "Snap|ID"
col tm format a15 heading "Snap|Start|Time"
col inst format 90 heading "i|n|s|t|#"
col cpu format 90 heading "C|P|U"
col cap format 9999990.00 heading "***|Total|CPU|Time|(s)"
col dbt format 999999990.00 heading "DB|Time"
col dbc format 99990.00 heading "DB|CPU"
col bgc format 99990.00 heading "Bg|CPU"
col rman format 9990.00 heading "RMAN|CPU"
col aas format 90.0 heading "A|A|S"
col totora format 9999990.00 heading "***|Total|Oracle|CPU|(s)"
col busy format 9999990.00 heading "Busy|Time"
col load format 990.00 heading "OS|Load"
col totos format 9999990.00 heading "***|Total|OS|CPU|(s)"
col mem format 999990.00 heading "Physical|Memory|(mb)"
col iors format 9990.000 heading "IOPs|r"
col iows format 9990.000 heading "IOPs|w"
col ioredo format 9990.000 heading "IOPs|redo"
col iormbs format 9990.000 heading "IO r|(mb)/s"
col iowmbs format 9990.000 heading "IO w|(mb)/s"
col redosizesec format 999990.000 heading "Redo|(mb)/s"
col logons format 99990 heading "Sess"
col logone format 990 heading "Sess|End"
col exsraw format 99990.000 heading "Exec|raw|delta"
col exs format 999990.000 heading "Exec|/s"
col oracpupct format 990 heading "Oracle|CPU|%"
col rmancpupct format 990 heading "RMAN|CPU|%"
col oscpupct format 990 heading "OS|CPU|%"
col oscpuusr format 990 heading "U|S|R|%"
col oscpusys format 990 heading "S|Y|S|%"
col oscpuio format 990 heading "I|O|%"
WITH sub_snap
AS (SELECT dbid,
instance_number,
snap_id,
fin end_interval_time,
Round(Extract(DAY FROM fin - debut) * 1440 + Extract(HOUR FROM fin - debut) * 60 + Extract(MINUTE FROM fin - debut) + Extract(SECOND FROM fin - debut) / 60, 2) dur
FROM (SELECT dbid,
instance_number,
snap_id,
end_interval_time fin,
( Lag(end_interval_time, 1) over (PARTITION BY dbid, instance_number ORDER BY snap_id) ) debut
FROM dba_hist_snapshot
WHERE instance_number = &instance_num
AND begin_interval_time >= To_date('&start_date', '&date_format')
-- AND end_interval_time = 7
AND To_number(To_char(end_interval_time, 'HH24')) <= 19
AND To_number(To_char(begin_interval_time, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')) < 6)),
sub_sys_time_model
AS (SELECT dbid,
snap_id,
instance_number,
SUM(db_time) db_time,
SUM(db_cpu) db_cpu,
SUM(bg_cpu) bg_cpu,
SUM(rm_cpu) rm_cpu
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'DB time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_time,
CASE
WHEN stat_name = 'DB CPU' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_cpu,
CASE
WHEN stat_name = 'background cpu time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END bg_cpu,
CASE
WHEN stat_name = 'RMAN cpu time (backup/restore)' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END rm_cpu
FROM dba_hist_snapshot
natural join dba_hist_sys_time_model
WHERE stat_name IN ( 'DB time', 'DB CPU', 'background cpu time', 'RMAN cpu time (backup/restore)' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_sys_stat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(logons_current) logons_current,
SUM(execute_count) execute_count,
SUM(physical_reads) physical_reads,
SUM(physical_writes) physical_writes,
SUM(redo_writes) redo_writes,
SUM(redo_size) redo_size,
SUM(physical_read_io_requests) physical_read_io_requests,
SUM(physical_write_io_requests) physical_write_io_requests
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'logons current' THEN VALUE
ELSE NULL
END logons_current,
CASE
WHEN stat_name = 'execute count' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END execute_count,
CASE
WHEN stat_name = 'physical reads' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_reads,
CASE
WHEN stat_name = 'physical writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_writes,
CASE
WHEN stat_name = 'redo writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_writes,
CASE
WHEN stat_name = 'redo size' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_size,
CASE
WHEN stat_name = 'physical read IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_read_io_requests,
CASE
WHEN stat_name = 'physical write IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_write_io_requests
FROM dba_hist_snapshot
natural join dba_hist_sysstat
WHERE stat_name IN ( 'logons current', 'execute count', 'physical reads', 'physical writes', 'redo writes', 'redo size', 'physical read IO requests', 'physical write IO requests' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_osstat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(load) load,
SUM(busy_time) busy_time,
SUM(user_time) user_time,
SUM(sys_time) sys_time,
SUM(io_wait_time) io_wait_time,
MAX(cpu) cpu,
MAX(phy_mem) phy_mem,
SUM(avg_sys_time),
SUM(avg_busy_time),
SUM(avg_iowait_time),
SUM(avg_idle_time),
SUM(avg_user_time)
FROM (SELECT e.snap_id,
e.dbid,
e.instance_number,
CASE
WHEN e.stat_name = 'LOAD' THEN e.VALUE
ELSE 0
END load,
CASE
WHEN e.stat_name = 'NUM_CPUS' THEN e.VALUE
ELSE 0
END cpu,
CASE
WHEN e.stat_name = 'AVG_BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_busy_time,
CASE
WHEN e.stat_name = 'AVG_SYS_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_sys_time,
CASE
WHEN e.stat_name = 'AVG_USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_user_time,
CASE
WHEN e.stat_name = 'AVG_IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_iowait_time,
CASE
WHEN e.stat_name = 'AVG_IDLE_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_idle_time,
CASE
WHEN e.stat_name = 'BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END busy_time,
CASE
WHEN e.stat_name = 'USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END user_time,
CASE
WHEN e.stat_name = 'SYSR_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END sys_time,
CASE
WHEN e.stat_name = 'IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END io_wait_time,
CASE
WHEN e.stat_name = 'PHYSICAL_MEMORY_BYTES' THEN e.VALUE
ELSE 0
END phy_mem
FROM dba_hist_osstat e,
dba_hist_snapshot s
WHERE s.snap_id = e.snap_id
AND e.instance_number = s.instance_number
AND e.dbid = s.dbid
AND e.stat_name IN ( 'LOAD', 'AVG_BUSY_TIME', 'AVG_IOWAIT_TIME', 'AVG_USER_TIME',
'AVG_SYS_TIME', 'AVG_IDLE_TIME', 'NUM_CPUS', 'BUSY_TIME',
'PHYSICAL_MEMORY_BYTES', 'USER_TIME', 'SYS_TIME', 'IOWAIT_TIME' ))
GROUP BY dbid,
snap_id,
instance_number)
SELECT snap_id id,
To_char(end_interval_time, 'YYYYMMDD HH24MI') tm,
instance_number inst,
dur,
cpu,
( dur * cpu * 60 ) cap,
( db_time / 1000000 ) dbt,
( db_cpu / 1000000 ) dbc,
( bg_cpu / 1000000 ) bgc,
( rm_cpu / 1000000 ) rman,
( db_time / 1000000 / 60 ) / dur aas,
Round(bg_cpu / 1000000 + db_cpu / 1000000, 2) totora,
Round(load, 2) AS load,
( busy_time ) / 100 AS totos,
( phy_mem / 1024 / 1024 ) AS mem,
physical_reads / ( dur * 60 ) AS iors,
physical_writes / ( dur * 60 ) AS iows,
redo_writes / ( dur * 60 ) AS ioredo,
physical_read_io_requests / ( dur * 60 ) AS iormbs,
- physical_write_IO_requests/(dur*60) as IOWmbs,
- redo_size/(dur*60) as redosizesec,
- logons_current as logons,
- execute_count as exs,
( ( Round(( ( db_cpu ) / 1000000 ) + ( ( bg_cpu ) / 1000000 ), 2) ) / ( dur * 60 * cpu ) ) * 100 AS oracpupct,
( ( Round(Decode(rm_cpu, NULL, 'null',rm_cpu / 1000000), 2) ) / ( dur * 60 * cpu ) ) * 100 AS rmancpupct,
( ( ( busy_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpupct,
( ( user_time / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpuusr,
( ( ( sys_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpusys,
( ( ( io_wait_time ) / 100 ) / ( dur * cpu * 60 ) ) * 100 AS oscpuio
FROM sub_snap
natural join sub_sys_time_model
natural join sub_sys_stat
natural join sub_osstat
ORDER BY id ASC
/
============
@mem1
old 13: WHERE instance_number = &instance_num
new 13: WHERE instance_number = 1
old 14: AND begin_interval_time >= To_date('&start_date', '&date_format')
new 14: AND begin_interval_time >= To_date('2011_1126_0009', 'YYYY_MMDD_HH24MI')
i *** *** ***
n Total Total Total
Snap s Snap C CPU A Oracle OS Physical
Snap Start t Dur P Time DB DB Bg RMAN A CPU OS CPU Memory IOPs IOPs IOPs IO r IO w
ID Time # (m) U (s) Time CPU CPU CPU S (s) Load (s) (mb) r w redo (mb)/s (mb)/s
------ --------------- --- ---------- --- ----------- ------------- --------- --------- -------- ----- ----------- ------- ----------- ---------- --------- --------- --------- --------- ---------
U S
Oracle RMAN OS S Y I
Redo Exec CPU CPU CPU R S O
(mb)/s Sess /s % % % % % %
----------- ------ ----------- ------ ---- ---- ---- ---- ----
5864 20111127 0100 1 1 2.13 1.99 20.20 0.00 22.19 0.00 276.09 2008.28
-33 -17736.000
5865 20111127 0200 1 60.03 1 3601.80 1.81 1.88 32.90 0.00 0.0 34.79 0.00 253.48 2008.28 0.017 0.213 0.042 0.016 -0.156
-476.717 -31 -3444.000 1 0 7 5 0 1
5866 20111127 0300 1 60.03 1 3601.80 1.89 2.08 5.75 0.00 0.0 7.82 0.11 251.37 2008.28 0.015 0.205 0.041 0.015 -0.150
-405.729 -31 -3048.000 0 0 7 5 0 1
5867 20111127 0400 1 60.03 1 3601.80 1.79 1.93 5.69 0.00 0.0 7.61 0.00 250.89 2008.28 0.002 0.200 0.039 0.002 -0.142
-373.793 -31 -2878.000 0 0 7 5 0 1
5868 20111127 0500 1 60.03 1 3601.80 1.65 1.84 5.61 0.00 0.0 7.44 0.00 251.16 2008.28 0.001 0.182 0.040 0.001 -0.134
-382.797 -31 -2803.000 0 0 7 5 0 1
5869 20111127 0600 1 60.03 1 3601.80 114.08 31.84 6.16 0.00 0.0 38.01 2.07 281.67 2008.28 1.533 0.575 0.095 1.299 -0.271
-2229.629 -35 -45405.000 1 0 8 6 0 1
5870 20111127 0700 1 60.03 1 3601.80 2922.71 2370.90 7.13 0.00 0.8 2378.03 0.32 1435.30 2008.28 6.731 1.596 0.176 2.565 -0.871
-8896.886 -32 -40023.000 66 0 40 38 0 1
5871 20111127 0800 1 60.03 1 3601.80 4.31 2.81 8.11 0.00 0.0 10.92 0.31 262.49 2008.28 0.187 0.385 0.044 0.178 -0.272
-2397.647 -33 -7563.000 0 0 7 5 0 2
5872 20111127 0900 1 60.04 1 3602.40 2.18 1.78 5.87 0.00 0.0 7.64 0.03 255.82 2008.28 0.023 0.231 0.041 0.023 -0.168
-592.602 -33 -3721.000 0 0 7 5 0 2
5873 20111127 1000 1 60.03 1 3601.80 40.03 36.49 6.03 0.00 0.0 42.53 0.05 278.79 2008.28 0.033 0.201 0.041 0.032 -0.152
-532.957 -33 -4260.000 1 0 8 6 0 1
5874 20111127 1100 1 60.03 1 3601.80 197.02 37.29 6.80 0.00 0.1 44.09 0.00 262.95 2008.28 2.206 0.676 0.085 1.624 -0.439
-3947.046 -33 -47552.000 1 0 7 5 0 2
5875 20111127 1200 1 60.03 1 3601.80 1.61 1.53 5.90 0.00 0.0 7.42 0.06 247.03 2008.28 0.023 0.234 0.041 0.022 -0.174
-568.728 -33 -3356.000 0 0 7 5 0 1
5876 20111127 1300 1 60.03 1 3601.80 1.72 1.50 6.07 0.00 0.0 7.56 0.15 246.49 2008.28 0.630 0.203 0.041 0.630 -0.149
-438.576 -32 -3237.000 0 0 7 5 0 1
5877 20111127 1400 1 60.03 1 3601.80 1.64 1.54 5.81 0.00 0.0 7.35 0.09 243.98 2008.28 0.020 0.193 0.040 0.020 -0.144
-411.757 -30 -2893.000 0 0 7 5 0 1
5878 20111127 1500 1 60.03 1 3601.80 134.56 50.66 6.04 0.00 0.0 56.71 0.35 271.03 2008.28 1.312 0.376 0.063 1.217 -0.265
-1219.445 -30 -39622.000 2 0 8 6 0 2
5879 20111127 1600 1 60.03 1 3601.80 1.62 1.52 5.81 0.00 0.0 7.33 0.12 246.28 2008.28 0.009 0.205 0.041 0.009 -0.156
-404.427 -30 -3032.000 0 0 7 5 0 1
5880 20111127 1700 1 60.03 1 3601.80 1.57 1.52 5.93 0.00 0.0 7.44 0.21 246.90 2008.28 0.009 0.189 0.040 0.009 -0.139
-429.958 -30 -2939.000 0 0 7 5 0 1
5881 20111127 1800 1 60.03 1 3601.80 1.60 1.51 5.71 0.00 0.0 7.22 0.21 249.48 2008.28 0.005 0.194 0.040 0.005 -0.142
-396.104 -30 -2872.000 0 0 7 5 0 1
5882 20111127 1900 1 59.03 1 3541.80 40.16 24.87 6.24 0.00 0.0 31.11 0.18 261.76 2008.28 1.195 0.385 0.070 1.190 -0.262
-1435.691 -33 -40145.000 1 0 7 5 0 2
5887 20111128 0000 1 300.15 1 18009.00 1.44 1.48 6.01 0.00 0.0 7.50 0.01 246.41 2008.28 0.003 0.039 0.008 0.002 -0.030
-82.805 -32 -3106.000 0 0 1 1 0 0
5888 20111128 0100 1 60.04 1 3602.40 3.31 1.63 20.80 0.00 0.0 22.43 0.09 263.24 2008.28 3.520 2.446 0.151 2.175 -1.980
-15881.552 -33 -17739.000 1 0 7 5 0 4
5889 20111128 0200 1 60.03 1 3601.80 1.47 1.46 32.32 0.00 0.0 33.78 0.28 247.52 2008.28 0.024 0.222 0.042 0.023 -0.162
-467.332 -31 -3594.000 1 0 7 5 0 1
5890 20111128 0300 1 60.03 1 3601.80 1.49 1.56 5.68 0.00 0.0 7.25 0.07 242.56 2008.28 0.007 0.206 0.040 0.007 -0.148
-403.055 -31 -2946.000 0 0 7 5 0 1
5891 20111128 0400 1 60.03 1 3601.80 1.35 1.46 5.56 0.00 0.0 7.02 0.00 250.56 2008.28 0.003 0.195 0.039 0.003 -0.139
-393.477 -31 -2820.000 0 0 7 5 0 1
5892 20111128 0500 1 60.03 1 3601.80 1.50 1.49 5.64 0.00 0.0 7.14 0.02 251.27 2008.28 0.002 0.189 0.041 0.002 -0.140
-377.975 -31 -2801.000 0 0 7 5 0 1
5893 20111128 0600 1 60.03 1 3601.80 1.40 1.46 5.55 0.00 0.0 7.02 0.13 242.30 2008.28 0.002 0.186 0.039 0.002 -0.138
-410.680 -31 -2802.000 0 0 7 5 0 1
5894 20111128 0700 1 60.03 1 3601.80 1.50 1.52 5.54 0.00 0.0 7.06 0.27 242.49 2008.28 0.002 0.191 0.041 0.002 -0.148
-394.106 -31 -2851.000 0 0 7 5 0 1
5895 20111128 0800 1 60.04 1 3602.40 1.56 1.58 5.70 0.00 0.0 7.28 0.14 242.87 2008.28 0.005 0.185 0.041 0.005 -0.141
-406.256 -32 -2813.000 0 0 7 5 0 1
5896 20111128 0900 1 60.03 1 3601.80 1.36 1.39 5.56 0.00 0.0 6.95 0.05 242.45 2008.28 0.002 0.184 0.039 0.002 -0.136
-409.270 -31 -2783.000 0 0 7 5 0 1
5897 20111128 1000 1 60.03 1 3601.80 2.34 1.51 5.76 0.00 0.0 7.27 0.30 332.63 2008.28 0.003 0.188 0.039 0.003 -0.146
-391.966 -31 -2797.000 0 0 9 7 0 3
5898 20111128 1100 1 60.13 1 3607.80 107.21 9.39 6.31 0.00 0.0 15.70 0.30 1137.09 2008.28 1.731 0.196 0.053 1.730 -0.151
-556.871 -31 -3629.000 0 0 32 27 0 10
31 rows selected.
No comments:
Post a Comment