1 2 Previous Next 29 Replies Latest reply: Jan 15, 2014 10:16 AM by rp0428 Go to original post RSS
  • 15. Re: Can we pin an object into shared pool ?
    jgarry Guru
    Currently Being Moderated

    Please read Memory Configuration and Use and Memory Configuration and Use

     

    Read your post before posting to be sure it makes sense.  You need to be clear on the basics before you say anything.

  • 16. Re: Can we pin an object into shared pool ?
    gandolf999 Journeyer
    Currently Being Moderated

    I believe that I was clear on the basics. Here is some sample code that shows you can create a table and store it in one of the three buffer caches. If you want to prevent something from being aged out of cache, then place it in the keep pool and make sure that there is enough space in the keep pool otherwise it will get aged out when the space is needed.

     

    If you have something concrete to add, please do so.

     

    SQL> create table my_table ( c1 number);

     

     

    Table created.

     

     

    Elapsed: 00:00:00.30

    SQL> select table_name, buffer_pool from dba_tables where table_name='MY_TABLE';

     

     

    TABLE_NAME                     BUFFER_POOL

    ------------------------------ ---------------------

    MY_TABLE                       DEFAULT

     

     

    Elapsed: 00:00:00.08

    SQL> alter table my_table storage (buffer_pool keep);

     

     

    Table altered.

     

     

    Elapsed: 00:00:00.05

    SQL> select table_name, buffer_pool from dba_tables where table_name='MY_TABLE';

     

     

    TABLE_NAME                     BUFFER_POOL

    ------------------------------ ---------------------

    MY_TABLE                       KEEP

     

     

    Elapsed: 00:00:00.01

    SQL>  alter table my_table storage (buffer_pool recycle);

     

     

    Table altered.

     

     

    Elapsed: 00:00:00.02

    SQL> select table_name, buffer_pool from dba_tables where table_name='MY_TABLE';

     

     

    TABLE_NAME                     BUFFER_POOL

    ------------------------------ ---------------------

    MY_TABLE                       RECYCLE

     

     

    Elapsed: 00:00:00.00

    SQL> drop table my_table;

     

     

    Table dropped.

     

     

    Elapsed: 00:00:00.29

    SQL> select table_name, buffer_pool from dba_tables where table_name='MY_TABLE';

     

     

    no rows selected

     

     

    Elapsed: 00:00:00.01

  • 17. Re: Can we pin an object into shared pool ?
    gandolf999 Journeyer
    Currently Being Moderated

    Aman.... wrote:

    What a horribly wrong reply! user12018115 Do you even understand what what is kept in the shared pool and what is kept in the Keep and Recycle Caches and what these structures are all about?

     

    Aman....

    If you disagree with something that I said, perhaps you can make a specific criticism of something that I said that is false. In your estimation what does Oracle store in the keep and recycle pools?

  • 18. Re: Can we pin an object into shared pool ?
    TSharma-Oracle Guru
    Currently Being Moderated

    I guess you need to read your first reply again.

     

    In addition to the shared pool, there is also a keep pool and a recycle pool. If you want to prevent something from being paged out of the shared pool, you can pin it in the keep pool

     

    You specifically said that "If you want to prevent something from being paged out of the shared pool, you can pin it in the keep pool".


    Now Please answer what is stored in Shared pool? Where did you read that table stores in shared pool? Did you want to say buffer cache instead of shared pool?

  • 19. Re: Can we pin an object into shared pool ?
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    I think it's this line that had Aman spitting feathers.

     

    "In addition to the shared pool, there is also a keep pool and a recycle pool"

     

    I think you mean the default pool in the buffer cache rather than the actual "shared pool". You example illustrates switching a table between these three sub caches of the buffer cache.

     

    CHeers,

    Rich

  • 20. Re: Can we pin an object into shared pool ?
    gandolf999 Journeyer
    Currently Being Moderated

    Richard Harrison . wrote:

     

    Hi,

    I think it's this line that had Aman spitting feathers.

     

    "In addition to the shared pool, there is also a keep pool and a recycle pool"

     

    I think you mean the default pool in the buffer cache rather than the actual "shared pool". You example illustrates switching a table between these three sub caches of the buffer cache.

     

    CHeers,

    Rich

    Would I be splitting feathers if I suggested that we are splitting hairs rather than feathers? It sounds like you

    and others are agreeing with what I said with the exception that I should have specified default pool rather than

    shared pool. Ultimately, worrying about pinning objects in memory is a waste of time for most applications.

    It would be better to focus on tuning SQL than to worry about how or where Oracle caches data.

  • 21. Re: Can we pin an object into shared pool ?
    Richard Harrison . Expert
    Currently Being Moderated

    :-)

     

    totally agree on pinning things in memory - normally a complete waste of time.

     

    Oracle's algorithmn's are written by far more clever people than the average DBA - there are always going to be edge cases where fiddling with something can help a little but generally i agree - concentrate on tuning SQL and understanding what your data actually is to get the best out of the system. If the system has sensible indexes/stats in place then the problem is almost always the SQL

     

    Cheers,

    Rich

  • 22. Re: Can we pin an object into shared pool ?
    jgarry Guru
    Currently Being Moderated

    Well, Richard has nicely explained it to you, but I think both Aman and I were more than a little put off that you were supporting the OP's confusion of the shared pool and buffer caches.  Did you even look at the links I posted?  You quoted Aman when he was quite clearly asking if you understood what is in the shared pool and the keep and recycle caches.  He could hardly have been more specific.

     

    Everyone makes mistakes.  It's an easy mistake to make to call something the wrong thing right after someone else made the same mistake.  It's owning up to them that is important.  Then everyone learns.  Calling it splitting hairs is not owning up.

  • 23. Re: Can we pin an object into shared pool ?
    rp0428 Guru
    Currently Being Moderated
    Would I be splitting feathers if I suggested that we are splitting hairs rather than feathers?

    I give up!

     

    Would I be splitting hairs if I told you that Richard said 'spitting feathers' and not 'splitting feathers'?

     

    Sorry - gotta 'split' for now.

  • 24. Re: Can we pin an object into shared pool ?
    Aman.... Oracle ACE
    Currently Being Moderated

    Nothing is kept permanently in either of the three "caches". It's only a matter of time (due to some parameters) before the data buffers would be flushed out of even form the Keep cache. Just because that the name is Keep Cache, doesn't mean that the buffers are kept into for forever.

     

    Aman....

  • 25. Re: Can we pin an object into shared pool ?
    Aman.... Oracle ACE
    Currently Being Moderated

    user12018115 wrote:

     

    Aman.... wrote:

    What a horribly wrong reply! user12018115 Do you even understand what what is kept in the shared pool and what is kept in the Keep and Recycle Caches and what these structures are all about?

     

    Aman....

    If you disagree with something that I said, perhaps you can make a specific criticism of something that I said that is false. In your estimation what does Oracle store in the keep and recycle pools?

    Well, I have no doubts in what oracle keeps in the Keep and Recycle "caches" but I am not sure that how one can mix Shared Pool for the same task? You mentioned that Shared Pool can be used to "pin" a table and that's exactly where I asked that do you understand that what goes into either of these memory structures-Keep, Recycle, Shared Pool. The question of pinning objects in the share pool is subjective to a different discussion.

     

    Aman....

  • 26. Re: Can we pin an object into shared pool ?
    Girish Sharma Guru
    Currently Being Moderated

    I got a script, written by Tim Gorman to pin appropriate objects into the Shared Pool by using DBMS_SHARED_POOL.KEEP procedure :

     

    /**********************************************************************

    * File: gen_pin.sql

    * Type: SQL*Plus script

    * Author: Tim Gorman (Evergreen Database Technologies, Inc.)

    * Date: 21-Oct-98

    *

    * Description:

    * SQL*Plus script which uses the technique of "SQL-generating-SQL"

    * to pin appropriate objects into the Shared Pool of the Oracle

    * SGA using the DBMS_SHARED_POOL.KEEP packaged procedure.

    *

    * The script is designed on the following concepts:

    * - it is good to pin objects that are executed frequently,

    * especially if they are large (i.e. larger than 4Kb)

    * - it is OK to periodically unpin objects which have been

    * pinned previously but which have not been executed

    * This script is intended to be run periodically to do exactly

    * that. It generates another (temporary) SQL*Plus script to

    * actually run the KEEP and UNKEEP procedures, to allow you to

    * review before running...

    *

    * Modifications:

    * TGorman 21oct98 written for Oracle7 v7.3

    * TGorman 15feb01 modified rules of selection and added UNKEEP

    * logic...

    * TGorman 04jun01 added another UNION'd subquery to KEEP/UNKEEP

    * frequently executed cursors (TYPE='INVALID TYPE'

    * in v8.0.x and TYPE='CURSOR' in v8.1.x)...

    *********************************************************************/

    set tab off echo off feedback off timing off trimout on pause off

    set trimspool on pages 0 lines 500 verify off

     

    col instance new_value V_INSTANCE noprint

    select lower(replace(t.instance,chr(0),'')) instance

    from sys.v_$thread t,

    sys.v_$parameter p

    where p.name = 'thread'

    and t.thread# = to_number(decode(p.value,'0','1',p.value));

     

    col sort0 noprint

    col sort1 noprint

    col sort2 noprint

    col sort3 noprint

     

    spool run_pin_&&V_INSTANCE..sql

    prompt whenever sqlerror exit failure

    prompt set echo on feedback on timing on pagesize 100

    prompt spool run_pin_&&V_INSTANCE

    select decode(kept, 'YES', 'unkeep', 'keep') sort0,

    type sort1,

    owner sort2,

    name sort3,

    'exec dbms_shared_pool.' ||

    decode(kept, 'YES', 'unkeep', 'keep') || '(''' ||

    owner || '.' || name || ''',''' ||

    decode(type, 'TYPE', 'T',

    'TRIGGER', 'R',

    'SEQUENCE', 'Q', 'P') || ''');' text

    from sys.v_$db_object_cache

    where ((executions >= 100 and kept = 'NO')

      or (executions < 50 and kept = 'YES'))

    and type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TYPE',

    'TRIGGER','SEQUENCE')

    union

    select distinct decode(o.kept, 'YES', 'unkeep', 'keep') sort0,

    o.type sort1,

    o.owner sort2,

    o.name sort3,

    'exec dbms_shared_pool.' ||

    decode(o.kept, 'YES', 'unkeep', 'keep') || '(''' ||

    a.address || ', ' || a.hash_value || ''');' text

    from sys.v_$db_object_cache o,

    sys.v_$sqlarea a

    where ((o.executions >= 100 and o.kept = 'NO')

      or (o.executions < 50 and o.kept = 'YES'))

    and o.type in ('CURSOR', 'INVALID TYPE')

    and a.sql_text = o.name

    order by 1 desc, 2 asc, 3 asc, 4 asc;

    prompt spool off

    spool off

    REM start run_pin_&&V_INSTANCE

     

     

    I could paste the source link (http://www.sagelogix.com/idc/groups/public/documents/collateral/sage013302.sql), but I thought to paste the code better, if site changes...! (Because earlier Tim Gorman's site was bit changed and posted scripts links have been changed and/or updating).  Even though script has been written on 21-Oct-98, but I think it should still works in targeted system(s).

     

    Regards

    Girish Sharma

  • 27. Re: Can we pin an object into shared pool ?
    jgarry Guru
    Currently Being Moderated

    Perhaps a better question than "Does it still work?" would be "Is it appropriate these days?"  Maybe someone should ask Tim.

  • 28. Re: Can we pin an object into shared pool ?
    rp0428 Guru
    Currently Being Moderated
    Perhaps a better question than "Does it still work?" would be "Is it appropriate these days?"

    Appropriate? Yes - if you mean using KEEP.

     

    We use it for two use cases - both of them related to the need to run some batch processes concurrently with the OLAP app.

     

    1. For key production code to ensure that the batch code won't flush it out.

    2. For key batch code to ensure that the production code won't flush it out.

     

    The use is limited to only a few (less than 5 in our case) key objects and only when we don't have a window to run a particular batch process standalone.

  • 29. Re: Can we pin an object into shared pool ?
    jgarry Guru
    Currently Being Moderated

    I was referring to the use of the script to decide what to pin, but your examples are informative.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points