Home » RDBMS Server » Performance Tuning » Howto avoid virtual circuit wait as application developer? (11gR2 (11.2.0.3))
Howto avoid virtual circuit wait as application developer? [message #592366] Wed, 07 August 2013 03:10 Go to next message
bet32978
Messages: 4
Registered: November 2009
Junior Member
Hello,

I'm an application developer of an automotive company and developing a lot of database-based applications with either oracle forms or c#.
Since we've moved from a 10g rac to 11g using a shared server configuration, the prevailing and overwhelming topic of addm performance analysis is "unusual network wait event" caused by virtual circuit waits. Therefore I cannot use grid control to detect bad sql as I could in 10g anymore, because all "tunable" sql is wiped out by virtual circuit wait.
In top activity, I see virtual circuit wait on every type of statement (select, insert...) and pl/sql execution.

My Question is:
What do I have to do as an application developer to avoid virtual circuit waits?
Especially in C#: we normally use auto committed dml statements and selects to fill either a datatable or generic list with a data reader. Usually we close a connection after each statement, but/and we are using connection pooling. How can such a activity cause virtual circuit waits?
In Oracle Forms: Seems that we have a virtual circuit wait if we show sorted data in a block where not all records are fetched from database. It doesn't make sense to us to rewrite all blocks to always get all records due to performance reasons.

How do I have to write and execute my statements in C#, oracle forms and/or pl/sql to avoid virtual circuit wait?
Re: Howto avoid virtual circuit wait as application developer? [message #592530 is a reply to message #592366] Fri, 09 August 2013 08:09 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Hello, I've found this reference on the web: I just upgraded from 10g to 11g and I now see poor performance. My AWR report looks like this, with high Oracle virtual circuit wait events:
. Mentions MOSC Note 1225943.1 titled High virtual circuit waits after upgrade
Re: Howto avoid virtual circuit wait as application developer? [message #596246 is a reply to message #592530] Thu, 19 September 2013 07:51 Go to previous messageGo to next message
bet32978
Messages: 4
Registered: November 2009
Junior Member
That doesn't help very much. Assume there's no shared server or dispatcher problem (from awr reports)

gv$session_wait says p2=2 (95%) or p2=10.

but my question wasn't how to see that there are virtual circuit waits but how to avoid them as a developer.

I have a statement:
select * from address_table (24cols, 150k rows) order by name

->session in virtual circuit wait

P1 circuit# 319
P2 type 2 (most typical operations) waiting on a receive from the client

-> What should the client send here, and why doesn't the client send what is needed (oracle forms, odp.net) and last but not least:

******how can I avoid that (specific, and then others) virtual circuit wait.*****

[Updated on: Thu, 19 September 2013 07:53]

Report message to a moderator

Re: Howto avoid virtual circuit wait as application developer? [message #596248 is a reply to message #596246] Thu, 19 September 2013 08:07 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that you actually have a problem? I think I saw the same issue with an APEX application that was using the embedded PL/SQL gateway (which also uses shared server) and as long as I had enough shared servers to support the number of concurrent queries.
I suspect that the virtual circuit wait is basically an idle event: the shared server is waiting to be asked to do something, and will be released when needed elsewhere.

I am of course open to correction on this.
Re: Howto avoid virtual circuit wait as application developer? [message #596249 is a reply to message #596248] Thu, 19 September 2013 08:28 Go to previous message
bet32978
Messages: 4
Registered: November 2009
Junior Member
The 'virtual circuit status' wait event which was available in pre 11.1.0.7 releases is now removed and replaced by two new wait events: 'shared server idle wait' and 'virtual circuit wait'. The wait on the common queue uses 'shared server idle wait' and all the other waits use 'virtual circuit wait'

therefore I don't think it's an idle event (I think it is stated as such in documentation, but not in the metalink note listed above)

-> the problem is: in oracle 10g, we had a chance to determine bad sql in top activity or addm results.
now we only have the addm result: 100% network:virtual circuit wait, and I don't think that we improved all our sql statements in such a short time...
at the moment we don't have performance problems, but we just migrated the half of our applications to 11g. as I said, I watch the situation more from a developers point of view, not as dba (they don't react on bad sql/ blocking sessions etc anyway until at least one node goes down Wink) )

[Updated on: Thu, 19 September 2013 08:30]

Report message to a moderator

Previous Topic: snapshot skipped in between the scheduled time (AWR)
Next Topic: Please help me to tune this query
Goto Forum:
  


Current Time: Thu Mar 28 14:50:10 CDT 2024