Another problematic wait event in Oracle is “SQL*Net break/reset to client”. This wait event typically happens when there is something syntactically wrong with an SQL statement but can happen when there are errors such as a constraint violation.
The problem with analyzing this wait event is that since the query had an error the associated SQL is not kept by Oracle. It’s thrown away so it can’t be looked up in v$SQL for example.
The only way to find out the SQL statement is to trace the problem session before the wait occurs. We’ll that’s easier said than done. Mostly I want to investigate after a problem and I certainly want to avoid tracing sessions willy nilly on my system.
One idea, which I have to investigate is getting the
PREV_SQL_ADDR
PREV_HASH_VALUE
and tracking down this SQL. If that is successful then it maybe a means at least to see where the application was just before the error/wait occured, with the idea being that the next SQL statement in the applicaiton is the problem.
Of course this is not of much use if the application has no syncronisity between which statements are called, but first I want to investigate whether this is an option or not at all.