All Things Oracle

¿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 cómo 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 qué 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
     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 pueda 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 carácter 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
     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 menciona 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.

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

Agregue un comentario

Su dirección de correo no se hará público. Los campos requeridos están marcados *

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

Posts Relacionados

Primer post de una serie dedicada al arte de parchar Oracle. Empezamos con el parchado in-place, la forma más común y también la más peligrosa.
Aprenda a descargar los parches de Oracle, tanto manualmente como de forma automatizada, usando el utilitario getMOSPatch.
Link a articulo publicado en Toad World, sobre como aplicar un patch out-of-place a Grid Infrastructure, usando un Golden Image.

¿Necesitas Ayuda?

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