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.
Start the discussion at forums.toadworld.com