1 2 3 Previous Next 38 Replies Latest reply: Sep 2, 2013 12:36 PM by Jonathan Lewis Go to original post RSS
      • 30. Re: Perfomance Issue

        Thanks a lot. Today Again After the Index GSS , issue disappears. I am not able to get how this is effecting during parsing. Would you please provide me some explain how index GSS/Rebuild help in reducing this . As per my understanding object is piined for definition and released immediately. Today Our Client asked to create a new composite index based on column involved in query. How this is going to reduce reduce  the contention. I can understand if any data fetching is happening , it may help in reduced in elapse time. But Creating an Index How it will reduce the contention. PLease Please reply.

        • 31. Re: Perfomance Issue
          Jonathan Lewis

          We still need to know WHICH latch is losing you time - counts may be irrelevant.  See my earlier comment:


          "The article of Alex Fatkulin's that Joel Garry referenced has a query linking the row cache object latches to the dictionary cache, and we need to know what the contention is."


          We don't know whether the latch problem is the cause or consequence of the CPU utilisation increasing.

          A couple of ideas (just for illustration) of why gathering stats might make a difference

          a) gathering index stats invalidates dependent cursors - this may release a large number of objects in the library cache which are causing the queue that creates the problem - discard the object, reparse the statement, and there is, for a while, no queue so no contention.

          b) gather stats changes the execution plan - making it use less CPU; when CPU utilisation drops turnaround on the latches is quicker (and there is less CPU spent on spinning)

          Update: Another idea (for illustration) you've changed your comment about dc_ entries from dc_tables to dc_tablespaces - if you're rolling back then Oracle does a get on dc_tablespaces for every undo record applied (this is just one reason for higher counts). Since your tkprof showed unexpectedly high LIOs for what seems to be a simple index access then you could be seeing lots of interference between sessions attempting cleanout and rollback. This could be a cause of CPU overload that results in latch problems.  But we shouldn't speculate until we know about TIME spent on dc_ latch gets - counts don't tell us enough.




          Jonathan Lewis

          • 32. Re: Perfomance Issue

            Hi Jonathan, Thanks a lot. I think I am getting Now few things. a)explain plan remain the same since we have created baseline for this sql. b)CPU was high only during this time. c)I will post the stats of data dictionary cache stats  from AWR Report soon.

            • 33. Re: Perfomance Issue

              Hi Jonathan,


              PLease Find the Details :




              select latch#, child#, sleeps  , wait_time    from v$latch_children    where name='row cache objects'       and sleeps > 0     order by sleeps desc;
               LATCH#     CHILD#     SLEEPS  WAIT_TIME
              ---------- ---------- ---------- ----------
                     280          8  564274776 2942777250155
                     280          5    1002920   12073403788
                     280         13      76089     710297346
                     280          9      50270     237754726
                     280          4        559        529077
              Child# 8 Belong to the dc_users & 5 belong to dc_tablespaces
              select "WHERE", sleep_count, location from v$latch_misses where parent_name='row cache objects' and sleep_count > 0;
              WHERE                                                                            SLEEP_COUNT LOCATION
              -------------------------------------------------------------------------------- ----------- --------------------------------------------------------------
              kqrpre: find obj                                                                   167730249 kqrpre: find obj
              kqrpre: init complete                                                                     52 kqrpre: init complete


              Today Again for the Same Contention we gather the stats for Index , Latch : row cache reduces immeditely



              Sourabh GUpta

              • 34. Re: Perfomance Issue
                Jonathan Lewis

                Looking at the extreme spike for dc_users it does seem likely that that's the one that causes problems when you see the problem, The only thing you could do to make this more obvious is to run the query a couple of times over a few minutes and calculate differences.


                The fact that you are doing very little "hard" parsing but plenty of parse calls suggests that something bad is happening (as I may have suggested earlier on) as you search the library cache and do something to validate your right to execute a child cursor. (The reason why I mentioned VPD is that there's a known bug that fits these symptoms.)


                Gathering stats invalidates cursors - which means you don't have to spend so much time searching for a valid cursor. You might find that any DDL on the index will have the same effect (one I used to use in the past was to change the PCTFREE - without actually changing the value). It might be worth checking how many child cursors this statement has when contention is occurring (count(*) from v$sql where sql_id = ''') You could also check v$open_cursor to how many sessions have this cursor open at any time - and how many copies each they've managed to acquire.


                Have you set the session cursor cache parameter ?

                What do the numbers for parse calls, session cursor cache hits and cursor authentications in the interval look like  ?



                Jonathan Lewis

                • 35. Re: Perfomance Issue
                  Jonathan Lewis

                  I don't know how other people manage, but I wait until I've completed the page, then switch to "use advanced editor" - see top right corner of form - highlight the text, then select courier new as the font family.  (But I cut-n-paste a copy of what I've done into a text document first).



                  Jonathan Lewis

                  • 36. Re: Perfomance Issue

                    Hi Jonathan,


                    Thanks a lot.


                    Please Find the Obersvation :


                    Before GSS



                    select count(*) from v$sql where sql_id = '6rf2f75wptjrk';


                    --> count was 19  , as soon as we did the GSS Count Reduced to 4 and gradually increase to 12


                    -- > Session Cashed Cursor is value :


                    session_cached_cursors               integer     1200



                    -- > Count Of the DC_USERS Taken at different time during the contention observed.



                    -- Time   Sleep        Time
                    -- 11:58 574236392  2967369332806
                    -- 12:02  574827287  2969900443831
                    -- 12:10  577951681  2980163642648
                    -- 12:34  583273451  2990521771589
                    -- 3:30   593503044  3059362695733




                    Sourabh Gupta

                    • 37. Re: Perfomance Issue

                      Hi Jonathan,


                      Thanks a lot of help for on this. I am understanding a lot on core DATABASE  with the help of you.



                      At present we are just invalidating the cursor and making the improvement in the performance.We have worked on few things to make Performance better ( to reduce this contention ) :


                      > Creating A New INdex on the same table.

                      > Changes are made in code to reduce the concurrent access to this particular table.



                      I have checked that VPD is implemented in our case. After the recent code  changes , three more policies has been applied . Is there any way I can find out  when the dba_policies has been implemented. Since I can't see any creation date in DBA_POLICIES.


                      select * from v$vpd_policy where sql_id = '6rf2f75wptjrk';


                      SELECT count(*) FROM DBA_POLICIES where enable='YES';


                      Sourabh GUpta

                      • 38. Re: Perfomance Issue
                        Jonathan Lewis

                        I don't know of an explicit stored date, but dba_policies records the PF_owner, package and function that generate the security predicate, so you could query dba_objects for the package to check its creation date and last DDL date - this MIGHT be an indicator, e.g.:



                        object_id, created, last_ddl_time




                        owner = 'XDB'

                        and object_name = 'DBMS_XDBZ0'

                        and object_type = 'PACKAGE'




                        Jonathan Lewis

                        1 2 3 Previous Next