jump to navigation

usage of DBMS_SHARED_POOL May 8, 2009

Posted by sendtoshailesh in Uncategorized.

Whenever i go for tuning the instance or sql, I first target shared_pool. because this is the pool which is directly connected with availability SQL or PL/SQL in shared cache for any connected user in DB.
By using the DBMS_SHARED_POOL package and by loading these SQL and PL/SQL areas early (before memory fragmentation occurs), the objects can be kept in memory, instead of aging out with the normal LRU mechanism. This procedure ensures that memory is available and prevents sudden, seemingly inexplicable slowdowns in user response time that occur when SQL and PL/SQL areas are accessed after aging out.

This is always a question before i facilitate the sql to be always aviailable in shared_pool

The procedures provided with the DBMS_SHARED_POOL package may be useful when loading large PL/SQL objects, such as the STANDARD and DIUTIL packages.

When large PL/SQL objects are loaded, users’ response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

DBMS_SHARED_POOL is also useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.

To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps.

1. Decide which packages/cursors you would like pinned in memory.
2. Startup the database.
3. Make a reference to the objects that causes them to be loaded. To pin a package, you can reference a dummy procedure defined in the package, or you can reference a package variable. To pin the cursor allocated for a SQL statement, execute the statement. To pin a trigger, issue a statement that causes the trigger to fire.
4. Make the call to DBMS_SHARED_POOL.KEEP to pin it.

This procedure ensures that the object is already loaded; otherwise, pinning may not be very useful. It also ensures that your system has not run out of the shared memory before the object is loaded. Finally, by pinning the object early in the life of the instance, this procedure prevents the memory fragmentation that could result from pinning a large chunk of memory in the middle of the shared pool.

Check for objects in the Shared Pool larger than X KBytes

set serveroutput on size 50000
exec dbms_shared_pool.sizes(200);
Pin something into the Shared Pool

exec dbms_shared_pool.keep(‘HR.MYPACKAGE’);

Unpin something from the Shared Pool

exec dbms_shared_pool.unkeep(‘HR.MYPACKAGE’);

Check what objects are pinned

SELECT * FROM v$db_object_cache WHERE kept = ‘YES’;



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: