6 Replies Latest reply: Oct 17, 2012 4:27 AM by user7110532 RSS

    cursor: Pin S wait on X error

    618702
      Hi Gurus,

      We have lots of waiting session because of "cursor: pin S wait on X" fault message. Actually i know there is something going background with latches and shared pool operations but i couldn't find work-around to overcome it.

      I did check metalink but i want to know has anybody ever observed this problem before?

      Thanks in advance,

      Ogan
        • 1. Re: cursor: Pin S wait on X error
          659743
          Ogan, I have observed this in few 10g database instances. The reason for this wait could be excessive parsing in the database. Btw did you look at the blocking session and what blocking session was doing when another database session was waiting on this wait event.

          Check if the SQL area is being reloaded frequently then you can think of setting session_cached_cursors parameter. The ultimate solution will be to reduce the overall parsing in the database. There are few bugs related to mutex pins that you may like to check with Oracle Support.

          Cheers
          • 2. Re: cursor: Pin S wait on X error
            699071
            "cursor: Pin S wait on X" wait event, maybe mutex problem.
            from 10.2.0.2, oracle use mutex instead of library cache latch, in case "_kks_use_mutex_pin"=true.
            so, you change that parameter value from true to false.
            SQL>alter system set "_kks_use_mutex_pin"=false scope=spfile;
            SQL>shutdown immediate
            SQL>startup
            [https://metalink2.oracle.com/metalink/plsql/f?p=130:14:8905679782306617791::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,564719.1,1,1,1,helvetica]
            • 3. Re: cursor: Pin S wait on X error
              618702
              Sean_kim,

              Thanks for the reply but surely it is an Oracle internal parameter. Noone can know what will be the impact to the database.


              I will create a SR and if Oracle Support recommends this solution either, it is possible to use in our database.

              Definately what i think is, it will fix the problem.
              • 4. Re: cursor: Pin S wait on X error
                601585
                Some fix-ups

                1. Some backgrounds
                - In previous versions of Oracle, library cache pin is protected by "library cache pin latch".
                - But in recent versions of Oracle(I believe it's 10.2.0.2), library cache pin for the cursor LCO is protected by mutext.
                - Mutex is allocated per LCO, so it enables fine-grained access control.

                2. "cursor: pin S wait on X" wait event is mostly related to mutex and hard parse.
                - When a process hard parses the SQL statement, it should acquire exclusive library cache pin for the corresponding LCO.
                - This means that the process acquires the mutex in exclusive mode.
                - Another process which also executes the same query needs to acquire the mutex but it's being blocked by preceding process. The wait event is "cursor: pin S wait on X".

                Some bugs would make the contention worse as many metalink notes describe.

                3. Cursor mutex is a replacement of library cache pin latch for cursor, not library cache latch.

                4. As of 11g, library cache latch is also replaced with mutex. Now, each library cache bucket is protected by independent mutexes, which enables fine-grained access control.


                ================================
                Dion Cho - Oracle Performance Storyteller

                http://dioncho.wordpress.com (english)
                http://ukja.tistory.com (korean)
                ================================
                • 5. Re: cursor: Pin S wait on X error
                  618702
                  Dion_Cho,

                  That was really helpful, thank you very much for the explanation!
                  • 6. Re: cursor: Pin S wait on X error
                    user7110532
                    Thank you for mentioned FGAC. I just had this issue and rolled back all my work to avoid any issues. We have an FGAC policy that prevents updates to old data. It got lost when I partitioned the table. Once I recreated the policy the waits went away.