| 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: Which objects to pin in which Multiple Buffer Pool?
My recommendation is small lookup type tables that are used very often.
Things like Employees tables, common LOV (list of values) tables should go
to the KEEP pool. With 9i, you can have a pool for each db_block_size, and a
corresponding TS for each. I have not done this in practice yet, but this is
the one feature that DB2 has had that as an Oracelite I had been envious of,
which we now finally have, having multiple buffer_pools per TS (well, kind
of). HTH.
Regards:
Ferenc Mantfeld
Senior Performance Engineer
Siebel Performance Engineering
Melbourne, 3000, VIC, Australia
Only Robinson Crusoe had all his work done by Friday
-----Original Message-----
Sent: Monday, 24 June 2002 10:48 PM
To: Multiple recipients of list ORACLE-L
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
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
>--- Original Message ---
>From: "Louis Avrami" <avramil_at_concentric.net>
>To: ORACLE-L_at_fatcity.com
>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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ferenc Mantfeld INET: fmantfeld_at_siebel.com 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 - 16:41:17 CDT
 |
 |