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

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’);

获取数据库缓冲区缓存丢失统计数据

set lines 80;
set pages 999;
column mydate heading ‘Yr. Mo Dy Hr.’ format a16
column c1 heading “execs” format 9,999,999
column c2 heading “Cache Misses|While Executing” format 9,999,999
column c3 heading “Library Cache|Miss Ratio” format 999,99999

break on mydate skip 2;
select
to_char(sn.snap_time,’yyyy-mm-dd HH24′) mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) c3
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where new.snap_id=sn.snap_id
and old.snap_id=new.snap_id-1
and old.namespace=new.namespace
group by to_char(sn.snap_time,’yyyy-mm-dd HH24′);

select
to_char(sn.begin_interval_time,’yyyy-mm-dd HH24′) mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) c3
from
dba_hist_librarycache old,
dba_hist_librarycache new,
dba_hist_snapshot sn
where new.snap_id=sn.snap_id
and old.snap_id=new.snap_id-1
and old.namespace=new.namespace
group by to_char(sn.begin_interval_time,’yyyy-mm-dd HH24′);

查询内存与磁盘排序统计数据

set pages 9999;
column mydate heading ‘Yr. Mo Dy Hr.’ format a16
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .99999

select
to_char(sn.snap_time,’yyyy-mm-dd HH24′) mydate,
newmem.value-oldmem.value sorts_memory,
newdsk.value-olddsk.value sorts_disk,
((newdsk.value-olddsk.value)/(newmem.value-oldmem.value)) ratio
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;

select
to_char(sn.begin_interval_time,’yyyy-mm-dd HH24′) mydate,
newmem.value-oldmem.value sorts_memory,
newdsk.value-olddsk.value sorts_disk,
((newdsk.value-olddsk.value)/(newmem.value-oldmem.value)) ratio
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;

获取热点对象

select tch,
file#,
dbablk,
case
when obj = 4294967295 then
‘rbs/compat segment’
else
(select max(‘(‘ || object_type || ‘) ‘ || owner || ‘.’ ||
object_name) || decode(count(*), 1, ”, ‘ maybe!’)
from dba_objects
where data_object_id = X.OBJ)
end what
from (select tch, file#, dbablk, obj
from x$bh
where state <> 0
order by tch desc) x
where rownum < = 5;

select * from dba_extents z,
(select tch, file#, dbablk, obj
from x$bh
where state <> 0
and rownum < = 5
order by tch desc) y
where z.file_id=y.file# and z.block_id<=y.dbablk and z.block_id+z.blocks-1>=y.dbablk;

select * from dba_extents where file_id = FILE# and block_id < = DBABLK and block_id+blocks-1 >= DBABLK。

Proudly powered by WordPress | Indrajeet by Sus Hill.