lunes, 24 de septiembre de 2007

Problemas en Vistas Materializadas - Parte 2

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.

En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.

Veamos un ejemplo:

SQL_10gR2> CREATE TABLE test1 AS
2 SELECT level id, level*level total
3 FROM dual
4 CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 20 ;

Table created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT test2.id,
2 test2.nom,
3 SUM(test1.total) sum_total
4 FROM test1,
5 test2
6 WHERE test1.id = test2.id(+)
7 GROUP BY test2.id,
8 test2.nom
9 ORDER BY id ;

ID NOM SUM_TOTAL
---------- ---------- ----------
1 nom_1 1
2 nom_2 4
3 nom_3 9
4 nom_4 16
5 nom_5 25
6 nom_6 36
7 nom_7 49
8 nom_8 64
9 nom_9 81
10 nom_10 100

10 rows selected.

Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (id) ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
2 WITH ROWID, SEQUENCE (id,nom) ;

Materialized view log created.

Procedemos a crear la Vista Materializada:

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;
FROM test1,
*
ERROR at line 15:
ORA-32401: materialized view log on "CDW"."TEST2" does not have new values

Efectivamente como supusimos, tenemos errores en las tablas de Log que creamos.
Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...

SQL_10gR2> DECLARE
2 l_nombre VARCHAR2(100) := 'test_1' ;
3 BEGIN
4 DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
5 NOCOMPRESS
6 LOGGING
7 BUILD IMMEDIATE
8 USING INDEX
9 REFRESH FAST ON DEMAND
10 USING DEFAULT LOCAL ROLLBACK SEGMENT
11 DISABLE QUERY REWRITE
12 AS
13 SELECT test2.id,
14 test2.nom,
15 SUM(test1.total) sum_total,
16 COUNT(*) cnt,
17 COUNT(test1.total) cnt_sum
18 FROM test1,
19 test2
20 WHERE test1.id = test2.id(+)
21 GROUP BY test2.id,
22 test2.nom
23 ORDER BY id') ;
24 END ;
25 /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
2 FROM user_tune_mview
3 WHERE task_name = 'test_1' ;

TASK_NAME ACTION_ID SCRIPT_TYPE STATEMENT
--------------- ---------- -------------- -----------------------------------
test_1 1 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
N "CDW"."TEST2" ADD ROWID, SEQUENCE
("ID","NOM") INCLUDING NEW VALUES

test_1 2 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
N "CDW"."TEST1" ADD ROWID, SEQUENCE
("ID","TOTAL") INCLUDING NEW VALU
ES


test_1 3 IMPLEMENTATION CREATE MATERIALIZED VIEW CDW.TEST_M
V NOCOMPRESS
LOGGING
BUILD IMMEDIATE
USING INDEX
REFRESH FAST WITH ROWID DISABLE QU
ERY REWRITE AS SELECT test2.id,
test2.nom,
SUM(test1.total) sum_total,
COUNT(*) cnt,
COUNT(test1.total) cnt_sum
FROM test1,
test2
WHERE test1.id = test2.id(+)
GROUP BY test2.id,
test2.nom
ORDER BY id

test_1 4 UNDO DROP MATERIALIZED VIEW CDW.TEST_MV

4 rows selected.

La columna SCRIPT_TYPE nos muestra el tipo de recomendación. Deberíamos ver las columnas con el valor 'IMPLEMENTED' (la recomendación ya está implementada); pero en cambio, estamos viendo que aparecen como 'IMPLEMENTATION' (la recomendación NO está implementada).
El próximo paso es ejecutar los script recomendados...

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST2"
2 ADD ROWID, SEQUENCE("ID","NOM") INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST1"
2 ADD ROWID, SEQUENCE("ID","TOTAL") INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;

Materialized view created.

Ejecutando las recomendaciones de la tabla, pudimos solucionar rápidamente el problema que teníamos en la Vista Materializada.

Qué sucede si ejecutamos el DBMS_ADVISOR.TUNE_MVIEW nuevamente?

SQL_10gR2> DECLARE
2 l_nombre VARCHAR2(100) := 'test_1' ;
3 BEGIN
4 DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
5 NOCOMPRESS
6 LOGGING
7 BUILD IMMEDIATE
8 USING INDEX
9 REFRESH FAST ON DEMAND
10 USING DEFAULT LOCAL ROLLBACK SEGMENT
11 DISABLE QUERY REWRITE
12 AS
13 SELECT test2.id,
14 test2.nom,
15 SUM(test1.total) sum_total,
16 COUNT(*) cnt,
17 COUNT(test1.total) cnt_sum
18 FROM test1,
19 test2
20 WHERE test1.id = test2.id(+)
21 GROUP BY test2.id,
22 test2.nom
23 ORDER BY id') ;
24 END ;
25 /
DECLARE
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 4

[TEST@TEST_10GR2 ~]$ oerr qsm 03116
03116, 00000, "The materialized view is already optimal and cannot be tuned any further"
// *Cause: The materialized view has the capabilities that are specified in
// the statement.
// *Action: none


Oracle nos está diciendo que el script de creación de la Vista Materializada que nosotros ejecutamos, ya cuenta con todas las capacidades necesarias para su creación, y por lo tanto, ya se encuentra óptima y no se puede seguir tuneando.

13 comentarios:

Anónimo dijo...

Hola
Estoy conociendo este mundo de vistas materializadas y tengo una consulta con la que espero me puedas ayudar.
Si en un esquema existen varias vistas materializadas, complejas, y que tienen dependencia entre ellas, existe algún método para controlar la secuencia de refresh de ellas?
Me explico, Si tengo las vistas A, B y C, y esta última está creada con una query que llama a A y B. Entonces cuando se realiza el refresh de "C", necesito obligatoriamente que se haya realizado correctamente el refresh de "A" y "B". Cómo puedo controlar esta integridad?

Ojalá me puedas ayudar. El DBA de mi compañía dice que debo calcular el tiempo que tomará el refresh de "A" y "B", y a partir de eso estimar la hora para programar el refresh de "C". Yo creo que debe haber un método más inteligente para esto.

Saludos.

Leonardo Horikian dijo...

Hola Marcela, una de las opciones que tenes para éste caso es crear un procedimiento.

Por ejemplo:

CREATE OR REPLACE PROCEDURE pr_refresh_vm
IS
BEGIN

DBMS_MVIEW.REFRESH('A') ;

DBMS_MVIEW.REFRESH('B') ;

DBMS_MVIEW.REFRESH('C') ;

EXCEPTION
WHEN OTHERS THEN
RAISE ;
END pr_refresh_vm ;
/

Con éste procedimiento tenes la seguridad que de la vista materializada C se va a refrescar si las dos anteriores se refrescaron correctamente.

Saludos.

Anónimo dijo...

A veces las cosas más obvias nos son invisibles!

Muchas gracias.

PapoAnaya dijo...

'chacho, me salvastes el pellejo, yo me 'tao rascandome la cabeza tratando de resolver el problema con las vistas materializadas. :)

Anónimo dijo...

Hola.
Leonardo, estoy iniciandome en el mundo de ORACLE, que aunque siemppre me ha gustado bastante, hasta ahora tengo la oportunidad e ponerlo en practica en mi trabajo, ya que estoy formando parte del grupo del Data WareHouse. Me gusta mucho la forma en que explicas tus argumentos y quisiera saber que link me podes recomendar para aprender aun mas sobre todo lo que tiene que ver con Oracle.


saludos,


Andres E.

Leonardo Horikian dijo...

Hola Andres E., podrías comenzar leyendo los documentos de tu interes entrando al OTN (http://www.oracle.com/technology/documentation/index.html).

Saludos

Unknown dijo...

Hola amigo,
estoy viendo eso de las vistas materializadas, yo utilizo el sql_developer 1.5.4 con oracle10g. Estoy creando mi vista materializada con varios joins, cree mi vista materializada asi no mas pero no me actualizaba, de alli busque informacion y segun entendi primero debo crear los logs para cada tabla que utiliza mi vista,lo hice, creo mi vista materializada y no permite nro 1 cambiar al refresco on commit con fast, porque yo quiero que apenas actualize, inserte o borre algun registro en algunas de las tablas a las que llamo mi vistamat, y nada ahorita le e dejado con FAST ON DEMAND y inserto algo y no me actualiza y no entiendo porque??, me gustaria saber porque y como se resolveria, ademas cree un procedimiento para llamar su refresh y me sale error al ejecutarlo :S . ayuda por favor

Leonardo Horikian dijo...

Hola Laly,

Para poder ayudarte necesito que me muestres las sentencias que utilizaste para crear la vista materializada y las tablas de logs. También necesito que me digas los errores que te están apareciendo.

Saludos

Toni dijo...

Hola,

Veo que usas un ORDER BY en la creación de la vista materializada, pero al refrescar creo que no aplica el ORDER BY
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref543

Sabes si hay alguna manera de conseguir que la vista materializada tenga en cuenta el Order By a la hora de refrescarse?.

Muchas gracias,
Toni Ramis.

Leonardo Horikian dijo...

Hola Toni,

Con respecto al ORDER BY, las vistas materializadas se comportan de la misma manera que las tablas.
En las tablas, Oracle no garantiza de que si consultas la tabla, los registros se van a devolver de manera ordenada. La única manera de ordenar los registros y garantizar el que se retornen ordenados, es utilizando explicitamente el ORDER BY al consultar la tabla. Esto tambien aplica para las vistas materializadas.
Oracle tiene en cuenta el ORDER BY sólo al crear la vista materializada para poder agrupar físicamente los datos (cluster) que se desean almacenar.

Para que los datos te retornen ordenados, puedes utilizar el ORDER BY al consultar la vista materializada o crear una vista normal incluyendo el ORDER BY en la consulta.

Saludos

Anónimo dijo...

Hola Leonardo,
Estoy intentando generar una vista materializada entre dos tablas unidas por un join del id de mi tabla A con una foreing key FK_A en la tabla B (where A.ID_A=b.FK_A).

Cuando utilizo DBMS_MVIEW.EXPLAIN_MVIEW sobre mi select siempre me devuelve en
REFRESH_FAST_AFTER_INSERT
"la lista SELECT no tiene los rowid de todas las tablas de detalles".
He probado a meter todos los campos de todas las tablas,a hacerlos coincidir con los definidos en los LOGS,pero siempre me devuelve lo mismo.
Tambien he intentado utilizar DBMS_ADVISOR.TUNE_MVIEW a ver si me aclara algo pero me devuelve un mensaje del tipo :
ORA-13600: se ha encontrado un error en el asesor
ORA-13635: El valor proporcionado para el parámetro ADJUSTED_SCALEUP_GREEN_THRESH no se puede convertir en un número.
ORA-06512: en "SYS.PRVT_ADVISOR", línea 3902
ORA-06512: en "SYS.DBMS_ADVISOR", línea 102
ORA-06512: en "SYS.PRVT_TUNE_MVIEW", línea 586
ORA-06512: en "SYS.PRVT_TUNE_MVIEW", línea 1061
ORA-06512: en "SYS.DBMS_ADVISOR", línea 754
ORA-06512: en línea 4


Agradezco cualquier ayuda por tu parte.
Muchas gracias por adelantado.

Leonardo Horikian dijo...

Hola Anónimo,

Ese error te esta indicando que los ROWID's de todas las tablas que se encuentran en el FROM deben aparecer en el SELECT de la consulta.

Ej:

SELECT a.id,
a.rowid ra,
b.rowid rb
FROM tabla_1 a,
tabla_2 b
WHERE a.id = b.id;

Saludos

M.@. dijo...

yo tengo también un error cuando intento crearla bajo on commit. Creo los logs para las estadísticas de ambas tablas pero al ejecutar la vista materializada me dice que no se puede definir el atributo de refrescamiento ON COOMIT para la vista materializada.

CREATE MATERIALIZED VIEW LOG ON TABLA_A
WITH PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON TABLA_B
WITH PRIMARY KEY
INCLUDING NEW VALUES;



CREATE MATERIALIZED VIEW Materiali
BUILD IMMEDIATE
REFRESH FAST ON commit
ENABLE QUERY REWRITE AS
select distinct * from Tabla_A A,
TABLA_B B
WHERE A.ACTIVO = 'N'
and B.PK = B.PK
and U.compoC in (select distinct campoC from TABLAC where campoY = B.campoY)

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.