I 've figured out how to do this in 8i, but in 9i, I have not succeeded =
in writing such a query. I spent some time running in circles a few =
weeks ago, and never did get anywhere.
For 8i, you can join x$bh (buffer headers) to x$kcbwds (working data =
sets, I think), to x$kcbwbpd (buffer pool definitions) and see which =
buffer is in which pool. However, in 9i, things are a bit different, =
and I 've not yet figured out exactly how. If anyone has or can come up =
with a working version of this script for 9i, I 'd be very interested in =
seeing it.
Rem show_cache_8i.sql
Rem See what 's in the buffer cache
Rem Original Author, Thirunavukarasu Pandian
Rem show_cache.sql
Rem See what 's in the buffer cache
Rem Original Author, Thirunavukarasu Pandian
Rem Modified by Mark J. Bobak on 09/14/2003
Rem re-formatted, corrected script to join
Rem v$bh.objd =3D dba_objects.data_object_id
Rem Re-written by Mark J. Bobak on 10/21/2003
Rem to use X$ and base dictionary for performance reasons
Rem
break on report
compute sum of blocks_currently_buffered on report
select /*+ ordered use_merge(ds) use_hash(bh) use_hash(o) */
bp.bp_name buffer_pool,
o.name,
count(*) blocks_currently_buffered
from x$kcbwbpd bp,
x$kcbwds ds,
x$bh bh,
sys.obj$ o
where bp.bp_id > 0
and bp.bp_size > 0
and ds.set_id between bp.bp_lo_sid and bp.bp_hi_sid
and bh.buf# between ds.start_buf# and ds.end_buf#
and ds.addr =3D bh.set_ds
and bh.obj=3Do.dataobj#
group by o.name, bp.bp_name
order by 1,3 desc
/
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc "
-- --Original Message-- --
From: oracle-l-bounce@(protected) =
[mailto:oracle-l-bounce@(protected)] On Behalf Of Juan Carlos Reyes =
Pacheco
Sent: Wednesday, July 21, 2004 1:22 PM
To: oracle-l@(protected)
Subject: Re: recycle and keep buffer in other tablespace block sizes
Hi Jurijs please
How do you know in which cache(default,keep,recycle)l is what is in the
cache
I tested and I don 't find a way to know where to find that information.
SQL > CREATE TABLESPACE TBL_2K DATAFILE 'D:\TEST2K '
2 SIZE 20M
3 REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED
4 SEGMENT SPACE MANAGEMENT AUTO
5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K BLOCKSIZE 2K;
Tablespace creado.
SQL > CREATE TABLE CTB.TEST2K ( TEST NUMBER )
2 TABLESPACE TBL_2K
3 STORAGE ( BUFFER_POOL KEEP) CACHE
4 /
Tabla creada.
SQL > INSERT INTO CTB.TEST2K SELECT ROWNUM FROM DBA_OBJECTS;
29576 filas creadas.
SQL > SELECT COUNT(*) FROM (SELECT * FROM CTB.TEST2K);
COUNT(*)
-- ---- --
29576
SQL > COMMIT;
Validaci=BEn terminada.
SQL > SELECT COUNT(*) FROM (SELECT * FROM CTB.TEST2K);
COUNT(*)
-- ---- --
29576
SQL > SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME =3D 'TEST2K ';
OBJECT_ID
-- ---- --
33937
SQL > select status,count(*) from v$bh group by status;
