tuning shared pool

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Wed, 22 Nov 2000 08:44:48 -0800 (PST)
Message-Id: <10688.122737@fatcity.com>

--0-760313750-974911488=:8612
Content-Type: text/plain; charset=us-ascii

On oracle's "resolving ora-4031" paper (note # 61623.1) they state the following - "All large packages that are shipped should be 'kept' if the customer uses PL/SQL. This includes 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'. With 7.3, the only package left in this list is 'STANDARD'." What about in 8i? Are there other packages that should be kept?

Another question. I have a package that is called on average every 5 seconds and is already pinned in memory. The package is about 100 lines long and according to v$db_object_cache is about 8k in size. However one very simple query in that package is showing up in v$db_object_cache with a size of over 400k. Why would a simple query against one table consume so much sharable memory? Here's the query:

SELECT p.person_org_id
INTO vrep_person_id
FROM mc.person p
WHERE p.user_id = Lower(pUser_id)
and active_user_id_ind = 'Y';

It translates into this in the shared pool...

SELECT P.PERSON_ORG_ID FROM MC.PERSON P WHERE P.USER_ID = LOWER(:b1) AND ACTIVE_USER_ID_IND = 'Y'

Last question. Is there anything in 8i that can be done to duplicate the functionality of the old SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_MIN_ALLOC parameters. I miss these sorely on every 8i instance. In most cases I've needed to dramatically increase the size of my shared pool because these parameters no longer exist.

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. --0-760313750-974911488=:8612
Content-Type: text/html; charset=us-ascii

On oracle's "resolving ora-4031" paper (note # 61623.1) they state the following - "All large packages that are shipped should be 'kept' if the customer uses PL/SQL. This includes 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'. With 7.3, the only package left in this list is 'STANDARD'." What about in 8i? Are there other packages that should be kept?
Another question. I have a package that is called on average every 5 seconds and is already pinned in memory. The package is about 100 lines long and according to v$db_object_cache is about 8k in size. However one very simple query in that package is showing up in v$db_object_cache with a size of over 400k. Why would a simple query against one table consume so much sharable memory? Here's the query:
SELECT p.person_org_id INTO vrep_person_id FROM mc.person p WHERE p.user_id = Lower(pUser_id) and active_user_id_ind = 'Y';
It translates into this in the shared pool...
SELECT P.PERSON_ORG_ID   FROM MC.PERSON P  WHERE P.USER_ID = LOWER(:b1)  AND ACTIVE_USER_ID_IND = 'Y'
Last question. Is there anything in 8i that can be done to duplicate the functionality of the old SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_MIN_ALLOC parameters. I miss these sorely on every 8i instance. In most cases I've needed to dramatically increase the size of my shared pool because these parameters no longer exist. <hr size=1>Do You Yahoo!? 
<a href="http://shopping.yahoo.com/">Yahoo! Shopping</a> -
Thousands of Stores. Millions of Products. Received on Wed Nov 22 2000 - 10:44:48 CST