This discussion is archived
13 Replies Latest reply: Jul 25, 2012 5:00 AM by 678171 RSS

Concurrency - library cache: mutex X

MR139 Newbie
Currently Being Moderated
We have Oracle 11gR2 (11.2.0.1) RAC (2-Node) database on HP-UX 11.31

We are having Active Sessions Waiting: Concurrency -->  library cache: mutex X

All the connections are evenly distributed to both the nodes but we see more load on node-1 (95% CPU). node-1 is less than 50% CPU.
And in node-1 the CPU is high because of Concurrency -->  library cache: mutex X wait. and there is no blocking sessions on the database. Dose anyone had similar issue?
  • 1. Re: Concurrency - library cache: mutex X
    sb92075 Guru
    Currently Being Moderated
    MR139 wrote:
    And in node-1 the CPU is high because of Concurrency -->  library cache: mutex X wait. and there is no blocking sessions on the database. Dose anyone had similar issue?
    please post SQL & results that shows above is true.
  • 2. Re: Concurrency - library cache: mutex X
    rahulras Explorer
    Currently Being Moderated
    Some time back I came across this, where my AWR was showing very high "library cache: mutex X".
    End of the analysis, that was due to many sessions trying to use/update same block. Certain set of processes were updating data in a way that was doing lot if index splitting and changes to the index, concurrently.
    We stopped few of the processes and things were much better (later on we fixed our application).
  • 3. Re: Concurrency - library cache: mutex X
    863768 Newbie
    Currently Being Moderated
    Providing Instance effeciency percentages will help.Also look at cusror_sharing parameter and session cached cursors.
    I encountered a issue similat to this after upgrade from 10g to 11g leaving cursor_sharing value to similar.(which shouldnot be).
    This may be due to execessive soft parsing as well .Take a lool at session cached cusor value if it less you may need to increase

    Edited by: rajdhanvi on Jul 4, 2012 8:37 AM
  • 4. Re: Concurrency - library cache: mutex X
    karan Pro
    Currently Being Moderated
    I think this wait event is not available in DOC.. I would recommend you see MOS Note WAITEVENT: 'library cache: mutex X' ID 727400.1

    Regards
    Karan
  • 5. Re: Concurrency - library cache: mutex X
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    is it consistently this high? Can you post excepts from an AWR report for that node covering:

    1) workload profile
    2) top timed events
    3) OS and instance CPU usage
    4) top SQL

    Also, please post some sample entries from the ASH.

    Best regards,
    Nikolay
  • 6. Re: Concurrency - library cache: mutex X
    MR139 Newbie
    Currently Being Moderated
    I think its because of the Memory size. we have too low shared pool, so I thought of enabling AMM and increase the SGA Size. But I woild like to have some suggestions.


    Cache Sizes                       Begin        End
    ~~~~~~~~~~~                  ---------- ----------
                   Buffer Cache:       464M       464M  Std Block Size:         8K
               Shared Pool Size:       720M       720M      Log Buffer:     7,892K
    
    Load Profile              Per Second    Per Transaction   Per Exec   Per Call
    ~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
          DB Time(s):                1.3                0.5       0.00       0.00
           DB CPU(s):                1.0                0.4       0.00       0.00
           Redo size:           10,348.4            4,055.3
       Logical reads:            9,824.0            3,849.8
       Block changes:               60.7               23.8
      Physical reads:               28.5               11.2
     Physical writes:               14.2                5.6
          User calls:              608.6              238.5
              Parses:              749.8              293.8
         Hard parses:              333.3              130.6
    W/A MB processed:                3.0                1.2
              Logons:                0.2                0.1
            Executes:            1,045.4              409.7
           Rollbacks:                0.0                0.0
        Transactions:                2.6
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:   99.99
                Buffer  Hit   %:   99.90    In-memory Sort %:  100.00
                Library Hit   %:   88.84        Soft Parse %:   55.54
             Execute to Parse %:   28.27         Latch Hit %:   99.85
    Parse CPU to Parse Elapsd %:   78.58     % Non-Parse CPU:   85.27
    
     Shared Pool Statistics        Begin    End
                                  ------  ------
                 Memory Usage %:   90.49   90.17
        % SQL with executions>1:   50.58   41.89
      % Memory for SQL w/exec>1:   63.73   42.30
    
    Top 5 Timed Foreground Events
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                               Avg
                                                              wait   % DB
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            7,528          79.2
    log file sync                        18,587         258     14    2.7 Commit
    library cache pin                   406,927         207      1    2.2 Concurrenc
    library cache lock                  263,777         147      1    1.5 Concurrenc
    db file sequential read              27,589         146      5    1.5 User I/O
    Host CPU (CPUs:    8 Cores:    8 Sockets:    2)
    ~~~~~~~~         Load Average
                   Begin       End     %User   %System      %WIO     %Idle
               --------- --------- --------- --------- --------- ---------
                    1.42      1.36      19.0       9.2       7.7      71.8
    
    Instance CPU
    ~~~~~~~~~~~~
                  % of total CPU for Instance:      13.9
                  % of busy  CPU for Instance:      49.2
      %DB time waiting for CPU - Resource Mgr:       0.0
    
    Memory Statistics
    ~~~~~~~~~~~~~~~~~                       Begin          End
                      Host Mem (MB):     65,434.5     65,434.5
                       SGA use (MB):      1,401.8      1,401.8
                       PGA use (MB):        694.0        719.8
        % Host Mem used for SGA+PGA:         3.20         3.24
    
    RAC Statistics  DB/Inst: PROD/prod1  Snaps: 13642-13644
    
                                    Begin   End
                                    ----- -----
               Number of Instances:     2     2
    
    
    Global Cache Load Profile
    ~~~~~~~~~~~~~~~~~~~~~~~~~                  Per Second       Per Transaction
                                          ---------------       ---------------
      Global Cache blocks received:                  7.93                  3.11
        Global Cache blocks served:                  4.05                  1.59
         GCS/GES messages received:                410.44                160.84
             GCS/GES messages sent:                432.50                169.48
                DBWR Fusion writes:                  0.52                  0.20
     Estd Interconnect traffic (KB)                260.45
    
    
    Global Cache Efficiency Percentages (Target local+remote 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer access -  local cache %:   99.82
    Buffer access - remote cache %:    0.08
    Buffer access -         disk %:    0.10
    
    
    Global Cache and Enqueue Services - Workload Characteristics
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                         Avg global enqueue get time (ms):      0.0
    
              Avg global cache cr block receive time (ms):      2.7
         Avg global cache current block receive time (ms):      1.7
    
                Avg global cache cr block build time (ms):      0.1
                 Avg global cache cr block send time (ms):      0.1
          Global cache log flushes for cr blocks served %:     10.2
                Avg global cache cr block flush time (ms):     17.6
    
             Avg global cache current block pin time (ms):      0.2
            Avg global cache current block send time (ms):      0.1
     Global cache log flushes for current blocks served %:      6.9
           Avg global cache current block flush time (ms):     17.1
    
    Global Cache and Enqueue Services - Messaging Statistics
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                         Avg message sent queue time (ms):      0.0
                 Avg message sent queue time on ksxp (ms):      0.3
                     Avg message received queue time (ms):      0.0
                        Avg GCS message process time (ms):      0.1
                        Avg GES message process time (ms):      0.1
    
                                % of direct sent messages:    66.63
                              % of indirect sent messages:    33.19
                            % of flow controlled messages:     0.17
              -------------------------------------------------------------
    
    Cluster Interconnect
    ~~~~~~~~~~~~~~~~~~~~
                                        Begin                             End
                 --------------------------------------------------   -----------
    Interface    IP Address      Pub Source                           IP  Pub Src
    ----------   --------------- --- ------------------------------   --- --- ---
    lan1         192.168.74.4    N   Oracle Cluster Repository
    Time Model Statistics           DB/Inst: PROD/prod1  Snaps: 13642-13644
    -> Total time in database user-calls (DB Time): 9506.3s
    -> Statistics including the word "background" measure background process
       time, and so do not contribute to the DB time statistic
    -> Ordered by % or DB time desc, Statistic name
    
    Statistic Name                                       Time (s) % of DB Time
    ------------------------------------------ ------------------ ------------
    DB CPU                                                7,528.4         79.2
    sql execute elapsed time                              7,396.1         77.8
    parse time elapsed                                    5,290.4         55.7
    hard parse elapsed time                               4,789.4         50.4
    PL/SQL compilation elapsed time                       3,282.3         34.5
    hard parse (sharing criteria) elapsed time            1,181.0         12.4
    hard parse (bind mismatch) elapsed time               1,162.1         12.2
    PL/SQL execution elapsed time                           705.3          7.4
    RMAN cpu time (backup/restore)                           41.9           .4
    inbound PL/SQL rpc elapsed time                          14.7           .2
    connection management call elapsed time                  10.1           .1
    failed parse elapsed time                                 2.6           .0
    sequence load elapsed time                                1.7           .0
    repeated bind elapsed time                                1.0           .0
    DB time                                               9,506.3
    background elapsed time                               2,471.8
    background cpu time                                     476.3
              -------------------------------------------------------------
  • 7. Re: Concurrency - library cache: mutex X
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    Your problem is quite obvious -- you are doing over 300 hard parses per second, i.e. 1 hard parse for every 3 executions (!!). Your hard parse time is accountable for most of the DB time ( 4,789.4 s out of 7,528.4 s). That, btw, could cause all sorts of library cache contention, including library cache: mutex X (and also explains why library cache: lock and library cache: pin are in the top-5 list, even though with rather small percentages).

    Fix your parsing problem, and that will make all the differences in the world.

    Best regards,
    Nikolay
  • 8. Re: Concurrency - library cache: mutex X
    Nikolay Savvinov Guru
    Currently Being Moderated
    some feedback would have been nice
  • 9. Re: Concurrency - library cache: mutex X
    MR139 Newbie
    Currently Being Moderated
    I really appreciate you time, I am sorry for not replying, Because I didn't get a chance to do any change/test in the database yesterday.

    We did enabled AMM and increased shared pool to 1G. Now it looks better but not 100%. I am not sure how to find and resolve the hard parse, I guess need to check the application code, so we are working with s/w vendor.

    Thank you so much for you help and sorry for late reply.
  • 10. Re: Concurrency - library cache: mutex X
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    thanks for the update.

    >
    We did enabled AMM and increased shared pool to 1G.
    I don't think how this would be help you with your issue. In fact, don't the two parts of this sentence mutually exclude each other?
    AMM means that memory is managed automatically, i.e. you don't get to decide how big your shared pool is, Oracle decides that for you.
    Now it looks better but not 100%.
    Looks like placebo effect to me.
    I am not sure how to find and resolve the hard parse, I guess need to check the application code, so we are working with s/w vendor.
    The first step would be to identify where all those unsharable SQL statements come from. The most likely possibility is not using binds, but there are
    other possibilities as well (e.g. an ACS bug).

    I would start by looking at:

    1) SQL by parse calls
    2) multiversioned cursors
    3) statements with similar text, different sql_id's and same force_matching_signature

    Also, take a look at http://savvinov.com/2012/05/29/where-awr-cant-help-identifying-top-sql-in-absence-of-cursor-sharing/
    Thank you so much for you help and sorry for late reply.
    You're welcome.

    Best regards,
    Nikolay
  • 11. Re: Concurrency - library cache: mutex X
    MR139 Newbie
    Currently Being Moderated
    Thanks for your quick reply, actually we did these changes as per the vendor recommendation. I will check the doc and find the SQL's.

    Thanks once again.
  • 12. Re: Concurrency - library cache: mutex X
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    MR139 wrote:
    I think its because of the Memory size. we have too low shared pool, so I thought of enabling AMM and increase the SGA Size. But I woild like to have some suggestions.
    It's possible that this is part of your problem - and we might ask why you're giving Oracle a couple of GB when your machine has 64 GB of RAM.
    However, this AWR report doesn't seem to be from the machine with the mutex problem, and although you have a suspiciously high hard parse COUNT, the time model doesn't point a finger at that being the entire cause of your problems. We could do with seeing the "parse time elapsed" and "parse time CPU" figures from the Instance Activity to confirm, but note the breakdown of parse time and CPU time in the time model stats:

    Cache Sizes                       Begin        End
    ~~~~~~~~~~~                  ---------- ----------
    Buffer Cache:       464M       464M  Std Block Size:         8K
    Shared Pool Size:       720M       720M      Log Buffer:     7,892K
    
    Memory Statistics
    ~~~~~~~~~~~~~~~~~                       Begin          End
    Host Mem (MB):     65,434.5     65,434.5
    SGA use (MB):      1,401.8      1,401.8
    PGA use (MB):        694.0        719.8
    % Host Mem used for SGA+PGA:         3.20         3.24
    
    
    Statistic Name                                       Time (s) % of DB Time
    ------------------------------------------ ------------------ ------------
    DB CPU                                                7,528.4         79.2
    parse time elapsed                                    5,290.4         55.7
    hard parse elapsed time                               4,789.4         50.4
    hard parse (sharing criteria) elapsed time            1,181.0         12.4
    hard parse (bind mismatch) elapsed time               1,162.1         12.2
    PL/SQL compilation elapsed time 3,282.3 34.5
    -------------------------------------------------------------

    The "sharing criteria" and "bind mismatch" times are responsible for nearly 50% of your hard parse time - so although a big chunk of hard parse time MIGHT be related to losing and re-optimising SQL plans, there are other parse-related issues. The fact that you have such a lot of PL/SQL compilation time suggests you might have lots of anonymous pl/sql blocks passing literals that are then used as variables to SQL statements - perhaps with RLS included to cause sharing problems (although sharing criteria problems could have several other causes).

    On top of everything else, you might simply want to look for CPU intensive SQL because I think a lot of your CPU is NOT spent on parsing.

    Regards
    Jonathan Lewis
  • 13. Re: Concurrency - library cache: mutex X
    678171 Newbie
    Currently Being Moderated
    Hi,

    We faced the similar kind of issues after upgrading the database to Oracle 11gR2. Our's is a mission control online trading application. It was working fine in Oracle 10.2.0.3 and 10.2.0.5. We rectified this issue by setting the parameter in the proc program as follows

    Earlier
    SET TRANSACTION READ/WRITE
    Changed to
    SET TRANSACTION READ WRITE.

    Would like to recommend the shared_cache to keep it to optimal size.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points