Tutorial Flow:
- Determining the problem category
- Performing detailed diagnosis of the problem
- Identifying the scope of the SQL statements involved
Determining the problem category
As part of the initial diagnosis, the database administrator looks for
familiar patterns of symptoms to categorize the problem at a high level.
This example uses the MON_GET_SERVICE_SUBCLASS table function.
Note: Set the refresh interval on the top right pane to 5 seconds.
Some metrics that indicate some general problem categories
- TOTAL_CPU_TIME (Higher than normal) :: High processor usage is a good indicator that the slowdown is located within data server processing. The data server is working harder than normal to handle application requests.
- CLIENT_IDLE_WAIT_TIME (Normal) :: The slowdown is NOT located in the client.
- TOTAL_WAIT_TIME (Normal) :: Requests are NOT spending more time waiting for resources needed such as locks or for input and output.
- TOTAL_SECTION_PROC_TIME (Normal) :: The execution of SQL statements (access plan sections) is NOT slowed down.
From this evidence, the database administrator is able to rule out some common explanations for performance problems. The data server is expending more resource to process requests and statement execution processing is not slowed down. The slowdown is occurring either before or after statement execution.