Optimización de consultas SQL

Abro este tema para comentar cuestiones sobre rendimiento y optimización de consultas SQL en bases de datos Oracle, ejecución y análisis de Explain Plans, etc.

Carlos, excelente haber encontrado este foro... felicidades!!

Estoy auditando las consutlas SQL que se han ejecutado por un periodo determinado, sin embargo, entre la información que me arroja, encuentro: Tipo de objeto, Orden, Filas, Tamaño (KB), Costo, Tiempo(seg), Costo de CPU y Costo de E/S. Los costos a qué se refieren? en qué unidades está expresado el dato? Esto, es por cada select, union, group by, etc. que tenga en mi consulta.

De antemano, gracias !!

En respuesta a por Tody1820 (no verificado)

Tody, el coste de las consultas es una estimación que hace el optimizador de Oracle cuando ejecutas un Explain Plan, y es una medida interna que has de tener en cuenta, pero que al ser una estimación no es muy precisa.
Básicamente te va a servir para estimar si un cambio en la manera de hacer una Query va a conllevar una mejora en su ejecución, no vas a poder compararlo con otras medidas de rendimiento, y ni siquiera es aconsejable compararlo entre dos consultas diferentes que no devuelvan los mismos datos.

En respuesta a por Carlos

Gracias por tu respuesta Carlos !! ... entendiendo entonces que cada consulta tiene su propia medición, hice unas adecuaciones optimizando una misma consulta y las cantidades cambiaron mucho. Sin embargo, me sigue quedando la duda del por qué los valores de un inner join, left join, etc. siempre salen muy altos a pesar de haber optimizado en gran medida los demás elementos de la consulta.  Deberían de aminorar al igual que los otros elementos, no es así? 

En respuesta a por Tody

No necesariamente. Depende de la optimización que hayas hecho. Si no varían quiere decir que la optimización puede mejorar otras cosas, pero no afecta demasiado a las joins.

Si la optimización la habías hecho justo para mejorar las joins, revisa la consulta porque puede que algo no se esté comportando como esperabas.

Si es una optimización orientada a mejorar en otros puntos, si el coste de esos puntos y el general se ha reducido ya irás bien encaminado.

Estoy intentando optimizar esta query y me es imposible se os ocurre algo:
SELECT COLOR.VAL_MINIMO,
MAX(COLOR.VAL_DESCRIPCION)
FROM COLOR, COLOR6
WHERE COLOR.VAL_CODIGO = COLOR6.DV_COD_VALOR AND
COLOR6.DV_COD_MODELO = 80 AND
COLOR6.DV_COD_VERSION = 2 AND
COLOR6.DV_COD_DATO = 118
GROUP BY COLOR.VAL_MINIMO
ORDER BY VAL_MINIMO

Tiene de coste 22 y es una exageracion.

Gracias.

 Hola,

 

Actualmente estoy trabajando con la versión 9 de oracle, y en mode=RULES, ahora queremos migrar a la versión 11g, y por lo que veo no admite trabajar en mode reglas, sinó que hay que trabajar en modo estadísticas. Me gustaría que alguien que se hubiera encontrado en esta situacuón pudiera ayudarme en los problemas a nivel de rendimiento que puedo tener en todas las querys de la aplicación. Es decir no tengo claro si voy a tener que modificar todas las querys al cambiar de reglas a estadísticas. Por otro lado entiendo que trabajar en modo estadísticas empieza a dar buen rendiemiento cuando las querys ya han sido ejecutadas, es decir cuando ya ha podido crear las estadísticas, de manera que el primer ciclo de ejecución de todas las querys no va a ser el más optimo, funciona esto así?. Bueno muchas gracias por todo.

 

Un saludo,

En respuesta a por neik11

No creo que vayas a tener tantos problemas como esperas, de hecho puede que hasta te mejore la velocidad de muchas queries sin tener que tocar nada.

Las sentencias SQL se pueden optimizar teniendo en cuenta el modo que utilice el analizador de consultas, pero no es lo habítual, como mucho puedes tener algunas consultas para las que se haya hecho una optimización especial, que son las que tendrías que revisar.

Lo que sí creo que es importante puntualizar es que las estadísticas que utiliza el analizador no son sobre las consultas, son sobre los objetos de la base de datos, por lo que lo importante no es ejecutar las consultas, sino tener al día las estadísticas sobre las tablas más importantes, más grandes o más utilizadas de tu base de datos.

Si la primera consulta te va más lenta que las siguientes será porque Oracle utiliza cachés que sirven para mejorar los tiempos de respuesta sobretodo en consultas repetitivas, pero es un tema independiente de las estadísticas.

Si tienes la estadística de las tablas que intervienen en la consulta medianamente actualizada, el analizador podrá afinar mucho más al preparar el mejor plan de ejecución y seguramente te mejorarán tanto la primera como las siguientes consultas.

 

Carlos, podrias por favor darme una explicacion detallada respecto a tecnicas de optimizacion o tal vez unos link de referencia (revision sitactica,plan de ejecucion, otros), muchas gracias

Hola Ramón

La mejor referencia que se me ocurre es la misma documentación de Oracle. Para temas de optimización, en el libro Oracle Database Performance Tuning Guide 11g, por ejemplo, seguro que vas a encontrar casi todo lo que necesites, y sabes que la fuente es fiable. Te enlazo el capítulo Optimizing SQL Statements de este libro de la documentación online de Oracle, espero que te sea útil.

 

hola estoy empezando a estudiar sql y quería saber si hay un analizador de consultas sql para oracle, y no cometer fallos en la sintaxis, y si lo hay se le puede poner cualquier tabla?? muchas gracias.

En respuesta a por caperucita

Que yo sepa no hay ninguna limitación específica para los OR que puedes incluir en una sentencia SQL de Oracle, aunque si utilizas demasiados, y dependiendo también de lo que contengan los OR, se puede llegar a producir un error de tipo ORA-03113: end-of-file on communication channel.

También te digo que el error te va a venir más bien porque hayas alcanzado el límite que tu base de datos, o los conectores que intervengan puedan manejar para una sola sentencia SQL, no por llegar a un límite para los OR que contiene la sentencia.

Este límite para sentencias, además, puede depender de la versión de la base de datos, de tu entorno, del cliente que utilices..

Te enlazo una respuesta en askTom, que lo deja bastante claro, y un documento de referencia de Oracle 10g en el que se indican los límites lógicos de la base de datos, y al final apunta esta nota: 

The limit on how long a SQL statement can be depends on many factors,  
including database configuration, disk space, and memory

Y de todas maneras, lo normal es que una sentencia con muchos OR siempre se pueda resolver de otra manera más simple, ¿cuántos OR necesitas incluir en tu sentencia?

 

como puedo optimizar esta consulta, me aparece un index fast full scan en la tabla en_objeto el indice de esta tabla es sgl_categoria y las pks son:cod_convocatoria, tpo_objeto, cod_etapa, sgl_tipologia