1 2 Previous Next 29 Replies Latest reply: Jan 15, 2014 12:20 PM by jgarry Go to original post RSS
      • 15. Re: Can we pin an object into shared pool ?
        jgarry

        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

          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

            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

              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 .

                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

                  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 .

                    :-)

                     

                    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

                      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
                        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....

                          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....

                            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

                              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

                                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
                                  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

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

                                    1 2 Previous Next