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
