RE: recycle and keep buffer in other tablespace block sizes
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 14:17:42 -0400
Juan,
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes =
Pacheco
Sent: Wednesday, July 21, 2004 1:22 PM
To: oracle-l@xxxxxxxxxxxxx
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;
STATU COUNT(*)
----- ----------
cr 10
free 20484
xcur 3024
SQL> ALTER SYSTEM SET DB_CACHE_ADVICE=3DON;
Sistema modificado.
SQL> SELECT COUNT(*) FROM (SELECT * FROM CTB.TEST2K);
COUNT(*)
----------
29576
SQL> select distinct name from V$DB_CACHE_ADVICE;
NAME
--------------------
DEFAULT
KEEP
RECYCLE
SQL> create table ctb.test2k_2 (test number ) tablespace tbl_2k;
Tabla creada.
SQL> INSERT INTO CTB.TEST2K_2 SELECT ROWNUM FROM DBA_OBJECTS;
29577 filas creadas.
SQL> SELECT COUNT(*) FROM (SELECT * FROM CTB.TEST2K_2);
COUNT(*)
----------
29577
SQL> select distinct name from V$DB_CACHE_ADVICE;
NAME
--------------------
DEFAULT
KEEP
RECYCLE
=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
