6 Replies Latest reply: Apr 4, 2011 11:34 AM by Kevin2010 RSS

    11.2 : library cache: mutex X

      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 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 only:

      -db file sequential read

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

      Please help


        • 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:
          • 2. Re: 11.2 : library cache: mutex X
            Timur Akhmadeev
            groxy wrote:
            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
            • 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
              • 4. Re: 11.2 : library cache: mutex X

                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


                past the whole mutex section in the thread


                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

                  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
                    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?