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/

image

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');
image

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'
image
Ping the query
exec dbms_shared_pool.keep('5F9E52F0,2724039703','C')
Verify
SELECT * FROM v$db_object_cache WHERE kept = 'YES'  and HASH_VALUE='2724039703'

image

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.

 

 

Reference

http://www.orafaq.com/wiki/DBMS_SHARED_POOL

0 comments:

Post a Comment

 

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