| Howto avoid virtual circuit wait as application developer? [message #592366] |
Wed, 07 August 2013 03:10  |
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 #596246 is a reply to message #592530] |
Thu, 19 September 2013 07:51  |
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 |
John Watson
Messages: 8963
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 |
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  ) )
[Updated on: Thu, 19 September 2013 08:30] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
