Monday, October 22, 2012

0 How to keep the object in the Shared pool

We can use dbmspool package to keep the object in the memory. It is only effective on the runtime.After the instance bounce, the object will release out to the memory. therefore if you know some objects like to keep in the shared pool for your application, you have to schedule it to run after the server bounce.

Oracle recommend to keep the oracle system object in the shared pool, such as STANDARD,DBMS_STANDARD,DBMS_UTILITY,DBMS_DESCRIBE and  DBMS_OUTPUT.

Install dbmspool.sql package

The package usually at $ORACLE_HOME/rdbms/admin/


Check for objects in the Shared Pool larger than X KBytes

exec dbms_shared_pool.sizes(200);

Pin something into the Shared Pool

dbms_shared_pool.keep takes 2 parameter, object name and object types. The default types is P ( package, procedure, and function). More detail can be found here.
exec dbms_shared_pool.keep('SYS.STANDARD');

Unpin something from the Shared Pool

exec dbms_shared_pool.unkeep('SYS.STANDARD');

Check what objects are pinned in the shared pool

SELECT * FROM v$db_object_cache WHERE kept = 'YES'
Pin specific SQL into the Shared Pool

Find the hash code for the query.

select address,hash_value from v$sqlarea
where sql_text = 'select * from testa'
Ping the query
exec dbms_shared_pool.keep('5F9E52F0,2724039703','C')
SELECT * FROM v$db_object_cache WHERE kept = 'YES'  and HASH_VALUE='2724039703'


also we can flush the shared pool to verify

alter system flush shared_pool


Run the query again and then check the query’s address and hash code. It does not change and the check the shared_reserved pool, the hash code still in there.





Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates