Oracle 10g introduced the ability to run shared server sessions within the workarea_size_policy=auto parameter. However, there is one caveat I would like to point out.

I noticed this after one of our databases was upgraded from the 9iR2 release. In a nutshell , the number of shared servers jumped significantly. Before the upgrade, we were able run the system with only 8-10 shared servers, but ended up running as many as 50-60 just to handle the same workload in 10gR2.

After a quick look at the system, I discovered a lot of inactive sessions still holding the shared server. That is…

SQL> select status, server
2   from v$session
3   where sid=147;

STATUS   SERVER
-------- ---------
INACTIVE SHARED

…and the shared server status was WAIT(RECEIVE):

SQL> select status
  2   from v$shared_server
  3   where paddr=(select paddr from v$session where sid=147);

STATUS
----------------
WAIT(RECEIVE)

…and we had a lot of those. But what was the cause?
I spent some time looking at the application SQL, and after some test-and-trial, I was finally able to produce a simple test case:

SESSION 1 — a shared server session

SQL> show parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
workarea_size_policy                 string      AUTO

SQL> select sys_context('userenv', 'sid') sid from dual;

SID
----------
147

SESSION 2 — monitors the state of our first session

SQL> select status, server
  2   from v$session
  3   where sid=147;

STATUS   SERVER
-------- ---------
INACTIVE NONE

SESSION 1

--simulate a client fetching from the ref cursor
SQL> create or replace procedure rc_fetch(
  2    p_rc  in out sys_refcursor
  3  ) is
  4    l_row user_objects%rowtype;
  5  begin
  6    fetch p_rc into l_row;
  7  end;
  8  /

Procedure created.

--declare and open the following ref cursor
SQL> variable rc refcursor;
SQL> exec open :rc for select * from user_objects order by object_id;

PL/SQL procedure successfully completed.

--fetch one row from the ref cursor
SQL> exec rc_fetch(:rc);

PL/SQL procedure successfully completed.

Now let’s take a look from our second session:
SESSION 2

SQL> select v.status sess_stat, server, s.status serv_stat
  2    from v$session v, v$shared_server s
  3    where v.paddr=s.paddr
  4      and v.sid=147;

SESS_STA SERVER    SERV_STAT
-------- --------- ----------------
INACTIVE SHARED    WAIT(RECEIVE)

We have an inactive session holding a shared server. It will hold it until all rows are fetched and the cursor is open. In our applications, we had a lot of reports with the ability to page through results. The operator would run the report, and while he was looking at the results, the shared server remained occupied.

This happens only if your shared server session touches some of the automatically managed workarea memory. If you remove the ORDER BY clause from my example, the shared server will not be stuck.

With some SQL, you don’t even need to fetch anything in order to block the shared server…

--all it takes
SQL> variable rc refcursor;
SQL> exec open :rc for select object_id from user_objects group by object_id;

PL/SQL procedure successfully completed.

…since the above statement will allocate workarea memory immediately:

SQL> select operation_type, work_area_size
2    from v$sql_workarea_active
3    where sid=147;

OPERATION_TYPE       WORK_AREA_SIZE
-------------------- --------------
GROUP BY (HASH)             3512320

Switching back to workarea_size_policy=manual for shared server sessions fixes the problem. So if you are planing to user shared servers with the auto workarea management, there is one more thing for you to consider.

You can follow any responses to this post through the RSS 2.0 feed. You can leave a response, or trackback from your own site.