recycle pool candidates

From: David <thump_at_cosmiccooler.org>
Date: Fri, 13 Aug 2004 13:25:53 -0700 (PDT)
Message-ID: <2848.64.37.153.21.1092428753.squirrel@www.cosmiccooler.org>

I downloaded the recycle pool candidate determination script from Oracle, but it uses db_block_buffers parameter setting as part of the determining criteria. I modified it to do the equivilant and divide db_cache_size by db_block_size for 9i db's. I just wanted to see if anyone here would eyeball/check my changes as I'm not 100% sure on the outcome(if I figured out an equivilant to what I think was being done.) Thanks!!

Here is what I came up with:
SELECT sysdate, object_id, owner, object_name, object_type

        FROM dba_objects
       WHERE owner not in('SYS','SYSTEM') and data_object_id  in (

SELECT obj object
FROM x$bh, (select (select value from v$parameter where name = 'db_cache_size')/(select value from v$parameter where name = 'db_block_size') totsize from dual) WHERE tch = 1
OR (tch = 0 AND lru_flag < 8)
GROUP BY obj, totsize
HAVING (COUNT(1)/totsize) * 100 > 5);

Here is the origional:
SELECT obj object,
COUNT(1) buffers,
(COUNT(1)/totsize) * 100 pct_cache
FROM x$bh,
SELECT value totsize FROM v$parameter
WHERE name = 'db_block_buffers')
WHERE tch = 1
OR (tch = 0 AND lru_flag < 8)
GROUP BY obj, totsize
HAVING (COUNT(1)/totsize) * 100 > 5;
--

..
David

---

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

---

Received on Fri Aug 13 2004 - 15:21:47 CDT