Oracle 并发等待事件

问候，
根据我的AWR报告::

Event                          Waits         Time(s)    Avg wait (ms) % DB time Wait Class
-----------------------------
SQL*Net more data from client  35,972         9,805    273                 65.90         Network
cursor: pin S wait on X          159         5,688    35770         38.23         Concurrency
DB CPU                                   1,629                   10.95  
enq: TX - row lock contention  9         136    15133         0.92         Application
direct path write temp          63,834         82    1                 0.55         User I/O

等待类并发是我开始一个DBA应该解决的主要问题。那么，它指的是什么，我应该怎么做才能从
前5名等待名单？

提前谢谢你。

有多少应用程序的用户抱怨 “光标: pin S等待X” 等待？

如果你说的不仅仅是零，我会非常惊讶。

你看，这不是你需要调整的等待事件。这是商业交易。找出其中哪些很慢。然后追踪他们，看看他们在做什么。

现在，可能会发现这些等待导致这些交易缓慢。但是，除非您采用以用户为中心的视图，否则您不知道您正在做的事情是否使应用程序更快for the users。

But even if it does turn out slow business transactions are spending lots of time waiting for this, you still don't know enough to take action。 This wait is more of a symptom than a root cause。 It's related to parsing。 But there are lots of things that could cause "cursor: pin S wait on X" as MOS note 1349387。1 discusses:

What is a 'Cursor: pin S wait on X' wait?

A cursor wait is associated with parsing in some form。 A session may wait for this event when it is trying to get a mutex pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive。  Frequently, waits for 'Cursor: pin S wait on X' is a symptom and not the cause。  There may be underlying tuning requirements or known issues。

What causes 'Cursor: pin S wait on X' waits?

* Firstly, ensure that the shared pool is sized correctly。 

If the shared pool is under sized or under load generally, this may manifest itself as 'Cursor: pin S wait on X'。 If Automatic Memory Management is being used then this should not normally be an issue。 See:

Document 443746。1 Automatic Memory Management (AMM) on 11g
 
* Frequent Hard Parses
If the frequency of Hard Parsing is extremely high, then contention can occur on this pin。
* High Version Counts
When Version counts become excessive, a long chain of versions needs to be examined and this can lead to contention on this event
* Known bugs
* Parse Errors, as following note indicates:

Document 1353015。1 How to Identify Hard Parse Failures