PLSQL dinámico con las funciones DBM_SESSION.SET_CONTEXT y SYS_CONTEXT (¿Por qué?)

En programación PL/SQL siempre tenemos lectores que nos hacen preguntas interesantes, en esta ocasión se nos ha preguntado acerca del motivo por el cual al utilizar PLSQL dinámico hay mucha gente que utiliza las funciones estándar de Oracle SYS_CONTEXT y DBM_SESSION.SET_CONTEXT, de manera que en la cláusula WHERE de cualquier consulta SQL, en lugar de utilizar simplemente literales se utiliza la función SYS_CONTEXT.

Es decir, por qué utilizar "WHERE valor = SYS_CONTEXT('mi_contexto','valor')", en vez de, por ejemplo, la simple y más corta sentencia "WHERE valor = 15".

Desde luego mi opinión está claramente a favor de utilizar siempre la función SYS_CONTEXT en lugar de literales, las razones son variadas y están relacionadas con el hecho de que utilizar literales es malo para:

• El rendimiento de la base de datos Oracle.
• La utilización de la shared pool.
• La escalabilidad de la aplicación.
• La seguridad (al favorecer algunos métodos de infiltración mediante inyección SQL).

A continuación voy a explicar un poco más en detalle sobre cómo cada uno de los aspectos anteriormente mencionados se pueden ver afectados por el uso o no uso de las funciones estándar de Oracle DBM_SESSION.SET_CONTEXT y SYS_CONTEXT al utilizar PL/SQL dinámico. Para ir avanzando os diré que el problema, en lo que respecta al rendimiento, radica en que utilizar literales implica que las tasas de hard parsing se incrementan significativamente (para saber que es el parsing podéis leer el artículo fases del procesamiento de una sentencia SQL), ya que diferentes valores de estos literales implican diferentes consultas SQL que la base de datos Oracle debe "parsear" desde cero al no encontrarse almacenadas en la shared pool.

No debe sorprenderos saber que para pequeñas sentencias PLSQL, casi un 95% del tiempo de ejecución se corresponde con el tiempo de parsing y no con el tiempo real de ejecución. Por este motivo, reducir el tiempo de parsing es fundamental a la hora de mejorar el rendimiento de nuestra base de datos Oracle. Tampoco debe extrañaros que una de las principales causas de la existencia de problemas de rendimiento en las bases de datos Oracle, sea tener una alta tasa de hard parsing al utilizar SQL dinámico no reutilizable.

Lo mejor para comprender todo este entramado que estoy contando es utilizar un ejemplo. Veamos el siguiente código:

CREATE OR REPLACE FUNCTION
apps.query_dinamica (p_owner dba_tables.owner%TYPE)
RETURN SYS_REFCURSOR
IS
  c_query SYS_REFCURSOR;
  v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT table_name
              FROM   dba_tables
              WHERE  1 = 1';
  IF p_owner IS NOT NULL THEN
  -- p_owner es VARCHAR2 por eso hay que utilizar '''
     v_query := v_query ||
                ' AND owner = '''||p_owner||'''';
  END IF;

  OPEN c_query FOR v_query;

  RETURN (c_query);
  
END;

En este ejemplo de PL/SQL dinámico hemos utilizado una forma de codificar que, como veremos a continuación, nos garantiza que la base de datos Oracle tenga que hacer hard parsing (sin posibilidad de acudir a la shared pool) cada vez que utilizamos un valor del parámetro p_owner diferente. Veamos por qué.

SQL> variable x refcursor;
SQL> EXECUTE :x := query_dinamica ('ASP');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
ASP_ALERT_SUBSCRIPTIONS
ASP_PROGRAM_RUN_DATES

SQL>  EXECUTE :x := query_dinamica ('MWA');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
MWA_CLASS_CUSTOM_FILES

SQL> SELECT sql_text, executions 
  2  FROM   v$sql
  3  WHERE  parsing_schema_name = 'APPS'
  4  AND    sql_text like '%table_name%dba_tables%1 = 1%';

SQL_TEXT             EXECUTIONS
-------------------------------
SELECT table_name
FROM   dba_tables
WHERE  1 = 1AND
owner = 'ASP'        1

SELECT table_name
FROM   dba_tables
WHERE  1 = 1AND
owner = 'MWA'        1

Como podéis ver, cada llamada a la función query_dinamica genera sentencias totalmente independientes que deben pasar por la fase de parsing de forma separada.

La solución para evitar esta circunstancia pasa por utilizar la librería estándar Oracle CONTEXTS, que si bien apareció para soportar VPD (Virtual Private Database), también puede emplearse para el caso que estamos analizando. Con la aplicación CONTEXTS es posible definir atributos y asignarles valores que residirán en el UGA (User Global Area) durante la vida de la sesión, son valores de carácter privado y, por lo tanto, cada sesión utiliza los suyos propios y no pueden ser accedidos desde otras sesiones.

Comprenderéis un poco mejor el funcionamiento de esta librería cuando vayamos resolviendo el problema que nos hemos planteado. Primero deberemos crear el contexto.

SQL> CREATE CONTEXT query_dinamica_ctx 
  2  USING apps.query_dinamica;

Context created.

Al crear el contexto query_dinamica_ctx hemos utilizado la cláusula USING para indicar a la base de datos Oracle que dicho contexto sólo puede ser utilizado desde nuestra función PLSQL apps.query_dinamica. Esto quiere decir que la creación de atributos y la asignación de valores sólo se puede hacer dentro de la función apps.query_dinamica, lo cual añade un nivel de seguridad adicional, ya que garantiza que los usuarios no van a poder crear atributos ni modificar sus valores desde, por ejemplo, SQL*Plus. Veamos a continuación un ejemplo para que veáis claramente a que me refiero.

SQL> EXECUTE DBMS_SESSION.SET_CONTEXT
  2  ('query_dinamica_ctx','owner_ctx','ASP');

BEGIN DBMS_SESSION.SET_CONTEXT
('query_dinamica_ctx','owner_ctx','ASP'); END;

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

A continuación veremos cómo quedaría nuestra función PLSQL apps.query_dinamica utilizando las funciones estándar Oracle DBM_SESSION.SET_CONTEXT y SYS_CONTEXT.

CREATE OR REPLACE FUNCTION 
apps.query_dinamica (p_owner dba_tables.owner%TYPE)
RETURN SYS_REFCURSOR
IS
  c_query SYS_REFCURSOR;
  v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT table_name
              FROM   dba_tables
              WHERE  1 = 1';
  IF p_owner IS NOT NULL THEN
     v_query := v_query ||
                ' AND owner = 
                  SYS_CONTEXT(''query_dinamica_ctx''
                             ,''owner_ctx'')';
     DBMS_SESSION.SET_CONTEXT
       ('query_dinamica_ctx','owner_ctx',p_owner);
  END IF;

  OPEN c_query FOR v_query;

  RETURN (c_query);
  
END;

Con la función PL/SQL DBMS_SESSION.SET_CONTEXT lo que hacemos es asignar el valor del parámetro p_owner al atributo owner_ctx, y con la función SYS_CONTEXT leemos desde el UGA el valor asignado a dicho atributo. Veamos a continuación como los cambios que hemos realizado afectan positivamente al rendimiento de nuestra función PLSQL.

SQL> execute :x := query_dinamica ('ASP');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
ASP_ALERT_SUBSCRIPTIONS
ASP_PROGRAM_RUN_DATES

SQL> execute :x := query_dinamica ('MWA');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
MWA_CLASS_CUSTOM_FILES

SQL> SELECT sql_text, executions 
  2  FROM   v$sql
  3  WHERE  parsing_schema_name = 'APPS'
  4  AND    sql_text like '%table_name%dba_tables%SYS_CON%';

SQL_TEXT                              EXECUTIONS
------------------------------------------------
SELECT table_name
FROM   dba_tables
WHERE  1 = 1
AND owner = SYS_CONTEXT
('query_dinamica_ctx','owner_ctx')    2

Hemos conseguido que la sentencia SQL que hemos ejecutado en un par de ocasiones, a pesar de utilizar parámetros con valores diferentes, sea exactamente la misma. De esta manera el parsing se hará durante la primera ejecución, mientras que para la segunda, el gestor de la base de datos Oracle acudirá a la shared pool evitándose el tiempo de parsing. Hemos dicho adiós al hard parsing y generado código PL/SQL reutilizable.

Pero no sólo la utilización de las funciones CONTEXT es importante a la hora de mejorar el rendimiento de una base de datos Oracle, sino que también resulta imperativo para conseguir escalabilidad multiusuario. Al realizar hard parsing es necesario bloquear o serializar las estructuras de datos en la SGA (System Global Area), lo que constituye una de las principales limitaciones a la escalabilidad de los sistemas actuales.

Y si nos fijamos en la seguridad de los sistemas, al utilizar funciones CONTEXT estaremos evitando problemas indeseados de inyección SQL, porque podremos evitar que los parámetros de entrada a nuestros procedimientos PL/SQL sean parte de la sentencia SQL. Usando funciones CONTEXT podemos conseguir que sólo valores contextuales, valores que podremos controlar, sean valores de entrada a dichos procedimientos (ojo, no es el caso de nuestro ejemplo).

Nota: en los scripts SQL también se pueden utilizar las llamadas bind variables para conseguir el mismo efecto que utilizando funciones CONTEXT (las variables bind son las que en el código SQL se representan con dos punto [":"] al principio de la misma).