Saturday, June 17, 2017

high "virtual circuit wait " due to open cursor still holds the shared server

curious on one database has 90% time spent on virtual circuit wait.

Luckily noted a blog says it is related a bug ,  Bug 5689608 : INACTIVE SESSION IS NOT RELEASING SHARED SERVER PROCESS .

when read the bug note, I realize this is a behaviou change since 10g , and it is true that 

Once the cursor is open, next doing fetch but idling there, the shared server is still held up  in a virtual circuit wait state from the time the cursor is first fetched until the application closes the
cursor or exits the session. 



This impacts the configuration of shared_servers and max_shared_servers, which should be high enough to avoid becoming an unnecessary bottleneck.

 
Below is my additional test on top the bug note scripts.

-- session 1
sqlplus liqy@pst01

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 7 17:07:39 2017

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

LIQY@pst01> select server from v$session where username='LIQY';

SERVER
---------
SHARED



--monitor again in another session 2

SYS@PST01> select sid, server from v$session where username='LIQY';

SERVER
---------
NONE



--session 1

LIQY@pst01> begin
 open cursor_package.mycursor;
  2    3  end;
  4  /

PL/SQL procedure successfully completed.

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

no rows selected


--session 2

SYS@PST01> /

SERVER
---------
NONE


--session 1 , fetch the cursor but do nothing

LIQY@pst01> exec test_case;      

PL/SQL procedure successfully completed.


--session 2
SYS@PST01> /

SERVER
---------
SHARED

--session 1

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                       2              0         733       366.58
       733           7331552 2900469894    2000153315           7 Network


LIQY@pst01> /

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                       6              0        1046       174.34
       733          10460632 2900469894    2000153315           7 Network


LIQY@pst01> host sleep 5

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      11              1        4704       427.62
      3000          47038538 2900469894    2000153315           7 Network


--session 2
SYS@PST01> /

SERVER
---------
SHARED



--session 1
LIQY@pst01> begin
 close cursor_package.mycursor;
  2    3  end;
  4  /

PL/SQL procedure successfully completed.

--session 2,  finally the shared server is released

SYS@PST01> /

SERVER
---------
NONE



LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      14              1        7341       524.37
      3000          73411576 2900469894    2000153315           7 Network


LIQY@pst01> /

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      14              1        7341       524.37
      3000          73411576 2900469894    2000153315           7 Network



2nd test,

if I open more sessions of session 1, once hit the minimum number of shared_servers, a new shared is raised up.  we can monitor their OS processes too.

ps -ef |grep s0 |grep