Of course, if anyone has some queries to help identify buffer
pool pinning candidates, they would be more than welcome.
Thanks,
Lou Avrami
>From the article "Oracle8i Buffer Cache: New Features", identifying
"hot blocks" (DEFAULT cache?) can be done with the query:
PCSS:SYS> select obj object,
2 DBARFIL file#,
3 DBABLK block#,
4 tch touches
5 from x$bh
6 where tch > 100
7 order by 1,2;
OBJECT FILE# BLOCK# TOUCHES
---------- ---------- ---------- ----------
61 1 187 337
83 1 246 661
83 1 21865 797
99 1 280 370
102 1 286 1319
102 1 24617 1317
102 1 24618 1334
195 1 465 792
31658 10 1289 152
31668 10 1929 148
31678 10 2569 152
The article also suggest the following query for identifying
Recycle Pool candidates:
SQL> select obj object,
2 count(1) buffers,
3 100 * (count(1)/totsize) pct_cache
4 from x$bh,
5 (select value totsize from v$parameter
6 where name = 'db_block_buffers')
7 where tch = 1
8 group by obj, totsize
9 having 100 * (count(1)/totsize) > 5;
>--- Original Message ---
>From: "Louis Avrami" <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Date: 6/25/02 12:35:32 AM
>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
