Friday, October 7, 2011

dbms_shared_pool


dbms_shared_pool

Loading the same objects into the shared pool repeatedly and shared pool fragmentation are common problems in many Oracle databases. The keys to tuning the shared pool are:
􀂃 Identifying the objects being reloaded into the shared pool time and time again:

SELECT owner, name||' - '||type name, loads , sharable_mem FROM v$db_object_cache WHERE loads > 1 AND type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY loads DESC;
􀂃 Identifying large objects that may not be able to be loaded into a fragmented shared pool:

SELECT owner, name||' - '||type name, sharable_mem FROM v$db_object_cache WHERE sharable_mem > 10000 AND type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')ORDER BY sharable_mem DESC;


The objects identified by the above queries can be pinned in the shared pool by executing the dbms_shared_pool.keep procedure. Several Oracle supplied packages that should be pinned in the shared pool are STANDARD, DBMS_STANDARD and UTIL. The objects must be pinned every time the Oracle database is started.

SQL> exec DBMS_SHARED_POOL.KEEP ('UTL_RAW');
PL/SQL procedure successfully completed.

SQL> exec DBMS_SHARED_POOL.KEEP ('DBMS_STATS');
PL/SQL procedure successfully completed.

No comments:

Post a Comment