Toad® Turnpike: Real Stories from the Road—Tuning makes friends

Real stories. Real people. Doing real things. With Toad® solutions.
This particular blog is about the Query Optimization capabilities found in Toad for Oracle (Xpert Edition or higher). Want to know how a Quest customer found a minutes-long alternative for a 13-hour query?  Keep reading to find out how tuning helped her to make friends, and save her company $40,000 hard money.

Here’s a blog series about our Toad® solution family, and how it’s changed lives for more than 20 years. Sometimes a vendor’s product does extraordinary things for its customers. The stories you read in this series are real accounts about real people doing real things.

What do you do with a 13-hour query?

A few years ago DBA Bethany (not her real name) calls me on the phone. This is unusual. As a solutions consultant, I usually do the calling out to customers, not the other way around. Bethany called to explain that her company had purchased Toad a few months ago, and she was just now getting into it. Toad has been identifying for her queries that consume heavy resources on her database.

One of those queries she knows well. It’s a query coming from an application Bethany’s company inherited a year prior when the company acquired a smaller company. The query executes twice a week for over 13 hours each time. Not only does it consume disk and memory resources, but the query lengthens their weekend batch window to the point where weekend batch processing could start spilling over into normal business hours. They’ve known about this query since day one, but no one pursued any tuning on it because “it always runs that long” according to the original owners of the app.

But Bethany’s voice right now sounds like a crackling high-voltage line. She is excited. Toad had done something better than merely drawing attention to this nasty SQL. In two minutes’ time, Toad had discovered about 20 different alternatives for that one 13-hour query. Of the 20 alternatives, one yielded the same data as the original 13-hour query but took only 16 minutes to complete. Bethany thought this was a fluke, but became an excited believer when the alternative query kept completing consistently around 16 minutes during each of her test runs. She promptly swapped out the bad SQL in production with the good one.

Bethany has good reasons to be excited. For one, the quicker query alternative shortens their batch processing window on weekends, allowing more buffer time to recuperate from occasional hiccups in weekend processing.

Suddenly, the Support team is Bethany’s friend.

Secondly, she is saving expensive resources on the database server: Just this one query runs now in 32 CPU- minutes per week vs. 26 CPU- HOURS.

Suddenly, the guys on the Operations team are her friends.

Thirdly, Bethany’s tuning efforts saves money for the application owner on the business unit side because the BU will be charged 25 CPU-hours LESS per week for the application’s processing time. She is saving the BU internal charge-back dollars.

The application owner is now her friend.

Bethany’s a hero. But that’s not why she called me. The REAL reason for her excitement (as if being dubbed a hero and being everyone’s friend isn’t enough already!) came when Bethany continued her SQL tuning efforts during the weeks that followed, finding more and more consumptive SQL code, and tuning the “bad boys” as she calls them.

Whoa! 20 times ROI with Toad?  In less than two months?

So today, she tells me in a delightful squeal that she recently saved her company over $40,000 hard cash. How does a product like Toad return 20 times more money than she paid for it? You better believe she had my ear.

Bethany’s IT team had known for a while that the database server platform was consistently edging toward dangerous load levels, frequently edging to near-100% CPU capacity. High time to upgrade the database platform with a more powerful server, and maybe better disk, wouldn’t you say? But Bethany’s flurry of tuning efforts made it apparent that running well-tuned, optimal SQL code was yielding improved throughput capacity on the database server and lowering CPU consumption. So much so, that a decision had just been made by IT management yesterday to pull back a pending $40K purchase order (remember that server/disk upgrade we mentioned?). Turns out that Toad is helping Bethany’s company to leverage more of the current computing investment without the need to upgrade hardware. And that got the attention of the company’s CEO.

Suddenly, Bethany has friends in all the right places.

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!

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

**AUTHOR’S NOTE:This is a re-posting of the first blog in the Toad Turnpike series, originally posted on Quest’s Communities site.

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