Os paso una serie de notas traducidas del libro “Troubleshooting Oracle Performance” de Christian Antognini, libro muy, muy recomendable
Una vista es una tabla virtual basada en un juego de resultados devuelto por una query especificada en el momento de creación de la vista. Cada vez que se accede a la vista se ejecuta la query. Para evitar que la query se ejecute en cada acceso, el resultado de la query se almacena en una vista materializada. En otras palabras, las vistas materializadas simplemente transforman y duplican los que están almacenados en otro lugar.
Las vistas materializadas también se utilizan en ambientes distribuidos, para repicar los datos entre diferentes bases de datos. Pero esta funcionalidad no la veremos en este post.
Como trabajan las vistas materializadas
Concepto
Partimos de la base del siguiente query:
SELECT p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id
Si revisamos la eficiencia del plan de ejecución podemos observar que todo es correcto. Las estimaciones son excelentes y el numero de de lecturas lógicas por fila devuelta en los diferentes accesos no es muy elevado.
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers|
------------------------------------------------------------------------
| 1 | SORT GROUP BY | | 68 | 81 | 3844 |
|* 2 | HASH JOIN | | 918K| 918K| 3844 |
| 3 | TABLE ACCESS FULL | PRODUCTS | 72 | 72 | 11 |
|* 4 | HASH JOIN | | 918K| 918K| 3833 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 55500 | 1457 |
| 6 | PARTITION RANGE ALL | | 918K| 918K| 2376 |
| 7 | TABLE ACCESS FULL | SALES | 918K| 918K| 2376 |
------------------------------------------------------------------------
2 - access("S"."PROD_ID"="P"."PROD_ID")
4 - access("S"."CUST_ID"="C"."CUST_ID")
El problema es que se procesan muchos datos antes de la agregación tenga lugar. Las prestaciones no se pueden mejorar cambiando a manera de acceso a los datos o el método de join, ya que es el más optimo posible. La única manera de mejorarlo es crear una vista materializada basada en el query a optimizar.
La vista materializada se crea con la sentencia CREATE MATERIAKIZED VIEW. En los casos más simples se tiene que especificar el nombre y la query en la cual se base la vista materializada. Fíjate que las tablas en las cuales la vista materializada se basa se llaman tablas base o tablas maestras. En esta creación no se va a utilizar la clausula ORDER BY, que se utilizaba en la select previa:
CREATE MATERIALIZED VIEW sales_mv AS SELECT p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id
Cuando se crea una vista materializada basada en una query con la clausula ORDER BY, las filas ordena de acuerdo a la clausula ORDER BY, en el momento de creación de la vista. Más tarde, en el momento de refrescarla, los criterios de ordenación no se mantienen. Esta es la razón por la cual la sentencia ORDER BY no se incluye en la definición que se almacena en el diccionario de datos.
Cuando se ejecuta el SQL previo, el motor de la base de datos crea la vista materializada (lo cual es un simple objeto en diccionario de datos, en otras palabras en simplemente metadatos) y la tabla contenedora. La tabla contenedora es una tabla “normal” con el mismo nombre que la vista materializada. Y se utiliza para almacenar los resultados de la query.
Puedes consultar la tabla contenedora como cualquier otra tabla. El siguiente SQL muestra un ejemplo de ello. Fíjate que el número de lecturas lógicas, comparado con la query original, ha disminuido de 3,844 a 3. También fíjate que el path de acceso MAT_VIEW ACCES FULL claramente indica que la vista materializada ha sido accedida. Este tipo de acceso existe desde Oracle 10. En versiones previas aparece registrado como TABLE ACCESS FULL, a pesar de que accede a través de la vista materializada. En realidad el acceso es igual.
SELECT * FROM sales_mv ORDER BY prod_category, country_id ---------------------------------------------------------------------- | Id | Operation | Name | E-Rows | A-Rows | Buffers | ---------------------------------------------------------------------- | 1 | SORT ORDER BY | | 81 | 81 | 3 | | 2 | MAT_VIEW ACCESS FULL | SALES_MV | 81 | 81 | 3 |
Dirigirse directamente a la tabla contenedora es una opción pero si lo que se quiere es mejorar el rendimiento, sin modificar el código, existe una segunda opción: la utilización de query rewrite. Cuando el optimizador de queries recibe un query para optimizarlo, puede decidir utilizarlo como está (en otras palabras no usar el query rewrite), o puede elegir reescribirlo, para utilizar la vista materializada que contiene todo o parte de los datos que se requieren en la ejecución de query. La decisión se basa en la estimación de costes. El hint rewrite y no_rewrite están disponibles para influir en las decisiones del optimizador.
Para utilizar el query rewrite, se deben activar dos niveles. Primero inicializar el parámetro query_rewrite_enable a True y segundo activarlo en la vista materializada.
ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE
Una vez que el query rewrite esta activado, si se ejecuta la select original, el optimizador considera que la vista materializada es una candidata para el query rewrite, en este caso, el optimizador de querys reescribe la query para utilizar vistas materializadas.
SELECT p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id ------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |A-Rows | Buffers | ------------------------------------------------------------------------------ | 1 | SORT ORDER BY | | 81 | 81 | 3 | | 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 81 | 81 | 3 | ------------------------------------------------------------------------------
Parámetros de las vistas:
- Se pueden especificar propiedades físicas como particionamiento, tablespace, y parámetros de almacenamiento, para la tabla contenedora, en este aspecto la tabla contenedora se trata como cualquier otra tabla.
- Cuando se crea la vista materializada, la query se ejecuta y el resultado se inserta en la tabla contenedora. Esto es porque el parámetro build immediate es utiliza por defecto, Existen dos posibilidades adicionales, la primera diferir la inserción de las filas al primer refresco, para esto su utiliza el parámetro deferred, y la segunda es reutilizar una tabla existente como contenedora para esto se especifica el parámetro prebuilt table.
- Por defecto el query rewrite está deshabilitado, para habilitarlo es necesario especificar el parámetro enable query rewrite.
- Para mejorar las prestaciones de refresco rápido un índice se crea en la tabla por defecto, para suprimir la creación del índice se puede utilizar el parámetro using no index. Esto último es útil, por ejemplo para evitar el exceso de mantenimiento en los índices, si nunca vas a utilizar refresco rápido.
Un ejemplo de los parámetros anteriores:
CREATE MATERIALIZED VIEW sales_mv PARTITION BY HASH (country_id) PARTITIONS 8 TABLESPACE users BUILD IMMEDIATE USING NO INDEX ENABLE QUERY REWRITE AS SELECT p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id
Refresco
Cuando se modifica una tabla, la vista materializada dependiente se queda obsoleta Si embargo gracias al refresco se mantiene la vista materializada actualizada. Cuando se crea la vista materializada se puede especificar cómo y cuándo tendrá lugar el refresco. Los parámetros para determiner ésto son:
- REFRESH COMPLETE: Todo el contenido de la tabla contenedora se borra y todos los datos se recargan desde las tablas base. Solo se debe utilizar este método cuando una parte considerable de la tabla base ha sido modificada.
- REFRESH FAST: El contenido de la tabla contenedora se reutiliza y solamente las modificaciones son propagadas a la tabla contenedora, si solamente se modifica una pequeña porción de los datos, este es el método que se debe usar. Este método solo está disponible si se cumplen toda una serie de requisitos. Si uno de ellos nos e cumple, la creación da error.
- REFRESH FORCE: Se intenta en un primer momento con FAST REFRESH y si no se puede se hace un REFRESH. Este es el método por defecto.
- NEVER REFRESH: La vista materializada nunca se refresca. Si se intenta refrescar parecerá el error ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view. Se debe utilizar este método cuando te quieres asegurar que nunca se va a refrescar.
Además se puede elegir el momento en el tiempo en que se va a realizar el refresco, existen dos opciones diferentes:
- ON DEMAND: La vista materializada se actualiza cuando explícitamente se le pide (ya sea manualmente o ejecutando un job en intervalos regulares). Esto significa que la vista materializada puede contener datos obsoletos durante un lapso de tiempo desde la modificación en las tablas hasta el refresco de la vista.
- ON COMMIT: La vista materializada se refresca automáticamente en la misma transacción que modifica la tabla(s) bases. En otras palabras siempre contiene los últimos datos
Se tiene que combinar estas opciones para determinar cómo y cuando la vista materializada se refrescará y se deben utilizar junto a las sentencias: CREATE MATERIALIZED VIEW y ALTER MATERIALIZED VIEW. He aquí un ejemplo:
ALTER MATERIALIZED VIEW sales_mv REFRESH FORCE ON DEMAND
A pesar de que es posible crear una vista materializada con las opciones REFRESH COMPLETE ON COMMIT, está combinación no tiene mucho sentido en la practica
Para ver los parámetros asociados a una vista materializada, , cuando y como se produce el refresco puedes consultar la siguiente vista user_mviews:
SQL> SELECT refresh_method, refresh_mode, staleness, 2 last_refresh_type, last_refresh_date 3 FROM user_mviews 4 WHERE mview_name = 'SALES_MV'; REFRESH_METHOD REFRESH_MODE STALENESS LAST_REFRESH_TYPE LAST_REFRESH_DATE -------------- ------------ --------- ----------------- ------------------- FORCE DEMAND FRESH COMPLETE 02.04.2008 20:38:31
Cuando se elige el refresco manual, se puede utilizar uno de los siguientes procedimientos en el paquete dbms_mview:
- refresh: Este procedimiento refresca la vista materilizada especificada:
dbms_mview.refresh(list => ‘sh.sales_mv,sh.cal_month_sales_mv’)
- refresh_all_mviews: Este procedimiento refresca todas las vistas almacenadas en la base de datos excepto aquellas que se marcan para que no se refresquen nunca. El parámetro de salida number_of_failures regresa el número de fallos ocurridos durante el proceso.
dbms_mview.refresh_all_mviews(number_of_failures => :r)
- refresh_dependent: Este procedimiento refrescan las vistas materializadas que dependen de tablas base que se pasan como parámetro de entrada list, el parámetro de salida number_of_failures regresa el numero de fallos que han ocurrido durante el proceso
dbms_mview.refresh_dependent(number_of_failures => :r, list => ‘sh.sales’)
Todos esto procedimientos también aceptan los parámetros metoh y atomic_refresh. El primero indica como se hace el refresco (‘c’ completo, ‘f’ fast y ‘?’ para force) En el segundo si se pone a FALSE, cuando se hace un refresco completo en vez de borrar la tabla la trunca, es más rápido pero puede provocar errores si una sesión consulta la tabla cuando se está haciendo el refresco no obtendrá resultados.
En caso de que se quiera in refresco automático a demanda en las sentencias CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW, se puede especificar el momento de primer refresco (START WITH) y la expresión que se encarga de los refrescos posteriores (NEXT)
ALTER MATERIALIZED VIEW sales_mv REFRESH COMPLETE ON DEMAND
START WITH sysdate NEXT sysdate+to_dsinterval('0 00:10:00')
Para realizar el refresco se crea un job, que se encarga del trabajo.
En caso de Oracle 9 este job no funciona, aparece el error: 12:54:38 Info: Job #80 could not be executed. ORA-12011: execution of 1 jobs failed ORA-06512: en “SYS.DBMS_IJOB”, línea 406ORA-06512: en “SYS.DBMS_JOB”, línea 272, cada vez que el job se ejecuta. Es necesario cambiar dentro del job el paquete que llama para el refresco, se debe reemplazar el paquete dbms_refresh.refresh por dbms_mview.refresh
El paquete dbms_refresh se utilice para manejar grupos de refresco, Un grupo de refresco es simplemente a colección de una o más vistas materializadas, Los refrescos ejecutados con el procedimiento refresh del paquete se realizan como una sola transacción (atomic_refresh is set to TRUE). Este comportamiento es necesario para garantizar la consistencia entre las distintas vistas materializadas. Esto implica que o todas las vistas materializadas en el grupo se refrescan o se da marcha atrás a todo el procedimiento.
Fast Refreshes con Materialized View Logs
En un fast refresh, el contenido de la tabla contenedora es reutilizado y solo se propagan las modificaciones de la tabla base a la tabla contenedora. Naturalmente, el motor de la base de datos es capaz de propagar solo las modificaciones que conoce. Para este fin, se tiene que crear un materialized view log para cada tabla base.
Algunos ejemplos de creación de materializad view logs, La clausula WITH ROWID se añade para especificar como identificar los registros en dentro del materilizaed view log. Se puede crear identificando los registros por la llave primaria.
SQL> CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
SQL> CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
SQL> CREATE MATERIALIZED VIEW LOG ON products WITH ROWID;
Query para comprobar las vistas materializadas.
SQL> SELECT master, log_table
2 FROM user_mview_logs
3 WHERE master IN ('SALES', 'CUSTOMERS', 'PRODUCTS');
MASTER LOG_TABLE
---------- ----------------
CUSTOMERS MLOG$_CUSTOMERS
PRODUCTS MLOG$_PRODUCTS
SALES MLOG$_SALES
Para la mayoría de las vistas materializadas este tipo de log no es suficiente para soportar el fast refreshes. Se deben cumplimetar otros requisitos. La manera de obtenerlos es mediante el procedimiento dbms_mview. Pongo el ejemplo:
SQL> execute dbms_mview.explain_mview(mv => 'sales_mv', stmt_id => '42')
La salida del procedimiento se almacena en la vista mv_capabilities_table. Para determinar que necesita la vista materializada para ser fast refreshed se pude utilizar un query como el siguiente:
SQL>set linesize 220 SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name , possible, SUBSTR(related_text,1,10)AS related_text, SUBSTR(msgtxt,1,90)AS msgtxt F FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq; CAPABILITY_NAME POSSIBLE MSGTXT RELATED_TEXT -------------------------- -------- ------------------------------- ------------- REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.PRODUCTS REFRESH_FAST_AFTER_INSERT N mv log does not have all SH.PRODUCTS necessary columns REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.CUSTOMERS REFRESH_FAST_AFTER_INSERT N mv log does not have all SH.CUSTOMERS necessary columns REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.SALES REFRESH_FAST_AFTER_INSERT N mv log does not have all SH.SALES necessary columns REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) AMOUNT_SOLD REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) QUANTITY_SOLD REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST _AFTER_INSERT is disabled REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.PRODUCTS REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.CUSTOMERS REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.SALES REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST _AFTER_ONETAB_DML is disabled
Para resolver los problemas hay que añadir algunas opciones en la creación de MATERIALIZED VIEW LOG Para el problema mv log does not have all necessary columns, se tiene que referencias todas las columnas referenciadas en la vista materializada para que se almacenen en the materialized view log. Para el problema mv log must have new values, se tiene que añadir la clausula INCLUDING NEW VALUES clause. Con está opción el materialized view logs almacena tanto los valores antiguos como los nuevos. Para el problema mv log does not have sequence, es necesario añadir la clausula SEQUENCE. Con esta opción, un numero secuencial es asociado a cada registro almacenado en el materialized view log. Algunos ejemplos:
SQL> CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE 2 (cust_id, prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; SQL> CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID, SEQUENCE 2 (cust_id, country_id) INCLUDING NEW VALUES; SQL> CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE 2 (prod_id, prod_category) INCLUDING NEW VALUES;
Cuándo utilizarlas:
Las vistas materializadas son estructuras de acceso redundantes, son útiles para acceder a los datos eficientemente, pero implican más trabajo para mantenerlas al día. Los momentos en los que usarlas pueden ser:
- Parar mejorar el rendimiento en agregaciones grandes y/o joing con un ratio muy alto entre el número de lecturas lógicas y número de registros recuperados.
- Para mejorar el rendimiento en acceso a una sola tabla, que por sus características necesitaría particionamiento pero que por cualquier razón no es fácil implantarlo.
Problemas con los que me he encontrado:
- Al utilizar dbms_mview.explain_mview, para analizar la vista materializada, me he encontrado con el error: ORA-30377
Ejecutar en el esquema @?/rdbms/admin/utlxmv.sql