Ciclo de vida de un Cursor

Es importante entender el funcionamiento de un cursor para poder optimizar aplicaciones que ejecuten sentencias SQL. A continuación mostraré una gráfica con los distintos pasos que se llevan a cabo en el proceso de un cursor:

• Open Cursor: una estructura de memoria es asignada al cursor en la memoria privada del proceso servidor del lado del servidor asociado a la sesión (UGA User Global Area). Tenga en cuenta que ninguna solicitud SQL está asociada con el cursor todavía.

• Parse Cursor: Una sentencia SQL está asociada con el cursor. La representación del recorrido de la solicitud incluyendo su plan de ejecución (que describe cómo el motor de SQL ejecutará la sentencia de SQL) es cargado en la shared pool, en concreto, en la library cache. La estructura en la UGA es actualizada para almacenar un puntero a la posición del cursor en la library cache.

• Define output variables: Si la sentencia devuelve los datos, las variables que los reciben deben ser definidas. Esto es necesario no sólo para las consultas, sino también para sentencias DELETE, INSERT y UPDATE que utilizan la cláusula RETURNING.

• Bind input variables: Si la instrucción SQL utiliza bind variables, sus valores deben ser proporcionados. Ninguna verificación se realiza durante el enlace. Si se pasan datos no válidos, un error de ejecución será activado.

• Execute cursor: La sentencia SQL se ejecuta. Pero tenga cuidado, porque el motor de base de datos no siempre hace algo importante durante esta fase. De hecho, para muchos tipos de consultas, el fase de ejecución real se puede retrasar a la fase de fetch.

• Fetch cursor: Si la sentencia devuelve los datos, es en esta etapa donde se obtienen. Especialmente para las consultas, en este paso es donde se lleva a cabo la mayor parte del procesamiento. En el caso de las consultas, las filas pueden ser parcialmente recibidas. En otras palabras, el cursor puede ser cerrado antes de ir a buscar todas las filas.

• Close cursor: Los recursos asociados con el cursor en la UGA se liberan y, en consecuencia quedan a disposición de otros cursores. El cursor compartido en la library cache no es eliminado. Quedan allí para poder ser reutilizados en el futuro.

Dependiendo del entorno de programación o técnicas de programación utilizadas, los diferentes pasos descritos pueden ser implícita o explícitamente ejecutados. Los siguientes ejemplos en PL/SQL muestran lo expuesto:

El primer bloque pl/sql muestra la técnica de forma explícita:

DECLARE
l_ename emp.ename%TYPE := ‘SCOTT’;
l_empno emp.empno%TYPE;
l_cursor INTEGER;
l_retval INTEGER;
BEGIN
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, ‘SELECT empno FROM emp WHERE ename = :ename’, 1);
dbms_sql.define_column(l_cursor, 1, l_empno);
dbms_sql.bind_variable(l_cursor, ‘:ename’, l_ename);
l_retval := dbms_sql.execute(l_cursor);
IF dbms_sql.fetch_rows(l_cursor) > 0
THEN
dbms_sql.column_value(l_cursor, 1, l_empno);
END IF;
dbms_sql.close_cursor(l_cursor);
END;

El siguiente toma ventaja de un cursor implícito:

DECLARE
l_ename emp.ename%TYPE := ‘SCOTT’;
l_empno emp.empno%TYPE;
BEGIN
SELECT empno INTO l_empno
FROM emp
WHERE ename = l_ename;
END;

Referencias:
Troubleshooting Oracle Performance (Christian Antognini)