Shared Servers and Automatic Workarea Management
Posted by Alex Fatkulin on Jun 20, 2007
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?
Read the rest of this entry . . .
