获取数据库每天的排序统计数据脚本

set pages 9999;
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .9999

select
to_char(sn.snap_time,’day’) DAY,
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_dsk
from
stats$sysstat oldmem,
stats$sysstat newmem,
stats$sysstat newdsk,
stats$sysstat olddsk,
stats$snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.name=’sorts (memory)’
and newmem.name=’sorts (memory)’
and olddsk.name=’sorts (disk)’
and newdsk.name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.snap_time,’day’);

select
to_char(sn.begin_interval_time,’day’) DAY,
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_dsk
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_sysstat newdsk,
dba_hist_sysstat olddsk,
dba_hist_snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.stat_name=’sorts (memory)’
and newmem.stat_name=’sorts (memory)’
and olddsk.stat_name=’sorts (disk)’
and newdsk.stat_name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.begin_interval_time,’day’);

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.