Oracle RAC DB Wait Time 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 cluster service.
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 while accessing databases in a cluster, it would be helpful to know which node is experiencing a slowdown and where it 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 each node of the cluster. This test enables you to perform ‘half’ this analysis. In other words, this test reports the percentage of time that every node 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 cluster service and which node has been waiting too long.
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 the Oracle Cluster as the desired Component type, set Performance as the Test type, choose the 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 : Oracle Cluster
Agent deploying the test : An internal agent
Outputs of the test : One set of results for each node in the Oracle cluster being monitored
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
DB time spent waiting: |
Indicates the percentage of time this node spent on waiting for one/more events to complete. |
Percent |
A high value is indicative of the following cases:
|