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

large pool for shared server + sga_target

Tue May 6 11:43:54 2008
Errors in file /software/oraprx/admin/PRXP/bdump/prxp_s001_26546.trc:
ORA-00600: internal error code, arguments: [17183], [0xC0000000651B8AF8], [], [], [], [], [], []
ORA-02071: error initializing capabilities for remote database CATP.WORLD
ORA-04031: unable to allocate 27168 bytes of shared memory ("large pool","SELECT ACCOUNT_NO FROM IVOC_...","session heap","oper caps")

Tue May 6 13:10:35 2008
Errors in file /software/oraprx/admin/PRXP/bdump/prxp_s003_26550.trc:
ORA-00600: internal error code, arguments: [17285], [0xC000000065C3DE20], [1], [0xC0000000A78B0EC8], [], [], [], []
ORA-04031: unable to allocate 2520 bytes of shared memory ("large pool","unknown object","session heap","koh-kghu session heap")


- Set SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE and HASH_AREA_SIZE to much lower
values, say, 64K – 1MB to resolve this problem. The smaller value for these
parameters will be OK for an OLTP system.

When we use Shared Servers (MTS), memory used for sort and hash area will be
allocated from the large pool for the MTS connections even if you are configuring
Automatic PGA feature by setting PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY.

If you also set PGA_AGGREGATE_TARGET parameter to a non-zero value and
WORKAREA_SIZE_POLICY to auto, it will be used by dedicated server connections
in the database for sort and hash area memory but not the MTS connections.

SORT_AREA_SIZE in 10g shared server

--In current 9i database, we don't use PGA_AGGREGATE_TARGET and use SORT_AREA_SIZE =1M
--In 10g database, we want to use PGA_AGGEGATE_TARGET, however below excepted from Oracle Document "Database Reference" confuses me, as we have shared server running with this database. Do I still need to set it explicitly?

"Note: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility."

Conclusion from AskTom:

can safely leave sort_area_retained_size & sort_area_size as default shown below for 10g database using shared server, if set PGA_AGGREGATE_TARGET and workarea_size_policy='auto' sort_area_retained_size integer 0
sort_area_size integer 65536

ANSWERS from AskTom:

if ( version = 9i )
then
 if ( pga_aggregate_target is set and workarea_size_policy is auto )
 then
      if ( connection is dedicated server )
      then
            these parameters (sort area [retained] size) need not be set
      else
            they do need to be set, shared server uses sort area size
      end if
 else
      they do need to be set
 end if
elsif (version > 9i )
then
 if ( pga_aggregate_target is set and workarea_size_policy is auto )
 then
      these parameters (sort area [retained] size) need not be set
 else
      they do need to be set
 end if

version 9i:
shared server -> sort_area_size is used regardless.
dedicated server AND workarea = auto -> pga_aggregate_target is used
dedicated server AND workarea = manual -> sort_area_size is used

version 10g
workarea=auto -> pga_aggregate_target
workarea= manual -> sort_area_size


you need not remove the sort_area_size, and since sessions could set workarea to manual - you might


just want to leave it in with YOUR default.
end if

Automatic PGA Memory Management
Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using
the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to
attempt to address a few issues:
 *    Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There
was also much confusion over how those parameters actually worked and how memory was allocated.
 *    Manual allocation was a “one-size-fits-all” method: Typically as the number of users
running similar applications against a database went up, the amount of memory used for
sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used
10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and
so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size
settings, everyone would pretty much use the same values all day long. Consider the previous
example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we
allowed ourselves to use went up. If you run that example for yourself, you will almost certainly
see a decrease in response time as the amount of RAM available for sorting increases. Manual
allocation fixes the amount of memory to be used for sorting at a more or less constant number,
regardless of how much memory is actually available. Automatic memory management allows us to use
the memory when it is available; it dynamically adjusts the amount of memory we use based on the
workload.
 *    Memory control: As a result of the previous point, it was hard, if not impossible, to keep
the Oracle instance inside a “box” memory-wise. You could not control the amount of memory the
instance was going to use, as you had no real control over the number of simultaneous sorts/hashes
taking place. It was far too easy to use more real memory (actual physical free memory) than was
available on the machine.
Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a
fixed-size piece of memory, so you can very accurately see how big it is, and that will be its
total size (until and if you change that). You then tell Oracle, “This is how much memory you
should try to limit yourself across all workareas—a new umbrella term for the sorting and hashing
areas you use.” Now, you could in theory take a machine with 2GB of physical memory and allocate
768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and
other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close.
Before I discuss why that is true, we’ll take a look at how to set up automatic PGA memory
management and turn it on.
The process to set this up involves deciding on the proper values for two instance initialization
parameters, namely
 *    WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort
area and hash area size parameters to control the amount of memory allocated, or AUTO, in which
case the amount of memory allocated will vary based on the current workload present in the
database. The default and recommended value is AUTO.
 *    PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should
allocate, in total, for all workareas used to sort/hash data. Its default value varies by version
and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory
management, you should explicitly set this parameter.
So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero
value, you will be using the new automatic PGA memory management. You can “turn it on" in your
session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
Note    Bear in mind the previously discussed caveat that in Oracle9i, shared server connections
will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and
HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10g
and up, automatic PGA memory management is available to both connection types. It is important to
properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections
with Oracle9i.
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the
same time not using more RAM than you want. Under manual memory management, this was virtually
impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort
operation that user would use up to 10MB for the sort workarea. If 100 users were doing the same,
they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing
a sort by himself could have used much more memory, and the 100 users should have used much less.
That is what automatic PGA memory management was designed to do. Under a light workload, memory
usage could be maximized as the load increases on the system, and as more users perform sort or
hash operations, the amount of memory allocated to them would decrease—to obtain the goal of using
all available RAM, but not attempting to use more than physically exists.