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
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 1SCOTT@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')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.