Esta herramienta nos permite automatizar el proceso de optimización de SQL y maximiza el rendimiento de nuestras sentencias de SQL. SQL Optimizer analiza, vuelve a escribir y evalúa las sentencias de SQL ubicadas dentro de objetos, archivos o colecciones de sentencias de SQL de la base de datos del Sistema del Área Global de Oracle (SGA). Una vez que SQL Optimizer identifica las sentencias de SQL problemáticas, optimiza el SQL y proporciona un código de reemplazo que incluye la sentencia optimizada.
SQL Optimizer también proporciona una solución completa de optimización de índice y análisis de cambios de plan. Proporciona recomendaciones de índice para varias sentencias de SQL, simula análisis de impacto de índice y genera alternativas de plan de ejecución de SQL.
Cuando abrimos la herramienta podemos ver la ventana de bienvenida que nos muestra una lista de tareas. Si queremos desactivar que se muestre esta ventana al iniciar la herramienta destildamos en la parte inferior la opción “Show Welcome on next startup” y cerramos la ventana.
Figura 1: Pantalla de Bienvenida
Características
Optimize SQLconsiste en el modo de reescritura de SQL y el modo de control de plan. El modo de reescritura de SQL nos permite generar sentencias SQL y alternativas de índice para sentencias SQL problemáticas. El modo de control de planes nos permite aprovechar la función de administración de planes de SQL introducida en Oracle 11g para optimizar los planes de ejecución y desplegar líneas de base de planes para sentencias SQL sin cambiar el código fuente original. Esta función es particularmente útil para aplicaciones de terceros donde no tenemos acceso al código fuente.
Batch Optimize identifica y optimiza automáticamente las declaraciones SQL problemáticas mediante el procesamiento por lotes. También genera sentencias SQL optimizadas que puede usar para reemplazar sentencias SQL problemáticas en nuestro entorno de base de datos.
Advise Indexes genera un conjunto de índices para un grupo de sentencias SQL y prueba el conjunto de índices para determinar la mejora del rendimiento.
Scan SQL identifica sentencias SQL problemáticas en su código fuente y objetos de base de datos sin ejecución. Luego analiza las afirmaciones problemáticas y las clasifica de acuerdo con los niveles de rendimiento.
Inspect SGA captura estadísticas de tiempo de ejecución para ejecutar sentencias de SQL desde la SGA para identificar las sentencias utilizando la mayoría de los recursos del sistema.
Analyze Impact nos permite determinar el impacto en el rendimiento de los cambios propuestos en nuestra base de datos antes de realizar los cambios físicamente.
Manage Plans organiza líneas de base y esquemas almacenados utilizados para mejorar el rendimiento de las sentencias SQL.
Caso real usando Optimize SQL
En el presente artículo quiero presentar un caso real en el uso de esta herramienta en el cual un proceso de portabilidad demoraba más de 6 horas siendo el acumulado total de miles de ejecuciones de una consulta SQL y que después de realizar la optimización se redujo el tiempo a 23 minutos en el mismo proceso con llamadas similares en cantidad.
Se realizó un trace del mismo para detectar cual era la consulta SQL que más demoraba. La consulta SQL encontrada fue una consulta distribuida entre dos bases de datos, CCARD Y PROD, cuyas tablas eran prepay_cellulars (CCARD) y cellulars (PROD) ejecutándose en la base de datos CCARD.
El plan de ejecución evidenciaba que al acceder a cellulars@prod, lo que en realidad hacía, era un acceso al sinónimo público llamado CELLULARS, que a su vez accedía a una vista v_pp_cellular en la base de datos PROD, y esta vista se formaba con un select a cellulars de la misma base de datos. Claramente podía reemplazarse el acceso al sinónimo, por la consulta a la tabla cellulars, sin pasar por una vista, lo que añadía tiempo de ejecución a la consulta.
El DBA eliminó el sinónimo que era obsoleto para el negocio, y observando el plan de ejecución, ahora sí accedía a cellulars@prod.
El experto DBA probo este nuevo plan de ejecución, el cual obtuvo buenos resultados, pero no conforme con eso, decidió probar en la herramienta para obtener un resultado más confiable, ya que las pruebas realizadas eran con valores aislados y a veces se obtienen falsos tiempos sobre todo cuando se usan índices, porque el índice queda alocado en el cache, entonces uno tiende a pensar que el tiempo es óptimo.
Entonces, veamos como el DBA utilizó la herramienta para analizar la consulta SQL original después que el DBA eliminó el sinónimo público:
SELECT PCE_HANDLE, CLU_ACTIVATION_DATE
FROM PREPAY_CELLULARS, CELLULARS
WHERE PCE_CLU_CELLULAR_NUMBER = :B1
AND CLU_CELLULAR_NUMBER = PCE_CLU_CELLULAR_NUMBER
AND PCE_ADD_DATE >= CLU_ACTIVATION_DATE
Abrir la herramienta:
Figura 2: Ficha Optimize SQL
Seleccionar SQL Rewrite.
Figura 3: Ficha SQL Rewrite 1 – SQL Details
Ingresar la consulta SQL en el apartado "Alternative Details".
Figura 4: Sección Alternative Details
Hacer click en "Auto Optimize SQL".
Figura 5: Seleccionar Conexión y Esquema
Ingresar los datos de conexión y esquema de la base de datos y hacer clic en el botón OK:
Aparece la ventana "Test Run Settings" que nos permite configurar el criterio con que debe optar la herramienta la mejor alternativa para nuestro SQL.
Figura 6: Pantalla Test Run Settings
En este caso el DBA seleccionó en primer lugar, que su consulta SQL se utilizaba en su sistema transaccional y que todos los registros debían recuperarse, luego, que es una consulta SQL dinámica enviada desde una aplicación, que tendrá una carga alta de ejecuciones por minuto, y por último, que el recurso a mejorar será el tiempo de ejecución.
También configuró que esta consulta SQL afectaba el comportamiento de su sistema drásticamente, y que deseaba probar cada alternativa como si fuera la primera vez, es decir, limpiando el cache.
Configurado esto, hacer clic en el botón “Start Test Run”.
Aparece la ventana “Setting Bind Variables”.
Figura 7: Pantalla Setting Bind Variables
Como la consulta tiene una variable enlazada o bind, se debe configurar un valor para las pruebas.
Podemos ingresar un valor manualmente, o dar clic al botón “Auto Fill”, que buscará en las vistas dinámicas algún valor utilizado recientemente si es que la consulta aún está en la memoria, y si no lo está, el DBA ingresará manualmente el valor.
Figura 8: Añadir Variable Bind
Y hacemos clic en el botón OK.
El SQL Optimizer comienza a trabajar. La herramienta genera todas las alternativas posibles para acceder a la consulta SQL según la estructura de los objetos y los hints disponibles en la base de datos. Luego para encontrar la mejor alternativa compara la consulta SQL Original con la primera alternativa presentada por la herramienta. Si la alternativa es mejor a la consulta SQL Original se le asigna un trofeo y sigue comparando la alternativa ganadora con la siguiente alternativa. Si la alternativa se demora más de 5 segundos (criterio configurado por defecto) que la consulta SQL ganadora en el momento de la comparación, el SQL Optimizer la descarta por considerar que no es una buena alternativa y sigue comparando con la siguiente alternativa para encontrar la mejor alternativa según los criterios seleccionados previamente por el DBA.
Figura 9: Buscando la mejor Alternativa de la Consulta SQL Original
Como vemos en la figura la herramienta ha generado 151 escenarios posibles y después de realizar las comparaciones, decide que la alternativa Alt110 es la mejor alternativa, basándose en los tiempos de CPU y de tiempo transcurrido, criterios configurados previamente por el DBA.
Es muy importante destacar que la herramienta ha generado 151 alternativas a la Original porque esta configurado el nivel de inteligencia con un valor de 5. Por defecto este criterio en la herramienta está definido con un valor de 2, eso significa que la herramienta genera todas las alternativas posibles básicas según la consulta SQL Original, pero cuando el DBA configura el nivel de inteligencia con un valor de 5 (siendo el más alto valor), la herramienta genera todas las alternativas posibles utilizando todos los hints disponibles de la base de datos según la estructura de los objetos de la consulta SQL Original.
Aquí es donde vemos la gran potencia de esta herramienta porque el DBA podría manualmente crear quizás no más de 20 alternativas para esta consulta SQL y como vemos la herramienta puede crear una gran cantidad de alternativas no contempladas por el DBA.
Figura 10: Pantalla Settings for Current Session
Además, el DBA tiene la opción de utilizar la funcionalidad "Test Run Different Bind Values", en el cual se puede configurar la carga de un archivo para varios valores de la/s variable/s bind. Y de esta forma hacer una prueba de stress real en la base de datos.
Figura 11: Ficha Test Run Different Bind Values
Como vemos, ahora la mejor alternativa, es la Original, en promedio de tiempo y recursos, si desplegamos la alternativa en el signo + podemos ver cada estadística individual:
Figura 12: Estadísticas Individuales para la Consulta SQL Original
Y para la alternativa 110 vemos que en algunos conjuntos de datos no se pudo completar:
Figura 13: Estadísticas Individuales para la Consulta SQL Alternativa 110 (Alt110)
Aquí vemos la importancia de realizar una prueba de stress con una muestra de datos distribuida, para ver cuál es el comportamiento real y determinar fehacientemente cual es el mejor plan de ejecución para la consulta.
La herramienta también permite comparar dos alternativas, en la ficha “compare”, y además permite generar un reporte en la ficha “Report”.
–Compare:
Figura 14: Ficha Compare
Conclusión
Como podemos observar el SQL Optimizer es una herramienta de soporte para un DBA experto en tuning, proporcionando alternativas de solución y permitiendo estresar la consulta SQL Original para el análisis con datos reales. El DBA siempre tiene la decisión final.
Si quisiéramos realizar las mismas pruebas de forma manual, las podríamos hacer, pero requeriría mucho más tiempo y esfuerzo, además que podría ser posible que se nos escape alguna posible buena alternativa.
También un DBA principiante puede utilizar esta herramienta para optimizar sus consultas SQL ya que la herramienta a partir de la consulta original propone diferentes alternativas según los criterios de configuración, nivel de inteligencia, estructura de datos y los hints disponibles en la base de datos.
Finalmente podemos decir que esta herramienta le brinda soporte al conocimiento experto del DBA, y orientación al DBA principiante en tareas de optimización para lograr encontrar la mejor alternativa de optimización.
Hola Clarisa primeramente felicitarte y agradecerte por toda la información que se puede encontrar en tus post. tiempo atras mire un post tuyo sobre Cómo Crear y Gestionar La Lista de Control de Acceso (ACL) en una Aplicación APEX 5.0 pero en la nueva version del blog no logre encontrar esa publicación me gustaria poder volver a leerlo para el manejo del menu de navegación por roles.
Saludos
Buen@s días/tardes Clarisa,
No acabo de entender bien la consulta que el software decide que es más optima.
Observo que añade el hint /*+ DRIVING_SITE(e) */, cuando yo tenía entendido que este hint provocaba la ejecución en la maquina donde se encuentre la tabla (e), tampoco veo el dblink, lo que me llama bastante la atención...
Un saludo y gracias por la respuesta!!!
Sergio