1 2 Previous Next 29 Replies Latest reply: Jan 15, 2014 12:16 PM by rp0428 RSS

    Can we pin an object into shared pool ?

    GTS (DBA)

      Good Morning all ;


      Can we pin an object into shared pool ?

      IF we  pin" an  object ,  so that it won't get flushed out by the circular nature of the shared pool.


      What i think , we can pin the object with buffer pool and NOT in shared pool.

      and those objects will be flushed , it depends.





        • 2. Re: Can we pin an object into shared pool ?
          Balazs Papp

          "Suprisingly" there is a package to do that.

          Read about DBMS_SHARED_POOL.KEEP below:

          DBMS_SHARED_POOL

          • 3. Re: Can we pin an object into shared pool ?
            jgarry

            What is this circular nature of the shared pool you speak of?

            • 4. Re: Can we pin an object into shared pool ?
              GTS (DBA)

              Hi jgarry

               

              Good morning !

               

              Pinning  (assigning a segment into buffer cache  -  i have idea about that).

              Pinning  (assigning a segment into shared pool  -   please provide some details about this.

               

              Thanks in advance ..

              • 5. Re: Can we pin an object into shared pool ?
                rp0428

                Answer the question that jgarry ask you:

                What is this circular nature of the shared pool you speak of?

                 

                • 6. Re: Can we pin an object into shared pool ?
                  GTS (DBA)

                  Hi jgarry ;-

                  jgarry wrote:

                   

                  What is this circular nature of the shared pool you speak of?

                  I  do NOT  understand that's why i ask you ...


                  What's the major difference ?


                  Pinning  (assigning a segment into buffer cache ?

                  Pinning  (assigning a segment into shared pool  ?

                  • 7. Re: Can we pin an object into shared pool ?
                    sb92075

                    >Pinning  (assigning a segment into buffer cache ?

                    post SQL which does above

                    >Pinning  (assigning a segment into shared pool  ?

                    post SQL which does above

                    • 8. Re: Can we pin an object into shared pool ?
                      jgarry

                      You need to ask questions that make sense.  Why do you even think you can pin a segment into a shared pool?  Please read the concepts manual to understand shared pool.

                      • 9. Re: Can we pin an object into shared pool ?
                        Aman....

                        GTS (DBA) wrote:

                         

                        Hi jgarry

                         

                        Good morning !

                         

                        Pinning  (assigning a segment into buffer cache  -  i have idea about that).

                        Pinning  (assigning a segment into shared pool  -   please provide some details about this.

                         

                        Thanks in advance ..

                        You are just not reading and making your own assumptions-which are absolutely wrong. Besides the question asked by Joel by Rp, another one for you,what benefit you think would be gained by "pinning" a segment into the buffer cache? Since you "have idea about it" , let's hear what your answer is.

                         

                        Aman....

                        • 10. Re: Can we pin an object into shared pool ?
                          Girish Sharma

                          Are you looking for DBMS_SHARED_POOL keep procedure ?

                           

                          To identify candidates that should be kept in the shared pool, first run the following query:

                           

                          select substr(owner,1,10)||'.'||substr(name,1,35) "ObjectName", type, sharable_mem,loads, executions, kept from v$db_object_cache where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0 order by executions desc,loads desc,sharable_mem desc

                           

                          https://blogs.oracle.com/stevenChan/entry/pinning_objects_to_improve_app

                           

                          Regards

                          Girish Sharma

                          • 11. Re: Can we pin an object into shared pool ?
                            Richard Harrison .

                            Hi,

                            you need to be clear on what the buffer cache and the shared pool are actually doing and what they contain.

                             

                            Basically:

                             

                            buffer cache is the cache of actual data - i.e. rows from tables, there are ways to try and keep this data in memory but you can't 'pin' it

                            shared pool is a cache of code that has been run, this can be pinned by using the process Girish describes but i don;t know when you'd ever really want to do this - if something is used a lot its generally always in memory anyway.

                             

                            Regards,

                            Rich

                            • 12. Re: Can we pin an object into shared pool ?
                              jgarry

                              I think you'd want to do this if you were stuck in the last century with limited memory and heavy pressure on the shared pool.

                               

                              Things are more complicated these days, people have even demonstrated data buffers sharing granules in the shared pool.

                               

                              The takeaways are:  don't try to over-anticipate problems, blindly making assumptions may make you aim for your foot.  If you have problems, solve them.  If you have problems, you don't know until you look if you are dealing with bugs or application issues.  You need to be clear on the basics before you do anything.

                              • 13. Re: Can we pin an object into shared pool ?
                                gandolf999

                                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 and make sure that the keep pool is sized large enough that everything placed in the keep pool will remain there. Just like if querying from a table is going to flush data from the shared pool, moving the object to the recycle pool will allow operations to happen in the recycle pool that will not effect the shared pool or the keep pool.

                                 

                                Regardless of whether or not tables are stored in the share pool, keep pool and recycle pool they can get flushed. The choice of which pool to use will effect the likeliness of a table getting flushed from memory. You can't completely prevent tables from getting flushed from memory. I generally only worry about pinning objects in memory if I have a lot of memory in an SGA and keeping a table in the SGA is important. Otherwise I just let Oracle manage the process.

                                 

                                It really comes down to how well do you know your application and what trade offs are you willing to make. Trying to pin things in memory that don't need to be pinned in memory is going to cause the database to more quickly flush something else out of memory, possibly something more important.

                                 

                                Generally if you want to tune, tune your most expensive queries to use the least amount of logical/physical IO as possible, and reduce locking as much as possible in your application. If you do that you are doing well.

                                 

                                http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm#PFGRF014

                                • 14. Re: Can we pin an object into shared pool ?
                                  Aman....

                                  user12018115 wrote:

                                   

                                  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 and make sure that the keep pool is sized large enough that everything placed in the keep pool will remain there. Just like if querying from a table is going to flush data from the shared pool, moving the object to the recycle pool will allow operations to happen in the recycle pool that will not effect the shared pool or the keep pool.

                                   

                                  Regardless of whether or not tables are stored in the share pool, keep pool and recycle pool they can get flushed. The choice of which pool to use will effect the likeliness of a table getting flushed from memory. You can't completely prevent tables from getting flushed from memory. I generally only worry about pinning objects in memory if I have a lot of memory in an SGA and keeping a table in the SGA is important. Otherwise I just let Oracle manage the process.

                                   

                                  It really comes down to how well do you know your application and what trade offs are you willing to make. Trying to pin things in memory that don't need to be pinned in memory is going to cause the database to more quickly flush something else out of memory, possibly something more important.

                                   

                                  Generally if you want to tune, tune your most expensive queries to use the least amount of logical/physical IO as possible, and reduce locking as much as possible in your application. If you do that you are doing well.

                                   

                                  http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm#PFGRF014

                                  What a horribly wrong reply! gandolf999 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....

                                  1 2 Previous Next