Graphing Oracle’s Shared Server configuration (V$QUEUE)

Tuning Oracle’s Shared Server(MTS) is not so straightforward task, but I’ve developed simple perl script named plotvqueue.pl that can draw over time the following info:
1) absolute COMMON waits – how many waits there were in the COMMON (VC) queue in interval time (1min)
2) no# of COMMON queued – number of items in the COMMON queue (serviced by shared servers)
3) absolute DISPATCHERS waits – how many waits there were for all the DISPATCHERs queues in interval time (1min)
4) no# of DISPATCHERS queued – number of items in the DISPATCHERs queues.

This can be used as a hint for raising dispatchers and/or shared_servers settings.

for detailed discussion be sure to read Metalink Doc.ID 1005259.6 (“Shared Server (MTS) Diagnosis”), especially get familiar with the following fragment about architecture:

sharedserverarchitecture

“The stages that Shared Server goes through are quite simple. After the client sends the connection request to the Listener, it will either redirect or hand off (called warm hand-off) the connection to the Dispatcher (the Dispatcher does not necessarily need to be on the same host as the Listener). Once the client has connected to a Dispatcher it stays connected to that Dispatcher. Before the client completes the database log in, the Dispatcher associates a Virtual Circuit (VC) for that database session. There exists exactly one row in the VC view (V$CIRCUIT) for each client connection. This view also shows the current status of the client’s VC. Once the VC has been associated with the database session, the client will complete the database logon by passing the username and password to the Dispatcher. This request, as part of the VC for that new session, will be placed in the Common Queue where the first available Shared Server will complete the logon. Once each phase of the logon has completed, the Shared Server will pass the VC back to the Dispatcher, which then passes the response back to the Client (this actually takes several round trips to the client, in just the same manner as if it was a Dedicated connection).

Once the logon has completed, the client starts a normal conversation with the database. When the client makes a request to the database, it is the Dispatcher that picks up this request from the operating system’s network protocol. The Dispatcher then determines which client session the request came from (remember that a Dispatcher can be configured for Connection Pooling and Multiplexing: see the Net Administration Guide for more information on those configurations), tags that sessions’ VC that there is a new message (there is also a pointer to that session buffer in the VC) and places the VC in the Common Queue. The CQ is serviced by the Shared Servers on a first-in-first-out basis. The next available Shared Server pulls the VC from the CQ and processes the request. Part of the VC structure is the identity of the Dispatcher that created it (and which client is connected to it). When the Shared Server is finished processing the request, it writes the output to the session buffer, changes the VC’s ownership back to the Dispatcher that created it, places the VC into that Dispatcher’s queue, and posts the Dispatcher it has something in its queue. The Dispatcher then checks its queue and sends what is in the session buffer through the operating system network protocol back to the Client.”

The process of drawing can be divided in 4 stages:
1) installation of this script and it’s requirements (DBD, DBI, instantclient, etc.)
2) creation of sampling tables (plotvqueue_*) — see script comments
3) real collecting the data, running PL/SQL statement, e.g. from screen(1) to sample 24h run of the database with 1 min interval
4) running this script to analyse data
Optional steps (to catch-up performance differences after changes):
5) online changing database parameters (max_shared_servers, etc.)
6) same as step #3
7) same as step #4

The following screenshots comes from real production system before and after tuning (raising max_shared_servers, shared_servers and dispatchers).
commonqueue_may12_13

dispqueue_may12_13

Comments are closed.