sábado, 24 de enero de 2009

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:

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:
Siga leyendo >>

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

sábado, 10 de enero de 2009

¿Vas a aplicar un patch?, verifica posibles conflictos

De cuando en cuando nos topamos con algún problema que afecta el normal desempeño de la base de datos, o quizás de alguna aplicación, y luego de investigar llegamos a la conclusión de que es inevitable aplicar uno o varios patches, pero habrán notado que en el README.txt que acompaña a todo patch se indica:
"You must have NO OTHER PATCHES installed on your Oracle Server since the latest patch set"
Esta advertencia hará dudar a algunos y habrá quienes harán caso omiso al mismo, siendo cuestión de suerte que todo salga bien, pero afortunadamente es posible determinar con anticipación si los patches por aplicar entran en conflicto entre ellos o con patches previamente aplicados, lo cual nos ahorrará muchos dolores de cabeza.

Patching "a lo macho"
Tenemos plena confianza en que todo saldrá bien y aplicamos el patch sin pensarlo mucho y cruzando los dedos, a veces se aplicará sin problemas, otras veces fallará, como en este caso en el que deseamos aplicar el patch 4459936 FGA doesn't work correctly with ansi outer joins in 10g.

[oracle@caliope 4459936]$ opatch apply
Invoking OPatch 10.2.0.4.5

Oracle Interim Patch Installer version 10.2.0.4.5
Copyright (c) 2008, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.5
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2008-12-31_01-11-38AM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '4459936' to OH '/u01/app/oracle/product/10.2.0/db_1'
Interim Patch 4459936 has Conflict with patch(es) [  6996030 ] in OH /u01/app/oracle/product/10.2.0/db_1

Conflict patches:   6996030

Patch(es)  6996030 conflict with the patch currently being installed (4459936).

If you continue, patch(es)  6996030 will be rolled back and the new patch (4459936) will be installed.

If a merge of the new patch (4459936) and the conflicting patch(es) ( 6996030) is required, contact Oracle Support Services and request a Merged patch.

Do you want to proceed? [y|n]

Pues ocurre que el nuevo patch entra en conflicto con el patch 6996030 Merge label request on top of 10.2.0.4 for audit trail clean-up y si deseamos aplicarlo entonces Oracle tendrá que desinstalar el ya existente, algo que usualmente no deseamos hacer, pero mientras tanto perdimos tiempo pues tuvimos que suspender el servicio y posiblemente trabajar en un horario fuera de lo normal.

Patching Planificado
Afortunadamente es posible evitar esta situación si previamente verificamos que no existan conflictos, algo que se puede lograr de forma muy sencilla con OPatch, pero antes de ver cómo, primero veamos qué patches ya tenemos instalados:

[oracle@caliope 5099019]$ opatch lsinventory
Invoking OPatch 10.2.0.4.5

Oracle Interim Patch Installer version 10.2.0.4.5
Copyright (c) 2008, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.5
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2008-12-31_01-45-36AM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2008-12-31_01-45-36AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 3 products installed in this Oracle Home.


Interim patches (2) :

Patch  5099019      : applied on Wed Dec 31 01:42:18 PET 2008
   Created on 1 Dec 2008, 08:03:06 hrs PST8PDT
   Bugs fixed:
     5099019

Patch  6996030      : applied on Wed Sep 10 11:08:30 PET 2008
   Created on 21 Oct 2008, 06:57:58 hrs PST8PDT
   Bugs fixed:
     6964283, 6940487, 4740049, 6996030, 6954407, 6023472, 4085593, 6340297
     6655588, 6726958



--------------------------------------------------------------------------------

OPatch succeeded.

Tenemos un par de patches ya aplicados, el 5099019 dbms_stats doesn't count leaf_blocks correctly y el 6996030 Merge label request on top of 10.2.0.4 for audit trail clean-up, pero ahora queremos aplicar muchos más:

[oracle@caliope ~]$ ls /home/oracle/patches
4459936  5586604  6051177  6084232  6399168  6894671  6926448  7123643  7191744
7243560  5523375  5895190  6055481  6369463  6678845  6897966  7025450  7189722
7226463  7331929

Ahora veamos si entre ellos existe algún conflicto:

[oracle@caliope ~]$ opatch prereq CheckConflictAmongPatchesWithDetail -phBaseDir /home/oracle/patches
Invoking OPatch 10.2.0.4.5

Oracle Interim Patch Installer version 10.2.0.4.5
Copyright (c) 2008, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.5
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2008-12-31_01-47-03AM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictamongpatcheswithdetail"

Prereq "checkConflictAmongPatchesWithDetail" failed.

Summary of Conflict Analysis:

Patches that can be applied now without any conflicts are :
4459936, 5523375, 5586604, 5895190, 6051177, 6055481, 6084232, 6369463, 6399168, 6678845, 6894671, 6897966, 6926448, 7123643, 7189722, 7226463, 7243560, 7331929

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
7025450, 7191744

Conflicts/Supersets for each patch are:

Patch : 7025450

        Generic Conflict with 7191744
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a:/kks1.o

Patch : 7191744

        Generic Conflict with 7025450
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a:/kks1.o

OPatch succeeded.

OPatch nos notifica que existe un conflicto entre los patches 7025450 dbms_shared_pool.keep doesn't keep future child cursors y 7191744 update in wrong schema, pero ¿existirán conflictos adicionales con los patches ya instalados? pues averigüémoslo:

[oracle@caliope ~]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/patches/
Invoking OPatch 10.2.0.4.5

Oracle Interim Patch Installer version 10.2.0.4.5
Copyright (c) 2008, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.5
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2008-12-31_01-47-28AM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

Patches that can be applied now without any conflicts are :
5523375, 5586604, 5895190, 6051177, 6055481, 6084232, 6369463, 6678845, 6894671, 6897966, 6926448, 7123643, 7189722, 7226463, 7243560, 7331929

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
6996030, 4459936, 5099019, 6399168, 7025450, 7191744

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
6996030, 5099019

Conflicts/Supersets for each patch are:

Patch : 4459936

        Generic Conflict with 6996030
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a:/kzft.o

Patch : 6399168

        Generic Conflict with 5099019
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/prvtstat.plb

Patch : 7025450

        Generic Conflict with 7191744
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a:/kks1.o

Patch : 7191744

        Generic Conflict with 7025450
        Conflict details:
        /u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a:/kks1.o

OPatch succeeded.

Pues desafortunadamente hay conflictos adicionales, esta vez entre el patch 4459936 FGA doesn't work correctly with ansi outer joins in 10g y el 6996030 Merge label request on top of 10.2.0.4 for audit trail clean-up, algo que ya habíamos visto, y también entre el patch 6399168 gather statistics on table with bitmap indexes runs forever y el 5099019 dbms_stats doesn't count leaf_blocks correctly.

Con esta información ya podemos continuar con la aplicación de los patches que no presentarán conflictos y para los casos en que sí, debemos abrir un Service Request en My Oracle Support, solicitando se nos proporcione un merged patch tal como OPatch nos ha sugerido:

"Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
6996030, 4459936, 5099019, 6399168, 7025450, 7191744"

Recomendaciones Finales
Procura tener la última versión de OPatch, misma que puedes obtener buscando el Patch 6880880, instalarlo es muy fácil solo tienes que descomprimirlo en $ORACLE_HOME/OPatch. Luego que lo tengas instalado procede a obtener todos los patches que te interesa aplicar y descomprímelos bajo una misma carpeta, ahora usa OPatch para verificar la presencia de conflictos entre ellos y los ya aplicados. De existir algún conflicto solicita a Oracle que te proporcione un merged patch. Finalmente, te recomiendo leer los Notes 458485.1 How to find whether the one-off Patches will conflict or not? y 551394.1 What Are The MANDATORY Information Required To File A Merge Patch Request?.

Posts Relacionados:
Siga leyendo >>

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

domingo, 4 de enero de 2009

¿Query dinámico?, pero sin perder de vista el contexto

Es relativamente frecuente que tengamos que recurrir a SQL dinámico cuando no sabemos en tiempo de compilación el texto completo de la sentencia. Si bien en los tiempos de Oracle 7 la única forma de lograrlo era usando dbms_sql, en la actualidad la forma preferida de procesar SQL dinámico es con execute immediate, el problema radica en que no se hace buen uso de él y se termina generando código no reutilizable y esto lleva a elevadas tasas de hard parsing con el consiguiente deterioro del desempeño. Como en el ejercicio de mi labor como Consultor veo que este es un problema bastante recurrente, les mostraré a continuación una forma sencilla de corregirlo.

El Problema
OK, para situarnos en el problema veamos el siguiente código:

create or replace
function emp_query (
deptno dept.deptno%type default null,
low_hiredate emp.hiredate%type default null )
return sys_refcursor
is
  c_query sys_refcursor;
  v_query varchar2(8000);
begin
  v_query := 'select empno, ename, hiredate
                from emp
               where 1 = 1';
  if deptno is not null then
     v_query := v_query || ' and deptno = '||deptno;
  end if;
  if low_hiredate is not null then
     v_query := v_query || ' and hiredate >= '''||low_hiredate||'''';
  end if;

  open c_query for v_query;

  return ( c_query );
end;

¿Qué problema le encuentran? Observen como se concatenan los valores de los parámetros al momento de armar la sentencia, noten que hay que tener cuidado con el tipo de dato, así si se trata de varchar2 o date habrá que agregar los respectivos "'" alrededor del valor. Esta forma de programar garantiza un 100% de hard parse cada vez que llamemos a esta rutina! sino veamos que ocurre luego de unas cuantas llamadas:

SCOTT@orcl> variable x refcursor;

SCOTT@orcl> execute :x := emp_query(deptno=>10,low_hiredate=>'01/01/1982');

PL/SQL procedure successfully completed.

SCOTT@orcl> print x

     EMPNO ENAME                          HIREDATE
---------- ------------------------------ -------------------
      7934 MILLER                         23/01/1982 00:00:00

SCOTT@orcl> execute :x := emp_query(deptno=>20,low_hiredate=>'01/01/1981')

PL/SQL procedure successfully completed.

SCOTT@orcl> print x

     EMPNO ENAME                          HIREDATE
---------- ------------------------------ -------------------
      7566 JONES                          02/04/1981 00:00:00
      7788 SCOTT                          09/12/1982 00:00:00
      7876 ADAMS                          12/01/1983 00:00:00
      7902 FORD                           03/12/1981 00:00:00

SYS@orcl> select sql_text, executions from v$sql
  2  where parsing_schema_name = 'SCOTT';

SQL_TEXT                                 EXECUTIONS
---------------------------------------- ----------
select empno, ename, hiredate                     1
from emp where 1 = 1 and deptno = 10 
and hiredate >= '01/01/1982 00:00:00'

select empno, ename, hiredate                     1
from emp where 1 = 1 and deptno = 20
and hiredate >= '01/01/1981 00:00:00'

Lo que esperábamos, cada llamada genera una sentencia totalmente independiente.

El argumento que siempre me dan los desarrolladores es que execute immediate exige una lista fija de parámetros y no dejan de tener razón pues la sintaxis tendría que ser algo así:

execute immediate
   'select empno, ename, hiredate
      from emp
     where deptno = :deptno
       and hiredate > :hiredate’
using deptno, low_hiredate;

Pero no se puede garantizar que se proporcionen ambos parámetros, tal como está conceptualizada la rutina se permite que en algunos casos se puede pasar uno de los parámetros, ambos o incluso ninguno. ¿Estamos entonces atrapados sin salida? Pues no, tenemos una alternativa: application contexts.

La Solución
Si bien application contexts aparece para soportar VPD (Virtual Private Database) su uso puede ampliarse a otros casos, como el que estamos analizando en este momento. Con application contexts podemos especificar atributos y asignarles valores, estos residen en el UGA (User Global Area) durante la vida de la sesión, son de caracter privado por tanto cada sesión tiene sus propios atributos y valores y no pueden leer los de otras sesiones.

Comprenderemos mejor cómo funcionan los application contexts mediante la resolución de nuestro problema con su uso, para ello primero debemos crear el context, indicando que solamente puede ser manipulado desde nuestro stored procedure scott.emp_query.

SYS@orcl> create context emp_query_ctx using scott.emp_query;

Context created.

Observen que el context, si bien fue creado por SYS, en realidad no tiene un dueño y no se requieren tampoco de permisos para que pueda ser usado.

Veamos la versión modificada de scott.emp_query, usando application contexts:

create or replace
function emp_query (
deptno dept.deptno%type default null,
low_hiredate emp.hiredate%type default null )
return sys_refcursor
is
  c_query sys_refcursor;
  v_query varchar2(8000);
begin
  v_query := 'select empno, ename, hiredate
                from emp
               where 1 = 1';
  if deptno is not null then
     v_query := v_query || 
        ' and deptno = sys_context(''emp_query_ctx'',''deptno'')';
     dbms_session.set_context('emp_query_ctx','deptno',deptno);
  end if;
  if low_hiredate is not null then
     v_query := v_query || 
        ' and hiredate >= sys_context(''emp_query_ctx'',''hiredate'')';
     dbms_session.set_context('emp_query_ctx','hiredate',low_hiredate);
  end if;
  open c_query for v_query;

  return ( c_query );
end;

Lo que se está haciendo es crear atributos (deptno y hiredate) dentro del context emp_query_ctx y de paso asignándole valores (los contenidos de los parámetros deptno y low_hiredate), esto se logra mediante llamadas a dbms_session.set_context, mientras que para leer los valores asignados se emplea sys_context. La creación de atributos y la asignación de valores solo se puede hacer, por definición, desde scott.emp_query, lo cual añade un nivel de seguridad al garantizar que no se pueden crear atributos ni modificar sus valores de forma arbitraria.

SCOTT@orcl> execute dbms_session.set_context('emp_query_ctx','deptno',10)
BEGIN dbms_session.set_context('emp_query_ctx','deptno',10); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at line 1

Veamos ahora el desempeño de nuestra rutina modificada:

SCOTT@orcl> execute :x := emp_query(deptno=>10,low_hiredate=>'01/01/1981')

PL/SQL procedure successfully completed.

SCOTT@orcl> print x

     EMPNO ENAME                          HIREDATE
---------- ------------------------------ -------------------
      7782 CLARK                          09/06/1981 00:00:00
      7839 KING                           17/11/1981 00:00:00
      7934 MILLER                         23/01/1982 00:00:00

SCOTT@orcl> execute :x := emp_query(deptno=>20,low_hiredate=>'01/01/1982')

PL/SQL procedure successfully completed.

SCOTT@orcl> print x

     EMPNO ENAME                          HIREDATE
---------- ------------------------------ -------------------
      7788 SCOTT                          09/12/1982 00:00:00
      7876 ADAMS                          12/01/1983 00:00:00

SYS@orcl> select sql_text, executions from v$sql
  2  where parsing_schema_name = 'SCOTT';

SQL_TEXT                                                  EXECUTIONS
--------------------------------------------------------  ----------
select empno, ename, hiredate                                      2
from emp where 1 = 1
and deptno = sys_context('emp_query_ctx','deptno')
and hiredate >= sys_context('emp_query_ctx','hiredate')

¡Ahora sí! Tenemos código perfectamente reutilizable, sayonara hard parsing!!!.

Recomendaciones Finales
Para los que vienen siguiendo mi Blog, ya habrán intuido que para mí el hard parsing es algo así como el demonio y una de las labores de los DBAs es la de exorcizar periódicamente la base de datos, pero créanme no estoy exagerando. Uno de mis clientes tenía un serio problema: su flamante computador con 10 procesadores duales de última tecnología bordeaba el 100% de uso de CPU, y el diagnóstico era: alta tasa de hard parsing. Resulta que nuestros entrañables amigos desarrolladores se las habían ingeniado para llenar la base de datos con sql dinámico no reutilizable, el problema no se nota si tenemos unos pocos usuarios con actividad esporádica, pero cuando tienes miles de usuarios interactuando en simultáneo el problema es evidente y los resultados son catastróficos. Si no lo han hecho ya, pueden leer el Post relacionado que mencionan el problema y señalan algunos paliativos, pero no se engañen: la solución más barata y segura siempre será codificar las aplicaciones de forma eficiente.

Post Relacionados:

Siga leyendo >>

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