Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

SET AUTOTRACE OFF - Deshabilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE ON - Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY - Habilita el análisis (traceado) de las sentencias SQL pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos analizando el rendimiento de la sentencia y no nos interesa conocer los registros que pueda devolver.

SET AUTOTRACE ON/TRACEONLY EXPLAIN - Muestra el plan de ejecución de la sentencia pero no muestra las estadísticas.

SET AUTOTRACE ON STATISTICS - Muestra las estadísticas pero no muestra el plan de ejecución de la sentencia.

Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.

Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido configurados para poder ejecutar el comando AUTOTRACE.

Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.

Una vez que se ha configurado convenientemente un usuario para que pueda acceder al plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los correspondientes valores estadísticos.

Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez activado el comando AUTOTRACE, son los siguientes:

• DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria caché.
• Consistent gets: Número de operaciones de entrada/salida realizadas sobre los segmentos de rollback debido a cambios en la memoria caché.
• Physical reads: Número de bloques leídos desde el disco.
• Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
• Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.

A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos conseguir que el número de db block gets, consistent gets y physical reads sea bajo comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la ordenación de los datos debe realizarse, siempre que sea posible, en memoria.

En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve interpretación de dicho plan: Ejemplo de plan de ejecución.

Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un índice.

Ya he escrito algunos artículos que pueden ayudar a interpretar un plan de ejecución desarrollado por el optimizador Oracle:

- Puesta a punto de sentencias SQL.
- Bucles y problemas de rendimiento.
- Hints en PL/SQL para el modo de optimización.
- Hints en PL/SQL para determinar el método de acceso.