Oracle DB Wait Times Test
Oracle’s response time for an operation is composed of time executing (=CPU time) and time spent waiting (=Waiting time). An increase in either or both the above-mentioned factors will adversely impact the responsiveness of the Oracle database server.
When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before it can proceed. For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding database block is not currently in the SGA, Oracle waits for this block to be available for modification. The Waiting time refers to the time spent by the Oracle server waiting for such events to complete. Oracle has a bunch of events that it can wait for - eg., buffer busy waits, db file scattered read, db file sequential read.
Whenever users complaint of a slowdown of the database server, it would be helpful to know where the database server is spending too much time - is the time executing more than the time spent waiting, or vice-versa? To determine this, you should monitor both the CPU time and the Waiting time of the database server. This test enables you to perform ‘half’ this analysis. In other words, this test reports the percentage of time that the Oracle server spent on waiting for one/more events to complete. This way, the test helps you understand whether/not the waiting time is contributing to the poor responsiveness of the server.
This test is disabled by default. To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Oracle Database as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.
Target of the test : An Oracle server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for the Oracle server being monitored.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
DB time spent waiting: |
Indicates the percentage of time the database spent on waiting for one/more events to complete. |
Percent |
A high value is indicative of the following cases:
|