Index/SQL tuning on relational databases, why is it so darn hard?

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.

SQL optimization, why is it so hard?

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.

SQL optimization use case

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.

History of SQL optimization

He also gave a brief history of SQL optimization, from the early days of computer theory. Some key milestones include:

1970 – Edgar Codd publishes seminal paper on relational model theory
1979 – the precursor company to Oracle launches one of the first commercial RDBMSs
2003 – Oracle 10g includes nearly130 optimization hints for SQL statements within the optimizer
Today – There are over 400 of those hints along with Adaptive Query Tuning features

SQL tuning in practical terms

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:

Physical: hardware, network, storage, disk fragmentation
Configuration: memory, data partitioning, object storage parameters, statistics
Data model: proper normalization, data types, indexes, triggers
Access: search predicates, parallelization, table data volume, DML operations

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.

SQL tuning factors that are inherent

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.

SQL tuning with an optimizer

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.

Toad can handle large index combinations and permutations

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