1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 13 Managing Optimizer Statistics -> Gathering Statistics Manually -> Extended Statistics

2. La idea de utilizar estadísticas multicolumna proviene del hecho de que en muchas ocasiones existe una relación entre varias columnas de una misma tablas. Si conocemos estas relaciones y obtenemos estadísticas sobre ellas, entonces el optimizador puede generar mejores planes de ejecución ya que la selectividad del predicado que utilicemos se ajusta mucho mejor a la realidad.

-- Veamos por ejemplo la selectividad de la siguiente consulta
-- Activamos el modo AUTOTRACE
SET AUTOTRACE ON

-- Lanzamos la siguiente consulta donde tenemos un predicado compuesto de dos columnas
--   · La consulta nos indica que hay 3341 filas que cumple la condición
--   · Pero si nos fijamos en el número de filas estimadas, vemos que son muchas menos
--   · En mi caso obtengo un valor de 128 (bastante alejado de 3341)
SELECT COUNT(*)
FROM   sh.customers
WHERE  cust_state_province = 'CA'
AND    country_id=52790;

-- Pasemos estadísticas a la tabla y veamos si mejoramos la estimación
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS');

-- Lanzamos la consulta de nuevo
--   · Al pasar estadísticas se han creado dos histogramas en la tabla SH.CUSTOMERS
--   · Esta vez la estimación ha mejorado llegando a 1218 filas (seguimos lejos de 3341)
SELECT COUNT(*)
FROM   sh.customers
WHERE  cust_state_province = 'CA'
AND    country_id=52790;

-- Creamos un grupo de columnas para poder utilizar estadísticas multicolumna
DECLARE
  cg_name VARCHAR2(30);
BEGIN
  cg_name := DBMS_STATS.CREATE_EXTENDED_STATS('SH','customers',
               '(cust_state_province,country_id)');
END;
/

-- Comprobamos que se ha creado
SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('sh','customers',
       '(cust_state_province,country_id)') col_group_name
FROM DUAL;

-- Volvemos a revisar el plan de ejecución tras crear el grupo de columnas
--   · Si nos fijamos, podemos tener hasta un valor peor (1152)
--   · Debemos pasar estadísticas sobre el nuevo grupo de columnas
SELECT COUNT(*)
FROM   sh.customers
WHERE  cust_state_province = 'CA'
AND    country_id=52790;

-- Verificamos que ni siquiera tenemos estadísticas en la nueva multicolumna
SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SH' AND TABLE_NAME='CUSTOMERS';

-- Pasamos estadísticas incluyendo la nueva multicolumna
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');

-- Revisamos de nuevo si hemos obtenido las estadísticas y si tenemos un histograma
--   · Debe aparecer la nueva columna (SYS_STU#S#WF25Z#QAHIHE#MOFFMM_)
--   · Además debe incluir un histograma (HISTOGRAM = FREQUENCY)
SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SH' AND TABLE_NAME='CUSTOMERS';

-- Lanzamos de nuevo la consulta para ver qué sucede con el optimizador
--   · Esta vez devuelve una estimación bastante precisa (Rows = 3326)
--   · Una selectividad muy precisa va a producir planes de ejecución más eficientes
SELECT COUNT(*)
FROM   sh.customers
WHERE  cust_state_province = 'CA'
AND    country_id=52790;

-- Podemos obtener información del grupo que hemos creado a través de la siguiente consulta
SELECT EXTENSION_NAME, EXTENSION
FROM   DBA_STAT_EXTENSIONS
WHERE  TABLE_NAME='CUSTOMERS' AND OWNER='SH';

-- Borrar el nuevo grupo de columnas es muy sencillo
EXEC DBMS_STATS.DROP_EXTENDED_STATS('sh','customers', '(cust_state_province,country_id)');

3. Aunque en principio en el examen no se indica que sea necesario conocer las estadísticas basadas en expresión (Expression Statistics) vamos a hacer un ejercicio con ello, ya que es muy similar a lo anterior. Además las estadísticas multicolumna junto con la estadísticas basadas en expresión es lo que se conoce como Extended Statistics.

Cuando utilizamos una función en un predicado, el optimizador no tiene manera de saber como afecta dicha función a la selectividad de la columna. Por ello, Oracle nos permite crear una extensión de dicha columna que incluya las estadísticas en base a la función que utilicemos.

-- Si vemos el plan de ejecución de esta consulta vemos que estima approx. unas 555 filas
SELECT COUNT(*)
FROM SH.CUSTOMERS
WHERE UPPER(CUST_STATE_PROVINCE)='CA';

-- Podemos utilizar cualquier de estas dos opciones para crear la extensión
-- La primera de ellas utiliza el método GATHER_TABLE_STATS
exec dbms_stats.gather_table_stats('sh','customers', method_opt => 'for all columns size skewonly for columns (upper(cust_state_province)) size skewonly');

-- La segunda forma es similar al punto anterior, a través del método CREATE_EXTENDED_STATS
dbms_stats.create_extended_stats(null,'customers','(upper(cust_state_province))')
from dual;

-- Una vez creada la extensión, volvemos a obtener el plan de ejecución de la consulta
-- · Vemos que la estimación es bastante precisa (Rows = 3422)
SELECT COUNT(*)
FROM SH.CUSTOMERS
WHERE UPPER(CUST_STATE_PROVINCE)='CA';

-- Borrar la extensión de estadísticas que hemos creado
EXEC DBMS_STATS.DROP_EXTENDED_STATS('sh','customers', '(upper(cust_state_province))');

4. Hay una nueva funcionalidad en Oracle 11gR2 que se llama «Cardinality Feedback». Cuando se lanza por primera vez una consulta, se obtienen las cardinalidades en función de las estadísticas que tengan los objetos. Al final de la ejecución, si las cardinalidades reales son muy distintas a las estimdadas, entonces se guardan para futuras ejecuciones del mismo cursor. Podemos ver qué sucede cuando obtenemos el plan de ejecución de memory (DBMS_XPLAN.DISPLAY_CURSOR) y descubrimos la siguiente linea «cardinality feedback used for this statement». Otra forma de comprobar que se está usando es observar la columna USE_FEEDBACK_STATS en la vista V$SQL_SHARED_CURSOR.