Pinning PL/SQL Packages in the Shared Pool

Pinning PL/SQL Packages in the Shared Pool


Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book “Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming” by Dr. Tim Hall, Oracle ACE of the year, 2006:

The initial call to a package causes the whole package to be loaded into the shared pool.  For large packages this may represent an unacceptable delay for two reasons.  First the size of the package causes a delay in load time.  Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it.  In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list.  If the shared pool is full and a new object must be loaded, items on the LRU list are aged out.  Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage.  The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

The dbms_shared_pool package is not loaded by default, so it must be loaded manually by running the dbmspool.sql script as the SYS user.

SQL> conn sys/password as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

View created.

Package body created.

Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

PROCEDURE keep (
name  VARCHAR2,
flag  CHAR      DEFAULT ‘P’
)

PROCEDURE unkeep (
name  VARCHAR2,
flag  CHAR      DEFAULT ‘P’
)

The name parameter is used to specify the object to be pinned or unpinned. The flag parameter specifies the object type, of which the following are valid:

  • P – Package, Procedure or Function.  This is the default value.
  • T – Type.
  • R – Trigger.
  • Q – Sequence.
  • C – Cursor. The current documentation does not list “C” as a valid parameter and suggests that for cursors any parameter can be used.  In practice this does not appear to be true and the parameter value of “C” must be specified for cursors.

If the object to be pinned is a cursor, it should be identified using the address concatenated to the hash_value from the v$open_cursor view, as shown below.

SQL> conn sys/password as sysdba
Connected.
SQL> SELECT address || ‘,’ || hash_value FROM v$open_cursor WHERE rownum = 1;

ADDRESS||’,’||HASH_VALUE
——————————————————————–
6A524ABC,3792707131

1 row selected.

SQL> EXEC DBMS_SHARED_POOL.keep(‘6A524ABC,3792707131’, ‘C’);
PL/SQL procedure successfully completed.

Only objects present in the shared pool can be pinned, so you must either wait for them to be loaded or force the load.

The keep_test_api.sql script creates a package specification and body that will be used to demonstrate the keep and unkeep procedure usage.  The package contains a reference to a procedure called stub that performs no work.  A call to this procedure forces the package to be loaded into the shared pool so it can be pinned.

keep_test_api.sql

CREATE OR REPLACE PACKAGE keep_test_api AS

PROCEDURE stub;

END keep_test_api;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY keep_test_api AS

— —————————————————————–
PROCEDURE stub AS
— —————————————————————–
BEGIN
NULL;
END stub;
— —————————————————————–

END keep_test_api;
/
SHOW ERRORS

The following examples assume this package has been loaded into a schema called TEST, starting with the an example of the keep procedure.

SQL> conn sys/password as sysdba
Connected.
SQL> EXEC test.keep_test_api.stub;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SHARED_POOL.keep(‘test.keep_test_api’);


PL/SQL procedure successfully completed.

To pin certain packages at instance startup, it makes sense to add an equivalent stub procedure to each of them, run a script to call the stubs and then pin the packages.

To unpin the package, use the unkeep procedure shown below.

SQL> conn sys/password as sysdba
Connected.
SQL> EXEC DBMS_SHARED_POOL.unkeep(‘test.keep_test_api’);

PL/SQL procedure successfully completed.

Generally I do not pin objects in the shared pool, preferring instead to let Oracle manage the contents of the shared pool for me.  If the application is well written and the shared pool is sized correctly, there should be no need to resort to pinning.  In the event you are forced to work with badly written applications or in environments where memory is limited, some gains may be seen using this method.  Like all tuning, the key is to experiment in a controlled manner, constantly testing the results of the modifications.

The dbms_shared_pool package contains two more procedures that should be mentioned for the sake of completeness.

The sizes procedure lists all objects present in the shared pool whose size in Kb exceeds the size specified in the procedure call.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 300);
SIZE(K) KEPT   NAME
——- —— ——————————————-
773 YES    SYS.oracle/gss/util/NLSLocale (JAVA CLASS)
470        SYS.STANDARD                  (PACKAGE)
390        SYS.DBMS_STATS                (PACKAGE BODY)
355        SYS.DBMS_RCVMAN               (PACKAGE BODY)

PL/SQL procedure successfully completed.

The aborted_request_threshold procedure can be used to prevent Oracle from flushing objects from the LRU list to make room for large objects. If there is insufficient free memory, an attempt to load an object whose size is above the threshold value will result in an ORA-4031 error.  The threshold can be set as follows.

SQL> EXEC DBMS_SHARED_POOL.aborted_request_threshold(40000);
PL/SQL procedure successfully completed.

A look back at the output from the dbmspool.sql script reveals that a view was created.  The name of that view was dba_keepsizes and is used to display the total space required by an object when it is kept in the shared pool.

SQL> SELECT *
2  FROM   dba_keepsizes
3  WHERE  owner = ‘SYS’
4  AND    name  = ‘STANDARD’;

   TOTSIZE OWNER                          NAME
———- —————————— ————————–
205 SYS                            STANDARD


1 row selected.

This allows the impact of pinning objects in the shared pool to be assessed prior to the operation taking place.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PLSQL TUNING, SHARED POOL e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s