Setting the Priority

Determine which problem to tune first. In the performance reports, you see many statistics; even a well-tuned database shows a set of top wait events. The Oracle server provides a set of wait event statistics for processes that are idle or waiting. The Oracle server also records CPU utilization for processes that are running. To determine the impact of a particular event, it must be compared with the overall time spent. 

Each request to the database server has a response time consisting of a wait time and a service time. The service time is the time spent actively working on the request (CPU time). The wait time is by definition the time waiting for any reason. Both service time and wait time may be tuned. 

To tune the service time something has to change: the processing, the SQL, the access path, or the data storage structure. Wait times can be tuned by reducing contention for the resource where the wait is occurring. 

How to determine the priority 

You can take help from AWR reports.

The top wait events always have some values. In example shown in the slide, the users are complaining of slow response time. The Top 5 Timed Foreground Events only shows that the instance is using the CPU.

The Instance CPU section show that the instance is using 65% of the OS CPU; this information is inconclusive. The instance is supposed to use CPU and not wait. This set of diagnostics may mean that the instance is CPU bound. As pointed out earlier, performance tuning can either reduce wait time or service time. In this case, the service time needs to be reduced. To reduce service time, SQL is the usual area to be examined.

Setting the Priority: Example

The top wait events did not give a clear direction. So you continue with the time model to find which areas are consuming the DB time. You can determine the top-priority tuning tasks by comparing the time spent in various waits and tasks with the overall wait time and service time. Both major tools report the Time Model Statistics to guide your tuning efforts. For example, the AWR report excerpt in the slide shows that the database CPU time (time in user calls) is 474.04 seconds. The time spent in user calls is 44.85% of the total DB time. The “sql execute elapsed time” shows 1050.06 seconds; this time includes wait times. Just from this limited view, the wait times for the SQL execution are significant, and would lead you to examine the wait statistics related to the SQL execution and the SQL reports to identify individual SQL statements for tuning.

The “% of DB Time” values indicate a relative impact tuning this area could have. If the “sql execute elapsed time” could be reduced, then the maximum possible improvement is 1050 seconds or 99%. SQL will always take some time to execute. Therefore, the actual improvement may be much less, depending on the amount of improvement you can get from that area.