lunes, 29 de septiembre de 2008

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.

12 comentarios:

Sergis dijo...

Leonardo, ante todo, felicitaciones... se nota lejos que la tenes atada, y es excelente tu forma de explicar y ejemplificar las cosas. Gracias por compartir tu conocimiento con otros geeks.

Pablo dijo...

Hola Leonardo!!.. vi que en tu perfil que estas certificado como desarrollador de pl/sql. Te queria preguntar como obtuviste esta certificación (a mi en realidad me interesan las dos cosas, tanto certificarme como hacer un curso de PL/SQL, igual programo hace casi 3 años en PL... pero no me vendria nada mal). Estuve averiguando en proydesa y en it-collage, pero no se dictan cursos de pl.

Gracias!!

Pablo.-

Ricardo dijo...

Hola Leonardo, he buscado mucho y creo tu me puedes ayudar, tengo un archivo .txt de 4 gigas de tamaño, quiero abrirlo con Oracle 10g pero no logro subirlo, aunque hizo el cargue no logro visualizar nada, tu me podrias indicar cual es el error o si es en la instacion del Oracle donde estoy fallando. Mucha gracias.

Ricardo V

Leonardo Horikian dijo...

Pablo,

Yo aprendí PL/SQL por cuenta propia (sobre todo leyendo la documentación oficial de Oracle) cuando trabajaba puramente como desarrollador.
Oracle University (http://education.oracle.com) dicta cursos de PL/SQL!

Saludos

alexander dijo...

hola Leonardo,
muchas gracias por compartir conocimiento con nosotros.

tengo una duda con respecto al tema este de las bind variables,
(versión oracle: 10.2.0.4.0
versión compatible con instancia: 10.2.0)

copio parte del código que ejecuto en sql plus (versión 10.2.0.4.0):


var n_id number;
var c_id varchar2(40);

begin
:n_id := 9999;
:c_id := '9999';
end;
/

cuando hago el explain plan para:
1. SELECT descripcion FROM test WHERE id = :c_id;

el resultado es:
---> INDEX (RANGE SCAN) OF 'TEST_UQ' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=15)

---> 3 consistent gets

2. SELECT descripcion FROM test WHERE id = :n_id;

el resultado es:
---> INDEX (RANGE SCAN) OF 'TEST_UQ' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=15)

---> 292 consistent gets

la pregunta es por qué muestra en ambos INDEX (RANGE SCAN); no debería de hacer un TABLE ACCESS FULL con el ejemplo 2.?

Leonardo Horikian dijo...

Hola Alexander,

El comando EXPLAIN PLAN trata todas las bind variables como VARCHAR2. Si la columna ID es del tipo number, se va a realizar una conversión implícita de la bind variables de VARCHAR2 a NUMBER ya que el tipo de dato VARCHAR2 siempre pierde en la conversión entre tipos.

Fijate el siguiente ejemplo:

test@test10gr2> create table test as
2 select level id, 'desc_'||level descripcion
3 from dual
4 connect by level <= 1000;

Tabla creada.

test@test10gr2> create unique index test_uq on test(id);

Indice creado.

test@test10gr2> EXEC dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) ;

Procedimiento PL/SQL terminado correctamente.

test@test10gr2> var n_id number;
test@test10gr2> var c_id varchar2(40);

test@test10gr2> begin
2 :n_id := 9999;
3 :c_id := '9999';
4 end;
5 /

Procedimiento PL/SQL terminado correctamente.

test@test10gr2> EXPLAIN PLAN FOR
2 SELECT descripcion FROM test WHERE id = :c_id;

Explicado.

test@test10gr2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3461093323

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | TEST_UQ | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=TO_NUMBER(:C_ID))

14 filas seleccionadas.


test@test10gr2> EXPLAIN PLAN FOR
2 SELECT descripcion FROM test WHERE id = :n_id;

Explicado.

test@test10gr2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3461093323

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | TEST_UQ | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=TO_NUMBER(:N_ID))

14 filas seleccionadas.


COMO PODEMOS VER EN EL "PREDICATE INFORMATION", PARA AMBAS CONSULTAS A LAS BIND VARIABLES SE LE APLICÓ LA FUNCIÓN TO_NUMBER PARA CONVERTIR EL VALOR AL TIPO NUMBER (YA QUE CON EXPLAIN PLAN SIEMPRE SE ASUME QUE ES DEL TIPO VARCHAR2). ES POR ESO QUE ESTAMOS VIENDO LOS 2 PLANES DE EJECUCIÓN IGUALES ACCEDIENDO POR ÍNDICE.

Saludos

Marian dijo...

Hola Leonardo, soy Marian y soy nueva en esto de Oracle.
Estoy intentando hacer la sentencia EXPLAIN PLAN sobre unas vistas que me proporcionan desde otro lugar. Y me da un error de privilegios.
¿Sabes porque puede ser? O directamente no se puede ejercutar el EXPLAIN PLAN sobre vistas.

Muchas gracias.
Un saludo.

Leonardo Horikian dijo...

Hola Marian,

Se puede ejecutar EXPLAIN PLAN sobre vistas. De hecho, se puede ejecutar sobre cualquier tipo de consultas.

Seguramente no te está dejando ejecutar el EXPLAIN PLAN porque necesitas tener privilegios de SELECT sobre los objetos de tu consulta y sobre los objetos incluidos en la vista.

Saludos

Marian dijo...

Muchas gracias Leonardo, he pedido al administrador que me de privilegios.

Ya te diré como queda la cosa, si me los da.
Un saludo.

Facundo dijo...

Hola Leonardo, en referencia a este artículo quisiera hacer una consulta que quizás le sirva a muchos.
Cuando tenemos una aplicación (como puede ser SAP o cualquier otra) que envía los querys a la base con bind variables, ¿Existe alguna manera de averiguar qué valores le fueron asignados a esas variables?

Muchas gracias y felicitaciones por este excelente blog.

Leonardo Horikian dijo...

Hola Ricardo,

Para poder ayudarte necesito que me proporciones más detalles. Cómo realizaste la carga de los datos del archivo .txt a la base de datos? Qué estas utilizando para visualizar los registros y dónde los estas queriendo visualizar?

Saludos

Leonardo Horikian dijo...

Hola Facundo,

Si queres saber qué bind variables utilizó un determinado proceso en el momento de la ejecución de las consultas SQL, una de la maneras es realizando un trace sobre la sesión que está ejecutando el proceso.

El trace hay que ejecutarlo con el evento 10046 y nivel 4 o 12.

Saludos