1 2 Previous Next 28 Replies Latest reply: Jun 24, 2009 1:10 PM by Randolf Geist RSS

    Latch problem.

    585227
      Hi All,

      I've severe latching problem in my database, when I look in AWR but confused what is causing it.
      Can someone guide me on this?
      NoWait Waiter
      Latch Name Where Misses Sleeps Sleeps
      ------------------------ -------------------------- ------- ---------- --------
      In memory undo latch ktiFlush: child 0 5 0
      In memory undo latch kturbk 0 2 2
      cache buffers chains kcbchg: kslbegin: bufs not 0 573 69
      cache buffers chains kcbgtcr: fast path 0 380 554
      cache buffers chains kcbgtcr: kslbegin excl 0 298 547
      cache buffers chains kcbrls: kslbegin 0 183 153
      cache buffers chains kcbzgb: scan from tail. no 0 83 0
      cache buffers chains kcbibr 0 31 40
      cache buffers chains kcbchg: kslbegin: call CR 0 10 70
      cache buffers chains kcbgcur: kslbegin 0 4 13
      cache buffers chains kcbget: pin buffer 0 3 72
      cache buffers chains kcbnew: new latch again 0 3 18
      cache buffers chains kcbzwb 0 2 3
      cache buffers chains kcbbxsv 0 1 3
      cache buffers chains kcbcge 0 1 5
      cache buffers lru chain kcbzgws 0 45 0
      cache buffers lru chain kcbibr 0 43 97
      cache buffers lru chain kcbo_link_q 0 20 5
      cache buffers lru chain kcbw_quiesce_granule 0 5 0
      cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 1 0
      enqueues ksqdel 0 1 0
      kks stats kks stats alloc/free 0 1 1
      library cache kglobpn: child: 0 2,978 4,471
      library cache kgldti: 2child 0 2,417 12
      library cache kglLockCursor 0 1,604 3,398
      library cache kglpin 0 1,426 538
      library cache kglpndl: child: after proc 0 1,185 7
      library cache kglhdgn: child: 0 766 1,485
      library cache kglpnp: child 0 618 8,902
      library cache kglhdgc: child: 0 331 0
      library cache kgldte: child 0 0 95 809
      library cache kglpndl: child: before pro 0 92 1,800
      library cache kglic 0 50 0
      library cache kglnti 0 30 0
      library cache kglati 0 28 0
      library cache kglobld 0 15 21
      library cache kglScanDependency 0 7 2
      library cache kglukp: child 0 6 5
      library cache kglhdbrnl: child 0 1 0
      library cache lock kgllkdl: child: no lock ha 0 10,017 217
      library cache lock kgllkdl: child: cleanup 0 87 119
      library cache lock kgllkal: child: multiinsta 0 80 48
      library cache lock alloc kgllkget 0 1 1
      library cache pin kglpndl 0 34 6
      library cache pin kglpnp: child 0 17 23
      library cache pin kglpnal: child: alloc spac 0 13 35
      object queue header oper kcbo_switch_cq 0 12 5
      object queue header oper kcbw_link_q 0 9 11
      object queue header oper kcbo_link_q:reget 0 5 0
      object queue header oper kcbw_unlink_q 0 2 10
      redo allocation kcrfw_redo_gen: redo alloc 0 13 0
      row cache objects kqreqd: reget 0 7 0
      row cache objects kqreqd 0 1 0
      session allocation ksuprc 0 44 7
      session allocation ksudlc 0 24 25
      session allocation ksuxds: not user session 0 16 1
      session allocation ksucri 0 9 62
      session allocation kspallmod 0 2 0
      shared pool kghalo 0 2,269 126
      shared pool kghupr1 0 690 3,122

      Thanks for the help.

      Thanks,

      Rana.
        • 1. Re: Latch problem.
          P.Forstmann
          Please post at least:
          1. header of AWR report (with database version, snapshot duration and the top 5 timed events)
          2. number of CPUS for machine hosting database instance

          Please use the '{ code }' formating rules (without the blanks between {,} and code).
          • 2. Re: Latch problem.
            585227
            '{
            DB Name DB Id Instance Inst Num Release RAC Host
            ------------ ----------- ------------ -------- ----------- --- ------------
            orvprd 3135597156 orvprd 1 10.2.0.3.0 NO ix205

            Snap Id Snap Time Sessions Curs/Sess
            --------- ------------------- -------- ---------
            Begin Snap: 8273 22-Jun-09 03:00:39 132 22.4
            End Snap: 8275 22-Jun-09 05:00:42 135 19.3
            Elapsed: 120.05 (mins)
            DB Time: 565.08 (mins)

            Cache Sizes
            ~~~~~~~~~~~ Begin End
            ---------- ----------
            Buffer Cache: 7,392M 7,264M Std Block Size: 8K
            Shared Pool Size: 2,768M 2,896M Log Buffer: 14,340K

            Load Profile
            ~~~~~~~~~~~~ Per Second Per Transaction
            --------------- ---------------
            Redo size: 33,684.43 7,421.03
            Logical reads: 133,813.99 29,480.60
            Block changes: 4,946.72 1,089.81
            Physical reads: 2.37 0.52
            Physical writes: 12.92 2.85
            User calls: 154.34 34.00
            Parses: 438.57 96.62
            Hard parses: 1.26 0.28
            Sorts: 5,130.10 1,130.21
            Logons: 0.07 0.02
            Executes: 5,604.35 1,234.70
            Transactions: 4.54

            % Blocks changed per Read: 3.70 Recursive Call %: 98.52
            Rollback per transaction %: 0.18 Rows per Sort: 2.85

            Instance Efficiency Percentages (Target 100%)
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %: 100.00 Redo NoWait %: 100.00
            Buffer Hit %: 100.00 In-memory Sort %: 100.00
            Library Hit %: 100.01 Soft Parse %: 99.71
            Execute to Parse %: 92.17 Latch Hit %: 99.32
            Parse CPU to Parse Elapsd %: 8.09 % Non-Parse CPU: 99.46

            Shared Pool Statistics Begin End
            ------ ------
            Memory Usage %: 84.14 84.89
            % SQL with executions>1: 97.57 97.11
            % Memory for SQL w/exec>1: 95.66 95.31

            Top 5 Timed Events Avg %Total
            ~~~~~~~~~~~~~~~~~~ wait Call
            Event Waits Time (s) (ms) Time Wait Class
            ------------------------------ ------------ ----------- ------ ------ ----------
            CPU time 14,995 44.2
            TCP Socket (KGAS) 611,193 6,230 10 18.4 Network
            latch: library cache 21,661 4,756 220 14.0 Concurrenc
            latch: shared pool 3,333 320 96 0.9 Concurrenc
            db file sequential read 17,316 193 11 0.6 User I/O
            -------------------------------------------------------------}'


            Thanks for the help.

            Rana.
            • 3. Re: Latch problem.
              P.Forstmann
              Please reformat your posting: start and end the section with the tag { code } (lowercase, curly brackets, no spaces) so that the text appears in
              fixed format
              See http://wiki.oracle.com/page/Oracle+Discussion+Forums+FAQ

              Edited by: P. Forstmann on Jun 23, 2009 1:51 PM
              • 4. Re: Latch problem.
                585227
                DB Name         DB Id    Instance     Inst Num Release     RAC Host
                ------------ ----------- ------------ -------- ----------- --- ------------
                orvprd        3135597156 orvprd              1 10.2.0.3.0  NO  ix205
                
                              Snap Id      Snap Time      Sessions Curs/Sess
                            --------- ------------------- -------- ---------
                Begin Snap:      8273 22-Jun-09 03:00:39       132      22.4
                  End Snap:      8275 22-Jun-09 05:00:42       135      19.3
                   Elapsed:              120.05 (mins)
                   DB Time:              565.08 (mins)
                
                Cache Sizes
                ~~~~~~~~~~~                       Begin        End
                                             ---------- ----------
                               Buffer Cache:     7,392M     7,264M  Std Block Size:         8K
                           Shared Pool Size:     2,768M     2,896M      Log Buffer:    14,340K
                
                Load Profile
                ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                   ---------------       ---------------
                                  Redo size:             33,684.43              7,421.03
                              Logical reads:            133,813.99             29,480.60
                              Block changes:              4,946.72              1,089.81
                             Physical reads:                  2.37                  0.52
                            Physical writes:                 12.92                  2.85
                                 User calls:                154.34                 34.00
                                     Parses:                438.57                 96.62
                                Hard parses:                  1.26                  0.28
                                      Sorts:              5,130.10              1,130.21
                                     Logons:                  0.07                  0.02
                                   Executes:              5,604.35              1,234.70
                               Transactions:                  4.54
                
                  % Blocks changed per Read:    3.70    Recursive Call %:    98.52
                 Rollback per transaction %:    0.18       Rows per Sort:     2.85
                
                Instance Efficiency Percentages (Target 100%)
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                            Buffer  Hit   %:  100.00    In-memory Sort %:  100.00
                            Library Hit   %:  100.01        Soft Parse %:   99.71
                         Execute to Parse %:   92.17         Latch Hit %:   99.32
                Parse CPU to Parse Elapsd %:    8.09     % Non-Parse CPU:   99.46
                
                 Shared Pool Statistics        Begin    End
                                              ------  ------
                             Memory Usage %:   84.14   84.89
                    % SQL with executions>1:   97.57   97.11
                  % Memory for SQL w/exec>1:   95.66   95.31
                
                Top 5 Timed Events                                         Avg %Total
                ~~~~~~~~~~~~~~~~~~                                        wait   Call
                Event                                 Waits    Time (s)   (ms)   Time Wait Class
                ------------------------------ ------------ ----------- ------ ------ ----------
                CPU time                                         14,995          44.2
                TCP Socket (KGAS)                   611,193       6,230     10   18.4    Network
                latch: library cache                 21,661       4,756    220   14.0 Concurrenc
                latch: shared pool                    3,333         320     96    0.9 Concurrenc
                db file sequential read              17,316         193     11    0.6   User I/O
                          -------------------------------------------------------------
                • 5. Re: Latch problem.
                  Liron Amitzi
                  Hi,
                  From the information you gave, it seems that you have problems with library cache latch and shared pool latch.

                  Shared pool latch is used to allocate memory in the shared pool (hard parse)
                  Library cache latch is used when looking for a cursor in the shared pool (soft parse)

                  As you can see in the load profile, you have quite a lot of parse operations, this causes latch contention and high CPU consumption.

                  Check in the rest of the report which SQL statement have a lot of versions. Also check the application for not using bind variables, or using many parse operations (instead of parse once, execute many).

                  Liron Amitzi
                  Senior DBA consultant
                  [www.dbsnaps.com]
                  [www.orbiumsoftware.com]
                  • 6. Re: Latch problem.
                    P.Forstmann
                    It is possible that your application code does not used bind variables. Try to adapt following script :http://asktom.oracle.com/pls/asktom/f?p=100:11:2537622989124160::::P11_QUESTION_ID:1163635055580 to check your shared pool.
                    • 7. Re: Latch problem.
                      Jonathan Lewis
                      I don't think the problem relates to parsing - although your 'parse calls' per second is quite high, the 'hard parse' count is low (and it's the hard parses that usually relate to the literals/bind variables problem).

                      How many CPUs do you have ? Any latching problems are made more significant when your CPU utilisation is very high, but it's not possible to guess from the figures how many you have. (It would be useful to see the OS Statistics section of the report as well to compare Oracle's use of CPU with the machine's use).

                      I note you have lots of wais on TCP socket (KGAS) - which suggest that you are doing a lot with utl_htp or one of the other packages that talks to the TCP sockets - and the number of waits makes it look like a very chatty application, which may be part of your problem.

                      The most significant numbers in your stats, though, are the 5,500 executes and sorts per second. That is a very large number of executions per second - if you only have a small number of CPUs (and I'm guessing 8 or less). I think your library and shared pool problems are related to the way you have written your application to do a large number of small operations, and you need to find out what is happening most frequently (check SQL ordered by executions) and reduce the executions.



                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      "For every expert there is an equal and opposite expert."
                      Arthur C. Clarke
                      • 8. Re: Latch problem.
                        Liron Amitzi
                        Hi,
                        There is a relation to parse here. You are right about hard parse (there are not that many), but there are many soft parses.
                        You can see that most of the latch contetion is on library cache latch (which is used in soft parse - to search the library cache for the cursor).

                        I had several cases in which many soft parses caused high library cache latch contention and this caused the server to hang with 100% CPU.


                        Liron Amitzi
                        Senior DBA consultant
                        http://www.dbsnaps.com
                        http://www.orbiumsoftware.com
                        • 9. Re: Latch problem.
                          483588
                          Hi Jonathan,

                          Would it be possible that those executions are "small UPDATES". I am using "small" here because the REDO is only 33K per second for such a huge number of executions (5604). Since there are lots of blocks changed, I am assunming these are UPDATES (not INSERTS)

                          I am also assuming that table has constrains (number of sorts are close to number of executions). Each of this small DMLs may require sort to check the table constraints.

                          Number of transactions per second are only 4....I am assuming this inserts are in a for loop or some kind of loop.


                          Thank you
                          WinRed
                          • 10. Re: Latch problem.
                            585227
                            Thanks Jonathan and Amitzi,

                            I bit description at the background is. Since this application uses lot of literal values a year back I'd suggested and changed cursor_sharing to similar. Since then there was improvement of around 25%.

                            Though I've cursor_sharing as similar, what I'm confused is about Library cache latches without having many hard parses.

                            Could this be a problem of SGA getting swapped?

                            I've 3 CPUs in my system and 16GB of RAM, but my SGA_TARGET is around 10GB and PGA_AGGREGATE_TARGET is 2 GB.

                            I've also check minperm% is set to 3 and maxperm% is 90.

                            Can you let me know your inputs on this please?

                            Thanks,

                            Rana.
                            • 11. Re: Latch problem.
                              585227
                              Hi Amitzi,

                              How do we address these kind of soft parses?

                              Thanks,

                              Rana.
                              • 12. Re: Latch problem.
                                Liron Amitzi
                                Hi Rana,
                                There should be no relation to the SGA being swapped, since even if it is swapped, Oracle thinks it is in memory.

                                Curosr sharing improved your performance since it reduced hard parses, but soft parses still happen.
                                When you execute a query, you have 3 choices:
                                1. Oracle hash the query, doesn't find it in the shared pool and performs hard parse (execution paln, syntax, etc.)
                                2. Oracle hash the query, finds it and executes it (soft parse)
                                3. Oracle doesn't have to hash the query becuase you have a "handle" to the query and you just execute it. This is "no parse".

                                Now, regarding the soft parses, it's not easy. We need to change the sql execution to use "no parse" instead of soft parse.
                                Several questions:
                                1. What Oracle version are you using?
                                2. What application are you using?
                                3. What is the value of SESSION_CACHED_CURSORS parameter in your database (it may help in caching sql "handlers" to prevent soft parse)

                                Liron Amitzi
                                Senior DBA consultant
                                [http://www.dbsnaps.com]
                                [http://www.orbiumsoftware.com]
                                • 13. Re: Latch problem.
                                  585227
                                  Hi Amitzi,

                                  Details are as follows:

                                  1. Oracle version is 10.2.0.3
                                  2. Basically a third party application which has mail interface into that.
                                  3. session_cached_cursors value is 50.

                                  Thanks,

                                  Rana.
                                  • 14. Re: Latch problem.
                                    Liron Amitzi
                                    Hi Rana,

                                    Does it make sense that this application is executing so many queries? Maybe you should contact the application provider.

                                    I would try to increase session_cached_cursor to let's say 200. Also if the parameter CURSOR_SPACE_FOR_TIME is false, set it to true.

                                    Be aware that we are changing the shared pool behavior here, so be careful and pay attention if there is any problem. Also it is always recommended to test the changes on a test environment first.

                                    Liron Amitzi
                                    Senior DBA consultant
                                    [www.dbsnaps.com]
                                    [www.orbiumsoftware.com]
                                    1 2 Previous Next