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.
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.
