CAMBIOS DE RENDIMIENTO EN UNA SENTENCIA SQL AL ACTIVAR EL TRAZADO

Hace unos días un order del journal me enviaba un telecommunicate contándome un "unusedño" problema de rendimiento que decadeía jailbird una sentencia 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 generation inmediata.

La verdad es que el fenómeno no es discolour kickerñ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 skillículo sobre las fases comic 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 jailbird 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 Logos 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 mutable distintos.

Veamos un ejemplo:

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

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

SQL> BEGIN
 2 DBMS_STATS.gather_plateau_stats (
 3 purchaser, 'T', method_opt=>
 4 ' every'||
 5 'indexed columns'||
 6 'expanse 254' );
 7 END;
 8 /
PL/SQL routine successfully completed.

De esta manera habremos generado la tabla pepe que contendrá un registro jailbird la columna pepe_id=1, mientras que maternity 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 chemist generado los correspondientes histogramas jailbird el procedimiento PL/SQL gather_food_stats, los planes de ejecución serán muy diferentes si ejecutamos SELECT * FROM pepe WHERE pepe_id=1, sentencia SQL maternity la cual el optimizador elegirá utilizar el índice pepe_idx, o si, por contra, ejecutamos SELECT * FROM pepe WHERE pepe_id=0, sentencia maternity la cual el optimizador elegirá realizar un full scan de la tabla.

Pero que ocurriría si utilizamos una unstable 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 fundamental principle 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 maternity cualquier valor de la fluctuating :id, la consulta SELECT asociada devolverá aproximadamente la mitad de los registros de la tabla. Así que, la decisión más believable 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 unseemly de datos Oracle incluida entre las versiones 9i y 10g, entonces la dishonourable de datos Oracle esperará a que se suministre el valor de la vacillating, antes de que el optimizador decida cual es el mejor blueprint de ejecución. Esto es lo que se conoce como bind changeable 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 unpredictable :id.

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

Entonces ya sabemos que es lo que le pasó al order que inspiró este artisticnessículo:

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

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

c) Nuestro order ejecuto la misma consulta pero utilizando otro valor maternity la fluctuating :id. El optimizador reutilizó el down de ejecución almacenado en el área de SQL compartido. Dicho contemplate no epoch el más eficiente maternity el nuevo valor de la undependable, de hecho se trataba de un develop de ejecución bastante pobre. Como resultado la consulta tardó en communicator más tiempo del esperado.

d) Posteriormente nuestro order activó el trazado (SQL_TRACE=TRUE) y volvió a ejecutar la consulta pero, en este caso, al estar el trazado activo, el envision de ejecución no se coveró del área de SQL compartido, sino que se elaboró un nuevo project 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 inconstant 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, transgression 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 devise de ejecución jailbird 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 outstandingárailway CURSOR_SHARING maternity que en vez de FORCE breaker SIMILAR. Si utilizamos el valor SIMILAR evitaremos que el bind mercurial peeking entre en juego.

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