Shared Server

Original Note: 3rd June 2009

It’s not uncommon to hear people describing a database or instance as “being shared server”, or “being dedicated server” – and I sometimes get the impression that some DBAs think that enabling shared servers (or multi-threaded servers – MTS – as the feature used to be known) makes it impossible for connections to use dedicated servers.

So, for reference, here’s a short thread on the OTN database forum that makes a couple of useful points about shared servers (with a particular reference to the way the feature is used in 11g).

Update: 27th July 2009

A few extra thoughts on shared servers: if you want to get some idea on whether you’ve set the parameter shared_servers to a suitable value, you could query v$shared_server fairly regularly over a short time period.

In the simplest case “select count(*) from v$shared_server” may be sufficient – if the total keeps going up and down then you’ve probably set the parameter too low, leading to possible interference problems as new servers have to be started up. Checking v$sga_resize_ops for automatic resizing of the large pool could also give you some clues about how appropriate your configuration was.

On the other hand, if you’ve set shared_servers too high (which probably isn’t too much of a threat – unless you’re on 32-bit Windows, perhaps) then a quick check of: “select count(*) from v$shared_server where messages = 0″ will give you some idea of how many servers have started up but done nothing.  (A more sophisticated check could be used to find how many servers had done very little work).

Finally, to take the longer-term view, you could query v$shared_server_monitor to get some idea of how many servers had been started and stopped (since instance startup if you just query the view, but you could take periodic snapshots).

SQL> desc v$shared_server_monitor
 Name                    Null?    Type
 ----------------------- -------- ----------------
 MAXIMUM_CONNECTIONS              NUMBER
 MAXIMUM_SESSIONS                 NUMBER
 SERVERS_STARTED                  NUMBER
 SERVERS_TERMINATED               NUMBER
 SERVERS_HIGHWATER                NUMBER

The other critical performance aspect of shared servers is that each call to a server should only last a short period of time. To allow you to monitor this, Oracle offers a little-known view called v$reqdist with the following simple structure:

Name                    Null?    Type
----------------------- -------- ----------------
BUCKET                           NUMBER
COUNT                            NUMBER

select
	*
from
	v$reqdist
order by
	bucket
;

    BUCKET      COUNT
---------- ----------
         0   56404898
         1          0
         2      28517
         3      14025
         4       6959
         5       4221
         6       1907
         7       2097
         8      60265
         9     141794
        10         89
        11         33

12 rows selected.

Like many of the summary stats views in Oracle, this is a “histogram” type view, where each row represents a doubling of the row before. In this case, the manual says that bucket N reports calls through a shared server that lasted at most 4 * 2^N hundredths of a second. So the stats above tell us that 56 million calls lasted less than 4/100 seconds.

In this case we are doing something a little worrying – buckets 8 and 9 are showing quite a lot of activity and bucket 9 represents calls that lasted 10 to 20 seconds – (1024 to 2048 hundredths). That’s a lot of calls that are each holding a shared server for a signficant amount of time.

If you do the arithmetic, you find that bucket zero is reporting at most around 2.2 million seconds; bucket 9 is reporting somewhere between 1.4 million and 2.8 million seconds. That’s a worrying distribution of time.

I don’t actually trust this view to be entirely accurate – I’ve managed to produce some results in the past where the counts are clearly a little suspect – but I do think it’s giving you a rough indication of what’s going on. So in this case I would be on the lookout for slow code and the side effects it might have on other processes that could end up queueing for shared servers.

[Further reading on Shared Server / MTS]