virtual circuit wait等待事件

virtual circuit wait

查詢 V$SHARED_SERVER 
SELECT * FROM v$shared_server WHERE circuit <> '00'

查詢 V$CIRCUIT 檢視中的狀態
SELECT waiter, status FROM V$CIRCUIT WHERE waiter <> '00'

檢查等待的Session 正在處理的SQL：
SELECT username, machine, event, status, server
FROM v$session
WHERE username IS NOT NULL
AND paddr IN
(SELECT waiter FROM V$CIRCUIT WHERE waiter <> '00')    

發現等待的 SESSION 正是當前 PLSQL 連線上檢查的 SESSION，很是困惑，當前客戶端（PLSQL）使用的TNSNAMES.ORA檔案中對連結的配置是 DEDICATED。

根據查詢的結果和機器實際網路正常、負載很低的分析，一直在懷疑是bug。

後來老大幫忙查詢了一下原因，發現和PLSQL客戶端有關，一旦使用客戶端連線上就出現這個等待事件，杯具。

問題出現前提條件：
1. 使用 oracle 11gR2
2. 是客戶端PL/SQL在連線資料庫的時候使用shared server
3. plsql在預設連線的時候是建立多個session的：Session  Mode 選擇 Multi session或者Dual session 

此時查詢 v$session 檢視可以看到，session的等待事件就是 virtual circuit wait，登出plsql即可看到等待事件取消

只有條件1,2和條件3同時達到才會產生這個等待事件，如果使用dedicated連結或者plsql配置上選擇Single session 都不會產生這個等待事件。


期間參考 metalink 上多篇文件和有人提問的回覆：
【參考一】

This is a performance monitoring enhancement to split the 'virtual circuit status' wait event into two new
wait events:

"shared server idle wait" - for when the shared server is
idle waiting for something to do

"virtual circuit wait" - for when the shared server is
blocked waiting on a specific 
circuit / message

The wait "virtual circuit status" no longer exist with this fix.
Before the bug fix  the 'virtual circuit status' event was classified as an 

idle event but was used for both idle and non-idle waits.  Since the idle time 

dominated the total time for the event, it obscured any true issues with the 

other waits under the event.

With this bug fix included in 11.1.0.7 and 11.2 The 'virtual circuit status' wait 
event was removed and replaced by two new wait events: 'shared server idle wait' and 
'virtual circuit wait'.  The wait on the common queue uses 'shared server idle wait' and 
all the otherwaits use 'virtual circuit wait'

原文連結：https://support.oracle.com/CSP/main/article?cmd=show&type=COMMUNITIES&doctype=COMTHREAD&id=85058

【參考二】Bug 6653834 - Split "virtual circuit status" into idle / non-idle waits [ID 6653834.8]

This note gives a brief overview of bug 6653834. 
The content was last updated on: 11-JAN-2010
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 11.2 Versions confirmed as being affected

• 9.2.0.8
• 10.1.0.5
• 10.2.0.3
• 10.2.0.4
• 11.1.0.6

Platforms affected Generic (all / most platforms affected) 
The fix for this bug introduces a notable change in behaviour thus:
Notable change of behaviour introduced in 11.1.0.7

Fixed: This issue is fixed in

• 11.1.0.7 (Server Patch Set)
• 11.2.0.1 (Base Release)

原文連結：https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PATCH&id=6653834.8