oracle学习11g性能调优官方PPTless_04.ppt

Oracle Database 11g: Performance Tuning 4 - * 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. Each server process is typically in one of three states: Idle: Waiting for something to do (sleeping) Running code: Using the CPU or in a run queue Waiting (blocked): For some resource to become available For a requested activity to complete Oracle Database 11g: Performance Tuning 4 - * Top Wait Events 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. Oracle Database 11g: Performance Tuning 4 - * Setting the