Toad World Blog

Comparación de Consultas SQL usando el “Auto Optimize SQL” del Toad for Oracle Vs “Autotrace” del Oracle SQL Developer

Jun 16, 2018 12:27:09 PM by Clarisa Maman Orfali

Cuando estamos trabajando en un proyecto, una importante tarea del desarrollador es lograr que sus instrucciones SQL sean lo mas eficiente posible ya que es un hecho bien conocido que abordar los problemas relacionados con el SQL en el desarrollo cuesta una fracción de lo que costaría corregir esos mismos problemas cuando ya estamos en producción.

Además, cuando estamos trabajando en un entorno dev/test con consultas SQL con una ejecución pobre, es decir, consultas SQL lentas, estamos condenando al proyecto a tener importantes problemas en producción; imagina el tema de la escabilidad de tu proyecto y una mala ejecución de tus consultas SQL, sería un verdadero desastre.

Si en nuestro equipo tenemos un DBA podemos pedir su ayuda para que corrija nuestras consultas SQL, pero en el caso que no dispongamos de uno tendríamos que incurrir en más costos.

Y si alguien te pregunta: ¿De cuántas formas diferentes crees tú que podemos escribir esta consulta SQL? ...

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

... ¿Cuál sería tu respuesta?

Los desarrolladores a menudo escriben sus consultas SQL de la forma en que inicialmente aprendieron y no saben exactamente si su SQL está escrito de la manera más eficiente para sus proyectos y además, no están seguros de qué hacer para corregir una declaración SQL que tiene una ejecución deficiente, entonces, si ellos creen que es deficiente su SQL, intentarán reescribirla manualmente, pero eso les llevará mucho tiempo y en general, es bastante ineficiente.

Por estas razones, en esta entrega quiero compartir con la comunidad esta herramienta poderosa que nos provee el Toad for Oracle llamada Auto Optimize SQL que nos permite optimizar rápidamente instrucciones SQL, es decir, el Toad for Oracle busca alternativas más rápidas y nos permite compararlas con la declaración SQL original.

Por otro lado, también vamos a trabajar con el Autotrace del Oracle SQL Developer para poder comparar dos instrucciones SQL y ver las diferencias entre ambas herramientas.

 

Toad for Oracle: Auto Optimize SQL

Para ingresar a la herramienta, desde el Toad for Oracle, seleccionamos el menú Database > Optimize > Auto Optimize SQL: 

Se abre la ventana del Auto Optimize SQL en el cual podemos definir el tipo de base de datos que estamos utilizando para el análisis de nuestras consultas SQL y las diferentes opciones de optimización.

En el recuadro de la derecha podemos ver tres fichas:

-          Statement to Tune: donde colocamos nuestra consulta SQL para analizar

-          Alternative Detail: se muestra la consulta alternativa seleccionada

-          Compare Alternatives: se comparan la consulta SQL original con la consulta SQL alternativa seleccionada

Las sentencias SQL permitidas para analizar son: SELECT, INSERT, UPDATE, DELETE y MERGE.

Ingresamos la consulta SQL a analizar dentro de la ficha “Statement to Tune”:

Ajustamos las opciones que deseamos para el análisis de la consulta, por ejemplo, en Search depth, si seleccionamos menos profundo consumimos menos tiempo y menos re-escritura, en cambio, si seleccionamos más profundo consumimos más tiempo y más re-escritura.

Para analizar esta consulta SQL ejecutamos la herramienta haciendo clic en el botón verde: 

En el caso que usemos la herramienta por primera vez, aparecerá una ventana modal solicitando que ingresemos el nombre del Plan Table Schema y hacemos clic en el botón Ok.

Una vez finalizada la ejecución del optimizador, el Toad nos brindará una serie de distintas alternativas a la consulta SQL original.

En nuestro caso de ejemplo, el Toad nos muestra 116 alternativas diferentes: 

Además, podemos visualizar las alternativas desde distintas vistas, por ejemplo, seleccionamos ver las alternativas en base al Plan Cost

 

Si queremos que la herramienta genere las alternativas sin que las ejecute, debemos tildar la opción: Generate rewrites only (do not executable), en la ventana de opciones.

Esta herramienta es muy poderosa porque califica el rendimiento de cada alternativa por tiempo transcurrido, ciclos de CPU, E/S y más de una docena de otras métricas. 

 

Cada alternativa tiene un plan de ejecución único y la información de las estadísticas. Además, la alternativa de mejor rendimiento aparece junto con nuestra SQL original para que podamos estudiarla. 

Para encontrar la mejor consulta SQL tenemos la posibilidad de comparar cada una de las alternativas sugeridas por el Toad con la consulta SQL original.

Por ejemplo, como vemos en la siguiente imagen, la consulta original tiene un costo de 15 (67% worse) y la alternativa 2 tiene un costo de 9 (40% better)

 

Oracle SQL Developer - versión 18.1:

Ingresamos en la ventana de trabajo la misma consulta SQL original con la cual estábamos trabajando en el Toad for Oracle.

Hacemos clic en el botón “Autotrace… (F6)” 

Podemos ver el “Plan de Ejecución” de la consulta original y la información del autotrace.

El Oracle SQL Developer no nos provee la funcionalidad de generar diferentes alternativas para ser comparadas con la consulta original y tampoco podemos ver las diferentes alternativas según distintas vistas, a menos que tengamos habilitado el Oracle Tuning Pack y utilizar el SQL Tuning Advisor el cual nos brinda recomendaciones de la consulta analizada con una justificación para cada recomendación y su beneficio esperado. Es importante mencionar que este paquete requiere de una licencia para ser habilitado.

Para el caso de realizar la comparación de dos sentencias SQL vamos a utilizar para este demo una de las consultas alternativas generadas por el 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

Para poder conservar el autotrace de la consulta original, hacemos clic en el icono del pin rojo.

Seleccionamos la consulta SQL alternativa con la cual queremos compararla con la consulta SQL original y hacemos clic en el botón de autotrace, de esa forma se abre una nueva ficha de autotrace. 

Para comparar las dos consultas SQL, hacemos clic con el botón derecho del ratón sobre la ficha Autotrace 1 y seleccionamos Compare with Autotrace. De esta forma se abre una nueva ficha con la comparación de las dos consultas SQL: 

 

Conclusión

Como vimos a lo largo de todo este artículo la ayuda que brinda a los desarrolladores la herramienta Auto Optimize SQL del Toad for Oracle es muy importante y necesaria, ya que, el trabajo de mejorar una instrucción SQL a menudo es un gran desafío para todos los que somos desarrolladores y disponer de una herramienta que reescribe automáticamente nuestras instrucciones SQL lentas con un par de clics del ratón, realmente es asombroso.

Además, con esta herramienta al usarla continuamente no solo podemos mejorar rápidamente el rendimiento de las instrucciones SQL, sino que también podemos de forma muy fácil aprender a escribir un mejor SQL.

Tags: Toad for Oracle

Clarisa Maman Orfali

Written by Clarisa Maman Orfali

Clarisa is from Argentina. She is Founder and Director at ClarTech Solutions, Inc. a company dedicated to the development, consultancy and training in Information Technologies located in Southern California in the United States.

She is a Systems Engineer with more than 18 years of experience in systems development. She was introduced in Oracle Technologies in 2009, a few years later she discovered the great power of development with Oracle Application Express (APEX) and has specialized since then in that area.

She is also Co-Founder of the Oracle Users Group of Argentina (AROUG) and founder of Oracle APEX Latin America meetup, has also actively participated in the organization and as Presenter of big and popular events such as the OTN Tour and the Oracle APEX Tour of Latin America. In 2014 was recognized by the Oracle Corporation with the Oracle ACE Award. Then, in March 2018, she was promoted to Oracle ACE Director.

She is also the author of the first three books in Spanish about Oracle APEX:

  • Published in June 30, 2017: "Oracle APEX 5.1" (Spanish Edition). You can get the book in Paperback version.
  • Published in November 5, 2015: "Introducción a Oracle APEX 5.0" (Spanish Edition). You can get the book in Paperback version and Kindle version.
  • Published In January 26, 2016: "Integración Sin Costo de JasperReports en Oracle APEX 5.0" (Spansih Edition). You can get the book in Paperback version and Kindle version.