DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: dbms_shared_pool.keep

Hybrid View

  1. #1
    Join Date
    Feb 2002
    Posts
    25
    Hello,

    I need to use dbms_shared_pool.keep procedure to pin 2 packages into the shared pool.
    How do I do that?
    Where can I find this dbms_shared_pool.keep?

    Thanks in advance,

    Boris

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You can find info about this at ORACLE_HOME/rdbms/admin/dbmspool.sql.

    rem
    rem $Header: dbmspool.sql 15-jun-99.08:54:18 mjungerm Exp $
    rem
    Rem Copyright (c) 1991, 1996, 1997, 1998, 1999 by Oracle Corporation
    Rem NAME
    Rem dbmspool.sql - dbms_shared_pool utility package.
    Rem DESCRIPTION
    Rem This package allows you to display the sizes of objects in the
    Rem shared pool, and mark them for keeping or unkeeping in order to
    Rem reduce memory fragmentation.

    Here are some of the packages Oracle recommends you pin in the shared pool:

    execute dbms_shared_pool.keep('STANDARD');
    execute dbms_shared_pool.keep('DBMS_STANDARD');
    execute dbms_shared_pool.keep('DBMS_UTILITY');
    execute dbms_shared_pool.keep('DBMS_DESCRIBE');
    execute dbms_shared_pool.keep('DBMS_OUTPUT');
    execute dbms_shared_pool.keep('DBMS_PIPE');
    execute dbms_shared_pool.keep('DBMS_SESSION');

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    In order to save you PINNING evertime you start the database.. (from failure etc)


    create the following packages in the SYS Schema (via SVRMGRL < 9i, otherwise sqlplus) to execute on database startup.

    CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
    BEGIN
    sys.dbms_shared_pool.keep('SYS.STANDARD');
    sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
    sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
    sys.dbms_shared_pool.keep('SCOTT.TEST_PACKAGE','P');
    END;
    /

    CREATE OR REPLACE TRIGGER db_startup_pin AFTER STARTUP ON DATABASE
    BEGIN
    sys.proc_pkgs_keep;
    END;
    /

    This will automatically pin your packages at databae startup.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width