Oracle Query Performance Tuning

Successful Oracle database performance analysis tooling depends on identifying which metrics are most important for understanding performance and how to make sense of them. While queries and monitoring throughput are often considered common Oracle performance metrics, other Oracle database performance metrics can go even deeper into the system and may also be important to track.

Some essential Oracle database performance metrics you should consider monitoring include:

Under-allocated RAM: Sometimes increasing your RAM (instead of using the much-slower disk access) can increase your database speed. When you don’t have enough RAM allocated to shared_pool_size, db_cache_size, and Aggregate_target, your database will be forced to operate at the speed of physical I/O. Keeping an eye on these metrics can help ensure this doesn’t happen.

Rows Read/Row Selected Ratio: This ratio tells you how many rows within your database had to be read before there was a return for your specified rows. If the ratio is higher than 20, you may have an issue with your database indexes. Monitoring this metric can save you hours of research in searching for the reasons for a slow database.

Transaction Log Response Time: Your log response times can impact latency during transactions. This can become a significant problem—especially when it comes to critical transactions. By identifying and addressing slow log response times, you can improve your latency issues.

Page Cleaning Ratio: The page cleaning metric has become an increasingly important metric, especially for e-commerce pages and online publishers. This ratio gives you insight into your page cleaners, which write your old pages to the disk asynchronously, allowing your new pages to be read into the buffer pool.

In Memory Sort Ratio: When your database is having issues with speed, this ratio can be an important piece of the puzzle in determining the root cause. The In Memory Sort Ratio tells you how many sorts are being handled on the disk as opposed to in memory. Sorts handled in the tablespace are significantly slower than those in RAM, so if you see more sorts occurring in disk, it may be the reason for your slow speeds.

Excessive nested loop joins: The unfortunate reality is that iterative loop joins are going to be slow. The best thing you can do is dig into the code to try to find better options than nested loop joins wherever possible.