Pin query to memory
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Pin query to memory

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    23

    Exclamation

    HELP!! I have heard that I can pin an object, table, or query to memory to make queries faster. How do I do this?

    Thanks,
    Michellea
    "Live Life to the Fullest"

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Use DBMS_SHARED_POOL package to keep the objects in shared pool.

    Sanjay

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Code:
    --  FILE:   cre_pin.sql
    --
    --  AUTHOR: Andy Rivenes, arivenes@appsdba.com, http://www.appsdba.com
    --          Copyright (C) 1998 AppsDBA Consulting
    --
    --  DATE:   05/26/1998
    --
    --  DESCRIPTION:
    --          Query to create a shared pool pin script.  This script
    --          looks for "large" packages in the shared pool and
    --          creates a "pin" SQL statement.
    --
    --          This script is based on the following information:
    --
    -- Article-ID:         
    -- Alias:              OLS:11578136.61
    -- Circulation:        PUBLISHED (EXTERNAL)
    -- Platform:           GENERIC  Generic issue
    -- Subject:            LIBRARY CACHE AND SHARED POOL TUNING
    --
    -- In Oracle7.3 onwards the best candidates for keeping can be seen by
    -- querying the table X$KSMSP to see if there are any chunks in the shared-pool
    -- that have the KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'.
    -- If so then one can identify the object name and owner of this chunk
    -- using the following SQL:
    --
    --   select distinct
    --   decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',
    --               11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY','OTHER')
    --    ||' - '||kglnaown||'.'||kglnaobj "Eligible PL/SQL objects"
    --   from x$kglob
    --   where kglobhd4 in
    --    (select  ksmchpar from x$ksmsp
    --       where ksmchcom='PL/SQL MPCODE' and ksmchsiz>5120)
    -- 
    -- If you are 'keeping' PL/SQL objects today and migrate to 7.3 or higher
    -- there is no need to re-assess the list of objects that you are keeping.
    --
    --          
    --  REQUIREMENTS:
    --         Must be run as SYS                 
    --		
    --  MODIFICATIONS:
    --          A. Rivenes, 10/24/1999, Added the code from libcache_pin.sql
    --                                  to create a more accurate list.
    --
    --
    SET PAGESIZE 0;
    SET FEEDBACK off;
    SET VERIFY off;
    SPOOL pin.sql;
    --
    SELECT DISTINCT
           'execute dbms_shared_pool.keep('''||dbc.owner||'.'||dbc.name||''');'
      FROM v$db_object_cache dbc,
           ( SELECT DISTINCT DECODE(kglobtyp,0,'CURSOR',
                                    7,'PROCEDURE',
                                    8,'FUNCTION',
                                    9,'PACKAGE',
                                   11,'PACKAGE BODY',
                                   12,'TRIGGER',
                                   13,'TYPE',
                                   14,'TYPE BODY',
                                   'OTHER') type,
                    kglnaown,
                    kglnaobj
               FROM x$kglob
              WHERE kglobhd4 IN ( SELECT ksmchpar
                                    FROM x$ksmsp
                                   WHERE ksmchcom = 'PL/SQL MPCODE'
                                     AND ksmchsiz > 5120 ) ) chnk
     WHERE dbc.owner = chnk.kglnaown
       AND dbc.name = chnk.kglnaobj
       AND dbc.type = chnk.type
       AND dbc.type = 'PACKAGE'
    /
    --
    SPOOL off;
    -- EXIT;
    HTH

    Sameer

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    You can also use for tables
    alter table cache;

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Oct 2002
    Posts
    23

    Thanks for your help

    Thanks everyone for your help. I found a few items on Metalink.

    To Pin a Table:
    Alter table cache;

    Pin SQL statements in memory using DBMS_SHARED_POOL:
    1. Issue the SQL statement
    For example:
    SQL>select * from scott.emp;

    2. Get the SQL address from v$SQLAREA
    SQL> select address, hash_value, sql_text
    from v$sqlarea
    where sql_text like '%scott.emp%';

    ADDRESS HASH_VALUE SQL_TEXT
    ________ ____________ ______________
    836187c0 1131378091 select * from scott.emp

    3. Pin the SQL statement using address and hash_value.
    SQL> exec dbms_shared_pool.keep('836187c0, 1131378091','c');

    4. To check if it was pinned properly
    SQL> select name, type, kept
    from v$db_object_cache
    where kept = 'YES';

    Thanks for all your help,
    Michellea
    "Live Life to the Fullest"

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