"Forró" adattárház táblák azonosítása

Egy adattárház vizsgálata közben feltettem magamnak a kérdést, hogy vajon melyek a felhasználók által leggyakrabban használt táblák. Erre a kérdésre, viszonylag könnyen választ lehet kapni az alábbi lekérdezéssel:

select
  t.object_name, s.statistic_name, sum(s.value) value
from
  dba_objects t,
  v$segstat s
where t.object_id = s.OBJ#
  and s.value <> 0
  and s.statistic_name = 'logical reads'
group by t.object_name, s.statistic_name
order by sum(s.value) desc
;

Adattárháznál szinte mindig az IO a szűk keresztmetszet, ezért célszerű megnézni, hogy mely táblák generálják a fizikai olvasásokat:

select
  t.object_name, s.statistic_name, sum(s.value) value
from
  dba_objects t,
  v$segstat s
where t.object_id = s.OBJ#
  and s.value <> 0
  and s.statistic_name = 'physical reads'
group by t.object_name, s.statistic_name
order by sum(s.value) desc
;

A fizikai olvasásokat vizsgálva kíváncsi lettem, hogy vajon milyen objektumok vannak bent az Oracle cahce - ben? Ezt az alábbi lekérdezéssel néztem meg.

select
  o.owner,
  o.object_name,
  b.block_size object_block_size,
  count(*) num_of_blocks_in_cache,
  min(s.segment_blocks) total_num_of_blocks,
  round(count(*) / min(s.segment_blocks) * 100) as percentage_in_cache,
  round(block_size * count(*) /1024/1024) as object_cace_size_mb,
  round(sum(block_size * count(*)) over ( partition by block_size) /1024/1024) as full_cace_size_mb
from
  v$bh b,
  dba_objects o,
  v$tablespace t,
  dba_tablespaces b,
  (
    select
      t.owner, t.segment_name, sum(blocks) segment_blocks
    from dba_segments t
    group by t.owner, t.segment_name
  ) s
where b.objd = o.object_id
  and b.TS# = t.TS#
  and t.name = b.tablespace_name
  and s.owner = o.owner
  and s.segment_name = o.object_name
group by o.owner, o.object_name, b.block_size
order by count(*) desc
;

A fenti információkat feldolgozva, a cache megfelelő méretezésével; cache hintek, cache opciók beállításával lehet csökkenteni a rendszer IO terhelését, azaz növelni a rendszer összteljesítményét.