个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2010-04-27 22:27:05
oracle support note id 422414.1
select begin_time,end_time,
trunc(physical_read_total_bps)physical_read_total_bps,
trunc(physical_write_total_bps,1) physical_write_total_bps ,
trunc(redo_bytes_per_sec ,1) redo_bytes_per_sec,
trunc(physical_read_iops ,1) physical_read_iops,
trunc(physical_write_iops ,1)physical_write_iops ,
trunc( physical_redo_iops,1)physical_redo_iops ,
trunc(os_load ,1) os_load,
trunc(db_cpu_usage_per_sec ,1)db_cpu_usage_per_sec ,
trunc( host_cpu_util,1) host_cpu_util,
trunc( network_bytes_per_sec,1)network_bytes_per_sec ,
snap_id
from(
select min(begin_time) begin_time, max(end_time) end_time,
sum(case metric_name when 'physical read total bytes per sec' then average end) physical_read_total_bps,
sum(case metric_name when 'physical write total bytes per sec' then average end) physical_write_total_bps,
sum(case metric_name when 'redo generated per sec' then average end) redo_bytes_per_sec,
sum(case metric_name when 'physical read total io requests per sec' then average end) physical_read_iops,
sum(case metric_name when 'physical write total io requests per sec' then average end) physical_write_iops,
sum(case metric_name when 'redo writes per sec' then average end) physical_redo_iops,
sum(case metric_name when 'current os load' then average end) os_load,
sum(case metric_name when 'cpu usage per sec' then average end) db_cpu_usage_per_sec,
sum(case metric_name when 'host cpu utilization (%)' then average end) host_cpu_util, --note 100% = 1 loaded rac node
sum(case metric_name when 'network traffic volume per sec' then average end) network_bytes_per_sec,
snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id);
trunc(physical_read_total_bps)physical_read_total_bps,
trunc(physical_write_total_bps,1) physical_write_total_bps ,
trunc(redo_bytes_per_sec ,1) redo_bytes_per_sec,
trunc(physical_read_iops ,1) physical_read_iops,
trunc(physical_write_iops ,1)physical_write_iops ,
trunc( physical_redo_iops,1)physical_redo_iops ,
trunc(os_load ,1) os_load,
trunc(db_cpu_usage_per_sec ,1)db_cpu_usage_per_sec ,
trunc( host_cpu_util,1) host_cpu_util,
trunc( network_bytes_per_sec,1)network_bytes_per_sec ,
snap_id
from(
select min(begin_time) begin_time, max(end_time) end_time,
sum(case metric_name when 'physical read total bytes per sec' then average end) physical_read_total_bps,
sum(case metric_name when 'physical write total bytes per sec' then average end) physical_write_total_bps,
sum(case metric_name when 'redo generated per sec' then average end) redo_bytes_per_sec,
sum(case metric_name when 'physical read total io requests per sec' then average end) physical_read_iops,
sum(case metric_name when 'physical write total io requests per sec' then average end) physical_write_iops,
sum(case metric_name when 'redo writes per sec' then average end) physical_redo_iops,
sum(case metric_name when 'current os load' then average end) os_load,
sum(case metric_name when 'cpu usage per sec' then average end) db_cpu_usage_per_sec,
sum(case metric_name when 'host cpu utilization (%)' then average end) host_cpu_util, --note 100% = 1 loaded rac node
sum(case metric_name when 'network traffic volume per sec' then average end) network_bytes_per_sec,
snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id);
汇总的
select begin_time ,end_time, trunc(phys_io_tot_mbps ,1) phys_io_tot_mbps,
trunc( phys_iops_tot,1)phys_iops_tot ,
trunc(os_load ,1)os_load ,
trunc( db_cpu_usage_per_sec,1)db_cpu_usage_per_sec ,
trunc( host_cpu_util,1) host_cpu_util,
trunc(network_bytes_per_sec ,1)network_bytes_per_sec ,snap_id
from (
select min(begin_time)begin_time, max(end_time)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);
trunc( phys_iops_tot,1)phys_iops_tot ,
trunc(os_load ,1)os_load ,
trunc( db_cpu_usage_per_sec,1)db_cpu_usage_per_sec ,
trunc( host_cpu_util,1) host_cpu_util,
trunc(network_bytes_per_sec ,1)network_bytes_per_sec ,snap_id
from (
select min(begin_time)begin_time, max(end_time)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);
给主人留下些什么吧！~~