Shared Server – 4

In earlier posts we looked at v$reqdist and v$queue, which report time spent running tasks, and time spent waiting in the COMMON and DISPATCHER queues.

I mentioned in the previous article that if we see too much time spent in the COMMON queue(s) then perhaps we needed more shared servers. Moving to the other end of the dialogue, one of the reasons why we might spend too much time waiting in a DISPATCHER queue for the result to go back to the user is that we don’t have enough dispatchers – and we can get a clue about this from the view v$dispatcher:

column messages     format 999,999,999,999
column bytes        format 999,999,999,999
column idle         format 999,999,999,999
column busy         format 999,999,999,999
column total_time   format 999,999,999,999
column busy_percent format 999.99

select
        name, /* network, */ messages, bytes,
        idle, busy,
        idle + busy total_time,
        100 * round(busy/nullif(idle+busy,0),4) busy_percent
from
        v$dispatcher
/

NAME         MESSAGES            BYTES             IDLE             BUSY       TOTAL_TIME BUSY_PERCENT
---- ---------------- ---------------- ---------------- ---------------- ---------------- ------------
D000      341,902,864   -1,875,035,869      498,676,896      897,199,945    1,395,876,841        64.28
D001      351,090,918     -860,132,585    1,672,899,708      216,085,537    1,888,985,245        11.44
D002        3,543,576    1,820,366,239        6,602,929           82,744        6,685,673         1.24
D003        5,994,180   -1,007,460,261        6,539,742          145,927        6,685,669         2.18

Unfortunately, it looks as if the critical columns in this view are recorded as 32-bit signed, which means they wrap from positive to negative at about 2,000,000,000 – and this means the figures for D000 and D001 are complete garbage. In my last note I pointed out that I had started up two extra dispatchers on a system that had been running for quite a long time – which is why dispatchers D002 and D003 have such small number compared to the others – they’ve only been running about 18 hours (66,857 seconds).

Clearly, to get some sensible figures, you really need to play around with snapshots and deltas and worry about all the usual problems of collecting information for the right interval. Even so, these figures do show you that D002 and D003 have been idle for most of the time they’ve been up – but you’ll have to take it from me that the 827 seconds and 1,459 seconds they’ve recorded as busy time was a small fraction of a soak test that we were running. It’s not obvious from the absolute figures, but with the background information I have I can say that there was a small benefit from having four dispatchers, but nothing significant.

Note: if we were able to trust the 64.28% figure for dispatcher D000 we could be reasonably confident that we needed at least the second dispatcher simply on the basis of the work being done by D000; but we might also worry about it for another reason – if the dispatcher is very busy, it’s possible that this is just a symptom of the whole machine being busy, in which case it’s possible that the dispatcher isn’t able to get CPU time to do its work.

[Further reading on Shared Server / MTS]

Shared Server – 3

The previous post in this series showed you how v$reqdist summarised the time taken by tasks running through shared servers – but there are several other ways we need to look at what’s going on with shared servers. One of the more important ones is to find out how much time a task is queueing before it gets to a shared server to start running – and Oracle gives us v$queue as the place to find this information:

Shared Server – 2

Although they are becoming increasingly rare (thanks, largely, to Web-based applications taking over the world) a few of the systems I get called in to review are still using Shared Server technology (formerly known as Mutli-threaded Server / MTS); and I have to say that there are a couple of nice “overview” features supporting this technology that I would like to see in the AWR or Statspack reports. These are the views which allow you to see how the workload is being shared out and what the time distribution looks like, and I’ll be taking a look at these views over the course of three or four blog notes.

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).

Shared Server

This item from Howard Rogers sums up the shared server technology (formerly multi-threaded server, or MTS) so well that I decided it was worth a special mention.

[Further reading on Shared Server / MTS]