v$queue中的type栏位的意义

http://jonathanlewis.wordpress.com/2011/01/11/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:

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]

Related