Cursor: Principle and solution of Pin s generation

From: http://www.dbafree.net /? P = 778

This evening, on a very important database, the CPU rushed seriously, resulting in slow dB response, a large number of applications connected to timeout, and then the listener went down, the number of connections is also full.

Our monitoring captures the system wait events at that time, active SQL and session_wait wait events, so the problem is more capacity-oriented, check the monitoring, and immediately find that a large number of cusor occurs at the time point of the problem: pin s, which is a common wait event.

Then, by querying the SQL statement that is being executed by the session holding the wait event cursor: pin s, we can find that it is only a simple SQL statement. Generally, latch pin operations in the memory are quite fast. If you wait, it is likely that the SQL statements are executed too frequently. Latch is a low-level lock in Oracle used to protect the data structure in the memory. It provides a serial access mechanism, while mutex is introduced by Oracle 10gr2, it is also a function for implementing serial access control and replacing part of latch.

You can query through the following SQL:

Click (here) to fold or open

1. -- Query SQL
2. Select a. *, S. SQL _text
3. From v $ SQL s,
4. (Select Sid,
5. Event,
6. Wait_class,
7. P1 cursor_hash_value,
8. P2raw mutex_value,
9. To_number (substr (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
10. From v $ session_wait
11. Where event like 'cursor % ')
12. Where S. hash_value = A. p1
14. -- Parameter description
15. P1 hash value of cursor
17. P2 mutex Value
18. 64 bit platforms
19. 8 bytes are used.
20. Top 4 bytes hold the session ID (if the mutex is held X)
21. Bottom 4 bytes hold the ref count (if the mutex is held S ).
23. 32 bit platforms
24. 4 bytes are used.
25. Top 2 bytes hold the session ID (if the mutex is held X)
26. Bottom 2 bytes hold the ref count (if the mutex is held S ).
28. P3 mutex where (an internal code Locator) or

Each child cursor has a simple memory structure such as mutexes. When a session needs to execute the SQL and the pin cursor operation, the session only 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 operations 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 a hardware problem, you can upgrade the hardware.

The SQL Execution frequency is too high. The simplest way is to split an SQL statement into multiple SQL statements. Increase the number of SQL versions to reduce concurrency. For example, an SQL statement:

select name from acct where acctno=:1

You can change it to the following four SQL statements, and the competition for concurrency can be reduced by four times.

     select /*A*/ name from acct where acctno=:1     select /*B*/ name from acct where acctno=:1     select /*C*/ name from acct where acctno=:1     select /*D*/ name from acct where acctno=:1

In addition, we often encounter another wait event "cursor: PIN s wait on X", which is mainly caused by hard parsing. The explanation is as follows:
"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 shoshould 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 ".

Cursor: pin s, cursor: Pin X, cursor: PIN s wait on X these three wait events are actually replaced by the library cache pin of cursor, pin s indicates the execution (share pin), Pin X indicates the exclusive pin, and pin s wait on X indicates that the execution is waiting for the parsing operation. It should be emphasized that they only replace the library cache pin for accessing cursor, and the object accessing procedure is still the traditional library cache pin.

Refer:

Https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx? _ Afrloop = 5051110464464000 & id = 1310764.1 & _ afrwindowmode = 0 & _ ADF. CTRL-state = fu77hl3v2_4

Bytes.