Oracle database performance improvements require continual and iterative efforts to produce results. It is not a one and done activity. Constant vigilance and performance monitoring are required for long-term success.
Neither is it a matter of finding and removing one performance bottleneck. There are likely more than one to be found. In fact, further investigation may lead a DBA to an even greater performance problem.
Oracle database performance problems typically result from lack of data throughput, unacceptable user or job response time, or both. To determine the nature of the problem and create a path to resolution, you must gather as much performance data as possible, including from end users, call centers, or any other stakeholder who can speak to end user system performance.
The next step is to gather Oracle database performance statistics. This can be done using the Automatic Workload Repository (AWR) or with third-party solutions such as Confio Ignite for Oracle. The statistics captured by these tools include:
- Time model statistics
- Wait events
- Session and system statistics
- Active session history
- High-load SQL statistics
Common Oracle Database Performance Issues Defined
Using the above statistics will assist DBAs with finding some of the most common Oracle database performance problems. Here are a few:
- CPU Bottlenecks
When the CPU approaches or exceeds maximum utilization performance problems begin. What is causing the CPU performance issues? Bad queries? Too many consecutive users? Solving CPU issues can greatly improve performance. - I/O Capacity Issues
Lack of I/O subsystem performance can translate to poor end-user performance. Increasing I/O capacity or decreasing I/O loads can assist in disk I/O utilization. - Concurrency Issues
Concurrent activities can lead to contention for shared resources that result in locks or waits for buffer cache. Monitoring session data can show DBAs where locks and waits are occurring while users wait for their request to be processed. Reducing or eliminating these with more efficient queries and database structures creates measurable performance improvements. - Database Configuration Issues
What configuration issues may be contributing to poor performance. Likely candidates include incorrect sizing of log files, archiving issues, excessive checkpoints, and sub-optimal parameter settings. Be sure to look for these first. - Unexpected Performance Loss after Tuning
This is a DBAs worse nightmare, but sometimes tuning SQL statement may result in decreased Oracle database performance. While the exact cause of such a regression isn't predictable, having it impact system performance can be avoided by using a test server to analyze SQL statement performance before launching to production environments.
Look for these and other common Oracle database performance issue when tuning your Oracle databases. Finding and repairing them will result increased database performance and decreased end user complaints.
Post by Josh Stein on behalf of Confio Software.
