All Things Oracle

Search
Close this search box.
Contáctame

El query dinámico de los pobres

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:
Copiar 
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:

Copiar 
SQL> variable empno number;
SQL> variable mgr number;
SQL> execute :empno := 7369;
SQL> execute :mgr := NULL;
SQL> 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 );

-----------------------------------------------------------------------------
| 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

Copiar 
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
Copiar 
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:
Copiar 
-------------------------------------------------------------------------------
| 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:

a. Plan de ejecución si :empno es NULL

Copiar 
|   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)|
b. Plan de ejecución si :empno es NOT NULL
Copiar 
|   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, usando Contextos, tal como detallo en un Post previo, 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:
Copiar 
. . .
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

Copiar 
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
Copiar 
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
Copiar 
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
Copiar 
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.

¿Te pareció interesante este artículo?, ¿te quedaron algunas dudas?, ¿quieres sugerirme un tema a tratar?, pues déjame tus comentarios o ¡contáctame ahora mismo!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Posts Relacionados

Troubleshoot: CRS-2304 GPnP profile signature verification failed.
Aprenda a resolver el error CRS-2304 GPnP profile signature verification failed al iniciar una base de datos 11.2 en un cluster 19c.
Siga Leyendo
¿Se dañaron los permisos de Oracle Home? Resuélvelo en menos que canta un gallo!
Aprenda a corregir los permisos dañados de un Oracle Home, ya sea de Oracle Grid o de Oracle Database Server
Siga Leyendo
Nada que ocultar
Aprenda a mejorar la seguridad de sus aplicaciones usando Oracle Wallet / Secure External Password Store.
Siga Leyendo

Copyright © 2024 – Enrique Orbegozo – Todos los derechos reservados

¿Necesitas Ayuda?

Completa estos datos y estaré en contacto a la brevedad.