Tuesday, 22 November 2011

Virtual Circuit Wait event

I have found few explanations connected with this event. It is specific for "shared server" configuration of the Oracle RDBMS.
  1. Dick Goulet suggests it is connected with periods of inaccessibility of shared servers due to easy to miss short bursts of load. The solution was suggested for the database in version 9.2.0.4
  2. Jonathan Lewis describes this event in similar way and suggests to check the v$reqdist view in order to get a histogram for duration of calls
  3. Very detailed description is available at Igor Usoltsev site and most up-to-date as it refers to the 11g version. Most important observations are:
    • old event virtual circuit status was split to shared server idle wait (which is recognized as an idle one) and virtual circuit wait (which consists actually of 2 parts: idle one and network non-idle one - the event will be probably split into such 2 components in future versions of the RDBMS)
    • the virtual circuit wait event is strongly connected with the SQL*Net message from client event (which is recognized usually as an idle one) and the existence of one or the other depends on the size of the array used for fetching rows - the bigger the array the more SQL*Net message from client the less virtual circuit wait

In our case we have had this occurred mostly on long-running queries over a db link, so very inconvenient, because even if this event is "partially idle", then for us a response time always includes this event. The solution for us is to tune every query over this db link as well as possible.

No comments: