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.

¿Tunear en base al COSTO del plan de ejecución?

Todos los días observo que alguien está queriendo tunear una consulta en base al costo del plan de ejecución. Pero... ¿Qué es el COSTO? ¿Qué representa? La respuesta es simple: El costo representa unidades de trabajo o recursos utilizados. El optimizador usa I/O a disco, CPU y memoria como unidades de trabajo. Entonces, el costo para una determinada consulta representa una estimación de la cantidad de I/O a disco, de CPU y memoria que se utilizará para la ejecución de la consulta.

Bien, con ésto ya dicho, porqué hay personas que tratan de tunear una consulta en base al costo??? El costo es simplemente un número que le asigna el optimizador de costos (CBO) a la consulta para saber qué plan de ejecución elegir entre todos los planes que genera en el momento de la optimización (el plan de ejecución que se genera con el menor costo es el que Oracle utiliza para ejecutar nuestra consulta), pero no existe un "mejor número" que debemos tener en mente para deducir si una consulta es óptima o no.

NO debemos tunear en base al costo. SI debemos tunear en base a los I/O lógicos (LIO's).

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test AS
2  SELECT level id, 'nombre_'||level nom
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Table created. 

SQL_9iR2> EXEC dbms_stats.gather_table_stats(user,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=17)
1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=1 Bytes=17)

Statistics
----------------------------------------------------------
     5  recursive calls
     0  db block gets
   323  consistent gets
     0  physical reads
     0  redo size
   335  bytes sent via SQL*Net to client
   495  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     2  sorts (memory)
     0  sorts (disk)
     1  rows processed

Como podemos observar, el costo de la consulta es de 49. Si yo tuneo en base al costo no puedo saber si el plan de ejecución que se está utilizando para ésta consulta es óptimo o no porque no sé que costo sería el ideal para saberlo. Es por eso que NO debemos tunear en base al costo. Por otro lado, podríamos tunear en base a la cantidad de I/O lógicos que se estén realizando. En éste ejemplo, se realizan 323 LIO's. Si observamos la consulta, estoy seleccionando la columna NOM que corresponde al ID 50000 . Como creé la tabla de forma tal que todos los ID's sean únicos, ésta consulta me debería traer un solo registro. Si nos ponemos a pensar, hacer 323 LIO's para traer sólo un registro es demasiado. En éste momento es en donde nos damos cuenta que tenemos un problema de performance porque estamos haciendo un Full Scan de una tabla de 100.000 registros para buscar solamente el ID 50000 que nos devuelve un sólo registro.

Veamos qué sucede si creamos un índice único por la columna ID,NOM...

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) ;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_index_stats(user,'TEST_ID_NOM_UQ') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1    0   INDEX (RANGE SCAN) OF 'TEST_ID_NOM_UQ' (UNIQUE) (Cost=2 Card=1 Bytes=17)

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
     3  consistent gets
     0  physical reads
     0  redo size
   335  bytes sent via SQL*Net to client
   495  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Luego de crear un índice único por la columna ID,NOM, ejecutando nuevamente la consulta, notamos que ahora sólo estamos realizando 3 LIO's y, por consiguiente, el costo se decrementó ya que estamos utilizando menos recursos que el caso anterior.