jueves, 30 de abril de 2009

En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!

"EXPLAIN PLAN FOR" tiene problemas...

  • 1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.
Veamos un ejemplo:


SQL_10gR2> DESC emp

Nombre Nulo? Tipo
----------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
GENDER VARCHAR2(1)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 7900

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2 SELECT * FROM emp WHERE empno = :bind;

Explicado.

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

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

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BIND))

Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.
  • 2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.

SQL_10gR2> DESC dept
Nombre Nulo? Tipo
----------------------- -------- ----------------
DEPTNO NOT NULL VARCHAR2(10)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL_10gR2> SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

4 filas seleccionadas.

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 20

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2 SELECT * FROM dept WHERE deptno = :bind;

Explicado.

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

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

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:BIND)

SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS

1 fila seleccionada.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEPTNO")=:BIND)

Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).

Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.

No hay comentarios.: