| 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: recycle and keep buffer in other tablespace block sizes
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#
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes =
Pacheco
Sent: Wednesday, July 21, 2004 1:22 PM
To: oracle-l_at_freelists.org
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) CACHE4 /
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
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Wed Jul 21 2004 - 13:15:21 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
 |
 |