Oracle Bottleneck Analysis

Whenever a user or application experiences a significant delay in response time from an Oracle database, the system is often described as having a bottleneck. However, before any steps can be taken to diminish the wait time caused by the bottleneck, the bottleneck needs to be quantifiably identified. To properly accomplish this, a thorough oracle bottleneck analysis should be conducted.

Collecting reliable statistical data should be the first step in analyzing the performance of any Oracle database. In Oracle, the timed_statistics initialization parameter is set to "false" by default. To obtain a realistic picture of where wait times are occurring and how long they are lasting, this parameter needs to be set to "true."

Once the information is gathered the database should immediately eliminate wait time events that are considered idle events. For example, the timed_statistics parameter might record the length of time that elapses between commands issued by the user at the command prompt. Instead, the database administrator should focus his or her attention on capturing informative metrics such as resource utilization rates.

Check Resource Rates

The resources to check include the CPU, memory and disk space. There are several resource monitoring tools out there, which can be used to measure the utilization rates of the system's resources. Many operating systems even come preloaded with the tools. The database administrator might also consider investing in one of the many third party tools on the market.