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:

Contents

[edit] Install the DBMS_SHARED_POOL package

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

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

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

[edit] Pin something into the Shared Pool

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

[edit] Unpin something from the Shared Pool

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

[edit] Check what objects are pinned

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

PS: Looks like Oracle pins some objects by itself.