DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ** How to cache objects in the Library Cache during INSTANCE STARTUP **

  1. #1
    Join Date
    Mar 2002
    Posts
    200

    ** How to cache objects in the Library Cache during INSTANCE STARTUP **

    Can someone here tell me how to cache objects in the Library Cache during INSTANCE STARTUP. I know we have to use some package. I am not getting the names and procedures. Kindly help me.

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Connect as svrmgrl and run (enter you own packages)

    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('SYS.DBMS_EXPORT_EXTENSION');
    sys.dbms_shared_pool.keep('SCOTT.PACKAGE','P');
    END;

    And then create a trigger

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

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by grjohnson
    Connect as svrmgrl and run (enter you own packages)

    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('SYS.DBMS_EXPORT_EXTENSION');
    sys.dbms_shared_pool.keep('SCOTT.PACKAGE','P');
    END;

    And then create a trigger

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

    Cheers
    Many thanks. The last example: does it mean that Scott's package called "P" is loaded into the cache? Does it mean it if I have load say a procedure "X" of scott, then it would become "SCOTT.PROCEDURE","X"??

    Kindly clarify.

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    sys.dbms_shared_pool.keep('SCOTT.X','P');

    ie.

    sys.dbms_shared_pool.keep('SCOTT.PACKAGE_NAME','P');
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by grjohnson
    sys.dbms_shared_pool.keep('SCOTT.X','P');

    ie.

    sys.dbms_shared_pool.keep('SCOTT.PACKAGE_NAME','P');
    Thanks. So what does the second parameter 'P' stand for then?

    Regards.
    Quester.

  6. #6
    Join Date
    Jun 2002
    Posts
    62
    P is default.

    flag:
    (Optional) If this is not specified, then the package assumes
    that the first parameter is the name of a
    package/procedure/function and resolves the name.
    Set to ’P’ or ’p’ to fully specify that the input is the name of a
    package/procedure/function.
    Set to ’T’ or ’t’ to specify that the input is the name of a type.
    Set to ’R’ or ’r’ to specify that the input is the name of a trigger.
    Set to ’Q’ or ’q’ to specify that the input is the name of a
    sequence.
    In case the first argument is a cursor address and hash-value,
    the parameter should be set to any character except ’P’ or ’p’ or
    ’Q’ or ’q’ or ’R’ or ’r’ or ’T’ or ’t’.
    Regards,
    Jack

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    PL/SQL or PACKAGE.. take your pick, documentation uses both terms.
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Mar 2002
    Posts
    200
    Now I see two different answers (ofcourse from two different members). Thanks anyways for the time and patience.

  9. #9
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    In fact they are the same answers really.

    What jack9190 has posted is correct. YOu actually don't have to specify 'P' if you are pinning a Package, Procedure or function since it the default. So P stands for

    Package, Procedure, Fucntion, PL/SQL

    How's that.. any clearer?
    OCP 8i, 9i DBA
    Brisbane Australia

  10. #10
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by grjohnson
    In fact they are the same answers really.

    What jack9190 has posted is correct. YOu actually don't have to specify 'P' if you are pinning a Package, Procedure or function since it the default. So P stands for

    Package, Procedure, Fucntion, PL/SQL

    How's that.. any clearer?
    O yEA. Excellent and many thanks.

    BTW, any idea whic system table hold the names and types of the objects cached? does V$LIBRARYCACHE indicate that?

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