Gather Statistics on a Specific Table Without Invalidating Cursors

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> PL/SQL Packages and Types Reference -> 141 DBMS_STATS

2. Este objetivo es muy específico, por lo que he adjuntado la página de documentación que explica todas las opciones que tenemos a la hora de recoger estadísticas de una tabla (método GATHER_TABLE_STATS del paquete DBMS_STATS).

El procedimento GATHER_TABLE_STATS tiene un parámetro para establecer si queremos invalidar o no los cursores que hay en memoria (NO_INVALIDATE). Cuando decidimos invalidar un cursor, si vuelve a ejecutarse, debe obtener un nuevo plan de ejecución que puede ser distinto al anterior en función de las estadísticas que acaban de recogerse.

Por defecto, el parámetro NO_INVALIDATE tiene el valor de DBMS_STATS.AUTO_INVALIDATE, lo que provoca que Oracle decida si quiere invalidar los cursores o no. Si nosotros queremos modificar este comportamiento tenemos dos formas de hacerlo. La forma más sencilla es llamar al procedimiento GATHER_TABLE_STATS indicando NO_INVALIDATE=TRUE. La segunda forma consiste en modificar las preferencias de la tabla para que no se invaliden los cursores asociados a ella.

-- Generamos un cursor de ejemplo para nuestras pruebas
-- Yo utilizo un Hint ficticio TEST_CURSOR para localizar el cursor facilmente
SELECT /* TEST_CURSOR */ COUNT(*) FROM SH.CUSTOMERS;

-- Obtenemos el estado del cursor
--   · Comprobamos que el cursor no ha sufrido invalidaciones y se ha ejecutado una sola vez
--   · El estado actual del cursor es VALID
SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS
FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';

-- Obtenemos estadísticas de la tabla sin invalidar el cursor
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS', no_invalidate => TRUE);

-- Comprobamos que no se ha invalidado el cursor
SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS
FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';

-- Recolectamos estadísticas de nuevo, esta vez invalidando el cursor el cursor
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS', no_invalidate => FALSE);

-- Comprobamos que esta vez se ha invalidado el cursor
--   · OBJECT_STATUS muestra el valor de "INVALID_UNAUTH"
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS', no_invalidate => FALSE);

-- Si lanzamos la consulta de nuevo, se genera un nuevo cursor válido
SELECT /* TEST_CURSOR */ COUNT(*) FROM SH.CUSTOMERS;

-- OBJECT_STATUS vuelve a mostrar la cadena "VALID"
SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS
FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';

-- Si no especificamos nada, ORacle decice si invalida los cursores o no
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS');

-- En el ejemplo que estamos haciendo no se invalida nuestro cursor
SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS
FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';

3. La segunda opción que tenemos para que no se invaliden los cursores de una tabla, un esquema o incluso la BD entera, es fijar los atributos por defecto de la tabla, esquema o BD.

-- Continuando con el ejemplo anterior, modificamos los atributos de la tabla SH.CUSTOMERS
EXEC DBMS_STATS.SET_TABLE_PREFS('SH','CUSTOMERS','NO_INVALIDATE','TRUE');

-- Podemos consultar los atributos por defecto de una tabla en la vista DBA_TAB_STAT_PREFS
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME='CUSTOMERS' AND OWNER='SH';

-- Eliminamos la configuración del atributo NO_INVALIDATE
EXEC DBMS_STATS.DELETE_TABLE_PREFS('SH','CUSTOMERS','NO_INVALIDATE');

-- Podemos configurar el atributo para todos los objetos de un esquema
-- Lo que hace el procedimiento es modificiar el atributo NO_INVALIDATE tabla por tabla
EXEC DBMS_STATS.SET_SCHEMA_PREFS('SH','NO_INVALIDATE','TRUE');

-- Borramos la configuración
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('SH','NO_INVALIDATE');

4. Pasar estadísticas con el paquete DBMS_STATS es bastante sencillo. También lo podemos hacer con Enterprise Manager aunque no es lo habitual.

En la página principal de EM ->
-> Click Databases ->
-> Click «OCM» ->
-> Click «Server» ->
-> Click «Manage Optimizer Statistics» ->
-> Click «Gather Optimizer Statistics» ->
-> Seleccionamos «Tables» ->
-> Selecccionamos «Customize Options» ->
-> Click «Next» ->
-> Click «Add» ->
-> Schema = «HR» ->
-> Object = «EMPLOYEES» ->
-> Click «Search» ->
-> Seleccionamos «HR.EMPLOYEES» ->
-> Click «OK» ->
-> Click «Next» ->
-> Cursor Invalidation = «None» (aquí es donde específicamos que no se invaliden los cursores ->
-> Click «Next» ->
-> Click «Next» ->
-> Click «Submit»

Vamos a ver si se han pasado las estadísticas correctamente.

Click «Object Statistics» ->
-> Schema = «HR» ->
-> Object = «EMPLOYEES» ->
-> Click «Go» ->
-> Comprobamos que es la fecha actual y la hora reciente en la que hemos pasado estadísticas