1 2 Previous Next 16 Replies Latest reply: Sep 20, 2011 4:31 AM by Rajib Sutradhar RSS

    Shared pool understanding.

    Rajib Sutradhar
      At my site one performance tuning guys said that when query enter into shared pool it searches complete shared pool during parsing to find its hash value. Since 70% percent queries which resides in shared pool have execution =1, so he recommended to flush the shared pool every night. So that queries has to search less shared pool area till other tuning activities(like bind variable,cursor_sharing) gets implemented. Our dba started doing that and we see gain in performance. Earlier simple "sqlplus sys as sysdba" use to take lot of time after database crosses 24hrs run window. Now such things not happening. Infact 'latch: library cache' wait event also gone down.

      Now my question is:
      1. Is he correct with his statement that query searches complete shared pool area to find it hash value presence in shared pool? If yes then how we can verify that query is searching 40G of shared pool area(in our env sharedpool_size=40G).
      2. Hash value is managed by link list(guessing) so whether shared_pool_size=1G or shared_pool_size=100G how it matter.
      3. How to decide when to reduce shared pool? Any awr event we need to monitor?
      4. Which event of awr report we should check other than 'latch: library cache' to see the shared pool usage?

      Oracle Version : 10.2.0.5
      Sga_target=110G
      shared_pool_size=5G
      sharedpool_size=40G
      db_cahe_size=14G
      dbcache_size=60G

      Thanks and Regards,
      Rajib Sutradhar
        • 1. Re: Shared pool understanding.
          Tubby
          Probably a good place to start reading
          http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94296
          • 2. Re: Shared pool understanding.
            758358
            A good MOS note on this subject:

            Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention (Doc ID 62143.1)

            Thanks
            Paul
            • 3. Re: Shared pool understanding.
              Billy~Verreynne
              Yes, the shared pool is searched for an existing reusable copy of the SQL statement (a soft parse). When that fails, a hard parse need to be performed and new cursor added to the shared pool.

              This will only really be an issue with a very large shared pool containing 100's of 1000's of cursors (likely due to not using bind variables).

              In your case - a 40Gb shared pool seems to me to be a serious overkill. It is enormous. Even with massive 1Mb cursors, it means over 40,000 unique SQL statements.

              If there are Shared Pool malloc errors, and these are caused by non-bind SQL, the very worse thing you can do for performance is to bump up the Shared Pool size. All this allows is more trash SQL (non-shareable SQL) to be stored in the Shared Pool - and seriously impact performance.

              In that case creating a very small Shared Pool and flushing it very often will provide better performance. Of course, needless to say that the app developers that are coding non-sharable SQL should be introduced to a lead pipe.
              • 4. Re: Shared pool understanding.
                Aman....
                The sga size if 110gb and on top of it the Shared Pool Size of 40gb is actually way too much big. Are you sure that this is right size that you need for the SGA?
                Is he correct with his statement that query searches complete shared pool area to find it hash value presence in shared pool?
                Yes, Oracle checks the shared poo's library cache to find out whether the query has already been parsed or not. This check is done every time when the parse request call for the query comes up. If you do know that you query is going to be run many times, you may want to skip the hard parse calls(and for some queries, even the soft parse calls too) .
                If yes then how we can verify that query is searching 40G of shared pool area(in our env sharedpool_size=40G).
                I can't think of anything right now for the entire system check but for a specific query,you can check the parse to execution calls using the Tkprof and see if they are matching. The Parsing should be happened once compared to number of executions. If oyu are seeing the Parse calls number reaching nearer to the execution calls , you may want to check implement bind variables etc to control it.
                Hash value is managed by link list(guessing) so whether shared_pool_size=1G or shared_pool_size=100G how it matter.
                What does it mean? The more bigger the number, the more bigger the list to scan isn't it?
                How to decide when to reduce shared pool? Any awr event we need to monitor?
                Easiest would be to use the Shared Pool Advisor to check this. It would tell you that whether there would be any benefit that you would be getting from the increased pool or not.
                Which event of awr report we should check other than 'latch: library cache' to see the shared pool usage?
                The other latch "latch_shared pool" would be a more prominent in telling that you are not using the shared pool properly because this would be used to allocate the cursor area and that's needed when the cursors are required to get hard parsed.

                HTH
                Aman....
                • 5. Re: Shared pool understanding.
                  Jonathan Lewis
                  Rajib Sutradhar wrote:
                  Now my question is:
                  1. Is he correct with his statement that query searches complete shared pool area to find it hash value presence in shared pool? If yes then how we can verify that query is searching 40G of shared pool area(in our env sharedpool_size=40G).
                  No.
                  2. Hash value is managed by link list(guessing) so whether shared_pool_size=1G or shared_pool_size=100G how it matter.
                  Correct - the search is based on a hash value of the SQL text, and that restricts the search to one library cache hash bucket (of the 131,072 that you probably have in your library cache)
                  There are cases (with cursor_sharing = similar, or under adaptive cursor sharing) where a single hash bucket could have lots (even thousands) of copies of the same text, but this is rare; otherwise the search is likely to walk a very short linked list.

                  The latch activity is more likely to be the result of Oracle taking objects off the LRU list putting memory back onto the shared pool free lists as it tries to find space to optimize new statements. The SQLPLUS logon is a particular special case, because I think your version needs to finr a fairly large contiguous chunk of memory (about 22KB, from memory) as the "session parameters" area, and in a smashed up shared pool that could result in a lot of things being discareded before a large enough single chunk can be coalesced out.
                  3. How to decide when to reduce shared pool? Any awr event we need to monitor?
                  A simple place to look is the summary in the AWR or statspack report that shows "percentage of SQL statements used more than once", and "percentage of memory used by statements executed more than once". If both figures are very low then this suggests your shared pool has grown to accommodate non-sharable SQL. Unfortunately you can't set a maximum size of the shared pool under automatic system memory management - unless you set a minimum for the db_cache_size that stops the shared pool from growing.
                  4. Which event of awr report we should check other than 'latch: library cache' to see the shared pool usage?
                  See (3). The heading is something like "Shared Pool Statistics"

                  >
                  Oracle Version : 10.2.0.5
                  Sga_target=110G
                  shared_pool_size=5G
                  sharedpool_size=40G
                  db_cahe_size=14G
                  dbcache_size=60G

                  Thanks and Regards,
                  Rajib Sutradhar
                  Regards
                  Jonathan Lewis
                  • 6. Re: Shared pool understanding.
                    VenkatB
                    >
                    Correct - the search is based on a hash value of the SQL text, and that restricts the search to one library cache hash bucket (of the 131,072 that you probably have in your library cache)
                    There are cases (with cursor_sharing = similar, or under adaptive cursor sharing) where a single hash bucket could have lots (even thousands) of copies of the same text, but this is rare; otherwise the search is likely to walk a very short linked list.

                    The latch activity is more likely to be the result of Oracle taking objects off the LRU list putting memory back onto the shared pool free lists as it tries to find space to optimize new statements. The SQLPLUS logon is a particular special case, because I think your version needs to finr a fairly large contiguous chunk of memory (about 22KB, from memory) as the "session parameters" area, and in a smashed up shared pool that could result in a lot of things being discareded before a large enough single chunk can be coalesced out.
                    >

                    Hi Jonathan, it was very good to know this, particularly the SQLPLUS logon case. Very informative and useful. Just one question, if you prefer to reply. In case of cursor sharing "similar" where you said you could have thousands of same SQL text, how does each text differ from the other when it comes to Oracle picking up the correct or the most appropriate one? Is it based on signature or the mostly used? Thanks very much

                    Thanks to OP for raising this question. And apologies if I have invaded your thread. Let me know if you feel so and I can open a new one but thought it was more relevant to ask it in the same context. Thanks.

                    Regards
                    • 7. Re: Shared pool understanding.
                      Rajib Sutradhar
                      "In case of cursor sharing "similar" where you said you could have thousands of same SQL text, how does each text differ from the other when it comes to Oracle picking up the correct or the most appropriate one? Is it based on signature or the mostly used? Thanks very much"

                      Query having same text having multiple entries in shared pool is differentiate on the basis of child_address. Same hash value for two queries might have multiple pointer. So when you say oracle picking i guess oracle check lots of things before equaling and picking. Details why the same queries have multiple entries can be collected from v$sql_shared_cursor.

                      sqlplus "/as sysdba" can take time because of dns entries also. I have seen such issue on one of my server. This is just for information purpose only.


                      Q>How oracle calculate "Memory Usage %: 80.21 79.60" in awr/stats pack report. Our oracle support team has shared with us below statistics

                      Date Queries with Executions Memory Utilized by
                      1 >1 1 >1
                      6-Sep-11 311309 20115 9.5GB 1.7GB
                      7-Sep-11 280309 23740 9.8GB 2.1 GB

                      Just wondering how he has calculated the above GB figure.

                      I have checked the sprepins.sql but not able to analyze anything. If someone can help.

                      --Rajib

                      Edited by: Rajib Sutradhar on Sep 19, 2011 6:22 AM
                      • 8. Re: Shared pool understanding.
                        Jonathan Lewis
                        VenkatB wrote:

                        Just one question, if you prefer to reply. In case of cursor sharing "similar" where you said you could have thousands of same SQL text, how does each text differ from the other when it comes to Oracle picking up the correct or the most appropriate one? Is it based on signature or the mostly used? Thanks very much
                        Oracle allocates a couple of chunks of memory for each child cursor with a list of all the bind values used to generate the plan, and the optimizer environment when it was generated. It also keeps a hash value for the bind variables and environment so that when a new call comes through it can check very quickly if there are any existing texts which already use exactly the same environment and variables.

                        Regards
                        Jonathan Lewis
                        • 9. Re: Shared pool understanding.
                          888973
                          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349391411093
                          • 10. Re: Shared pool understanding.
                            Pavan Kumar
                            Hi Venkat,

                            Adding to what jonathan said, you can refer to SQL PLAN MANAGEMENT (SPM), in oracle 11g and try to check the
                            v$sql_bind_capture and v$sql_bin_data. so that get the details of present sql details.

                            - Pavan kumar N
                            • 11. Re: Shared pool understanding.
                              Jonathan Lewis
                              Rajib Sutradhar wrote:

                              Q>How oracle calculate "Memory Usage %: 80.21 79.60" in awr/stats pack report. Our oracle support team has shared with us below statistics
                              Date    Queries with Executions    Memory Utilized  by
                              1            >1                  1             >1
                              6-Sep-11    311309      20115          9.5GB        1.7GB
                              7-Sep-11    280309      23740          9.8GB        2.1 GB
                              Just wondering how he has calculated the above GB figure.
                              We can't tell how your dba derived these figures, you'll have to ask him.
                              We can only guess that he's either copied some numbers from a couple of awr or statspack reports, or he's run a query against v$sql or v$sqlstats to checking the columns "executions" and "sharable_mem".

                              If these figures are correct, by the way, you have a huge amount of garbage in your shared pool (most of your SQL is executed only once) so you might as well try using the memory for something else.

                              Regards
                              Jonathan Lewis
                              • 12. Re: Shared pool understanding.
                                Rajib Sutradhar
                                Jonathan,

                                Request you to provide some information about SHARABLE_MEM,PERSISTENT_MEM and RUNTIME_MEM.

                                Regards,
                                Rajib
                                • 13. Re: Shared pool understanding.
                                  698658
                                  Whats wrong with documentation statements ?
                                  SHARABLE_MEM      NUMBER      Amount of shared memory used by the child cursor (in bytes)
                                  PERSISTENT_MEM      NUMBER      Fixed amount of memory used for the lifetime of the child cursor (in bytes)
                                  RUNTIME_MEM      NUMBER      Fixed amount of memory required during the execution of the child cursor
                                  regards
                                  GregG
                                  • 14. Re: Shared pool understanding.
                                    VenkatB
                                    Oracle allocates a couple of chunks of memory for each child cursor with a list of all the bind values used to generate the plan, and the optimizer environment when it was generated. It also keeps a hash value for the bind variables and environment so that when a new call comes through it can check very quickly if there are any existing texts which already use exactly the same environment and variables.
                                    Regards
                                    Jonathan Lewis
                                    >

                                    Thanks very much Jonathan. It makes good sense.

                                    >
                                    Hi Venkat,

                                    Adding to what jonathan said, you can refer to SQL PLAN MANAGEMENT (SPM), in oracle 11g and try to check the
                                    v$sql_bind_capture and v$sql_bin_data. so that get the details of present sql details.

                                    - Pavan kumar N
                                    >

                                    Hi Pavan, V$SQL_BIND_CAPTURE cannot be depended upon always. This view has lots of restrictions and it's not guaranteed that this view will capture all bind variables. I used to depend on this in 10g to write my check scripts but it won't reflect 100% of your situation.

                                    Hope this helps
                                    1 2 Previous Next