Cursor: pin S, cursorpin

Cursor: pin S, cursorpin

Cursor: pin S

OTN is explained as follows:

Cursor: pin SA session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. this wait event shocould rarely be seen because a shared mutex pin update is very fast. (Wait Time: Microseconds)
Parameter Description
P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR 'd with Mutex Sleeps

Top 5 performance reports collected today

 

Top 5 Timed Foreground Events~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg                                                          wait   % DBEvent                                 Waits     Time(s)   (ms)   time Wait Class------------------------------ ------------ ----------- ------ ------ ----------DB CPU                                          205,412          99.8db file sequential read              68,063         487      7     .2 User I/Olog file sync                       139,316         233      2     .1 Commitcursor: pin S                     1,163,270         127      0     .1 ConcurrencSQL*Net message from dblink          97,575          46      0     .0 Network

The mutexes mechanism referenced in Oracle10gR2 replaces the library cache pin to a certain extent. Its structure is simpler, which is equivalent to a simple memory structure such as mutexes under each child cursor, when the pin cursor operation is required for executing the SQL statement in a session, the session needs to set the memory bit + 1 in the shared mode, indicating that the session gets the shared mode lock of the mutex. many sessions can have the shared mode lock of mutex at the same time. However, at the same time, only one session can operate on the mutext + 1 or-1. + 1-1 operations are scheduled atomic operations. If a session is waiting for mutext + 1/-1 of other sessions due to too many parallel sessions, the session will wait for the cursor: pin S to wait for the event.

When you see that the system has many sessions waiting for the cursor: pin S event, either the CPU is not fast enough, it is either because the number of parallel executions of an SQL statement is too large, resulting in competition for mutex operations on the child cursor. If it is the former, you can upgrade the hardware. If the SQL statement is too parallel, you can either reduce the SQL statement execution times or copy the SQL statement into N other SQL statements.

    select /*SQL 1*/ename from t where deptno=?    select /*SQL 2*/ename from t where deptno=?    select /*SQL …*/ename from t where deptno=?    select /*SQL N*/ename from t where deptno=?

In this way, there will be n SQL Cursor and N Mutex memory structures, and the competition will be dispersed, similar to the role of partition.