Cambios de rendimiento en una sentencia SQL al activar el trazado

Hace unos días un lector del blog me enviaba un email contándome un "extraño" problema de rendimiento que tenía con una sentencia SQL o PL/SQL. Dicha sentencia SQL tardaba mucho tiempo en devolver resultados y, tras activar la utilidad de trazado SQL (SQL_TRACE=TRUE), el problema desaparecía y la respuesta de la sentencia SQL era inmediata.

La verdad es que el fenómeno no es tan extraño una vez que se conoce la causa. Cuando se activa el trazado haciendo SQL_TRACE=TRUE, lo que ocurre es que la sesión Oracle utiliza una nueva área de SQL compartido. Esto supone que el parsing (ver artículo sobre las fases durante el procesamiento de una sentencia SQL) de cualquier sentencia SQL que se ejecute después de activar el trazado vuelva a tener lugar o que, de existir una versión de dicha sentencia SQL ya parseada en la nueva área de SQL compartido, dicha versión no coincida con la versión existente cuando el trazado no está activo. Esto causa que, cuando la sentencia SQL utiliza variables (bind variables), puesto que los valores reales de dichas variables son tomados en el momento del parsing, muy probablemente, los planes de ejecución de la misma sentencia SQL sean diferentes antes y después de activar el trazado al haberse generado utilizando valores de variable distintos.

Veamos un ejemplo:

SQL> CREATE TABLE pepe AS
2   SELECT CASE WHEN rownum = 1 THEN 1
3   ELSE 0 END pepe_id, all_tab_columns.*
4   FROM all_tab_columns
5   /
Table created.

SQL> CREATE INDEX pepe_idx ON pepe(pepe_id);
Index created.

SQL> BEGIN
2   DBMS_STATS.gather_table_stats (
3   user, 'T', method_opt=>
4   'for all'||
5   'indexed columns'||
6   'size 254' );
7   END;
8   /
PL/SQL procedure successfully completed.

De esta manera habremos generado la tabla pepe que contendrá un registro con la columna pepe_id=1, mientras que para el resto de registros (digamos que alrededor de 75.000), dicha columna tomará el valor de 0.

Resulta pues evidente que, si utilizamos el optimizador basado en costes, al haber generado los correspondientes histogramas con el procedimiento PL/SQL gather_table_stats, los planes de ejecución serán muy diferentes si ejecutamos SELECT * FROM pepe WHERE pepe_id=1, sentencia SQL para la cual el optimizador elegirá utilizar el índice pepe_idx, o si, por contra, ejecutamos SELECT * FROM pepe WHERE pepe_id=0, sentencia para la cual el optimizador elegirá realizar un full scan de la tabla.

Pero que ocurriría si utilizamos una variable a la hora de ejecutar la sentencia SQL, es decir, si ejecutamos SELECT * FROM pepe WHERE pepe_id=:id. En este caso la respuesta varía dependiendo de cual es la versión de la base de datos Oracle que estemos utilizando:

Base de datos Oracle 8i Release 3 y anteriores

En este caso el optimizador dispone de la siguiente información, la columna pepe_id puede tomar dos valores (1 y 0) y, además, la tabla tiene unos 75.000 registros. Debido al hecho de que la columna puede tomar sólo dos valores, el viejo optimizador de la versión 8i supondrá que para cualquier valor de la variable :id, la consulta SELECT asociada devolverá aproximadamente la mitad de los registros de la tabla. Así que, la decisión más probable del optimizador será realizar un escaneado completo (full scan) de la tabla.

Bases de datos Oracle 9i y 10g

Si disponemos de una versión de la base de datos Oracle incluida entre las versiones 9i y 10g, entonces la base de datos Oracle esperará a que se suministre el valor de la variable, antes de que el optimizador decida cual es el mejor plan de ejecución. Esto es lo que se conoce como bind variable peeking (que traducido vendría a ser algo como echar un vistazo a la variable). Así pues, en lo que se refiere a nuestra consulta SELECT, el optimizador elegirá bien realizar un full scan (:id=0), o bien utilizar el índice pepe_idx (:id=1), dependiendo del valor de la variable :id.

Y aquí es donde nos podemos encontrar con un problema de rendimiento ya que, el plan de ejecución que el optimizador utilice por primera vez, será el plan de ejecución que se almacene en el área de SQL compartido, y dicho plan será el que se utilice para ejecutar nuestra sentencia SQL SELECT * FROM pepe WHERE pepe_id=:id, independientemente del valor que demos a la variable :id con posterioridad.

Entonces ya sabemos que es lo que le pasó al lector que inspiró este artículo:

a) Alguien ejecuto la consulta en cuestión utilizando un determinado valor para la variable :id.

b) El plan de ejecución correspondiente a dicho valor de la variable se almacenó en el área de SQL compartido.

c) Nuestro lector ejecuto la misma consulta pero utilizando otro valor para la variable :id. El optimizador reutilizó el plan de ejecución almacenado en el área de SQL compartido. Dicho plan no era el más eficiente para el nuevo valor de la variable, de hecho se trataba de un plan de ejecución bastante pobre. Como resultado la consulta tardó en responder más tiempo del esperado.

d) Posteriormente nuestro lector activó el trazado (SQL_TRACE=TRUE) y volvió a ejecutar la consulta pero, en este caso, al estar el trazado activo, el plan de ejecución no se sacó del área de SQL compartido, sino que se elaboró un nuevo plan de ejecución mucho más eficiente, dando como resultado que la sentencia SQL se ejecutase de forma rápida.

Posibles soluciones

Existen algunas soluciones que nos permitirán evitar este tipo de situaciones provocadas por el bind variable peeking:

a) No utilizar variables (bind variables). En el ejemplo que he puesto es bastante evidente que no es necesario utilizar variables, simplemente podemos utilizar constantes (valores literales) en nuestra cláusula WHERE. Esta es, sin duda, la mejor solución.

b) No generar estadísticas que puedan hacer variar los planes de ejecución. Obviamente, si no hubiéramos generado los histogramas de la tabla pepe, el optimizador siempre generaría el mismo plan de ejecución con independencia del valor de la variable. A veces esto puede ser recomendable, pero si no generamos estadísticas, entonces perderemos algunas de las ventajas derivadas de utilizar el optimizador basado en costes.

c) Cambiar el valor del parámetro CURSOR_SHARING para que en vez de FORCE sea SIMILAR. Si utilizamos el valor SIMILAR evitaremos que el bind variable peeking entre en juego.

d) Actualizar nuestra base de datos Oracle a la versión 11g, ya que esta versión está dotada de una nueva funcionalidad conocida como intelligent cursor sharing (compartición de cursores inteligente). Así pues, para nuestra consulta ejemplo, el optimizador de la versión 11g es capaz de identificar que un plan de ejecución no es suficiente para todos los valores posibles de la variable :id, permitiendo que se generen varios planes de ejecución distintos para una misma sentencia SQL.