分类：scripts

select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
from x$ksppi x, x$ksppcv y
where x.inst_id=USERENV(‘Instance’)
and y.inst_id=USERENV(‘Instance’)
and x.indx=y.indx
and x.ksppinm like ‘%&par%’;

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

select
to_char(sn.snap_time,’HH24′),
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_disk
from
stats$sysstat oldmem,
stats$sysstat newmem,
stats$sysstat olddsk,
stats$sysstat newdsk,
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,’HH24′);

select
to_char(sn.begin_interval_time,’HH24′),
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_disk
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_sysstat olddsk,
dba_hist_sysstat newdsk,
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,’HH24′);