Oracle mechanics

Igor Usoltsev's Oracle blog

virtual circuit wait

Posted by Игорь Усольцев on March 26, 2011

When using shared (multi-threaded) servers connections with queries returning huge number of rows, the wait events virtual circuit wait may became noticable. Top 5 from AWR report for Oracle 11.1.0.7, for examle:

                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                           41,456          24.1
db file scattered read           16,480,224      40,887      2   23.7 User I/O
virtual circuit wait                674,827      39,310     58   22.8 Network
db file sequential read           1,820,163      27,374     15   15.9 User I/O
direct path read temp               166,394       3,653     22    2.1 User I/O

Adequate event description from Bug 6653834 – Split “virtual circuit status” into idle / non-idle waits [ID 6653834.8]:

This is a performance monitoring enhancement to split the ‘virtual circuit status’ wait event into two new wait events:

“shared server idle wait” – for when the shared server is idle waiting for something to do
“virtual circuit wait” – for when the shared server is blocked waiting on a specific circuit / message

i.e starting from Oracle  version 11.1.0.7/11.2.0.1 wait event virtual circuit status was splitted into two: shared server idle wait (wait class idle) and virtual circuit wait – non-idle event (wait class – network)

SQL> select name, wait_class
2    from v$event_name
3   where name in ('virtual circuit wait', 'shared server idle wait');

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
virtual circuit wait                                             Network
shared server idle wait                                          Idle

Let’s make some tests to clarify this event actual meaning

Oracle 11.1.0.7, shared server connection:

SQL> show arraysize
arraysize 15
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 08:54:28.07
...
Plan hash value: 3415772365
...
Statistics

----------------------------------------------------------
...
11281848  SQL*Net roundtrips to/from client
...

When using arraysize 15 (default for SQL*Plus) wait events distribution shows more then 22.500.000  virtual circuit wait events for this huge query (this good example was executed not specially for wait event investigation of course):

EVENT                       WAIT_CLASS    TOTAL_WAITS TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT,CS
virtual circuit wait        Network          22538592              0        2420249            0,11
db file scattered read      User I/O            69400              0         237079            3,42
gc cr multi block request   Cluster            402246              0          56108            0,14
SQL*Net message from client Idle             11281894              0          38453               0
...
SQL*Net message to client   Network          11281871              0           2439               0
...

Except the longest part of query (fetch phase) time was spent on virtual circuit wait, the proportion of events virtual circuit wait : SQL*Net message from client : SQL*Net message to client is almoust equal to  2 : 1 : 1. In sql trace file this looks like repeatable FETCH records:

FETCH #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3415772365,tim=1298897604041737  -- r=15: ARRAYSIZE
WAIT #6: nam='virtual circuit wait' ela= 20 circuit#=76 type=2 p3=0 obj#=942592 tim=1298897604055373
WAIT #6: nam='virtual circuit wait' ela= 1758 circuit#=76 type=2 p3=0 obj#=942592 tim=1298897604057143
WAIT #6: nam='SQL*Net message from client' ela= 41 driver id=1297371904 #bytes=1 p3=0 obj#=942592 tim=1298897604057158
WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1297371904 #bytes=1 p3=0 obj#=942592 tim=1298897604057181
FETCH #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3415772365,tim=1298897604041737

Every two events virtual circuit wait (one short + one long (ela= 1758 microseconds ~ 1,8 ms) followed by one SQL*Net message from client and one SQL*Net message to client events

Oracle support specialists explain these two trace event record in the next way:

it is a shared server and therefore some synchronisation needs to be done. This is shown in the wait event ‘virtual circuit wait’. Most of the time shown there is also considered idle time and will be reported in future release accordingly

Oracle writes two events virtual circuit wait: first, short time (ela= 20) – waiting for synchronisation reasons (from shared server connection usage) and second, long event (ela= 1758) – idle wait event, which is recorded as SQL*Net message from client when the dedicated server connection is used. These two different (by meaning) wait events are recorded in trace file with the same parameters circuit#=76 type=2

To check analogy with well-known SQL*Net message from client event I increased arraysize to 5000 (maximum available value for SQL*Plus):

SQL> set arraysize 5000
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 01:20:34.92
...
Plan hash value: 3415772365
...
Statistics

----------------------------------------------------------
...
33848  SQL*Net roundtrips to/from client
...
169227681  rows processed

Huge query execution time decreased almost 7 times, and event virtual circuit wait went away from the wait time top

EVENT                         WAIT_CLASS    TOTAL_WAITS    TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT
db file scattered read          User I/O          69417                 0         195355            2,81
virtual circuit wait             Network          75810                 0          61933            0,82
gc cr multi block request        Cluster         312639                 0          52799            0,17
SQL*Net message from client         Idle          33892                 0          11521            0,34
...

As we can see virtual circuit wait looks like the classical event SQL*Net message from client, which strongly depends on SQL*Net roundtrips to/from client count and used arraysize value respectively

What will change in this testcase for dedicated server connection?

SQL> show arraysize
arraysize 15
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 07:13:10.32

Execution Plan
----------------------------------------------------------
Plan hash value: 3415772365

Statistics
----------------------------------------------------------
...
11281847  SQL*Net roundtrips to/from client
...

With default arraysize=15 the query was executed a bit quicker ~ 20%. But this acceleration isn’t linked to discussed idle waits – we can see 2,420,249 cs of virtual circuit wait with shared server connection against 2,454,168 cs of SQL*Net message from client with dedicated server. Improved query execution time was only different system workload effect imho:

EVENT                        WAIT_CLASS    TOTAL_WAITS    TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT
SQL*Net message from client        Idle       11281870                 0        2454168            0,22
gc cr multi block request       Cluster          36584                 5          32780            0,9
direct path read               User I/O          77241                 0           9931            0,13
db file scattered read         User I/O            854                 0           2560            3
SQL*Net message to client       Network       11281871                 0           2260            0
...

As far as I understand the dedicated server connection usage may NOT give any significant improvement in query execution time (fetching phase) at least for version >= 11.1.0.7. Some insignificant query acceleration can be achieved from the very short part of virtual circuit wait event  (I mean the “real” waiting on a specific circuit / message) – something about 20 microseconds of  1778 (1758+20) ~ 1% only in my trace example

Whole evolution path of virtual circuit wait events, looks like this (imho):

                          11.1.0.7/11.2.0.1                                Oracle 12(?)
virtual circuit status -> shared server idle wait (Idle)
                       -> virtual circuit wait (Network, Idle+non-Idle) -> ?virtual circuit wait (Idle)
                                                                        -> virtual circuit wait (Network, non-Idle)

virtual circuit wait event will be separated into two diffent parts: Idle and non-Idle (Network WaitClass) wait events, if I correctly understand

Sometimes this is interesting to check (or control) what arraysize value is used by applications (or developers):

SQL> select--+ RULE
2         sql_id,
3         --sql_text,
4         fetches,
5         rows_processed,
6         round(rows_processed / decode(fetches, 0, 1, fetches)) as rows_per_fetch
7    from gv$sql
8   where (inst_id, sql_id) in (select inst_id, sql_id
9                                 from (select inst_id, sql_id, count(*) ccount
10                                         from gv$active_session_history
11                                        where EVENT = 'virtual circuit wait'
12                                        group by inst_id, sql_id
13                                        order by 3 desc)
14                                where rownum < = 10) 15   and COMMAND_TYPE = 3 --SELECTs only 16   and rows_processed > 0
17  /

SQL_ID           FETCHES ROWS_PROCESSED ROWS_PER_FETCH
------------- ---------- -------------- --------------
136fmurj44n9h        955        9550000          10000
38xhnjzwkp91s       1459       14580000           9993
2kmm08qg8rrrb       1099       10990000          10000
b1yp9p84xgxqf       6090       60890548           9998
g8vthx4t6cm9v       1004       10028942           9989
grsbfbbvg6tpq       4357       43560748           9998
58jks1y60urf8       1257       12563447           9995
bcmfsj5w3bn4a       1018       10180000          10000
f5q7u9rpsz8cb       1418       14180000          10000
8zdmfzpbh4j1x       1196       11953562           9995

This looks ok, arraysize 10000 is huge enough for the most waiting sql’s from v$active_session_hitory

P.S. Just interesting fact about the shared server connection from Joel Goodman blog

After discussing this with some support colleagues it was confirmed that the kernel treats both SYSOPER and SYSDBA sessions differently and that these sessions do NOT share their servers when using shared server. This means that several idling sessions connected as SYSOPER or SYSDBA could severely deplete the shared server pool and adversely affect performance

Advertisements

2 Responses to “virtual circuit wait”

  1. souluran said

    Thanks! Your post is helped me understanding about virtual circuit wait in Oracle DB.

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

 
%d bloggers like this: