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