1 2 Previous Next 17 Replies Latest reply: May 10, 2011 10:55 AM by jgarry RSS

    Contention for latches related to the shared pool

    user7640966
      Dear Experts,
      I am getting the following section in the ADDM:
      FINDING 1: 80% impact (7733 seconds)
      ------------------------------------
      Contention for latches related to the shared pool was consuming significant
      database time.
      
         NO RECOMMENDATIONS AVAILABLE
      
         ADDITIONAL INFORMATION:
            Waits for "latch: library cache" amounted to 43% of database time.
            Waits for "latch: shared pool" amounted to 36% of database time.
      
         SYMPTOMS THAT LED TO THE FINDING:
            SYMPTOM: Wait class "Concurrency" was consuming significant database
                     time. (82% impact [7848 seconds])
      How do we check which session/sql is causing the issue? What action can be taken to resolve this?

      Thanks
      Anand
        • 1. Re: Contention for latches related to the shared pool
          user12000301
          This may help ..

          http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-latch:-shared-pool-latch
          • 2. Re: Contention for latches related to the shared pool
            user7640966
            Hi,
            The suggestions made in that site are based on v$ views which displays the current status of the instance. The problem is because of this issue, I am not even able to connect and get a db session.

            Is there a mechanism to investigate the issue after it has occurred. For example, I should be able to connect to the db and check what went wrong in the past.

            Thanks
            Anand
            • 3. Re: Contention for latches related to the shared pool
              user7640966
              I guess because of the same issue, users are unable to connect and there are ORA-3136 errors in the alert log file. I have set SGA_TARGET, so the ASMM should be on and i have no control on the shared pool size.

              Appreciate some help.

              -Anand
              • 4. Re: Contention for latches related to the shared pool
                Iordan Iotzov
                If you are licensed to use Active Session History (ASH) you can look at dba_hist_active_sess_history view.
                This view, along with many other DBA_HIST_% views, gives the ability to troubleshoot past problems.
                • 5. Re: Contention for latches related to the shared pool
                  sb92075
                  The problem is because of this issue, I am not even able to connect and get a db session.
                  I find this statement difficult to believe.
                  What happens EXACTLY when you try to login?


                  do as below so we can know complete Oracle version & OS name.

                  Post via COPY & PASTE complete results of
                  SELECT * from v$version;

                  what clues exist within alert_SID.log file?
                  • 6. Re: Contention for latches related to the shared pool
                    user7640966
                    When I try to connect, I do not get connectivity for atleast 10 mins. In the alert log file, I get periodic ORA-3136 error. This is occasional and when I see the AWR file, it shows the latch contention.

                    The database version is 10.2.0.4.

                    Regards,
                    Anand
                    • 7. Re: Contention for latches related to the shared pool
                      sb92075
                      SQL*Net is NOT required to connect to Oracle RDBMS.
                      • 8. Re: Contention for latches related to the shared pool
                        user7640966
                        I meant connecting to the db as sysdba or as any other user.

                        Regards,
                        -Anand
                        • 9. Re: Contention for latches related to the shared pool
                          amardeep.sidhu
                          Can you post the AWR report for that duration.
                          • 10. Re: Contention for latches related to the shared pool
                            user7640966
                            I am posting Top 5 Timed Events from the AWR:
                            Top 5 Timed Events                                         Avg %Total
                            ~~~~~~~~~~~~~~~~~~                                        wait   Call
                            Event                                 Waits    Time (s)   (ms)   Time Wait Class
                            ------------------------------ ------------ ----------- ------ ------ ----------
                            latch: library cache                  7,625      15,044   1973   58.0 Concurrenc
                            latch: shared pool                    2,167      14,635   6753   56.4 Concurrenc
                            latch free                              208       3,099  14898   11.9      Other
                            CPU time                                          2,636          10.2
                            db file sequential read              30,070         239      8    0.9   User I/O
                            Let me know if you need any other section of the AWR.

                            Thanks
                            Anand
                            • 11. Re: Contention for latches related to the shared pool
                              Jonathan Lewis
                              user7640966 wrote:
                              I am posting Top 5 Timed Events from the AWR:
                              Top 5 Timed Events                                         Avg %Total
                              ~~~~~~~~~~~~~~~~~~                                        wait   Call
                              Event                                 Waits    Time (s)   (ms)   Time Wait Class
                              ------------------------------ ------------ ----------- ------ ------ ----------
                              latch: library cache                  7,625      15,044   1973   58.0 Concurrenc
                              latch: shared pool                    2,167      14,635   6753   56.4 Concurrenc
                              latch free                              208       3,099  14898   11.9      Other
                              CPU time                                          2,636          10.2
                              db file sequential read              30,070         239      8    0.9   User I/O
                              Let me know if you need any other section of the AWR.
                              Cache Sizes
                              Load Profile
                              Operating System Stats
                              Latch Actvity (but edit out any lines where the wait time is zero)

                              Also - how many CPUs do you have, and how long is the snapshot interval.

                              Your latch activity times are so bizarre that as a first guess I wonder if you've overallocated memory and spend all your time paging virtual memory on and off disc.

                              Regards
                              Jonathan Lewis
                              • 12. Re: Contention for latches related to the shared pool
                                user7640966
                                Hi Jonathan,
                                I am uploading some more portion of AWR:
                                Cache Sizes
                                ~~~~~~~~~~~                       Begin        End
                                                             ---------- ----------
                                               Buffer Cache:     7,520M     7,520M  Std Block Size:         8K
                                           Shared Pool Size:    12,512M    12,512M      Log Buffer:    14,308K
                                
                                Load Profile
                                ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                                   ---------------       ---------------
                                                  Redo size:             66,926.22              6,498.19
                                              Logical reads:            116,274.96             11,289.70
                                              Block changes:                238.70                 23.18
                                             Physical reads:                 13.84                  1.34
                                            Physical writes:                 10.28                  1.00
                                                 User calls:                254.49                 24.71
                                                     Parses:                 93.14                  9.04
                                                Hard parses:                  3.75                  0.36
                                                      Sorts:                 22.32                  2.17
                                                     Logons:                  0.32                  0.03
                                                   Executes:                323.13                 31.37
                                               Transactions:                 10.30
                                
                                  % Blocks changed per Read:    0.21    Recursive Call %:    70.53
                                 Rollback per transaction %:   86.60       Rows per Sort:    81.90
                                
                                Instance Efficiency Percentages (Target 100%)
                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                                            Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
                                            Library Hit   %:   97.74        Soft Parse %:   95.97
                                         Execute to Parse %:   71.17         Latch Hit %:   99.95
                                Parse CPU to Parse Elapsd %:   17.23     % Non-Parse CPU:   73.47
                                
                                 Shared Pool Statistics        Begin    End
                                                              ------  ------
                                             Memory Usage %:   59.16   61.62
                                    % SQL with executions>1:   94.32   95.75
                                  % Memory for SQL w/exec>1:   93.74   94.90
                                Server has 2 CPU (Quadcore) and 32 GB of RAM on Linux. Snapshot interval is 1 hour. I'll upload other stats also..

                                Thanks
                                Anand
                                • 13. Re: Contention for latches related to the shared pool
                                  P F-Oracle
                                  I would start looking at SGA resize operations as a starting point. Check the frequency of the resize operations during the period you are having concurrency issues.

                                  Edited by: Peter F on May 10, 2011 7:26 PM

                                  If you do see resize operation at the time of the issue, refer to the following note FREQUENT RESIZE OF SGA [ID 742599.1]
                                  • 14. Re: Contention for latches related to the shared pool
                                    user7640966
                                    Updating other info from the AWR:
                                    Latch Activity                           DB/Inst: PROD/PROD  Snaps: 2264-2265
                                    -> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
                                       willing-to-wait latch get requests
                                    -> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                                    -> "Pct Misses" for both should be very close to 0.0
                                    
                                                                              Pct    Avg   Wait                 Pct
                                                                        Get    Get   Slps   Time       NoWait NoWait
                                    Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
                                    ------------------------ -------------- ------ ------ ------ ------------ ------
                                    kokc descriptor allocati         13,881    5.8    0.1   2985            0    N/A
                                    library cache                 3,566,082    1.1    0.2  15044       24,792   11.4
                                    library cache lock              876,886    0.1    0.0      2            0    N/A
                                    shared pool                     789,079    1.2    0.2  14635            0    N/A
                                    user lock                         2,020    1.7    0.6    113            0    N/A
                                              -------------------------------------------------------------
                                    
                                    
                                    
                                    Operating System Statistics               DB/Inst: PROD/PROD  Snaps: 2264-2265
                                    
                                    Statistic                                       Total
                                    -------------------------------- --------------------
                                    BUSY_TIME                                     285,803
                                    IDLE_TIME                                   5,523,433
                                    IOWAIT_TIME                                    44,839
                                    NICE_TIME                                          36
                                    SYS_TIME                                      129,609
                                    USER_TIME                                     154,968
                                    LOAD                                                1
                                    RSRC_MGR_CPU_WAIT_TIME                              0
                                    PHYSICAL_MEMORY_BYTES                  33,724,571,648
                                    NUM_CPUS                                           16
                                    NUM_CPU_SOCKETS                                     2
                                              -------------------------------------------------------------
                                    1 2 Previous Next