Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson

Chapter 2 Inside the Oracle Shared Pool

simple act of pinning a package, procedure or function using the dbms_shared_pool package will call it into memory.

The dbms_shared_pool package may have to be created in earlier releases of Oracle. The dbms_shared_pool package is built using the dbmspool.sql and prvtpool.plb scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT) x:\orant\rdbms\admin  (where x: is the home drive for the install).

How are the packages, procedures, and functions to be pinned determined? Actually, Oracle has made this easy by providing the v$db_object_cache view. This view shows all the objects in the pool, and more importantly, how they are being utilized. The script below provides a list of objects that have been loaded more than once, and have executions greater than one. A sample output is also provided. A rule of thumb is that if an object is being frequently executed and frequently reloaded, it should be pinned into the shared pool.


set lines 132 feedback off verify off
set pages 0
spool keep_them.sql
select  'execute dbms_shared_pool.keep('||chr(39)||OWNER||'.'||name||chr(39)||','||
 and executions>loads and executions>1 and kept='NO'
order by owner,namespace,type,executions desc
spool off

The output from the above script  is shown below.

execute dbms_shared_pool.keep('SYS.DICTIONARY_OBJ_OWNER','P')
execute dbms_shared_pool.keep('SYS.DICTIONARY_OBJ_NAME','P')
execute dbms_shared_pool.keep('SYS.PLITBLM','P')
execute dbms_shared_pool.keep('SYS.DBMS_STANDARD','P') 

Inside the Oracle Shared Pool

Triggers, cursors, packages, and procedures or functions can all be pinned. Note, only the package has to be pinned, not the package and package body.

Another criteria for determining whether an object should be pinned into the shared pool is its size. The dbms_shared_pool.sizes procedure searches the shared pool for any objects larger (in kilobytes) than the argument passed. Generally, the larger the size, the more likely that the object is a package, and it should probably be kept in the pool. Smaller objects tend to be individual queries and can be aged out of the pool.  Remember, that the dbms_shared_pool procedure is not generally loaded automatically when an instance is built; the dbmspool.sql and prvtpool.plb scripts must be run from internal or sys users for it to be created. The use of dbms_shared_pool.sizes is shown below.

SQL> set serveroutput on size 4000;
SQL> execute sys.dbms_shared_pool.sizes(10);
------- ------ -------------------------------------------------
139             SYS.STANDARD        (PACKAGE)
56              SYS.DBMS_SHARED_POOL       (PACKAGE BODY)
31              SELECT TO_CHAR(SHARABLE_MEM / 1000 ,'999999')
                SZ,DECOD(KEPT_VERSIONS,0,'   ',RPAD('YES(' ||
                TO_CHAR(KEPT_VERSIONS)  || ')' ,6)) KEEPED,
                RAWTOHEX(ADDRESS)|| ','  || TO_CHAR(HASH

               FROM V$SQLAREA WHERE SHARABLE_MEM > :b1 * 1000  
               1000 ,'999999') SZ,DECODE(KEPT,'YES','YES
30             SYS.STANDARD             (PACKAGE BODY)
27             SYS.DBMS_SHARED_POOL       (PACKAGE)
17             SYS.V$SQLAREA             (VIEW)
16             SYS.V$DB_OBJECT_CACHE       (VIEW)
15             insert into idl_ub2$(obj#,part,version,
               piece#, length,piece)values(:1,:2,:3,:4,
               :5,:6)  (0027BA44,-512326869)(CURSOR)

The ‘set serveroutput’ command in line 1 limits the size of the output buffer to 4000 bytes. This command is required. Perhaps in the future Oracle will incorporate the use of util_file, which would simply generate a report listing that could be reviewed as desired.

The script above indicates there is one large package in shared memory. A keep issued against this package would retain it. The results of this action are shown below.

SQL> execute dbms_shared_pool.keep('sys.standard');
PL/SQL procedure successfully completed.
SQL> execute dbms_shared_pool.sizes(130);

------- ------ ---------------------------------------------
139     YES    SYS.STANDARD        (PACKAGE)

The keep issued against large packages, retaining them in memory, mitigates shared pool fragmentation resulting in the ORA-04031 error. Pinning the packages so they don’t age out prevents smaller queries, cursors, and procedures from using the package space. If the packages are not pinned, they must seek a space large enough to reinstall themselves, leading to the ORA-04031 error. This error is supposedly eliminated in Oracle8, by changing the way the shared memory area is used. However, there have been some reports of errors on versions as late as

Guideline 4

Determine the usage patterns of packages, procedures, functions, triggers, and cursors. Pin those that are frequently used.

The Shared Pool and the MTS

Using the Oracle multi-threaded server option (MTS) may require a dramatic increase in the size of the shared pool. This increase is due to the addition of the user global areas required for sorting and message queues. When using MTS, the v$sgastat values for MTS-related memory areas should be monitored, and the shared pool memory allocations adjusted accordingly.

Note: When using Oracle 8 and later version with MTS, the large pool feature should be used to pull the user global areas (UGA) and multi-threaded server queues out of the shared pool area. This prevents the





The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks