Check IO Scripts
from Metalink
How To Calculate IOPS of an Oracle Database
V$SYSMETRIC – last 15 and 60 seconds
V$SYSMETRIC_SUMMARY – values last hour (last snapshot) like avg, max, min etc
V$SYSMETRIC_HISTORY – last hour for 1 minute, last 3 mintes for 15 second deltas
DBA_HIST_SYSMETRIC_SUMMARY – hour summaries for last week.
break on day
set pagesize 1000 linesize 5000
col day for a8
col metric_name for 30
select
to_char(begin_time,'DD-Mon') Day,
A.METRIC_NAME,
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'00',maxval,0)),'999999999999999') "00",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'01',maxval,0)),'999999999999999') "01",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'02',maxval,0)),'999999999999999') "02",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'03',maxval,0)),'999999999999999') "03",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'04',maxval,0)),'999999999999999') "04",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'05',maxval,0)),'999999999999999') "05",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'06',maxval,0)),'999999999999999') "06",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'07',maxval,0)),'999999999999999') "07",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'08',maxval,0)),'999999999999999') "08",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'09',maxval,0)),'999999999999999') "09",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'10',maxval,0)),'999999999999999') "10",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'11',maxval,0)),'999999999999999') "11",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'12',maxval,0)),'999999999999999') "12",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'13',maxval,0)),'999999999999999') "13",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'14',maxval,0)),'999999999999999') "14",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'15',maxval,0)),'999999999999999') "15",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'16',maxval,0)),'999999999999999') "16",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'17',maxval,0)),'999999999999999') "17",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'18',maxval,0)),'999999999999999') "18",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'19',maxval,0)),'999999999999999') "19",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'20',maxval,0)),'999999999999999') "20",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'21',maxval,0)),'999999999999999') "21",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'22',maxval,0)),'999999999999999') "22",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'23',maxval,0)),'999999999999999') "23"
from dba_hist_sysmetric_summary A
where
A.METRIC_NAME in
('Host CPU Utilization (%)',
'Average Active Sessions',
'Session Count',
'SQL Service Response Time',
'User Transaction Per Sec',
'Temp Space Used',
'Total PGA Allocated'
)
and BEGIN_TIME > trunc(sysdate) - 7
group by A.METRIC_NAME, to_char(begin_time,'DD-Mon')
order by to_char(begin_time,'DD-Mon'),a.metric_name ;
08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
-------- ---------------------------------------------------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
19-Jan Average Active Sessions 37 32 32 32 33 32 32 32 32 32 32 32 32 32 32 32 31 32 32 32 32 38 34 32
set linesize 500
col day for a11
select
to_char(begin_time,'DD-MON-YY') Day,
round(max(decode(to_char(begin_time,'HH24'),'00',maxval,NULL)),2) "00",
round(max(decode(to_char(begin_time,'HH24'),'01',maxval,NULL)),2) "01",
round(max(decode(to_char(begin_time,'HH24'),'02',maxval,NULL)),2) "02",
round(max(decode(to_char(begin_time,'HH24'),'03',maxval,NULL)),2) "03",
round(max(decode(to_char(begin_time,'HH24'),'04',maxval,NULL)),2) "04",
round(max(decode(to_char(begin_time,'HH24'),'05',maxval,NULL)),2) "05",
round(max(decode(to_char(begin_time,'HH24'),'06',maxval,NULL)),2) "06",
round(max(decode(to_char(begin_time,'HH24'),'07',maxval,NULL)),2) "07",
round(max(decode(to_char(begin_time,'HH24'),'08',maxval,NULL)),2) "08",
round(max(decode(to_char(begin_time,'HH24'),'09',maxval,NULL)),2) "09",
round(max(decode(to_char(begin_time,'HH24'),'10',maxval,NULL)),2) "10",
round(max(decode(to_char(begin_time,'HH24'),'11',maxval,NULL)),2) "11",
round(max(decode(to_char(begin_time,'HH24'),'12',maxval,NULL)),2) "12",
round(max(decode(to_char(begin_time,'HH24'),'13',maxval,NULL)),2) "13",
round(max(decode(to_char(begin_time,'HH24'),'14',maxval,NULL)),2) "14",
round(max(decode(to_char(begin_time,'HH24'),'15',maxval,NULL)),2) "15",
round(max(decode(to_char(begin_time,'HH24'),'16',maxval,NULL)),2) "16",
round(max(decode(to_char(begin_time,'HH24'),'17',maxval,NULL)),2) "17",
round(max(decode(to_char(begin_time,'HH24'),'18',maxval,NULL)),2) "18",
round(max(decode(to_char(begin_time,'HH24'),'19',maxval,NULL)),2) "19",
round(max(decode(to_char(begin_time,'HH24'),'20',maxval,NULL)),2) "20",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "21",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "22",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "23"
from dba_hist_sysmetric_SUMMARY A where BEGIN_TIME > sysdate - 7
and A.METRIC_NAME in('Average Active Sessions') group by to_char(begin_time,'DD-MON-YY')
/
set lines 500 pages 50000
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col Phys_Read_Total_Bps for 999999999999
col Phys_Write_Total_Bps for 999999999999
col Redo_Bytes_per_sec for 999999999999
col Phys_Read_IOPS for 999999999999
col Phys_write_IOPS for 999999999999
col Phys_redo_IOPS for 999999999999
col OS_LOad for 999999999999
col DB_CPU_Usage_per_sec for 999999999999
col Host_CPU_util for 999999999999
col Network_bytes_per_sec for 999999999999
col Phys_IO_Tot_MBps for 999999999999
col Phys_IOPS_Tot for 999999999999
spool io_max_checkup.log
select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then maxval end) Phys_Read_Tot_Bps,
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then maxval end) Phys_Write_Tot_Bps,
sum(case metric_name when 'Redo Generated Per Sec' then maxval end) Redo_Bytes_per_sec,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then maxval end) Phys_Read_IOPS,
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then maxval end) Phys_write_IOPS,
sum(case metric_name when 'Redo Writes Per Sec' then maxval end) Phys_redo_IOPS,
sum(case metric_name when 'Current OS Load' then maxval end) OS_LOad,
sum(case metric_name when 'CPU Usage Per Sec' then maxval end) DB_CPU_Usage_per_sec,
sum(case metric_name when 'Host CPU Utilization (%)' then maxval end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
sum(case metric_name when 'Network Traffic Volume Per Sec' then maxval end) Network_bytes_per_sec,
snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;
select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then maxval end)/1024/1024 +
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then maxval end)/1024/1024 +
sum(case metric_name when 'Redo Generated Per Sec' then maxval end)/1024/1024 Phys_IO_Tot_MBps,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then maxval end) +
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then maxval end) +
sum(case metric_name when 'Redo Writes Per Sec' then maxval end) Phys_IOPS_Tot,
sum(case metric_name when 'Current OS Load' then maxval end) OS_LOad,
sum(case metric_name when 'CPU Usage Per Sec' then maxval end) DB_CPU_Usage_per_sec,
sum(case metric_name when 'Host CPU Utilization (%)' then maxval end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
sum(case metric_name when 'Network Traffic Volume Per Sec' then maxval end) Network_bytes_per_sec,
snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;
spool off
spool io_maxtot_summary.log
No comments:
Post a Comment