13 Replies Latest reply on Nov 25, 2010 11:59 AM by Carlovski

    Need huge shared pool size

    575833
      Hi,

      My database has been having shared pool issue. Our version is 10.2.0.4. We use manually managed SGA instead of ASSM. The related parameters are listed below.

      sga_target = 0
      cursor_sharing = SIMILAR
      shared_pool_size = 10 GB
      shared_pool_reserved_size = 1 GB
      db_cache_size = 6 GB

      Our AWR analysis shows that shared pool is not enough for performance. There are too many SQL are not shared. Unfortunately we do not have more memory to be allocated to the instance.

      Anyone has good suggestion to handle this?

      Thanks
        • 1. Re: Need huge shared pool size
          Dom Brooks
          Have you done any investigation into why SQL is not sharable?
          For example, are you using bind variables?

          This is what you should look at otherwise your SGA may never be big enough whatever memory you throw at it.

          Edited by: dombrooks on Oct 13, 2009 4:44 PM
          • 2. Re: Need huge shared pool size
            William Robertson
            That was my first thought as well. The report is suggesting a larger shared pool based on the number of SQL statements that were not cached, but perhaps if you ever did give it what the report says, the database would then have a CPU problem as it tried to find every SQL statement among gigabytes of shared pool buffers. If you can't fix the application, possibly a smaller SGA would be better, as at least searching it will be quicker. (This is all a wild guess of course, but it's a pattern I've heard of before.)

            Edited by: William Robertson on Oct 13, 2009 5:01 PM
            • 3. Re: Need huge shared pool size
              575833
              As I understand, when cursor_sharing = SIMILAR, the SQL will still be shared in case of only literal value difference - it might need some more shared pool with child cursors. We have tried to reduce the child cursor shared pool usage by setting cursor_sharing = forced or "cursor_sharing = SIMILAR and not collecting histogram", but that generated big performance problem.

              Yes, the application is the biggest problem since we have no control of the SQL codes. Vendor is not cooperating with the code improvement.

              So it sounds to me that you recommend to keep shared pool "small" instead of increasing and eventually getting CPU issue, right. How "small" is better? We aleady allocated 10GB, that is already the biggest I have ever used for all the databases I have administered in my 10 year DBA career.
              • 4. Re: Need huge shared pool size
                706417
                I recommend checking your SQL for binds/no binds:
                http://asktom.oracle.com/pls/asktom/f?p=100:11:224045167773571::::P11_QUESTION_ID:1163635055580

                Regards - Don Lewis
                • 5. Re: Need huge shared pool size
                  575833
                  1. The SQL checking function is not working anymore since current (10G or later) gv$sqlarea show all SQLs as using bind variables (system generated bind variables).

                  2. We know from the application (without checking from backend) that the application is not using bind variable well as I mentioned earlier. So what? We need to know the solution. I have mentioned the the vendor is not cooperating with code changes.
                  • 6. Re: Need huge shared pool size
                    Jonathan Lewis
                    bsong wrote:
                    As I understand, when cursor_sharing = SIMILAR, the SQL will still be shared in case of only literal value difference - it might need some more shared pool with child cursors. We have tried to reduce the child cursor shared pool usage by setting cursor_sharing = forced or "cursor_sharing = SIMILAR and not collecting histogram", but that generated big performance problem.
                    There are (at least) three different issues that conspire to cause you problems.

                    a) Literal string SQL is typically non-sharable SQL - but can get the best execution plans each time, at a cost in optimisation overheads (CPU, latching, memory).

                    b) If you set cursor_sharing = force, statement which differ only in literal values become "the same" because of bind variable substitution into the text. But this means the plan for every execution of the statement will be the same (until something flushes the current plan from memory and a new plan appears). With bind variables in place, Oracle uses bind variable peeking to find that values that should be used for the first optimisation. This means that at random intervals you may get a plan which was great for the first person and terrible for everyone else. Typical causes of extreme variation: queries that use widely varying date ranges, queries against columns on which you have built histograms, queries where the parition key of a partitioned object. Typical ymptoms: wild variations in performance, but only a very small number of copies (often only one) of substituted SQL statements in the library cache.

                    c) If you set cursor_sharing = similar, Oracle does bind variable substitution - then checks to see if any of the substitutions are for range-based predicates (e.g. between two date values), are predicates on columns with histograms or partitioning keys. If any of these are seen then Oracle re-optimises the query if it hasn't got a plan for the same set of values already. Typical symptoms: large numbers of copies of substituted SQL statements in the library cache.


                    You've tried cursor_sharing = exact - but did you get rid of histograms at the same time. If not this could explain performance problems (although this combination should not cause an extreme growth in library cache usage).

                    You've tried cursor_sharing = similar with no histograms - but were you still getting multiple copies of substituted queries due to range-based, or partition key, predicates ?

                    If I were examining your system, I would check the AWR report for SQL ordered by version count to see if there were any pattern in the SQL that had very large numbers of copies. I would also check the SQL ordered by elapsed time and run a few checks for expensive SQL to see the number of different plans they had recorded in the AWR - dbms_xplan.display_awr({sql_id},null,null,'peeked_binds').


                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk

                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                    fixed format
                    .
                    
                    
                    "Science is more than a body of knowledge; it is a way of thinking" 
                    Carl Sagan
                    
                    
                    
                    
                    
                    
                    Cursor_sharing = similar                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                    • 7. Re: Need huge shared pool size
                      575833
                      Thanks Jonathon for your great response.

                      Our current setting is cursor_sharing= similar and not collecting histograms only for some tables which are frequently hit, the size is not too big, and column values are kind of evenly distributed.

                      From the posts so far, our setting is the best we can do from the backend without really fix the code issue from the front end application.
                      • 8. Re: Need huge shared pool size
                        Ospin
                        Hi,

                        First follow Mr. Lewis recomendation ALWAYS. But let me add another view to see. V$SQL_SHARED_CURSOR. So get the query with the most number of childs, you can use this two views v$sql or v$sqlstats, even you can use v$sqlarea. v$sqlarea have parents cursor. So excute this:

                        select sql_id, tot
                        from (select sql_id, version_count tot
                        from v$sqlstats
                        order by 2 desc)
                        where rownum <= 10
                        /

                        So, here you have top 10 queries non shared. After you take a look, sometimes you can't know why they can't be share. Sometimes does not appears, but in this view (V$SQL_SHARED_CURSOR) you have more than 50 reasons.
                        Personally I don't really like CURSOR_SHARING=SIMILAR, even Oracle does not recommends his use (see Metalink note 396940.1). In this document you can find "The implementation of SIMILAR is not optimal". And well, something happened to me a few months ago. I execute a "select count(8) from v$sql" and "select count(8) from v$sqlarea" in a production database with horrible problems (not only shared pool problems), the result for the first query was over 74.000 (I don't remember exactly) and the second less than 5000.
                        I found some sql statement with more than 5000 child cursor, and even looking into v$sql_shared_cursor, I couldn't found the reason the creation of so many child. But "bazinga", less childs was created after change the way we collect the stats, without histograms. :) and drop that horrible "METHOD_OPT => FOR ALL COLUMNS SIZE AUTO"
                        Oracle talk about memory leaks, and I found two memory leaks provoked by SIMILAR. And one of them gave me an instance crash as a result (was a BUG fixed in 10.2.0.4 on Windows).
                        By the way, let me ask you something. How can you see you need more shared pool with an AWR report? I'm bit curios about it, to know wich is the part of the AWR report you interpret, that tells you need to still increase the shared pool.
                        Oh, just another cool stuff. Please read Tanel Poder's blog, you'll find a post, that say the shared pool can even content data blocks in some special case. cool !!! :D

                        John Ospino Rivas

                        PD: Hope this can help you
                        • 9. Re: Need huge shared pool size
                          575833
                          Thanks for your great response. But here is our current situation

                          1. We already knew what SQLs are not sharing well and there is nothing we can do to change the SQLs since the application vendor is not cooperating
                          2. I think CURSOR_SHARING=SIMILAR is the best we can use since "FORCE" will make the performance suffer, "EXACT' will reqire even more shared pool
                          3. "METHOD_OPT => FOR ALL COLUMNS SIZE AUTO" is good for most of our tables. We do not use it for only some tables to reduce the child cursors.
                          4. Your are right about the memory leak bugs. The purpose for us to use manual management of SGA is to avoid the memory leak bugs.
                          5. "How can you see you need more shared pool with an AWR report? " - because there are too much hard parsing and shared pool latch contention
                          • 10. Re: Need huge shared pool size
                            Ospin
                            <blockquote>

                            bsong wrote:
                            3. "METHOD_OPT => FOR ALL COLUMNS SIZE AUTO" is good for most of our tables.
                            We do not use it for only some tables to reduce the child cursors.

                            </blockquote>


                            There're amazing documents, can help you with you stats and can explain why I don't like <strong>METHOD_OPT => AUTO</strong>:

                            http://www.jlcomp.demon.co.uk/stats.doc
                            Metalink Note 557594.1
                            http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/

                            <blockquote>

                            I wrote:
                            5. "How can you see you need more shared pool with an AWR report? " -
                            bsomg wrote:
                            because there are too much hard parsing and shared pool latch contention

                            </blockquote>

                            Could you please copy&paste some information of your AWR report.

                            - Header (Begin Snap, End Snap, Elapsed, DB Time)
                            - Report Summary
                            · Load profile (User calls, Parses, Hard parses, Executes)
                            · Instance Efficiency Percentages
                            - Wait Events (CPU time or Top 5 Timed Events in Report Summary)
                            - Time Model Statistics
                            - Instance Activiti Statistics
                            · CPU used by this session
                            · parse time cpu
                            · recursive cpu usage
                            - Latch Sleep Breakdown
                            · library cache
                            - Library Cache Activity
                            · SQL AREA (Pct Miss)

                            Only if you want. It's just I'm still impresive about a 10G shared pool with problems. A friend of mine said, almost every application inn the world can works well with 2G shared pool, and I use to believe him. :D

                            It doesn't matter if you don't want.

                            John Ospino Rivas

                            Edited by: Ospino on Oct 14, 2009 8:26 PM
                            • 11. Re: Need huge shared pool size
                              575833
                              Here is part of the ADDM report for the 5 hour interval

                              FINDING 2: 38% impact (8216 seconds)
                              ------------------------------------
                              Hard parses due to an inadequately sized shared pool were consuming
                              significant database time.

                              RECOMMENDATION 1: DB Configuration, 22% benefit (4752 seconds)
                              ACTION: Increase the shared pool size by setting the value of parameter
                              "shared_pool_size" to 15360 M.

                              ADDITIONAL INFORMATION:
                              The value of parameter "shared_pool_size" was "10240 M" during the
                              analysis period.

                              SYMPTOMS THAT LED TO THE FINDING:
                              SYMPTOM: Hard parsing of SQL statements was consuming significant
                              database time. (16% impact [3514 seconds])
                              SYMPTOM: Contention for latches related to the shared pool was
                              consuming significant database time. (15% impact [3220
                              seconds])
                              INFO: Waits for "cursor: pin S wait on X" amounted to 14% of
                              database time.
                              SYMPTOM: Wait class "Concurrency" was consuming significant
                              database time. (15% impact [3275 seconds])
                              • 12. Re: Need huge shared pool size
                                Ospin
                                First depends on for SO (in HP-UX happened), but you could experiment some problems with pwait system call, how show a huge amount of "cursor: pin S wait on X". But this is a BUG fixed with HP-UX patch. Shreky (my best friend, I'm the only one who call him like that : ) had this problem.
                                But doesn't look like you have this problem.

                                You're showing me an ADDM report. Could you please show me a AWR report?

                                If you want, generate a report for 1 hours with 10 minutes inteval time between snapshots.

                                exec dbms_workload_repository.modify_snapshot_settings(interval => 10);

                                and then:

                                @?/rdbms/admin/awrrpt

                                And then copy&paste the information I was asking to you. If is not possible, it's OK.

                                John Ospino Rivas
                                • 13. Re: Need huge shared pool size
                                  Carlovski
                                  You are having similar problems to us with our database.
                                  ADDM will keep reporting that you need a bigger shared pool, but it's not being very smart about it. Yes you are filling up the shared pool, but even if you had more space for the sql statements, what is the chance that they will actually get reused?
                                  I'm keen for us to reduce the size of our Shared Pool (Currently ~6gb) but it's a hard sell as we have had issues with shared pool fragmentation in the past and 'fixed' it by throwing more space at it.

                                  Carl