This discussion is archived
9 Replies Latest reply: May 1, 2012 10:54 PM by sb92075 RSS

cursou mutex event

926639 Newbie
Currently Being Moderated
hello,we are using 11gr2 on windows 8.

we got time consuming evet Waiting for event "cursor: mutex S" in wait class "Concurrency" in addm report.
Our cpu goes beyond sga_max_size.

also for one sql statment it is opening thousand of child cursor.

please give me solution what to do?
  • 1. Re: cursou mutex event
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    923636 wrote:
    hello,we are using 11gr2 on windows 8.

    we got time consuming evet Waiting for event "cursor: mutex S" in wait class "Concurrency" in addm report.
    well, if that information alone were sufficient to fix the problem, then the database would have fixed it itself.

    Can you post the workload profile section of your AWR report -- user calls, parse calls, hard parses, transactions etc.?

    Mutex contention is often a sign of excessive hard-parsing (not using bind variables), see an example in my blog:

    http://savvinov.com/2012/04/10/the-case-of-a-stuck-query-mutexes-in-10g/#more-478

    There are other possibilities as well: self-deadlocks, bugs in shared pool housekeeping mechanisms etc.
    Our cpu goes beyond sga_max_size.
    Your CPU can't go beyond SGA_MAX_SIZE because SGA is in memory. If you ment memory -- then that's not informative, either,
    because Oracle memory = SGA + PGA, where latter can be quite big (could be bigger than SGA) and is not fixed in size.
    also for one sql statment it is opening thousand of child cursor.
    What's your CURSOR_SHARING setting? How many thousands of cursors? What does V$SQL_SHARED_CURSOR say?

    Best regards,
    Nikolay
  • 2. Re: cursou mutex event
    912595 Expert
    Currently Being Moderated
    I was expecting your reply on mutex, so i just holdup my comments :)

    @OP, Also show us the value of "SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES"
  • 3. Re: cursou mutex event
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Ranjit,

    good point about OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES -- it principle high number of child cursors could also be side effects of 11g features such as SPM or ACS.

    Best regards,
    Nikolay
  • 4. Re: cursou mutex event
    chris_c Journeyer
    Currently Being Moderated
    If you have thousands of child cursors this could be the issue, as Nikolay suggested check V$SQL_SHARED_CURSOR for the reasons behind having many shared cursors, I have encountered problems with bind variable mismatch causing this issue due to the application developer mishandeling nulls when binding. is this a new problem or something that has been a problem for some time?
  • 5. Re: cursou mutex event
    926639 Newbie
    Currently Being Moderated
    hi.,my parameter as per you reply is

    cursor_sharing SIMILAR

    optimizer_capture_sql_plan_baseline
    FALSE

    awr work load info:-

         Per Second     Per Transaction     Per Exec     Per Call
    DB Time(s):     0.9     1.2     0.01     0.01
    DB CPU(s):     0.7     1.0     0.01     0.01
    Redo size:     4,785.3     6,485.5          
    Logical reads:     47,930.7     64,960.4          
    Block changes:     29.4     39.8          
    Physical reads:     206.4     279.7          
    Physical writes:     18.8     25.5          
    User calls:     113.7     154.1          
    Parses:     67.3     91.2          
    Hard parses:     0.8     1.1          
    W/A MB processed:     3.9     5.3          
    Logons:     1.4     1.9          
    Executes:     142.8     193.5          
    Rollbacks:     0.0     0.0          
    Transactions:     0.7               


    also time consuming sql stat is:-

         select /*+ first_rows(10) */ * from (select a.*, rownum rnum from (select js.job_fk, js.doc_filename, DDSEARCH.dd_bfile.get_file_name(js.doc_bfile) filepath , DDSEARCH.dd_bfile.get_dir_obj_path(js.doc_bfile) dpath from DDSEARCH.jobs_search js, DD.author au where js.job_in_dt between to_date(:"SYS_B_0", :"SYS_B_1") and to_date(:"SYS_B_2", :"SYS_B_3") and js.author_fk=au.author_pk and au.ac_fk=nvl((select ac_fk from dd.ac_dbo WHERE mgrp=:"SYS_B_4"), -:"SYS_B_5") and contains(js.doc_bfile, :"SYS_B_6")>:"SYS_B_7" order by js.job_fk desc) a where rownum <= :"SYS_B_8" )where rnum >= :"SYS_B_9"

    Edited by: 923636 on Apr 20, 2012 1:13 AM
  • 6. Re: cursou mutex event
    chris_c Journeyer
    Currently Being Moderated
    check Jonathan lewioses posts on this thread Library Cache Lock you appear to have a similar issue with cursor_sharing = similar causing many child cursors for this statement it may be worth testing with curosr_sharing = force.

    Also keep in mind curosr_sharing=similar is deprecated in 11g and will not be available in 12.1 see metalink note 1169017.1 for details.

    Chris
  • 7. Re: cursou mutex event
    912595 Expert
    Currently Being Moderated
    923636 wrote:
    hi.,my parameter as per you reply is

    cursor_sharing SIMILAR

    optimizer_capture_sql_plan_baseline
    FALSE

    awr work load info:-

         Per Second     Per Transaction     Per Exec     Per Call
    DB Time(s):     0.9     1.2     0.01     0.01
    DB CPU(s):     0.7     1.0     0.01     0.01
    Redo size:     4,785.3     6,485.5          
    Logical reads:     47,930.7     64,960.4          
    Block changes:     29.4     39.8          
    Physical reads:     206.4     279.7          
    Physical writes:     18.8     25.5          
    User calls:     113.7     154.1          
    Parses:     67.3     91.2          
    Hard parses:     0.8     1.1          
    W/A MB processed:     3.9     5.3          
    Logons:     1.4     1.9          
    Executes:     142.8     193.5          
    Rollbacks:     0.0     0.0          
    Transactions:     0.7               


    also time consuming sql stat is:-

         select /*+ first_rows(10) */ * from (select a.*, rownum rnum from (select js.job_fk, js.doc_filename, DDSEARCH.dd_bfile.get_file_name(js.doc_bfile) filepath , DDSEARCH.dd_bfile.get_dir_obj_path(js.doc_bfile) dpath from DDSEARCH.jobs_search js, DD.author au where js.job_in_dt between to_date(:"SYS_B_0", :"SYS_B_1") and to_date(:"SYS_B_2", :"SYS_B_3") and js.author_fk=au.author_pk and au.ac_fk=nvl((select ac_fk from dd.ac_dbo WHERE mgrp=:"SYS_B_4"), -:"SYS_B_5") and contains(js.doc_bfile, :"SYS_B_6")>:"SYS_B_7" order by js.job_fk desc) a where rownum <= :"SYS_B_8" )where rnum >= :"SYS_B_9"

    Edited by: 923636 on Apr 20, 2012 1:13 AM
    Yes, as its said cursor_sharing to SIMILAR is discouraged in 11.2, so you should check with FORCE and rest Adaptive Cursor Sharing would take care. Please check following ink from Oracle's development team.
    https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
    https://blogs.oracle.com/optimizer/entry/whydo_i_have_hundreds_of_child_cursors_when_cursor_sharing_is_set_to_similar_in_10g
  • 8. Re: cursou mutex event
    926639 Newbie
    Currently Being Moderated
    Finally solved iisue.
  • 9. Re: cursou mutex event
    sb92075 Guru
    Currently Being Moderated
    923636 wrote:
    Finally solved iisue.
    please post solution

Legend

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