OraFAQ Forum: Server Administration » KEEP Buffer Pool of VLM

Home » RDBMS Server » Server Administration » KEEP Buffer Pool of VLM

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

KEEP Buffer Pool of VLM [message #173681]

Wed, 24 May 2006 00:15

ranmal
Messages: 2
Registered: May 2006

Junior Member

Hi All,

I've configured an Oracle 9.2.0.7 database on Windows 2003 Server to put a 3GB-index(Storage Buffer KEEP) on the KEEP buffer pool of VLM.

However, I couldn't see whehter the index is on the pool with SQL*Plus.
Does anyone know how to see the cached objects in KEEP buffer pool?

Thanks for the help and support.

ranmal

Report message to a moderator

Re: KEEP Buffer Pool of VLM [message #173715 is a reply to message #173681]

Wed, 24 May 2006 03:09

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

Run this query to see what blocks are cached in the various buffer pools:

SELECT pd.bp_name, bh.ts#, bh.file#, bh.dbarfil, bh.dbablk, bh.state
  FROM x$kcbwds ds,
       x$kcbwbpd pd,
       (SELECT /*+ USE_HASH(x) */ x.*
          FROM obj$ o, x$bh x
         WHERE o.dataobj# = x.obj) bh
 WHERE ds.set_id >= pd.bp_lo_sid
   AND ds.set_id <= pd.bp_hi_sid
   AND pd.bp_size != 0
   AND ds.addr = bh.set_ds
/

Report message to a moderator

Re: KEEP Buffer Pool of VLM [message #173738 is a reply to message #173715]

Wed, 24 May 2006 05:07

ranmal
Messages: 2
Registered: May 2006

Junior Member

Thank you, Frank.

I run the query, and found that all of the column "pd.bp_name" are "DEFAULT". Does it mean no object is cached on "KEEP" buffer pool?

Thanks for the help and support.

ranmal

Report message to a moderator

Re: KEEP Buffer Pool of VLM [message #173790 is a reply to message #173738]

Wed, 24 May 2006 07:53

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

Yip. Put a table in the KEEP pool. Select some data from it and run the above query again.

Example:

SQL> ALTER SYSTEM SET db_keep_cache_size = 100M SCOPE=SPFILE;

System altered.

SQL>
SQL> STARTUP FORCE;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2030880 bytes
Variable Size             142607072 bytes
Database Buffers          465567744 bytes
Redo Buffers                2162688 bytes
Database mounted.
Database opened.
SQL>
SQL> ALTER TABLE scott.emp STORAGE ( BUFFER_POOL KEEP );

Table altered.

SQL>
SQL> SELECT * FROM scott.emp WHERE ROWNUM = 1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20


SQL>
SQL> SELECT pd.bp_name, bh.ts#, bh.file#, bh.dbarfil, bh.dbablk, bh.state
  2    FROM x$kcbwds ds,
  3         x$kcbwbpd pd,
  4         (SELECT /*+ USE_HASH(x) */ x.*
  5            FROM obj$ o, x$bh x
  6           WHERE o.dataobj# = x.obj) bh
  7   WHERE ds.set_id >= pd.bp_lo_sid
  8     AND ds.set_id <= pd.bp_hi_sid
  9     AND pd.bp_size != 0
 10     AND ds.addr = bh.set_ds
 11     AND pd.bp_name not like 'DEFAULT%'
 12  /

BP_NAME                     TS#      FILE#    DBARFIL     DBABLK      STATE
-------------------- ---------- ---------- ---------- ---------- ----------
KEEP                          4          4          4         21          1
KEEP                          4          4          4         24          1
KEEP                          4          4          4         19          1
KEEP                          4          4          4         22          1
KEEP                          4          4          4         20          1
KEEP                          4          4          4         23          1

6 rows selected.

Report message to a moderator

Previous Topic:	Is taking off and taking on the table partitioned data that simple?
Next Topic:	General purpose auditing of all application generated sqls

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 12:44:43 CDT 2024