DBA Junior

Manage Materialized Views to Improve Rewrite and Refresh Performance

1. Documentación en Tahiti -> Masters Book List -> Database Concepts -> 4 Partitions, Views, and Other Schema Objects -> Overview of Materialized Views

Documentación en Tahiti -> Masters Book List -> Data Warehousing Guide -> 9 Basic Materialized Views

2. Instalamos el esquema de ejemplo SH para estas prácticas. Los ejemplos de Oracle están incluidos en el fichero «p10404530_112030_platform_6of7.zip» del software de Oracle 11.2.0.3. Una vez se descomprime, tan sólo hay que ejecutar el fichero runInstaller y seguir los sencillos pasos que se indican en el asistente. Una vez hemos instalado los esquemas de ejemplo ya podemos instalar el esquema SH.

# Nos ubicamos en el directorio del esquema SH
cd $ORACLE_HOME/demo/schema/sales_history/

# Iniciamos SQL*Plus
sqlplus / as sysdba

SQL> @sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: sh

specify default tablespace for SH as parameter 2:
Enter value for 2: USERS

specify temporary tablespace for SH as parameter 3:
Enter value for 3: TEMP

specify password for SYS as parameter 4:
Enter value for 4: *********

specify directory path for the data files as parameter 5:
Enter value for 5: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

writeable directory path for the log files as parameter 6:
Enter value for 6: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

specify version as parameter 7:
Enter value for 7: v3

3. Primero hacemos un repaso de las vistas materializadas. Una vista materializada tiene dos finalidades. La primera de ellas es replicar una tabla, ya sea en una BD distinta, o en la misma BD con la finalidad de tener una copia. Esta réplica se puede refrescar manual o automáticamente según lo deseemos. La segunda finalidad es almacenar los resultados de una sentencia pesada de forma que si queremos consultar varias veces dichos datos, no tenemos que lanzar la misma consulta y consumir recursos innecesarios.

Cuando se crea una vista materializada, si no se específica lo contrario, se ejecuta la sentencia indicada y se almacena el resultado (BUILD IMMEDIATE). También podemos crear una vista materializada «vacía» (BUILD DEFERRED) y actualizarla bajo demanda.

-- Creamos una vista materializada de ejemplo (MVIEW)
-- Como no indicamos el tipo de MVIEW el refresco se produce inmmediatamente (BUILD IMMEDIATE)
CREATE MATERIALIZED VIEW SALES_MV AS
  SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Consultamos el contenido
SELECT * FROM SALES_MV WHERE ROWNUM < 5;

-- Consultamos los atributos de la MVIEW
SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME='SALES_MV';

-- Borramos la MVIEW
DROP MATERIALIZED VIEW SALES_MV;

-- Un ejempo de creación de MVIEW sin el refresco inicial de datos
CREATE MATERIALIZED VIEW sales_mv BUILD DEFERRED AS
  SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Comprobamos que no tienen ningún registro
SELECT * FROM SALES_MV WHERE ROWNUM < 5;

-- Lanzamos el refresco manual
EXEC DBMS_MVIEW.REFRESH('SALES_MV');

-- Probamos otras formas para refrescar la MVIEW
-- Refreso de todas las MVIEWS dependientes de la tabla SALES
VARIABLE failures NUMBER;
EXEC DBMS_MVIEW.REFRESH_DEPENDENT(:failures,'SALES');

-- Refreso de todas las MVIEWS (realizar como SYSDBA)
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS(:failures);

-- Comprobamos que la MVIEW ya está poblada
SELECT * FROM SALES_MV WHERE ROWNUM < 5;

-- Borramos la MVIEW
DROP MATERIALIZED VIEW SALES_MV;

4. Hemos visto un par de sentencia de creación de MVIEWS en la que los refrescos se realizan de forma completa. Pero es muy habitual tener MVIEWS con refrescos incrementales para tener una réplica en constante actualización sin tener que leer todos los datos de la tabla maestra de nuevo. El objetivo es mejorar el rendimiento de los refrescos. Este tipo de MVIEWS requiere de un nuevo objeto conocido como MATERIALIZED VIEW LOG. Necesitamos estos objetos registrar los cambios que se producen a las tablas maestras y poder aplicar únicamente esos cambios a la tabla replicada.

-- Creamos los LOGS de las MVIEWS
-- Si quereís, podéis comprobar que se crean varias tablas:
--   · MLOG$_SALES
--   · MLOG$_PRODUCTS
--   · MLOGS$_TIMES
-- Como utilizamos funciones agregadas en la MVIEW => añadimos INCLUDING NEW VALUES y SEQUENCE
-- Normalmente hay que añadir siempre ROWID para permitir Fast Refresh
-- En general COMMIT SCN proporciona mejor rendimiento con la MVIEW LOGS, con excepciones:
--   · COMMIT SCT no está soportado en tablas con uno o más campos de tipo LOB
--   · Todas las tablas maestras de una MVIEW deben crearse con COMMIT SCN
-- También añadimos las columnas referencias en el SELECT o en el JOIN de la MVIEW
CREATE MATERIALIZED VIEW LOG ON sales
  WITH SEQUENCE, ROWID (prod_id, time_id, amount_sold),
  COMMIT SCN INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
  WITH PRIMARY KEY, SEQUENCE, ROWID, COMMIT SCN
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON times
  WITH PRIMARY KEY, SEQUENCE, ROWID (calendar_year),
  COMMIT SCN INCLUDING NEW VALUES;

-- Creamos una MVIEW con refrescos incrementales (bajo demanda)
CREATE MATERIALIZED VIEW sales_mv REFRESH FAST AS
  SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Creamos la MVIEW con un refresco cada día
CREATE MATERIALIZED VIEW sales_mv_daily REFRESH FAST NEXT SYSDATE+1 AS
  SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Podemos especificar que se actualice una MVIEW cada vez que se haga un COMMIT
CREATE MATERIALIZED VIEW sales_mv_current REFRESH FAST ON COMMIT AS
  SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Podemos modificar la frecuencia del refresco (cada minuto)
ALTER MATERIALIZED VIEW sales_MV REFRESH FAST NEXT SYSDATE+(1/1440);

-- Obtener la fecha del próximo refresco de una MVIEW
SELECT NAME, NEXT FROM DBA_SNAPSHOTS WHERE NAME LIKE 'SALES_MV%';

5. Oracle proporciona una técnica de optimización de consultas, por la cual una sentencia que ataca a una serie de tablas maestra puede ser reescrita para utilizar vistas materializadas, con el conseguiente ahorro de CPU y/o operaciones I/O. Vamos a prácticar un ejemplo de cómo conseguirlo.

-- Comprobamos que tenemos activado el parámetro QUERY_REWRITE_ENABLED
-- Este parámetro puede tomar tres valores:
--   · FALSE: Query Rewrite desactivado
--   · TRUE: Se escoger el plan con menor coste CON o SIN Query Write
--   · FORCE: Siempre que se pueda utilizar Query Rewrite, se utilizará
SHOW PARAMETER QUERY_REWRITE_ENABLED

-- Comprobamos si nuestra MVIEW tiene habilitado Query Rewrite
-- Por defecto no está habilitado (REWRITE_CAPABILITY=N)
SELECT MVIEW_NAME, REWRITE_ENABLED FROM DBA_MVIEWS;

-- Habilitamos la generación de planes de ejecución
SET AUTOTRACE TRACE EXPLAIN

-- Comprobamos el plan de ejecución de la siguiente consulta
-- Comprobamos que el plan consulta todas las tablas relacionadas
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  AND    s.prod_id = 13
  GROUP BY t.calendar_year, p.prod_id;

-- Habilitamos Query Rewrite para la MVIEW SALES_MV
ALTER MATERIALIZED VIEW SALES_MV ENABLE QUERY REWRITE;

-- Volvemos a ejecutar la misma consulta de antes para ver si cambia el plan
-- La siguiente operación "MAT_VIEW REWRITE ACCESS FULL" sobre SALES_MV nos confirma que es correcto
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  AND    s.prod_id = 13
  GROUP BY t.calendar_year, p.prod_id;

6. Podemos ver las capacidades de la MVIEW realizando el siguiente procedimiento. Esto es útil para ver en qué determinadas situaciones la MVIEW realiza un FAST REFRESH o no.

-- Ejecutamos la creación de la tabla MV_CAPABILITIES_TABLE donde sale el informe
@?/rdbms/admin/utlxmv.sql

-- Lanzamos el análisis de la MVIEW
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SALES_MV');

-- Consultamos el informe
-- El informe nos dice que necesitamos para que haya FAST REFRESH
-- con cualquier DML (REFRESH_FAST_AFTER_ONETAB_DML):
--   · Tenemos que añadir COUNT(expr) = COUNT(s.amount_sold) a la MVIEW
--   · Tenemos que añadir COUNT(*) a la MVIEW
-- PCT "Partition Change Tracking" no aplica en varios casos porque TIMES y PRODUCTS no están particionadas
SELECT * FROM MV_CAPABILITIES_TABLE;

-- Recreamos la vista para recrearla con todas las capacidades posibles
DROP MATERIALIZED VIEW SALES_MV;
CREATE MATERIALIZED VIEW sales_mv REFRESH FAST AS
  SELECT COUNT(*), COUNT(s.amount_sold), t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
  FROM   times t, products p, sales s
  WHERE  t.time_id = s.time_id
  AND    p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_id;

-- Volvemos a obtener el informe de capacidades de la MVIEW
--   · Ya se puede hacer REFRESH FAST despues de cualquier tipo de DML
TRUNCATE TABLE MV_CAPABILITIES_TABLE;
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SALES_MV');
SELECT * FROM MV_CAPABILITIES_TABLE;

-- Borramos las MVIEW Y MVIEW LOGS
DROP MATERIALIZED VIEW SALES_MV;
DROP MATERIALIZED VIEW SALES_MV_DAILY;
DROP MATERIALIZED VIEW SALES_MV_CURRENT;
DROP MATERIALIZED VIEW LOG ON TIMES;
DROP MATERIALIZED VIEW LOG ON PRODUCTS;
DROP MATERIALIZED VIEW LOG ON SALES;

7. Hay una serie de directrices generales a la hora de crear vistas materalizadas:

· Hay que intentar que cada vista materializada responda al mayor número de consultas posibles
· Se pueden incluir simultaneamente muchos tipos de agregaciones (SUM, COUNT(x), COUNT(*), AVG, MIN, …)
· Es recomendable agrupar todas los cálculos posibles en una sola MVIEW que tenga las mismas tablas y mismo GROUP BY
· Incluir COUNT(x) cuando se utilice AVG(x) para permitir el refresco incremental
· Incluir COUNT(x) y SUM(x) cuando tengamos VARIANCE(x) o STDDEV(x) en la definición de la MVIEW
· Sacar un informe de las Capabilities nos puede ayudar a ver que nos falta en la definición

8. Un posible ejercicio que nos pueden plantear en el examen es mejorar el rendimiento general que existe en una vista materializada, como la primera que hemos creado. El procedimiento que podríamos seguir es el siguiente:

· Revisar el tipo de refresco que tiene (DBA_MVIEWS columnas REFRESH_METHOD y LAST_REFRESH_TYPE)
· Si LAST_REFRESH_TYPE=COMPLETE entonces no hay refrescos incrementales
· Revisamos la vista DBA_MVIEW_LOGS para ver si existen los MVIEW LOGS apropiados
· Si no existen los MVIEW LOGS los creamos (valorar clausulas PARALLEL y CACHE para mayor rendimiento)
· Lanzamos un refresco manual y comprobamos que ha sido incremental (DBA_MVIEWS.LAST_REFRESH_TYPE = FAST)
· También podemos comprobar si está habilitada la funcionalidad QUERY REWRITE para la MVIEW
· Si DBA_MVIEW.REWRITE_ENABLED = N entonces podemos habilitarlo (ENABLE QUERY REWRITE)
· Una vez que hemos hecho estas comprobaciones, los siguientes pasos ya entran dentro de los ejercicios de «Performance Management» (índices, …)

9. En algunas circunstancias nos interesa tener un conjunto de vistas materializadas que sean consistentes en un momento dado para que los datos que consultemos entre ellas también sean consistentes entre sí. Para ello se utiliza el paquete DBMS_REFRESH, el cual nos permite crear grupos de vistas materializadas.

-- Creamos un par de vistas materializadas
CREATE MATERIALIZED VIEW SALES_G AS
  SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
  FROM SH.SALES;
CREATE MATERIALIZED VIEW PRODUCTS_G AS
  SELECT PROD_ID, PROD_NAME, PROD_DESC
  FROM SH.PRODUCTS;

-- Creamos el grupo de refresco
-- Aunque especificamos la frecuencia de refresco de la MVIEWS, no se tiene en cuenta porque son ON DEMAND
EXEC DBMS_REFRESH.MAKE ('TEST_GROUP','SALES_G,PRODUCTS_G', SYSDATE, 'SYSDATE+1');

-- Lanzamos un refresco manual
EXEC DBMS_REFRESH.REFRESH('TEST_GROUP');

-- Consultamos la fecha del último refresco
SELECT MVIEW_NAME, TO_CHAR(LAST_REFRESH_DATE,'YYYY/MM/DD HH24:MI:SS')
  from DBA_MVIEWS WHERE MVIEW_NAME IN ('SALES_G','PRODUCTS_G');

-- Eliminamos el grupo de refrescos
EXEC DBMS_REFRESH.DESTROY('TEST_GROUP');

-- Borramos las vistas materializadas
DROP MATERIALIZED VIEW SALES_G;
DROP MATERIALIZED VIEW PRODUCTS_G;
Fortunas y adversidades de un DBA Oracle
Linkedin Github

Raúl Ibáñez Peral © CC BY 4.0