9 Replies Latest reply: Feb 9, 2013 7:43 AM by Chuck1958 RSS

    Reducing shared pool latch contention

    Chuck1958
      I see a lot of shared pool latch contention on my 11gR2 database because there are over 100 identical schemas that run identical SQL. This causes lots of child cursors to be created. Scheduled jobs kick off hourly for every schema at the same time and that produces contention for shared pool latches for the same parent cursor.

      I want to reduce that contention. Here's my question...

      If I make the SQL text unique by either
      (a) changing the object referenbes from "TableName" to "SchemaName.TableName", or
      (b) adding a comment to the SQL that includes the SchemaName

      ...will that cause a separate cursor to be created in the shared pool?

      I know in older versions even a change in a comment would cause Oracle to treat it as a separate statement. Is that still true in 11gR2?

      FWIW cursor_sharing is set to EXACT.
        • 1. Re: Reducing shared pool latch contention
          sb92075
          >
          there are over 100 identical schemas that run identical SQL.
          show with examples just 2 of "identical schemas"

          To me that is an oxymoron
          • 2. Re: Reducing shared pool latch contention
            jgarry
            Kinda sounds like you got it backwards? See cursou mutex event for why I might say that. If the contention is because you have too many cursors, wouldn't making more be the wrong thing to do?

            Which patch level are you on? See http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/

            Edit: MOS High SQL Version Counts - Script to determine reason(s) [ID 438755.1] might be useful, too.

            Edited by: jgarry on Feb 8, 2013 10:01 AM
            • 3. Re: Reducing shared pool latch contention
              jgarry
              sb92075 wrote:
              >
              there are over 100 identical schemas that run identical SQL.
              show with examples just 2 of "identical schemas"

              To me that is an oxymoron
              Part of my job is to be sure metadata is the same across some schemata. Isn't that what an identical schema means? It surely doesn't mean the data.
              • 4. Re: Reducing shared pool latch contention
                Chuck1958
                I can't post examples because we sell a proprietary software product and I cannot reveal the details. We host that product for over 100 customers. Each customer gets their own schema.

                What I mean by 100 identical schemas is that the structure is identical - tables, columns, indexes, views, etc. Only the data is different.

                This query demonstrates what I'm talking about. Here's a single sql_id with 468 child cursors.

                select count(*)
                from v$sql
                where sql_id = '7p8n9r993gsgg'
                /

                COUNT(*)
                --------
                468
                • 5. Re: Reducing shared pool latch contention
                  P.Forstmann
                  Chuck1958 wrote:
                  If I make the SQL text unique by either
                  (a) changing the object referenbes from "TableName" to "SchemaName.TableName", or
                  (b) adding a comment to the SQL that includes the SchemaName

                  ...will that cause a separate cursor to be created in the shared pool?

                  I know in older versions even a change in a comment would cause Oracle to treat it as a separate statement. Is that still true in 11gR2?
                  Yes:
                  SQL> select * from v$version;
                  
                  BANNER
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  CORE    11.2.0.1.0      Production
                  TNS for Linux: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  
                  SQL> show parameter cursor_sharing
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  cursor_sharing                       string      EXACT
                  SQL> select * /* schema1 */ from t where x=0;
                  
                  no rows selected
                  
                  SQL> select * /* schema2 */ from t where x=0;
                  
                  no rows selected
                  
                  SQL> select sql_id, child_number, sql_text
                    2  from v$sql
                    3  where sql_text like '%/* schema%';
                  
                  SQL_ID        CHILD_NUMBER SQL_TEXT
                  ------------- ------------ ----------------------------------------
                  4d03bxnvbw4ac            0 select * /* schema2 */ from t where x=0
                  76xtkkpvfqx1h            0 select * /* schema1 */ from t where x=0
                  b3zjvv0cfbhrx            0 select sql_id, child_number, sql_text fr
                                             om v$sql where sql_text like '%/* schema
                                             %'
                  • 6. Re: Reducing shared pool latch contention
                    Chuck1958
                    I am on 11.2.0.1.

                    I checked v$sql_shared_cursor and the reason for all of the child cursors is "TRANSLATION_MISMATCH". If I understand it correctly, that basically means what I said in the OP. The SQL is the same, but the objects it's operating on are different (i.e. 100 identical schemas running the same SQL at the same moment).

                    I think whats happening is 100 requests all hit the server at the same exact moment to execute a particular SQL statement. Its already cached so a latch is taken out to lock the cursor and determine which child cursor to use. With 100 sessions all doing this simultaneously, there is contention for that latch.
                    • 7. Re: Reducing shared pool latch contention
                      jgarry
                      Hmm, well you could probably use Tanel Poder's tools (snapper, latchprofx) to more clearly demonstrate what you already know.

                      Perhaps the initial answer is twofold:
                      Don't run them all at the same time.
                      Patch up and see how the problem changes with all the fixes that have been put in place, watching for any adaptive cursor sharing features that make it worse.

                      There are probably better answers from those more deeply into this than me.
                      • 8. Re: Reducing shared pool latch contention
                        Nikolay Savvinov
                        Hi,

                        it should suffice to fully qualify table name with the schema prefix to alleviate your problem, but I wonder why would you need 100+ identical schemas in the same database?

                        Best regards,
                        Nikolay
                        • 9. Re: Reducing shared pool latch contention
                          Chuck1958
                          100 different customers running the same application each with their own data.