Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

ss 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.

Gen_keep.sql

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)||','||
         chr(39)||decod
(type,'PACKAGE','P','PROCEDURE','P','FUNCTION','P','SEQUENCE','Q',
                              'TRIGGER','R')||chr(39)||')'
from
 v$db_object_cache
SEE CODE DEPOT FOR FULL SCRIPT
 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);
SIZE(K) KEPT   NAME
------- ------ -------------------------------------------------
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

_VALUE)  NAME,SUBSTR(SQL_TEXT,1,354) EXTRA  
               FROM V$SQLAREA WHERE SHARABLE_MEM > :b1 * 1000  
               UNION SELECT TO_CHAR(SHARABLE_MEM /
               1000 ,'999999') SZ,DECODE(KEPT,'YES','YES
               (004D7F84,2008220828)(CURSOR)
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);

SIZE(K) KEPT   NAME
------- ------ ---------------------------------------------
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 9.2.0.4.

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.