At the recent Oracle users conference COLLABORATE 13, IOUG hosted an evening hands-on lab for attendees. The turnout was great—even after a full day of conference sessions! A majority of those in attendance were DBAs, with a smattering of database developers. The crazy thing is, if we had kept the lab open into the wee hours of the night, quite a few would have stayed for the long haul. What topic drew such a dedicated crowd, you ask? Two words: Performance Tuning.
This topic, while incredibly important in database environments, is a bit of a tricky one. There is no one set solution, no tuning silver bullet. What might have worked for one release of the database may not be the same for the next release. This is why so many people showed up for a late night lab: There is always something new and exciting to learn and add to your performance tuning bag of tricks.
Is the database ever fast enough? Maybe when no one is using it! A DBA is never satisfied with the performance of the database. There will be times when no one is complaining that it is slow, but there will also be times when the database performance is costing the business quite a bit of money. There are always parameters, system settings, and queries to adjust and modify. The challenge is knowing which ones to modify, and where to start. This comes from experience in different database environments and an understanding of what might be happening on the database system.
There may not always be a quick answer to a performance problem, but there are some things you can do to gain the experience and knowledge needed to be able to address any issue that comes your way.
Understand your environment. This means everything from knowing when the maintenance jobs are running to identifying every application connected to the database. This also means getting benchmark information as well. The words “slow” and “fast” are relative—how slow and how fast is it? It is essential to have benchmark data gathered at peak times and when the system is quiet. These will come in handy when actually addressing any myriad of issues. The before times and after times will demonstrate the improvements, and provide additional benchmark information to compare against later on.
Have a checklist. Every tuning situation is different, but having a set list that can be checked quickly will help you either solve the problem quickly or rule out a simple issue. Check for processes, and see if there are any blocking issues or waits. Check for long running queries, even if they are not part of the immediate problem, and gather those to add to a tuning list for later. Validate statistics of the database objects if they are current and available. Check the database logs for anything that stands out. Compare benchmark jobs against the times that are currently being seen. Check the overall system. Is there anything with resource issues? Build yourself a list of about 10 things to check right away to troubleshoot and hang it up on your wall (or fridge).
Build your portfolio. In every tuning situation, gather the information you checked and found. Build a set of examples and a set of possible solutions. In tuning one side of an equation, you may find statements on the other side that take a hit, so include those types of examples of things to watch for in your checklists and benchmarks.
Build your experience and knowledge. If you have the opportunity, tune queries in test environments. Set up a sandbox area to test your benchmark when things change or when you experience performance issues. Having a place to test parameter changes, indexes, and other options is the best way to gain experience. Learn from others! There might be different applications running in your environment, but learning from your performance tuning partners in crime increases your options and diversifies the tuning tool kit you have available. Another great place for resources on tuning is the IOUG website. You can access great white papers and content from COLLABORATE 13 and previous conferences, and sign up for upcoming webinars and events to build up your portfolio and checklists and become a performance tuning grand master. Check out upcoming in-person and virtual events from the IOUG at www.ioug.org/events.
Performance tuning is one of the most challenging parts of being a DBA. You might find though, when you break out your well-prepared tool kit and solve a particularly challenging problem plaguing your database, that it is good to be a DBA.