6 Replies Latest reply on Apr 4, 2011 4:34 PM by Kevin2010

    11.2 : library cache: mutex X

    606914
      Hi,
      I am using 11.2 on Linux Redhat 5.3 (64-bit)

      I have simple scenario. I am performing many (~10.000 ) insertions to a table e.g. TEST_TABLE and after insertion of a single row, a commit is issued. On the table TEST_TABLE, a on-commit materialized view is built. During the process the performance of the database decreases ( starting from 10 commits per second down to 1 commit). Which finally causes the database to be “saturated” Restart of the database solves the problem till next "insertion operation"

      I reply the scenario on the same machine having installed Oracle 10.2.0.4 and the problem does not occur. During the whole insertion, there is constant number of transaction per second and it does not decrease!

      In 11.2 I am getting the following waits (in WaitHistory):

      - library cache: mutex X

      - db file sequential read

      In 10.2.0.4 only:

      -db file sequential read

      What can cause the difference? Any initialization parameters may help?

      Please help

      Regards

      Groxy
        • 1. Re: 11.2 : library cache: mutex X
          Dom Brooks
          In 11g, this mutex has replaced the library cache latch.
          Actually, they started to replace in 10gR2 but it depends on your setting of kksuse_mutex_pin

          See here for more info:
          http://blog.tanelpoder.com/2008/08/03/library-cache-latches-gone-in-oracle-11g/
          • 2. Re: 11.2 : library cache: mutex X
            Timur Akhmadeev
            Hi,
            groxy wrote:
            Hi,
            I am using 11.2 on Linux Redhat 5.3 (64-bit)

            I have simple scenario.
            please post it here using proper tags: {noformat}
            code here
            {noformat}
            • 3. Re: 11.2 : library cache: mutex X
              Tanel Poder
              I would run snapper on that session during normal operation and during the slowness and post the results here. This should give additional info about what's going on here (cursor flushes, hard parses for example).

              Additonally, you could run this just when you kick your code running after restart and sample this every few second to see whether the number of child cursors is growing:

              SELECT COUNT(*) FROM v$sql WHERE sql_id = '&sql_id_of_your_query';

              The hypothesis here is that the slowness happens because the on-commit-refreshes (or bind variables in inserts (you're using binds, right?)) cause existing cursors to be unusable for new executions and new child cursors are constantly parsed under the parent. And this causes the next library cache lookups take longer and longer and when a new child cursor has to be compiled again, finding memory from shared pool gets harder (and causes more and more chunk flushes during memory search).

              This is just a hypothesis based on my previous experience with similar problems, but there's no way to be sure without gathering additional data, so I'd run the query above and snapper on the session to get an execution profile.

              --
              Tanel Poder
              http://tech.e2sn.com
              http://blog.tanelpoder.com
              • 4. Re: 11.2 : library cache: mutex X
                askraks
                Hi,


                You can see my action plan

                library cache: mutex X
                According to me Latches are replaced by Mutex not mutex replaced by latch.

                How to analyze the mutex contention?

                Please generate the AWR report for the duration of 60min when you see mutex x wait event

                there will be section called ' mutex sleep section'
                Tell on which function its have more sleep and i will help

                or

                past the whole mutex section in the thread

                or

                you can generate the system state dump and you can analzye the same


                Kind Regards,
                Rakesh Jayappa
                • 5. Re: 11.2 : library cache: mutex X
                  824753
                  Hi

                  I am getting Mutex X and my awr section is as follows , can you tell me what is the issue

                  Mutex Type Location Sleeps Time (ms)
                  --------------------- -------------------------------- ------------ ------------
                  Library Cache kglget1 1 49,355,999 2,490,453
                  Library Cache kgllkdl1 85 4,779,939 720,141
                  Cursor Pin kksfbc [KKSCHLFSP2] 75,321 2,228
                  Library Cache kgllkc1 57 65,367 4,075
                  Cursor Pin kksLockDelete [KKSCHLPIN6] 61,769 3,263
                  Library Cache kglpndl1 95 5,586 296
                  Library Cache kglpin1 4 5,555 238
                  Library Cache kglpnal1 90 4,073 211
                  Cursor Pin kksfbc [KKSCHLPIN1] 45 3
                  Cursor Pin kkslce [KKSCHLPIN2] 4 0
                  Library Cache kglGetHandleReference 124 1 0
                  Library Cache kglhdgn1 62 1 0
                  Library Cache kglhdgn2 106 1 0
                  -------------------------------------------------------------
                  • 6. Re: 11.2 : library cache: mutex X
                    Kevin2010
                    Tanel: I can confirm that i see 700 child cursors coming out of same sql_id. Can you please post the resolution to this issue?

                    Thanks
                    Kevin