The performance teeter-totter—Toad Turnpike: Real stories from the road

“Have you ever made a database change that helped some users, but messed up others?” Part 1 of 3. Remember: This series is about real people doing real things. With Toad solutions.

Do you really want to ask that?

Within the previous month alone, I’ve had three different customers ask me the same question: “Does Quest have a solution that recommends indexes for my SQL Statement?”

It’s a common question.  Short answer: “Yeah, we got that.” 

But then I ask: “Is that what you really want to ask?”  

Before the customer even has time to think about whether I’m trying to be flippant, I ask a second question to clarify my motivation.

“Have you ever made a database change that helped some users, but messed up others?”  One of those three customers I talked to last month was Alex, and he got reminded instantly of what I was getting at.  His name is not real, but his DBA persona is real.  And his experience is real.

From DBA to Hero in minutes…

“Yeah, wasn’t too long ago that I made some changes that put us in a spot,” Alex starts, building his story from there. 

Line Managers on the manufacturing floor were complaining about a slow Materials Management report.  The DBA team used their database vendor’s Tuning Advisor that suggested an index on the Materials Inventory Master table.  Adding that index drastically cut down the report’s run time.  Problem solved, it would seem.

The production line managers were happy with Alex’s team for shortening that report’s execution time.  That extra time gave them a head start with validating the materials inventory for next day’s factory line quotas.  One of those guys (we’ll call him “Paul” … remember that name) even sent a nice e-mail to Alex’s boss and the boss’ boss, saying how much they appreciated Alex and his team’s help.  Alex felt like a hero.

Ah, but hero-status never lasts long, does it?  Alex continues describing the nightmare part of his story.

DBAs do have nightmares…

Fast forward to Alex's weekend that follows.  Alex’s manager gets a late-night call from the onsite Operations Analyst (we’ll call the Ops man “Peter”… remember that name, too… see where I’m going with this?)  The boss calls Alex.  It’s after 2:00 AM Saturday night and something is wrong.  (Does it always have to be on a weekend?   And late at night?  C'mon, really!  And to your boss, no less?)  Alex has serious doubts now about his Sunday morning fishing trip.

One of the weekend batch order entry processes normally takes a half-hour to run.  Tonight, it’s now coming up on two hours, and the job is still running when Alex’s Manager calls him.  The weekend batch-processing schedule is now dangerously delayed.

For the readers who have lived through scenarios like this one, you may have guessed already what happened.  While that new index improved the SELECT query that drives the daily materials report, that same index is now causing a major snarl-jam in one of the weekend batch UPDATE jobs. 

We have here the classic proverbial case of robbing Ops guy Peter to pay Line Manager Paul.  Peter's role is concerned with overall database performance for all applications, but Paul is concerned only about his report’s performance.

Sometimes, really long nightmares…

Alex quickly implemented a stop-gap resolution that weekend: he simply disabled that new index right before the batch update job starts. Then, the table gets re-indexed after all updates are complete.  Simple enough fix.  But that didn’t stop a few other mass-INSERT/UPDATE jobs from having similar trouble later that morning.  To make matters worse, more jobs were affected by the same issue a few weeks later, during the month-end processing window. All told, Alex says he and his team logged over 90 total team hours of effort to investigate, band-aid, and test other jobs whose SQL runtimes were affected adversely by the new index on that one master table.

Get me out of this mess!

Think about this… Alex spent less than an hour testing and implementing a suggested index that teetered in favor of one man’s report performance.  But with that teeter came a totter that negatively impacted other processes and caused Alex and team 90 times that effort to fix.  90 times!  Could he have avoided that pain?   Could he have foreseen which processes might be affected by the original change, and by how much?

Quest has a solution that can avoid pain in scenarios like Alex’s. It’s called SQL Optimizer. SQL Optimizer is included in Toad for Oracle Xpert Edition and above.

Yes, that product can re-write bad SQL to find better execution plans, but can it suggest indexes for your one query? 

Short answer: Yeah, we got that. But is that the real question to ask?  Quest takes a more holistic approach to index tuning, one that extends the tuning scope to multiple SQL statements, not just one, or two.

Here’s the 90- hour question for you:  would you know which SQL statements to consider to index-tune?   If you don’t know WHAT they are—or WHERE they are—how can you come up with the best potential for improvement?   How could Alex have identified up front which SQL statements should be looked at, and not stop at just one?   Turns out thatQuest’s SQL Optimizer can help here too.   In Part 2 of this blog, I’ll outline some important approaches to consider for index-tuning, and a few challenges to overcome while doing it.

Interested in learning more about Query/Index Optimization?

Check out other real-world success stories about Quest's SQL Optimizer. 

• "Tuning Makes Friends in High Places", another of my Toad Turnpike series blogs
• "SQL Optimizer for Oracle – a success story", a success story from one of my colleagues

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.

Got questions?

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®.

More blogs from Gary

For more real-world stories like this one, and other Toad solution insights, read Gary’s other posts.

Help your colleagues

If you think your colleagues would benefit from the ToadTurnpike 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.