DBMS SHARED POOL

From Oracle FAQ
Jump to: navigation, search

This article explains how to use the DBMS_SHARED_POOL package to manage the SHARED_POOL in the instance's SGA:

Install the DBMS_SHARED_POOL package[edit]

connect SYS AS SYSDBA
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/prvtpool.plb

Check for objects in the Shared Pool larger than X KBytes[edit]

set serveroutput on size 50000
exec dbms_shared_pool.sizes(200);

Pin something into the Shared Pool[edit]

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

Unpin something from the Shared Pool[edit]

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

Check what objects are pinned[edit]

SELECT * FROM v$db_object_cache WHERE kept = 'YES';

PS: Looks like Oracle pins some objects by itself.