Comparison of SQL Queries Using Toad for Oracle “Auto Optimize SQL” vs. Oracle SQL Developer “Autotrace”

When we are working on a project, an important task of the developer is to make their SQL statements as efficient as possible since it is well known that addressing the problems related to SQL in development costs a fraction of what it would cost to correct those same problems when we are already in production.

Also, when we are working in a dev/test environment with SQL queries that have a poor execution, meaning they are slow SQL queries, we are condemning the project to having significant problems in production; imagine the issue of the scalability of your project and poor execution of your SQL queries, it would be a real disaster.

If we have a DBA on our team, we can ask for his/her help to correct our SQL queries. But if we do not have one, we would have to incur more costs to get one.

And if someone asks you, “how many different ways do you think we can write this SQL query?”…

SELECT c.customer_id,
       c.cust_last_name || ', ' || c.cust_first_name Customer_Name,
       SUM(DECODE(p.category, 'Accessories', oi.quantity * oi.unit_price, 0)) "Accessories",
       SUM(DECODE(p.category, 'Mens', oi.quantity * oi.unit_price, 0)) "Mens",
       SUM(DECODE(p.category, 'Womens', oi.quantity * oi.unit_price, 0)) "Womens"
FROM demo_customers c
       INNER JOIN demo_order_items oi
         ON 1 = 1
       INNER JOIN demo_product_info p
         ON oi.product_id = p.product_id
       INNER JOIN demo_orders o
         ON c.customer_id = o.customer_id + 0
             AND oi.order_id = o.order_id + 0
GROUP BY c.customer_id, c.cust_last_name, c.cust_first_name
ORDER BY c.cust_last_name

…what would your answer be?

Developers often write their SQL queries the way they initially learned and don’t know exactly if their SQL is written in the most efficient way for their projects. Also, they are often not sure what to do to correct a SQL statement that has a deficient execution, so if they think their SQL is deficient, they will try to rewrite it manually, but that will take a long time and in general, will be quite inefficient.

For these reasons, I want to share with the community this powerful tool that Toad for Oracle provides, called Auto Optimize SQL. This tool allows us to quickly optimize SQL statements, so that Toad for Oracle can search for faster alternatives and then compare them with the original SQL statement.

We will also work with Autotrace of the Oracle SQL Developer to compare two SQL statements and see the differences between both tools.

Toad for Oracle: Auto Optimize SQL

To access the tool, from Toad for Oracle, select the menu Database > Optimize > Auto Optimize SQL:

The Auto Optimize SQL window opens, in which we can define the type of database that we are using for the analysis of our SQL queries and different optimization options.

In the box on the right we can see three tabs:

– Statement to Tune: where we place our SQL query for analysis.

– Alternative Detail: where the selected alternative query is displayed.

– Compare Alternatives: compare the original SQL query with the selected alternative SQL query.

The SQL statements that can be analyzed are: SELECT, INSERT, UPDATE, DELETE and MERGE.

Enter the SQL query to be analyzed within the "Statement to Tune" tab:

We adjust the options that we want for the analysis of the query; for example, in Search depth, if we select less deep we consume less time and less rewriting; however, if we select deeper we consume more time and more rewriting.

To analyze this SQL query, we execute the tool by clicking on the green button: 

If we are using the tool for the first time, a modal window will appear requesting that we enter the name of the Table Schema Plan and click on the OK button.

Once the execution of the optimizer is finished, Toad will provide us with a series of different alternatives to the original SQL query.

In our example case, Toad shows us 116 different alternatives:

We can visualize the alternatives from different views. For example, we select to see the alternatives based on the Plan Cost. 

If we want the tool to generate the alternatives without executing them, we must check the option: Generate rewrites only (do not executable) in the options window.

This tool is very powerful because it evaluates the performance of each alternative by elapsed time, CPU cycles, I/O and more than a dozen other metrics. 

Each alternative has a unique execution plan and statistics information. In addition, the best performance alternative appears along with our original SQL so we can study it. 

To find the best SQL query we have the ability to compare each of the alternatives suggested by Toad with the original SQL query.

For example, as we see in the following image, the original query has a cost of 15 (67% worse) and alternative 2 has a cost of 9 (40% better). 

Oracle SQL Developer – version 18.1:

Now let’s compare with using Oracle SQL Developer. Let’s enter in the working area the same original SQL query which we were working with in Toad for Oracle and then click on the "Autotrace … (F6)" button. 

We can see the "Execution Plan" of the original query and the auto trace information.

Oracle SQL Developer doesn’t provide the functionality to generate different alternatives to be compared with the original query and we can’t see the different alternatives according to different views, unless we have enabled the Oracle Tuning Pack and use the SQL Tuning Advisor, which gives us recommendations on the analyzed consultation with a justification for each recommendation and its expected benefit. It is important to mention that this package requires a license to be enabled.

To make the comparison of two SQL statements for this demo, we will use one of the alternative queries generated by Toad for Oracle.

SELECT /*+ INDEX(C) */ c.customer_id,
       c.cust_last_name || ', ' || c.cust_first_name Customer_Name,
       SUM(DECODE(p.category, 'Accessories', oi.quantity * oi.unit_price, 0)) "Accessories",
       SUM(DECODE(p.category, 'Mens', oi.quantity * oi.unit_price, 0)) "Mens",
       SUM(DECODE(p.category, 'Womens', oi.quantity * oi.unit_price, 0)) "Womens"
FROM demo_customers c
       INNER JOIN demo_order_items oi
         ON 1 = 1
       INNER JOIN demo_product_info p
         ON oi.product_id = p.product_id
       INNER JOIN demo_orders o
         ON c.customer_id = o.customer_id
             AND oi.order_id = o.order_id
GROUP BY c.customer_id, c.cust_last_name, c.cust_first_name
ORDER BY c.cust_last_name

In order to keep the autotrace of the original query, we click on the red pin icon.

We select the alternative SQL query which we want to compare to the original SQL query and we click on the autotrace button; that way, a new autotrace file is opened. 

To compare the two SQL queries, we click with the right mouse button on the Autotrace 1 tab and select Compare with Autotrace. This opens a new tab with the comparison of the two SQL queries:

Conclusion

As we have seen in this article, the help that the Auto Optimize SQL tool gives to the developers is very important and necessary, since the work of improving an SQL statement is often a great challenge for all of us who are developers. Also, having a tool that automatically rewrites our slow SQL statements with a couple of mouse clicks is really quite amazing.

In addition, by using this tool regularly, we not only can quickly improve the performance of SQL statements, but we can also easily learn how to write a better SQL.