Jan 14, 2021 2:03:17 PM by Quest Software
Read this webcast recap and watch the replay to get insights to help you appreciate why query and index performance tuning gets so difficult on relational databases.
According to Gary Jerep, Solutions Consultant at Quest® Software®, SQL query tuning and index optimization remain the leading topics that database professionals care about. They care because it is absolutely necessary to achieve optimal database performance through tuning, but it is also extremely difficult. Why is it so hard?
In a recent webcast, Gary discussed this topic, and also how Toad® by Quest has advanced features that use sophisticated algorithms to optimize queries and indexes.
He started with a familiar scenario where a database professional adds an index to speed up a report, but finds that batch jobs have slowed down as an unintended consequence.
He also gave a brief history of SQL optimization, from the early days of computer theory. Some key milestones include:
However, commercial relational databases have been around for over 40 years , and tuning is still a top concern. This is because it’s difficult on two levels – in practical terms, there are many factors to consider, including:
These practical factors make tuning complex simply because there are so many individual items that must be addressed and so many ways they can interact for better or worse outcomes.
On another level, there are the factors that are inherent to the inner workings of relational databases, such as those involving queries and indexes.
As Gary explained, the first inherent issue is that SQL is a declarative (not procedural) language. A SQL statement tells the database engine what you want, and the database has to figure out the procedure (convert the query to a plan) to produce what you’ve specified. There are many different ways the database engine can return the same data set.
The database optimizer comes up with multiple plans and must choose which one is best, but only has a fraction of a second to prepare a query to execute. This leads to several questions, such as how long does it take to generate enough execution plans, and how many plans are enough? How does the optimizer evaluate each plan? How does the database know which plan is best, and what criteria is it using to decide?
Of course, we know that cost is one way the database optimizer evaluates execution plans, and we also know that the least cost plan isn’t always the most optimal choice. Gary showed a short query and indicated there could be over 30,000 different ways to rewrite it to obtain the same results set. Given that number, there could also be over 8 million execution plans that would return the same data set.
A database engine could never evaluate all these plans and choose the best one in the miniscule amount of time allowed. So, no one should be surprised that a relational database engine like Oracle could miss plans that are lower cost or take less time to execute. That’s where a third-party tool like Toad comes in.
Using slightly more time (long by database standards, but short by human standards), the Advanced SQL Optimizer in the Xpert edition of Toad (and all higher editions) can generate explain plans and determine which one is best. At the touch of a button, you can submit a query to Toad and ask it to run an algorithm that will rewrite and restructure the SQL statement.
Toad will see if any of the rewritten alternatives yield unique execution plans that return the same data set at lower cost or lower execution time. When Gary ran an example, it only took 49 seconds for Toad to return over 150 plans, 10 of which were unique, that Oracle could not find in the time allotted.
Once these plans are identified, you can compare them, see how Toad rewrote the query, and test run the plans to see how well they do against the original statement.
When it comes to indexes, this is another area where Toad can handle the astronomical number of index combinations and permutations to determine which ones will speed up the workload. Here, Gary gave the example of a very small database with five tables, each with three columns. In the simplest case, there would be 15 indexes per table and 75 indexes for the database.
But what about index sets? If index sets of three were needed, there would be over 70,000 options. If you were trying to determine the best set of five indexes, there would be nearly 1 trillion combinations. For only five tables! Now consider that some ERP systems can have thousands of tables and it quickly becomes apparent how index optimization could be daunting.
Here again, Toad can help using an index optimization engine. Toad has an algorithm that allows the user to provide a workload – a DML mix. Toad examines all the objects in the SQL statements and evaluates the combinations and permutations for optimizing the index sets. Once you’ve settled on an index set, Toad can generate the creation script for the indexes.
With these two intelligent engines, Toad addresses the inherent foundational complexities of relationship databases to provide quick and easy tools for query and index optimization.
Sponsored by Database Trends and Applications (DBTA), take a break and watch the webcast, "Why is it So Darn Hard? Index/SQL Tuning on Relational Databases: How Toad Can Help."
Try Toad free for 30 days.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Blog Collection: SQL Optimizer for Oracle
Blog Collection: SQL Optimizer for SQL Sever
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers and lots of experienced users.
Written by Quest Software