Wednesday, January 8, 2020

"virtual circuit wait" Wait Event in AWR on a Dedicated Architecture Server

Problem:

I noticed "virtual circuit wait" wait event in the AWR report for one of the databases in the TOP 5 Wait Events:









Analysis:

Honestly I didn't know what this event for, I googled it and landed on (Doc ID 1415999.1) which is discussing the same event on Shared Server architecture, but wait a second my database architecture is Dedicated not Shared!

Connections come to the Shared Server through Dispatchers, then let's check if the connections are coming through the Dispatcher:

SQL> col Network for a80
SQL> SELECT accept "Accept", bytes "Total Bytes", owned "Current_Connections", CREATED "Connections_History"
FROM   v$dispatcher  ORDER BY 1;


Acc Total Bytes Current_Connections Connections_History
--- ----------- ------------------- -------------------
YES  1760871229         1640          304735


From OS, I can see the connections that are coming through the dispatchers:

$ netstat -tplna|grep ora_d|grep QA1(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 x.x.x.x:1521           x.x.x.x:53642         ESTABLISHED 980/ora_d000_QA1
tcp        0      0 x.x.x.x:1521           x.x.x.x:59358         ESTABLISHED 980/ora_d000_QA1
...


This command shows how many connections are established it should return same number from above query +1
$ netstat -tplna|grep ora_d|grep QA1|wc -l
 1641

Ideally on a Dedicated Connection server, connections should not suppose to come through the dispatcher, now let's check the dispatcher setting on this database:

SQL> sho parameter dispatchers
NAME                     TYPE     VALUE
------------------------------------ -----------
dispatchers                 string     (PROTOCOL=TCP) (SERVICE=QA1)


Now it's clear that the dispatcher is set to the same service name which is being used by the applications, which makes sense why application connections are coming through the dispatcher which also justifies the existence of "virtual circuit wait" wait event in the AWR report !

Solution:

I've fixed this wrong configuration by setting the "dispatchers" dynamic parameter to its default to handle only the connections coming through XDB service:

SQL> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=QA1XDB)';
System altered.

SQL> sho parameter dispatch

NAME                     TYPE     VALUE
------------------------------------ -----------
dispatchers                 string     (PROTOCOL=TCP) (SERVICE=QA1XDB)


Now the new application connections will not pass through the dispatcher, connections that are already connected through the dispatcher will not get impacted.