Monday, October 22, 2012

6 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.





  1. For that very purpose, we've got created a listing of our listing of first-class promoting Pool Cues just for you. We had been going to do a pinnacle 10 Pool Cues listing pingpongtableguide Ping Pong Rules For 21 Point Game

  2. I really thank you for the valuable info on this great subject and look forward to more great posts. Thanks a lot for enjoying this beauty article with me. I am appreciating it very much! Looking forward to another great article. Good luck to the author! All the best! horseshoe bay custom pool builder

  3. In case you have some sort of pool area or merely received the latest pool area fitted, you need to have many pool area clean-up guidance for making factors much easier. Will possibly not learn each of the tactics or maybe the tools meant for pool area clean-up. poolcleaningguide. com will let you on this typically wearisome undertaking. check this post

  4. Fondée en 1905, fausses rolex montres les montres Rolex sont dans le fausses rolex gmt master ii montres domaine de l'innovation depuis plus d'un siècle. Le premier chronographe certifié, la première montre à remontage automatique et le premier boîtier étanche efficace ont été des jalons dans la conception de Rolex. Aujourd'hui, la marque Rolex est instantanément reconnue comme un symbole de prestige, de luxe et d'innovation à l'échelle mondiale. Acheter une montre Rolex, c'est acheter un morceau d'histoire horlogère. Ici, nous avons un vaste catalogue de montres Rolex exceptionnelles d'occasion à vendre.



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