First, there was no denying the users were waiting and staring at their screen for 19 seconds. I was standing there watching them! The Oracle CPU time was gathered from v$sesstat, the network and client time was wait event SNMFC time, database server IO was all IO-related wait event time, and any leftover time was placed into a category I called unaccounted for time. No one disputed the numbers, as they could be easily demonstrated and were based on the rtsess.sql OraPub script.5 Obviously, the majority of time was spent either by the Oracle client process or network activity between the client process and the server process. We did a few quick tnsping executions, and as everyone expected, communication to the database server on the floor below us took around 1 ms. This meant nearly all of the 12 seconds was consumed by the Oracle client process. It turned out the application was doing some fairly advanced numerical calculations, which required significant CPU resources. The vendor could have responded that the user's PC was undersized, but the vendor knew the customer's PCs met the stated sizing recommendations. This squarely placed any real performance improvement on the vendor. It was one of those rare (but not impossible) situations where focusing on the other two circles (Oracle and the operating system) would not materially improve performance.

The unaccounted for time of 3 seconds is important to understand. There are many reasons for this time. Because the session was not being traced, the data collection was manually started and stopped. The manual collection can and does produce some error, but this can be minimized by familiarizing yourself and your user with the script. There are also issues related with the posting for SNMFC wait time because v$session_event is updated perhaps only every 3 seconds or when the wait situation changes. So from a script development perspective, you need to be aware of this.

The final cause for the unaccounted for time is simply because there could be 3 seconds of time between the Oracle client process and the user's experience, which is the missing component of the true end-to-end user time.