Effective Oracle Database tuning
Many years ago, when dinosaurs roamed the earth and graphical installers were the exception rather than the norm, I learned my first lessons in trying to resolve database performance problems. These lessons mostly revolved around learning how to check various metrics in the database to get a health check. Measurements such as the database cache-hit ratio were used to declare the database “healthy,” and when they were out of alignment, there were steps to take to bring them back in line.
Unfortunately, many times these metrics had nothing to do with the source of the problem the users were complaining about. When the database administrator (DBA) would say “The hit ratio is fine, there is no problem in the database,” the user would often still be saying “But my report is still taking an hour to run.” This resulted in database tuning being somewhat of a black art, where the DBAs would keep gathering more measurements until the users agreed that the database was fixed, or more often, I expect they just gave up on getting better performance.
The performance bottleneck
When we take a step back and look at computing systems and how they have grown over the years, we see that some components have grown by leaps and bounds. CPU speeds and network bandwidth have grown dramatically in the past 10-15 years. Storage systems (hard drives) have grown in capacity (my first hard drive was 20 MB, my laptop now has 512 GB) but performance increases stalled out in the spinning disk arena. This difference means that many times systems with performance problems have run into a bottleneck related to being able to store and retrieve data.
For a while, the solutions we tried involved adding more cache to storage arrays, but as data sizes grew, this became more and more expensive. Tuning applications and databases to create more efficient storage utilization is very helpful, but the amount of testing needed to ensure no unwanted side effects limited the number of changes that could be implemented. Business owners are more interested in adding more features to solve new business problems, rather than just fixing old code to run better.
The age of flash storage
In the past several years, a new technology has made a splash in the storage market. Flash is everywhere now. It has allowed unprecedented storage on our smartphones and tablets and gives us faster storage on our laptop computers. It has also landed right in the middle of data centers around the world. It takes different shapes and names. Whether you are talking about solid-state drives (SSDs) or all-flash arrays (with SSDs or other flash technology), it has started to revolutionize the storage market.
IBM has a leading family of storage systems to meet a wide variety of client and business requirements. A key component of that family is our FlashSystem products. I won’t tell you that FlashSystem is the solution to all of your performance problems, but I will tell you that after over a dozen years as a DBA dealing with performance problems, the vast majority of the problems I have dealt with were solved or would have been solved more quickly and easily with a faster storage layer.
No-charge AWR report review
We offer a service without charge to our clients looking to solve performance problems with their Oracle Database. We will analyze an Automatic Workload Repository (AWR) report from your Oracle Database and prepare a custom report for you, showing you if your system is currently bottlenecked on storage or if there is some other area that you need to focus on to improve performance. No black art here, just the facts, based on your system performance. Talk to your storage sales team and ask them about it. Or stop by our booth at Oracle Open World and we can talk about your system performance issues.
The post Effective Oracle Database tuning appeared first on IBM Systems Blog: In the Making.