Oracle-L: Re: buffer cache

From: Jack Silvey <jack_silvey_at_YAHOO.COM>
Date: Tue, 11 Jun 2002 19:03:21 -0800
Message-ID: <F001.0047B642.20020611190321@fatcity.com>

this query will tell you how many blocks are in cache for each segment. multiply by your db_block_size and divide that number by 1048576 to get megabytes.

remember that full table scan blocks get put on the LRU end of the LRU list (pre-816) so they might not be represented fairly by this query.

hth,

jack silvey

select
e.owner||'.'||e.segment_name segment_name,

  sum(cnt)  all_buffers,
  sum(hot)  hot_buffers,
  sum(tch)  touches

from
( select

      min(file#||'.'||dbablk)  fb,
      count(*)  cnt,
      sum(decode(lru_flag, 8, 1, 0))  hot,
      sum(tch)  tch
    from
      sys.x_$bh
    where
      inst_id = userenv('Instance') and
      state in (1, 3)
    group by
      obj,
      class

  ) b,
  sys.apt_extents e
where
  e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1) and
  substr(b.fb, instr(b.fb, '.') + 1) between e.block_id and e.block_id + e.blocks - 1 group by
  e.owner||'.'||e.segment_name
order by
  2
/

BigP <big_planet_2000_at_hotmail.com> wrote:
> Hi Guys ,
> IS there any way I can find what is occupying how
> much of buffer cache . Like .. what table is taking
> most of space etc .
> Thanks ,
> Bp
>

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 11 2002 - 22:03:21 CDT