Toad World Blog

The performance teeter-totter part 3: Toad® Turnpike [Finding indexes]

Jul 21, 2020 11:45:00 AM by Gary Jerep

Part three in a blog series about SQL optimization, index tuning and yes … the quiz answers!

Toad Turnpike: Real stories from the road.

Optimizing indexes? It’s hard.  Really, really hard.

At the end of my previous blog post, I gave you a pop-quiz. Remember the scenario?  You have five small tables, and each table has only three columns. That’s it. For simplicity’s sake, I’ll keep the answers short here, but see the forum thread discussion here, where you’ll see others’ quiz responses, and where I get into more details regarding calculations and practical considerations.  What were the questions again?

  1. What is the total number of possible indexes on just one of the tables? On all five tables?
    15 indexes per table, assuming you can define one-, two-, or three-column indexes. 
    75 total possible indexes on the 5 tables.

  2. What is the total number of different combination sets of these indexes, considering a max of three indexes at a time?
    70,375 index sets.

  3. What is the total number of index sets, considering up to 10 indexes for each set?
    Almost one trillion such index sets (973,602,516,870 to be exact).

  4. (Extra Credit) What is the number of all possible combinations of all the indexes?
    2 to the power of 75,
    which is beyond enormous, by the way.  It is so large that it would take the fastest computer close to 1,198 YEARS of continuous operation to define all the index sets here, assuming it could generate them at one trillion sets per second!

What’s the real point of the quiz?  Simply this: most of us don’t realize how super difficult it really is to identify an effective set of indexes for the way your applications access database tables.

Develop your own applications in-house?  Purchase vendor-supplied solutions?  Either way, you’re sure to have more than five small database tables, and you’re going to need some super-duper uber-intelligent assistance to find the ultimate set of indexes to make your applications hum.  Quest® SQL Optimizer, has that uber-intelligence.  Plain and simple. 

Overcoming SQL / index optimization challenges

Remember Alex from my previous post?  Here were his challenges.  And here’s how Quest SQL Optimizer overcomes each of these.

Overcoming challenge #1: Finding the SQL
Alex confessed that he has a hard time “seeing” all the SQL that hits his databases. 

Quest SQL Optimizer has parsers that can search for SQL in:

      • Database code (SQL embedded in stored procedures, packages, view definitions, etc.)
      • Source code off the database (like Java source code, or shell scripts, for example).
      • Database SQL memory (for SQL built dynamically at runtime or submitted by ad hoc users).

Overcoming challenge #2: Finding feasible index combinations
How does Alex identify and keep track of index candidates as the number of objects accessed in his SQL workload gets bigger?

You took the quiz. You’ve seen the numbers. This task is humanly impossible. But when you feed your workload to SQL Optimizer’s Index Analyzer, the product employs AI techniques and smart genetic algorithms to rapidly find feasible sets of candidate indexes.  And it does this exponentially faster—literally!—than the best consultants your money can afford.

The screen capture below shows how SQL Optimizer finds several index sets only mere seconds after the search starts. Other index sets are discovered at other points in time. In minutes!

SQL Optimizer finds several index sets only mere seconds after the search starts. Other index sets are discovered at other points in time. In minutes!

 

Overcoming challenge #3: Testing the impact of index combinations
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 impact Production?

SQL Optimizer can use virtual indexes to assess the performance impact of proposed indexes on your SQL workload, as the screen capture below shows. 

Using virtual indexes, which have negligible impact on production databases, SQL Optimizer can assess the performance impact of such indexes on your SQL workload.

 

Bottom line

So there you have it, a one-two-three punch.  Plain and simple.  Quest SQL Optimizer can:

  1.  Find various SQL-based workloads 
  2.  Analyze the object access patterns to recommend candidate indexes
  3.  Assess the impact of suggested indexes

 

Try SQL Optimizer for Free

SQL Optimizer is included in many of our Toad elite editions like Xpert, Dev and DBA, and not just for Oracle, but also for SAP and Microsoft SQL Server. If you’re already using a Toad product, check to see which edition you have and consider upgrading. Or, try SQL Optimizer out for free.

Add on data modeling, code analysis, and code testing in this free trial

Besides SQL Optimizer, do you need more features like data modeling, code analysis, and code testing? Compare our top three Toad for Oracle editions and try Toad free for 30 days. Are you already in a trial and ready to purchase? Talk to sales or buy now online­.

 

 

More resources:

Webcast: How can you simplify and automate SQL optimization for developers in Toad® for Oracle?—Ask Toad!

Blog: SQL Optimizer saves $40,000

Blog: SQL Optimizer for Oracle - another success story

Blog: Quick and Easy SQL Optimization with Toad for Oracle

Blog: Understand the optimization process used in Quest SQL Optimizer

Blog: Optimize SQL (Plan Control

Blog: SQL in COBOL? Find and tune your SQL wherever it is.

Read more blogs from Gary.

 

Need help managing data?

Quest Software is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.  

Toad® database management tools are cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments.

Learn more about how Toad database management tools from Quest can help with database development, performance monitoring and database DevOps.

 

Share on social media

If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!

 

Tags: Toad for Oracle SQL Optimizer for Oracle SQL Optimizer for SQL Server Optimize

Gary Jerep

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.