Explain Plan Vs. Bind Variables

Obtener el plan de ejecución de una consulta que contiene Bind Variables sin haberlas reemplazado??? NO!!! NO!!! NO!!!!!!!!!!

Ya hablamos en otras ocasiones del beneficio que obtenemos al utilizar Bind Variables y también explicamos qué son. Cuando ejecutamos una consulta con Bind Variables (sin haberlas reemplazado) para obtener el plan de ejecución, el optimizador de costos (CBO) no sabe el valor de la Bind Variable; y por lo tanto, calcula la selectividad del filtro utilizando reglas definidas por defecto. Que quiere decir ésto? Que el plan de ejecución que obtenemos puede ser MUY distinto al plan de ejecución real!!! Porqué muy distinto? Porque todo depende del valor con el que se reemplazará la Bind Variable y el tipo de dato de la misma.

Veamos un ejemplo:

SQL_10gR> CREATE TABLE test AS
  2 SELECT TO_CHAR(level) id, 'test'||level descripcion
  3 FROM dual
  4 CONNECT BY level <= 100000;

Table created.

SQL_10gR> DESC test

Name                    Null?    Type
----------------------- -------- ----------------
ID                               VARCHAR2(40)
DESCRIPCION                      VARCHAR2(44)

SQL_10gR> CREATE UNIQUE INDEX test_uq ON test(id, descripcion);

Index created.

SQL_10gR> EXEC dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) ;

PL/SQL procedure successfully completed.

Supongamos que detectamos un problema grave en la performance de una de nuestras aplicaciones. Al identificar la consulta que nos está causando problemas, obtenemos el plan de ejecución de la misma para ver si está accediendo correctamente...

Ejecutamos la consulta con Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = :b1;

Explained.

SQL_10gR> @explains
Plan hash value: 1087767317

----------------------------------------------------------------------------
| Id | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |         |     1 |    15 |       2 (0)|  00:00:01 |
|* 1 | INDEX RANGE SCAN | TEST_UQ |     1 |    15 |       2 (0)|  00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - access("ID"=:B1)

Por lo que vemos en el plan de ejecución, si filtramos la columna ID con un valor del mismo tipo de dato, el optimizador eligirá acceder por índice en vez de realizar un full scan de la tabla. Esto suena lógico sabiendo que los valores de la columna ID son únicos y que por cada valor con el que filtremos, a lo sumo obtendremos una ocurrencia del mismo valor en la tabla.

Ejecutamos una consulta sin Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = 10000;

Explained.

SQL_10gR> @explains
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    15 |      54 (4)|    00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |    1 |    15 |      54 (4)|    00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - filter(TO_NUMBER("ID")=10000)

Pero qué sucede si filtramos la columna ID con un valor de distinto tipo de dato? Como en éste caso estamos realizando una conversión implícita, el optimizador no puede utilizar el índice que tenemos creado en la tabla y por lo tanto se ve forzado a realizar un full scan de la misma.

Si en nuestra aplicación el problema es justamente éste (que estamos realizando una conversión implícita), si no reemplazamos las Bind Variables con valores reales, estaremos pensando que el optimizador está accediendo de la manera correcta... cuando en realidad ésto no es cierto.

Recuerden lo siguiente: Siempre que obtengan el plan de ejecución de una consulta... reemplacen las Bind Variables con valores reales!!! En caso contrario... no deberíamos fiarnos demasiado con el plan de ejecución obtenido.