PL-SQL Argentina: Oracle 23c - Mejoras Varias #1

En este artículo vamos a ver algunas mejoras de Oracle 23c que no evaluamos en otros artículos.

Mas de 1000 Valores en Operador IN

Una mejora que puede ser útil en el caso de código generado en forma dinámica o automática es la posibilidad de usar el operador IN con hasta 64.000 valores, en vez del limite de 1000 valores que existía previamente.

Lo primero que vamos a hacer, es generar una lista de mas de 1000 valores:

SELECT LISTAGG( LEVEL,',' )
--FROM dual
CONNECT BY LEVEL <= 1010;

Nota: Oracle Database 23c permite escribir sentencias select sin la clausula FROM si la misma no es necesaria, como vemos en el ejemplo que está comentada.

El paso siguiente es pegar el resultado de esa consulta que devuelve 1010 valores separados por comas (el formato que espera el operador IN) en la siguiente sentencia, y ejecutarla:

SELECT *
FROM all_objects a
WHERE object_id IN (<PegarAca>);

Veremos que la query se ejecuta sin ningún problema:

Auditoría a Nivel Columnas

Oracle 23c permite definir políticas de auditoría a nivel de columnas específicas tanto de vistas como de tablas, permitiendo de esa forma reducir el número de "falsos positivos" al tener que auditar sobre toda la tabla cuando nos interesa sólo una o varias columnas en particular.

Vamos a crear una tabla con varias columnas para demostrar cómo se define y cómo funciona la auditoría a nivel columna:

CREATE TABLE AuditarColumnas (
    ID NUMBER(10) NOT NULL, Texto1 VARCHAR2(10), Texto2 VARCHAR2(10), Texto3 VARCHAR2(10));

Y ahora vamos a insertar algunos registros (usando la funcionalidad de constructor de tablas descripta en el artículo "Oracle 23c - Constructor de Tabla a partir de Valores"):

INSERT INTO AuditarColumnas VALUES
    (1, 'Fila 1 A', 'Fila 1 B', 'Fila 1 C'),
    (2, 'Fila 2 A', 'Fila 2 B', 'Fila 2 C'),
    (3, 'Fila 3 A', 'Fila 3 B', 'Fila 3 C'),
    (4, 'Fila 4 A', 'Fila 4 B', 'Fila 4 C');

Definiendo Auditoría a Nivel Columna

En el ejemplo podemos ver como crear una regla de auditoría en el caso de que se ejecute sentencias SELECT contra las columnas Texto1 o Texto2, o cuando se ejecute un UPDATE contra la columna Texto3, y a continuación vamos a habilitar dicha regla:

CREATE AUDIT POLICY auditar_columnas
    ACTIONS SELECT(Texto1, Texto2) ON demo23c.AuditarColumnas,
            UPDATE(Texto3) ON demo23c.AuditarColumnas
    CONTAINER = CURRENT;
--
AUDIT POLICY auditar_columnas;

Nota: Para crear auditorías es necesario tener permisos, en este caso la misma la creamos con el usuario SYSTEM.

La lista de acciones que se pueden auditar sobre columnas esta disponible en la documentación oficial.

Probando la Auditoría

Vamos a ejecutar sentencias SELECT y UPDATE que en algunos casos tienen que ser auditados y en otros casos no:

UPDATE AuditarColumnas SET Texto1 = 'Modificado' WHERE ID =1; -- No debe Auditar
SELECT ID, Texto1 FROM AuditarColumnas WHERE ID <3; -- Si debe Auditar
UPDATE AuditarColumnas SET Texto3 = 'Modificado' WHERE ID =3; --Si debe Auditar
SELECT ID, Texto3 FROM AuditarColumnas WHERE ID >2; -- No debe Auditar

Y sólo nos resta consultar la tabla de auditoría unificada (nuevamente, usando el usuario SYSTEM u otro usuario con privilegios de acceder a la auditoría) de esta manera:

SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD HH24:MI:SS') AS Fecha, dbusername AS Usuario,
       action_name AS Accion, object_schema AS Esquema, object_name AS Objeto, sql_text AS CodigoSQL
FROM unified_audit_trail
WHERE object_name = 'AUDITARCOLUMNAS';

Obteniendo el siguiente resultado:

FECHA                USUARIO    ACCION     ESQUEMA    OBJETO          CODIGOSQL                                                   
-------------------- ---------- ---------- ---------- --------------- ------------------------------------------------------------
2023-06-15 22:03:40  DEMO23C    SELECT     DEMO23C    AUDITARCOLUMNAS SELECT ID, Texto1 FROM AuditarColumnas WHERE ID <3
2023-06-15 22:04:03  DEMO23C    UPDATE     DEMO23C    AUDITARCOLUMNAS UPDATE AuditarColumnas SET Texto3 = 'Modificado' WHERE ID =3

Conversor Automático de PL/SQL a SQL (SQL Transpiler)

Esta nueva funcionalidad permite, en ciertos casos, convertir funciones desarrollada en PL/SQL directamente a código SQL, reduciendo notablemente el costo de ejecutar sentencias SQL que llaman veces a dichas funciones. Cuando el código SQL ejecuta una función PL/SQL, se produce un "cambio de contexto" en el que la sentencia SQL se detiene, se ejecuta el código PL/SQL en el motor de ejecución PL/SQL, el cual devuelve un resultado, y a continuación se vuelve el control al motor SQL, que obtiene y utiliza el resultado devuelto por el motor PL/SQL y luego toma otra fila y repite el proceso para ejecutar la función en ella.

Oracle 23c permite que funciones PL/SQL pueden ser convertidas directamente en la sentencia que las utiliza en código SQL (para ver más detalles sobre que funciones pueden ser convertidas consultar la documentación oficial), evitando de esa forma los cambios de contexto.

Esta conversión es automática, y solo requiere habilitarla mediante un parámetro configurable a nivel de instancia o de sesión.

Probando la conversión

Vamos a crear una función que concatena dos cadenas con un separador en el medio:

CREATE OR REPLACE FUNCTION Concatenar (pTexto1 VARCHAR, pTexto2 VARCHAR)
RETURN VARCHAR AS
BEGIN
    RETURN pTexto1 || '-' || pTexto2;
END;
/

Y vamos a ejecutar un SELECT sobre la tabla creada en el ejemplo anterior de auditoría de columnas utilizando la función recién creada:

SELECT * FROM AuditarColumnas ac
WHERE Concatenar(ac.Texto1, ac.Texto2) = 'Fila 2 A-Fila 2 B';

Si consultamos el plan de ejecución de esta sentencia, veremos que en el predicado figura la llamada a la función:

A continuación vamos a cambiar la configuración de nuestra sesión, habilitando la funcionalidad de conversión (llamada transpiler) modificando el parámetro SQL_TRANSPILER de la siguiente forma:

ALTER SESSION SET sql_transpiler = 'ON';

Si volvemos a ejecutar la sentencia y vemos su plan de ejecución, nos encontraremos que la misma ha sido convertida y ya no contiene la llamada a la función sino que el código de la misma ha sido "embebido" dentro de nuestra sentencia:

Conclusión

En este artículo discutimos tres mejoras disponibles en Oracle 23c que facilitan la generación de código en forma dinámica, simplifican la auditoría y permiten una ejecución más eficiente de código PL/SQL sin necesidad de reescribir el mismo ni las sentencias que lo llaman.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Páginas

viernes, 16 de junio de 2023

Oracle 23c - Mejoras Varias #1