Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

El almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

Los resultados de la subconsulta quedan almacenados en una estructura de datos interna o hash table que, mientras dura la ejecución de la consulta PLSQL, queda residente en la memoria caché de la sesión Oracle correspondiente. Dicha estructura de datos desaparece de la caché en el momento que la consulta PL/SQL termina.

Aunque nosotros como desarrolladores no podremos localizar físicamente esa hash table, si que seremos capaces de "verla" en acción realizando algunas medidas bastante sencillas. Para ello podemos utilizar el siguiente ejemplo.

Primero crearemos una función PL/SQL como sigue:

SQL> CREATE OR REPLACE 
2  FUNCTION subc (v in VARCHAR2)
3  RETURN NUMBER AS
4  BEGIN
5    DBMS_APPLICATION_INFO.SET_CLIENT_INFO
6      (userenv('client_info')+1);
7    RETURN LENGTH(v);
8  END;
9  /

La función PLSQL que hemos creado lo que hace básicamente es contar cuantas veces ha sido llamada almacenando el resultado en CLIENT_INFO en la vista estándar V$SESSION.

A continuación invocamos la función utilizando la siguiente secuencia de comandos SQL:

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
710

SQL> EXEC 
2  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(0);
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT OWNER, subc(OWNER) FROM ALL_TABLES;
28764 rows selected.

Statistics
---------------------------
.....................
28764  rows processed

SQL> SET AUTOTRACE OFF
SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
997

SQL> SELECT USERENV('client_info') FROM DUAL;

USERENV('CLIENT_INFO')
----------------------
28764

El script SQL superior llama directamente a la función PL/SQL subc desde una consulta SELECT (ver línea en color naranja), mostrando cual ha sido el tiempo de CPU consumido por dicha consulta (997-710=287) y cuantas veces fue llamada dicha función (en este caso una vez por cada registro en la tabla ALL_TABLES, es decir, 28764 veces).

A continuación, en lugar de realizar la llamada a la función PLSQL directamente desde la consulta SELECT principal, hacemos dicha llamada dentro de una subconsulta.

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
997

SQL> EXEC 
2  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(0);
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT
2    OWNER,
3    (SELECT subc(OWNER) FROM DUAL) subc
4  FROM ALL_TABLES;
28764 rows selected.

Statistics
---------------------------
.....................
28764  rows processed

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
1186

SQL> SELECT USERENV('client_info') FROM DUAL;

USERENV('CLIENT_INFO')
----------------------
1444

De esta manera observamos que la función PL/SQL sólo ha sido llamada 1444 veces (frente a las 28764 anteriores) y que el tiempo de CPU se ha visto reducido en 0,98 segundos (1186-997=189, 287-189=98).

Nota: la función DBMS_UTILITY.GET_CPU_TIME fue introducida en la versión 10g de las bases de datos Oracle y mide el tiempo de uso de la CPU en centésimas de segundo.