Oracle SQL Developer y SQL Tuning Advisor
Acerca del Autor: Jhon Robert Quintero H.
Oracle SQL Developer tiene integrado un Asesor de Ajustes SQL (SQL Tuning Advisor). Este asesor nos va a ayudar dando recomendaciones para optimizar nuestros Querys.
Automatiza el proceso de Tuning SQL al explorar completamente todas las maneras
posibles de adaptar una sentencia SQL.
En los siguientes tipos de recomendaciones nos puede ayudar:
- Estadísticas: Detecta para un query que tablas necesitan actualización de estadísticas.
- Perfil de SQL: Nos recomienda la aceptación de nuevos perfiles
- Indices: Recomienda creación de indices que se necesiten
- Reestructurar SQL: Nos dice que cambiarle al query para que mejore.
Muy fácil lo podemos encontrar con (Ctrl + F12) al abrir un SQL. Icono de la llavecita.
Propósito
He preparado un tutorial para ver de forma sencilla su aplicación y uso. Vamos a borrar las estadísticas del esquema HR, luego ejecutamos el SQL Tuning Advisor, revisamos las recomendaciones y corregimos para mejorar.
Requisitos
- Oracle SQL Developer 4.2
- Oracle Database 11g Enterprise Edition con Tuning and Diagnostic Packs
- Esquema de prueba HR
Asignamos Permisos
Se necesita un usuario con ciertos permisos para ejecutar SQL Tuning Advisor.
- Ingresamos con el usuario SYSTEM.
- Asignamos permisos al usuario HR desde SYSTEM
grant advisor to hr;
grant administer sql tuning set to hr;
- Asignacion exitosa
Borramos Estadísticas
Debido a que la base de datos recolecta estadísticas para que el optimizador entregue planes de ejecución óptimos, necesitamos borrar las estadísticas del esquema HR porque como ejemplo ilustrativo queremos arrancar en limpio para ver que nos recomienda.
- exec DBMS_STATS.DELETE_SCHEMA_STATS (‘hr’);
- Seleccione la sentencia y ejecútela
- Ejecución exitosa
Ejecucion de SQL Tuning Advisor en SQL
- Abrimos la sentencia SQL en el usuario HR.
- Ingresar la siguiente sentencia
select sum(e.salary), avg(e.salary), count(1), e.department_id from departments d, employees e group by e.department_id order by e.department_id;
- Ejecutamos SQL Tuning Advisor en el icono de la llavecita o con Ctrl + F12
- Nos despliega una visión general, las recomendaciones y sus detalles.
- Al dar click en Estadísticas vemos que recomienda generar estadísticas del optimizador para la tabla EMPLOYEES y para el indice EMP_EMAIL_UK
- En Perfil SQL. recomienda mejorar el plan de ejecución si aceptamos un nuevo Perfil SQL.
- En la pestaña Detalle vemos mas informacion del SQL Profile
- En cuanto a indices no recomienda nada
- En Reestructurar SQL encontró un producto cartesiano. Para este ejemplo muy sencillo intencionalmente se dejo una tabla suelta.
Implementar las Recomendaciones
Hay varias formas de implementar estas recomendaciones, para el ejercicio lo haremos por aquí:
- En la ventana Conexiones, click derecho en HR y seleccionamos Recopilar Estadísticas del Esquema
- Para el ejemplo escogemos un porcentaje del 100%, aunque en ambiente de producción generalmente es del 30% aproximadamente, para un esquema pequeño y de pruebas no tendremos inconveniente.
- Click Apply.
- Click OK.
Ejecutamos SQL Tuning Advisor otra vez para ver mejoras
Seleccionar la sentencia y ejecutar
- Se resolvieron los problemas de estadísticas y de Perfil SQL
Nada reportado para Indices.
Sigue el problema del producto cartesiano
- Quitar la tabla «departments» de la sentencia porque sobra
select sum(e.salary), avg(e.salary), count(1), e.department_id from employees e group by e.department_id order by e.department_id;
- Finalmente se corrigieron todas las recomendaciones.
Resumen
Conocimos el SQL Tuning Advisor, que es una herramienta incluida en Oracle SQL Developer, y desconocida por muchos.
Hemos aprendido a ejecutar SQL Tuning Advisor y a implementar las recomendaciones.
Espero les sea muy útil en sus proyectos.
https://analisys.co/2017/06/26/102/