Here's the strange part: both the application user and the Oracle server process are waiting! That is very unusual indeed. If the user has executed a command and is waiting for the command to complete, while at the same time, the associated Oracle server process is waiting to hear from its client process, then there is problem between the two. The two broad problem areas are the network and the client process.

Referring back to my customer, the problem was indeed the client process. We checked the network, and multiple tnsping executions resulted in a very fast and dependable network. I asked my customer what the application was doing. The answer was something like, "Well, it's doing all sorts of very advanced processing related to inventory management." I responded, "I bet it's doing a lot of advanced processing, because that's what you're waiting for!" Armed with this simple session-level response time profile and the underlying SQL, the manager was able to approach the vendor with full confidence that the problem was clearly focused on the application. And guess what? The vendor listened and fixed the problem!

Modern-day Oracle architectures make identifying specific Oracle sessions sometimes impractical, if not impossible. Due to multitier architectures, identifying a specific user's activity may simply not be worth the effort. However, the client identifier column in v$session can be used to identify a user or perhaps just as useful, a group of users.