Luego de casi dos semanas sin postear, les presento un caso curioso que me ocurrió en estos días. Pues ocurre que como parte de una tarea de afinamiento de aplicaciones que me solicitó un cliente, encontré con ayuda de dbms_monitor y tkprof, que la sentencia responsable del problema de desempeño era una muy peculiar, se trataba de un query que siendo estático estaba escrito de tal forma que pretendía satisfacer varios criterios de búsqueda, esto sin saber de antemano cuáles han sido proporcionados. Les mostraré cómo esta forma de programar genera código con un pésimo desempeño y veremos desde luego cómo resolverlo, todo esto empleando nuestras conocidas tablas emp y dept.
El Problema Original
Observen el siguiente query detenidamente:
SELECT e.empno, e.sal, d.dname FROM emp e JOIN dept d USING (deptno) WHERE ( e.empno = :empno OR :empno IS NULL ) AND ( e.mgr = :mgr OR :mgr IS NULL )
El programador no sabe si el usuario final proporcionará los valores de :empno y :mgr por lo que, luego de meditarlo, recurre a un "artificio" que le permite escribir una sola sentencia que satisfaga todos los escenarios posibles. Misión cumplida, el query compila y trae resultados, eso es suficiente, lo pasa a producción y sigue con sus tareas, con la plena convicción de que ha hecho un "faenón" 
.
.Pues bien, cuando la aplicación empieza a ser utilizada los problemas aparecen: aún cuando el usuario final proporciona los datos de la clave primaria de la tabla emp, los resultados tardan mucho en aparecer, y es que no podía ser de otra forma, la sentencia está escrita de forma que el Optimizador se confunde y no saca provecho de los índices existentes:
SQL> variable empno number;
SQL> variable mgr number;
SQL> execute :empno := 7369;
SQL> execute :mgr := NULL;
SQL> SELECT e.empno, e.sal, d.dname
2 FROM emp e JOIN dept d USING (deptno)
3 WHERE ( e.empno = :empno OR :empno IS NULL )
4 AND ( e.mgr = :mgr OR :mgr IS NULL );
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)|
| 1 | NESTED LOOPS | | 1 | 28 | 4 (0)|
|* 2 | TABLE ACCESS FULL | EMP | 1 | 15 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(((:EMPNO IS NULL OR "E"."EMPNO"=:EMPNO) AND ("E"."MGR"=:MGR OR
:MGR IS NULL)))
4 - access("E"."DEPTNO"="D"."DEPTNO")No importa qué variables proporcionemos, el plan de ejecución será el mismo: Oracle opta por realizar un Full Table Scan (FTS) de la tabla emp.
Variantes del Problema
Desde luego esta no es la única forma de codificar una sentencia estática que pretende ser dinámica, abundan las ideas ingeniosas y por tanto hay variantes pero, como es de prever, con los mismos pésimos resultados:
1. Variante usando NVL
SELECT e.empno, e.sal, d.dname FROM emp e JOIN dept d USING (deptno) WHERE e.empno = NVL(:empno,e.empno) AND e.mgr = NVL(:mgr,e.mgr);
2. Variante usando DECODE
SELECT e.empno, e.sal, d.dname FROM emp e JOIN dept d USING (deptno) WHERE e.empno = DECODE(:empno,NULL,e.empno,:empno) AND e.mgr = DECODE(:mgr,NULL,e.mgr,:mgr);
Ambas son interpretadas de forma similar por el Optimizador y producen el mismo plan de ejecución:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)|
| 1 | CONCATENATION | | | | |
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 2 | 56 | 4 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 30 | 2 (0)|
|* 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
|* 8 | FILTER | | | | |
| 9 | NESTED LOOPS | | 1 | 28 | 2 (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 1 (0)|
|* 11 | INDEX RANGE SCAN | PK_EMP | 1 | | 0 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 1 (0)|
|* 13 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:EMPNO IS NULL)
4 - filter("E"."MGR"=NVL(:MGR,"E"."MGR"))
5 - filter("E"."EMPNO" IS NOT NULL)
7 - access("E"."DEPTNO"="D"."DEPTNO")
8 - filter(:EMPNO IS NOT NULL)
10 - filter("E"."MGR"=NVL(:MGR,"E"."MGR"))
11 - access("E"."EMPNO"=:EMPNO)
13 - access("E"."DEPTNO"="D"."DEPTNO")Pero observemos que, sin llegar a ser óptimo, el plan está mejor pensado: el Optimizador crea un plan de ejecución condicional, dependiendo de si se proporciona o no un valor para :empno y que se nota por la presencia de FILTER en los pasos 2 y 8 :
1. Plan de ejecución si :empno es NULL
| 3 | NESTED LOOPS | | 2 | 56 | 4 (0)| |* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 30 | 2 (0)| |* 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| | 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| |* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
2. Plan de ejecución si :empno es NOT NULL
| 9 | NESTED LOOPS | | 1 | 28 | 2 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 1 (0)| |* 11 | INDEX RANGE SCAN | PK_EMP | 1 | | 0 (0)| | 12 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 1 (0)| |* 13 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
¿Está mejor que el query original no?, pero al ignorar completamente lo que ocurre con :mgr el plan de ejecución es desastroso para escenarios en los que sí interviene.
La solución
Tratar de soportar todos los escenarios posibles con una sola sentencia ha probado ser una bomba de tiempo antes que una solución, si no sabemos qué variables serán proporcionadas pues entonces hagamos un query dinámico, tomará algo más de tiempo pero garantiza que al Optimizador le será más fácil encontrar el plan de ejecución ideal para el escenario que se le presente. Veamos parte del código modificado:
. . .
v_query := 'SELECT e.empno, e.sal, d.dname
FROM emp e JOIN dept d USING (deptno)';
v_filtro := ' WHERE 1=1';
IF empno IS NOT NULL THEN
v_filtro := v_filtro || ' AND e.empno = sys_context(''ctx'',''empno'')';
dbms_session.set_context ('ctx','empno',empno);
END IF;
IF mgr IS NOT NULL THEN
v_filtro := v_filtro || ' AND e.mgr = sys_context(''ctx'',''mgr'')';
dbms_session.set_context ('ctx','mgr',mgr);
END IF;
. . .Los planes de ejecución se ven mejor ahora:
1. Se proporciona solamente :empno
SELECT e.empno, e.sal, d.dname
FROM emp e JOIN dept d USING (deptno)
WHERE e.empno = :empno
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | NESTED LOOPS | | 1 | 24 | 2 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 11 | 1 (0)|
|* 3 | INDEX RANGE SCAN | PK_EMP | 1 | | 0 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 1 (0)|
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"=:EMPNO)
5 - access("E"."DEPTNO"="D"."DEPTNO")2. Se proporciona solamente :mgr
SELECT e.empno, e.sal, d.dname
FROM emp e JOIN dept d USING (deptno)
WHERE mgr = :mgr
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)|
| 1 | NESTED LOOPS | | 1 | 28 | 3 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)|
|* 3 | INDEX RANGE SCAN | EMP_MGR | 1 | | 1 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."MGR"=:MGR)
5 - access("E"."DEPTNO"="D"."DEPTNO")3. Se proporciona tanto :empno como :mgr
SELECT e.empno, e.sal, d.dname
FROM emp e JOIN dept d USING (deptno)
WHERE e.empno = :empno
AND e.mgr = :mgr
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | NESTED LOOPS | | 1 | 28 | 2 (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 1 (0)|
|* 3 | INDEX RANGE SCAN | PK_EMP | 1 | | 0 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 1 (0)|
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."MGR"=:MGR)
3 - access("E"."EMPNO"=:EMPNO)
5 - access("E"."DEPTNO"="D"."DEPTNO")4. No se proporciona :empno ni :mgr
SELECT e.empno, e.sal, d.dname
FROM emp e JOIN dept d USING (deptno)
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)|
| 1 | MERGE JOIN | | 14 | 336 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 4 | SORT JOIN | | 14 | 154 | 4 (25)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")Para cada una de las combinaciones mostradas, el Optimizador ha estado en la capacidad de determinar un plan de ejecución eficiente.
Conclusiones
Quizás parte del razonamiento en este caso incluyó la consideración de que más vale tener una sentencia que varias y así evitar saturar el Shared Pool, pero en tanto Oracle permite sentencias SQL estáticas y dinámicas, debemos usar estas últimas cuando no sabemos con anticipación lo que el usuario final desea filtrar, no importa tanto que se generen múltiples sentencias. Los "artificios" permiten crear una sola sentencia pero aún cuando el Optimizador haga su mejor esfuerzo, los planes de ejecución obtenidos serán sub-óptimos para la mayor parte de los casos, por tanto es preferible invertir algo más de tiempo y codificar una sentencia dinámica que nos da la garantía de planes de ejecución eficientes para cada caso.
Posts Relacionados:
- ¿Query dinámico?, pero sin perder de vista el contexto
- Cursor sharing, histograms y bind peeking, lo que deberías saber para evitar sorpresas
- ¿Cómo deshacernos de un cursor en el Shared Pool?
¿Te pareció interesante este artículo?, ¿te quedaron algunas dudas?, ¿quieres sugerirme un tema a tratar?, pues déjame tus comentarios o envíame un email y para que NO te pierdas ningún Post, suscríbete por email ahora mismo!
Entradas
5 comentarios, agrega el tuyo!
Enrique lo felicito por este blog.
Por casualidad llegué a este sitio y he leído varios de sus artículos.
Me han parecido muy interesantes, instructivos e ilustradores.
Al igual que usted, tengo más de 15 años de estar en el mundo Oracle; y de los pocos sitios que he encontrado en español, éste en particular a mi criterio es uno de los más claros y mejores.
Espero que siga adelante...
Francisco Chavarría Mora
Oracle OCP
San José, Costa Rica
Hola Francisco y gracias por tus palabras, en efecto hay pocos sites en Español por lo que, dada tu experiencia e interés, seria bueno si te animes a abrir el tuyo propio.
Saludos desde Perú.
Hola Enrique.Muy buenos tus ejemplos, los he leido todos. Creo que uno no explota lo suficiente todas las bondades de los dbms packages de Oracle. En tus ejemplos nos muestras que son una herramienta poderosa.
Sería interesante un articulo tuyo sobre las diferencias entre manejo de extents entre tablespaces manejandos por diccionario y los manejados localmente.
Saludos!
José Manuel Vizcaíno
México, Distrito Federal
Kike... ¡¡ Lo Máximo !!.... esta explicación es muy buena... a tomar en cuenta....
Saludos
Shirley.
Hola Enrique,
Sencillo y muy práctico tu ejemplo.
Gracias por publicar.
Saludos,
A-Z
Santa Cruz - Bolivia
http://orabolivia.blogspot.com/
Publicar un comentario