Oracle Scratchpad

January 11, 2011

Shared Server – 3

Filed under: Shared Server / MTS — Jonathan Lewis @ 6:43 pm BST Jan 11,2011

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:

set linesize 120
set trimspool on
set pagesize 40

column program format a24
column totalq  format 9,999,999,999
column wait    format 9,999,999,999

select
        prc.program,
        que.type,
        que.totalq,
        que.wait,
        round(que.wait/nullif(que.totalq,0),2)  avg_wait
from
        v$queue que,
        v$process prc
where
        prc.addr(+) = que.paddr
/

PROGRAM                  TYPE               TOTALQ          WAIT   AVG_WAIT
------------------------ ---------- -------------- ------------- ----------
oracle@xxxxxxxx (D000)   DISPATCHER    199,171,228     4,405,840        .02
oracle@xxxxxxxx (D001)   DISPATCHER    204,687,986 1,435,915,630       7.02
oracle@xxxxxxxx (D002)   DISPATCHER      2,068,714        29,477        .01
oracle@xxxxxxxx (D003)   DISPATCHER      3,541,736        61,355        .02
                         COMMON        286,032,262 1,105,120,053       3.86

┬áThe view gives statistics since instance startup, usually, but can produce very strange results because it is possible to stop and start dispatchers dynamically. In this example the system had been running for quite a long time with just two dispatchers and I had increased it to four dispatchers in the previous 24 hours – hence the two dispatchers with a relatively tiny number of waits in the queue. (I stopped and restarted the dispatchers for subsequent test runs so that I didn’t have to keep working out the deltas.)

The COMMON queue is where incoming requests are queued waiting for a shared server to servive them, the DISPATCHER queues are where a shared server places its result set when it has processed the message. (A session is permanently “connected” to a dispatcher – but the association between a session and a shared server is transient, and each session request could end up being serviced by a different shared server).

If there are too many concurrent messages for the number of shared servers (or, almost equivalently) too many long-running tasks, then we would expect to see the time spent in the COMMON queue climbing as sessions wait for a shared served to become free. In principle I wouldn’t really expect to see much time spent in the DISPATCHER queues.

Before you ask, I can’t explain how the waits on D001 got to be so long when the similar number of waits on D000 were so short – all the explanations I hypothesised end up with a logical (usually statistical) flaw . Suggestions will be welcomed.

Footnote: Most of the notes I’ve seen about v$queue on the Internet say that there is only every one COMMON queue, so the number of rows in v$queue will always be one more than the number of rows in v$dispatcher – but my copy of 11.1.0.6 seems to start up with two COMMON queues (dictated by hidden parameter _shared_server_num_queues) – which are always fairly evenly loaded.

[Further reading on Shared Server / MTS]

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,529 other followers