Jun 16, 2020 5:45:00 AM by Gary Jerep
Finding new, effective indexes to improve database performance is hard. How hard? Take a short quiz at the end of this blog. Remember, Toad® Turnpike is about real people doing real things with Toad solutions.
In my previous blog post, DBA Alex described the backlash that kicked up when he introduced just one new index to speed up one user’s report. In short, that one little change on the database caused slow-down issues for other jobs. Alex and his team had to address those issues, and no one was happy. One teeter hour to introduce one index on one table—while creating a huge performance boost for one SQL statement—caused over 90 totter hours of follow-up fixes to re-work other applications whose SQL operations were touching that same table.
Alex’s openness about sharing his experience fostered a healthy discussion about performance management using indexes. Some of the healthiest tuning strategies start with identifying (SQL) workloads that, in turn, identify tables for which new indexes can be identified. And there’s different ways to define such workloads. Here are a few Alex and I discussed:
Alex had thought of some of these approaches before, but they are more easily described than done. Here were Alex’s top three concerns about implementing the strategies above:
Alex confessed that he has a hard time identifying all the SQL. SQL could come from database-stored procedural code, or code external to the database, sure. But what about SQL built dynamically at runtime, or some-time SQL submitted by ad hoc users?
How does Alex identify index candidates as the number of objects accessed in his SQL workload gets larger? How does he keep track of the different ways to combine those indexes?
Even if Alex *could* find a small fraction of the index possibilities, does he have the right tools to measure the performance impact of new indexes introduced to his workload? Ideally, BEFORE they affect Production?
Quest Software solutions can help with each of these challenges, and I’ll discuss this in the upcoming part three of this blog.
The second challenge Alex mentions above is nearly impossible. It may not be difficult to find an index or two that help speed up performance for a few queries here and there. But to find sets of indexes that speed up database activity on a workload basis? That’s beyond hard. Curious? Here’s a quick quiz.
Suppose you have a SQL workload that collectively accesses only five small tables. Each table has only three columns. See if you can guess the answers to these questions. To keep it simple, let’s consider only “vanilla” column-based indexes. Composite indexes, ok, but don’t consider clustered-type indexes, function-based, or other attributes, etc.
Click here to post your answers and compare to other readers’ responses. Answer the quiz questions by July 17 and we’ll send you some cool Toad stickers. The actual answers will be posted next month in part 3 of this blog. Stay tuned!
Check out other real-world success stories about Quest's SQL Optimizer.
Quest Software has been helping database management professionals for nearly 25 years, providing solutions that help you simplify the complex, reduce cost and risk, and drive improved performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate. Learn more about Toad for Oracle by visiting our product page and find out how you can modernize your Oracle database operation to enable business agility.
Learn more about how Toad database management tools from Quest can help with database development, performance tuning and database DevOps.
Our Toad family of products are continually evolving and getting better because we listen to you, our customers.
Do you have questions? Please post questions to the Toad for Oracle forum on Toad World®.
For more real-world stories like this one, and other Toad solution insights, read Gary’s other posts.
If you think your colleagues would benefit from the Toad Turnpike blog series, share it now on social media with the buttons located at the top of this blog post. Thanks!
**AUTHOR’S NOTE: This blog originally appeared on Quest's Communities site...re-posting here for the ToadWorld community.
Written by Gary Jerep
As a Software Consultant within Quest, Gary Jerep has over 20 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis, operations research, Business Intelligence, and end-user experience monitoring.