Toad World Blog

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

Jun 16, 2018 11:02:44 AM by Clarisa Maman Orfali

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.

Tags: Toad for Oracle

Clarisa Maman Orfali

Written by Clarisa Maman Orfali

Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area.

Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses.

She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707).

Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.