Shared Servers and Automatic Workarea Management

Jun 20, 2007 / By Alex Fatkulin

Tags:

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.

12 Responses to “Shared Servers and Automatic Workarea Management”

  • Hey, who is there! Welcome to the English blogosphere! ;-)

    Good catch, by the way. So we can set pga_aggregate_target on instance level and workarea_size_policy=manual for certain shared sessions exhibiting thus buggy behavior.

    By the way, you reminded me about a similar issue I hit with shared servers. But that is a good topic for another blog.

  • nice and good point.10g automatic managed pga enable SQL WORK AREA allocated in PGA under MTS mode.
    We will also notice this issue when using workarea_size_policy=AUTO.

  • Hesham says:

    Dear friend ,

    can you tell me Alex how you can do that

    “set pga_aggregate_target on instance level and workarea_size_policy=manual for certain shared sessions ”

    thank you

  • Hesham,

    just use a logon trigger. Recognize the shared server connection (by looking at v$session.server) and do an alter system set workarea_size_policy=manual if it’s a shared server session.

  • Hesham,

    you would use alter session instead of alter system of course…

    I mistyped.

  • Hesham says:

    dear friends ,
    first : can you explain how can i create this trigger to change workarea_size_policy to manual on session level ?

    what will happen if i change all things on instance level ?

    thank you

  • Hesham, logon trigger could be like this:

    SQL> create trigger logon_trigger after logon on database
    2 begin
    3 for cur in (
    4 select null
    5 from v$session
    6 where sid=sys_context(‘userenv’, ‘sid’)
    7 and server=’SHARED’
    8 ) loop
    9 execute immediate ‘alter session set workarea_size_policy=manual’;
    10 end loop;
    11 end;
    12 /

    Trigger created.

    Changing this at instance level will affect, well, instance instead of a session. This can be appropriate if you do not want Oracle instance to use auto pga memory management by “default”.

  • Hesham says:

    Thank you Alex

  • Fran Zolick says:

    How can you find out the SID if one user can have multiple sessions

  • Fran Zolick says:

    Please ignore my previous email, I just found out that SID is part of the user system conext.

  • Alex,

    It’s a long time since you wrote this note, but I’ve just seen it and tried to reproduce your test on 10.2.0.1 on Windows XP Pro. I don’t get the same results – I even switched to all_objects from user_objects to have a much larger result set on the fetch but the inactive session still released the shared server.

    What platform were you on – and have you seen any change in later versions of 10g2 or 11g ?

  • I’ve just gone on to try the second example, though – and it did leave the session inactive but holding the server.

    I wonder if the different in behaviour on the first example could be dependent on the execution plan (and resources).

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>