| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How much memory is consumed by cache tables?
Hey Gaja. Good to hear from you!!
It appears small cached tables may occupy more than one buffer. Here's what I did.
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.
"Curiouser and curiouser..."
Steve Orr
-----Original Message-----
Sent: Monday, March 25, 2002 3:44 PM
To: Multiple recipients of list ORACLE-L
Steve,
Just curious whether you have already looked at X$BH and V$CACHE? Do these two provide the information that you are looking for? If you find it, please post your findings, so that we all can benefit from it. If not, we just got to keep on looking....;-)
Cheers,
Gaja
--- "Orr, Steve" <sorr_at_rightnow.com> wrote:
> How much memory is consumed by cache tables? For
> example, if you have a
> small amount of data in a lookup table that's
> locally managed with 128K
> uniform extents, does it consume 128K of memory even
> if the data is only in
> one 8k DB block? (If that's the case then it would
> be better to assign it to
> a keep buffer pool.)
>
> Steve Orr
> --
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: oraperfman_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr_at_rightnow.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 Mon Mar 25 2002 - 19:33:18 CST
 |
 |