Re: How much memory is consumed by cache tables?

Find the table's highwater mark. That tells you how many blocks are actually in use
holding data, hence the maximum number
of blocks that needs to be buffered.

Remember that when updating or getting
read-consistent blocks, Oracle may clone blocks in the buffer, and you can get up to 6 versions of any one block in the buffer (a parameter named something like
_max_cr_dba_clone controls this).

Watch out with Oracle 9's automatic
segment space management - you may
find that the first row you insert into a 16 block table (say) goes into the 13th
block, leaving you with a 10 block tablescan to find just one row, and 10 datablocks in the buffer (plus one segment header, plus one first level bitmap plus one second level bitmap).

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 26 March 2002 02:15

Hey Gaja. Good to hear from you!!

It appears small cached tables may occupy more than one buffer. Here's what I did.

1. Got the file_id and block_id range from dba_extents: select file_id, block_id from dba_extents where owner=? and segment_name=?
   /
   
2. Got the buffers from x$bh: select buf#,file#,dbablk from x$BH where file#=65 and dbablk between 325 and 348
   /
   

      BUF# FILE# DBABLK
---------- ---------- ----------

     10212 65 346

It only returned one row so if I'm interpreting this correctly it must only
be using one buffer (16K in my case).

3) To get a list of all the cached tables and the buffers which they occupy
I did this:
select t.owner, t.table_name, b.buf#
from x$bh b, dba_extents e, dba_tables t

where   e.owner = t.owner
and     e.segment_name = t.table_name
and     e.file_id = b.file#
and     b.dbablk between e.block_id and e.block_id + e.blocks - 1
and     t.cache = '    Y'

When I did this I found a few small cached tables that occupied more than
one buffer. Even though the amount of data could easily fit into 16K it took
more because the data was spread into multiple dba_extents.block_id's. Static tables that are cached only take up one buffer but the more dynamic
cached tables often take more because their data gets spread around into
multiple block_id's.

I wonder how O9i handles this? If a buffer corresponds to a block_id then
what happens when you use tablespaces with a different db_block_size? The
theory would be to put cache tables in tablespaces with a smaller block
size.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).