Oracle-L: Which objects to pin in which Multiple Buffer Pool?

From: Louis Avrami <avramil_at_concentric.net>
Date: Mon, 24 Jun 2002 22:18:19 -0800
Message-ID: <F001.00486380.20020624221819@fatcity.com>

Sorry if this is a repeat, I received a message that my original message was being return because of "locking problems"

Hi all,

I'm in an environment where we're running RDBMS 8.1.7.2 on multiple Solaris 2.8 servers.

I would like to set up multiple buffer pools in several of our databases and pin objects appropriately in the KEEP, RECYCLE and default buffer cache. Unfortunately, many of the applications that we work with are developed by outside vendors with whom we have very little contact or documentation (don't ask).

I did come across an article titled "Oracle8i Buffer Cache: New Features" in the July 2000 issue of the ORACLE INTERNALS newsletter, which has some interesting queries which might help to identify candidates for the various buffer pools.

For the KEEP pool, the article suggests the following SQL:

1 select obj object,

  2     count(1) buffers,
  3     avg(tch) avg_touches

  4 from x$bh
  5 group by obj
  6 having avg(tch) > 5
  7* and count(1) > 20
SQL> /

    OBJECT    BUFFERS AVG_TOUCHES                           
                   
---------- ---------- -----------                           
                   
         2        271  7.90405904                           
                   
         6         23          19                           
                   
         8         52  14.4038462                           
                   
        18        299  9.18394649                           
                   
        33         31  12.9354839                           
                   
        34        219  6.66666667                           
                   
     32365        151  145.748344                           
                   
     32369         22  72.8181818                           
                   
     32376         21  5.38095238                           
                   
     32383         23  94.7391304                           
                   
     32433         86  5.69767442

In the above query, I'm not sure how to map back the OBJ (OBJECT) number to a database table/object, so that it could subsequently be pinned in the KEEP pool.

A better question might be this:

Can someone help map X$BH.obj, X$BH.file# and X$BH.block# so that they can be identified as database tables, indexes, data files, etc.?

Thanks,
Lou Avrami

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Louis Avrami
INET: avramil_at_concentric.net

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 Tue Jun 25 2002 - 01:18:19 CDT