Uno de mis Clientes me comunica que ha detectado unos materialized view logs con decenas de millones de filas, y me solicita ayudarles a investigar el caso, así que ni corto ni perezoso pongo manos a la obra.
No te imaginas todos los «trucos» a los que tuve que recurrir, pero si usas vistas materializadas es posible que estés pasando por un problema similar y ni te has enterado, así que acompáñame en esta nueva aventura!
Antecedentes
La empresa ACME hace un uso intensivo de vistas materializadas, y ha identificado que en la base de datos ZEUS_DB el materialized view log de la tabla CUSTOMER tiene casi 27 millones de filas, y el de la tabla POLICY está cerca de 237 millones!
Según sus registros, estas tablas son referenciadas por un par de vistas materializadas, una en la base de datos ARES_DB y la otra en HERA_DB; se refrescan cada hora con modalidad FAST, por lo que no se justifica un número tan grande de filas en los materialized view logs. Será algún bug?
Un poco de teoría
Para soportar un refresco en modalidad FAST, es requisito crear un log de vista materializada en cada una de las tablas a usar en la vista materializada. Estos logs se registran en la tabla mlog$.
Cuando se crea la vista materializada, Oracle la registra localmente en la tabla snap$, y también remotamente en la tabla reg_snap$.
Solo cuando el refresco de la vista materializadas es del tipo FAST, Oracle registra remotamente la dependencia de la vista materializada con el log de vista materializada, en la tabla slog$.
El diagnóstico
Casos como este usualmente se presentan cuando en la tabla slog$ existen registros para vistas materializadas que han dejado de refrescarse o que sencillamente ya no existen.
Como Oracle encuentra estas filas, tiene que mantener los contenidos de los logs de vista materializada para el momento en el que se solicite un refresco en modalidad FAST, momento que quizás no llegue nunca.
Para identificar la raíz del problema, nos apoyaremos en los resultados obtenidos con el siguiente query:
SELECT m.mowner, m.master, m.log,
LEAST(m.oldest,m.oldest_pk) oldest,
s.snapid, s.snaptime,
NVL(r.sowner, '<<NOT') sowner,
NVL(r.snapname, 'REGISTERED') snapname,
NVL(r.snapsite, 'MVIEW>>') snapsite
FROM sys.slog$ s,
sys.reg_snap$ r,
sys.mlog$ m
WHERE s.snapid = r.snapshot_id (+)
AND s.mowner = m.mowner
AND s.master = m.master
AND LEAST(m.oldest,m.oldest_pk) < SYSDATE - 120
ORDER BY m.mowner, m.master, s.snapid;
MOWNER MASTER LOG OLDEST SNAPID SNAPTIME SOWNER SNAPNAME SNAPSITE
------- -------- -------------- ---------- ------ ---------- ------ ----------- --------
UMASTER CUSTOMER MLOG$_CUSTOMER 2018-11-19 3088 2018-11-19 UMVIEW MV_CUSTOMER HERA_DB
UMASTER CUSTOMER MLOG$_CUSTOMER 2018-11-19 3090 2025-07-07 UMVIEW MV_CUSTOMER ARES_DB
UMASTER POLICY MLOG$_POLICY 2023-04-20 4506 2023-04-20 <<NOT REGISTERED MVIEW>>
UMASTER POLICY MLOG$_POLICY 2023-04-20 9928 2025-07-07 UMVIEW MV_POLICY HERA_DBSolución para CUSTOMER
Para este primer caso tenemos plenamente identificada a la vista materializada que ya no existe, con SNAPID=3088, y el log de vista materializada tiene casi 27 millones de filas, que si bien es elevado aún es manejable.
Este es el escenario más común, por lo que es bastante simple de resolver siguiendo estos pasos:
Verificar situación inicial
Tomamos nota de la cantidad de filas que contiene el log de vista materializada.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_customer;
N_ROWS
------------
26,942,3911
2
Borrar las filas innecesarias
Recurrimos al procedimiento PURGE_MVIEW_FROM_LOG del package DBMS_MVIEW, el cual requiere de los datos que identifican a la vista materializada que ya no existe.
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'UMVIEW',
mviewname => 'MV_CUSTOMER',
mviewsite => 'HERA_DB' );
END;
/Verificar
Comprobamos que las filas en exceso del log de vista materializada ya no estén.
Las que quedan son las necesarias para el refresco de las vistas materializadas que sí existen.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_customer;
N_ROWS
------------
14,4543
4
Eliminar referencia a vista materializada
Ya hemos eliminado las filas en el log de vista materializada, y no se volverán a acumular, pero para tener todo en orden, debemos ejecutar un paso adicional: eliminar el registro de la vista materializada que ya no existe.
Usaremos el procedimiento UNREGISTER_MVIEW del package DBMS_MVIEW, el cual requiere de los datos que identifican a la vista materializada que ya no existe.
BEGIN
DBMS_MVIEW.UNREGISTER_MVIEW (
mviewowner => 'UMVIEW',
mviewname => 'MV_CUSTOMER',
mviewsite => 'HERA_DB' );
END;
/
Verificar
Consultamos en la tabla slog$ y observamos que ya no quedan referencias al SNAPID=3088.
SELECT snapid, snaptime
FROM sys.slog$
WHERE mowner = 'UMASTER'
AND master = 'CUSTOMER'
ORDER BY snapid;
SNAPID SNAPTIME
------ -------------------
3090 2025-07-07 20:19:495
Solución para POLICY
Para este caso lo único que sabemos es que SNAPID=4506 para la vista materializada que ya no existe, y el log de vista materializada tiene casi 237 millones de filas, lo cual ya no es manejable.
Este es el escenario más complejo, y para poderlo resolver tendremos que seguir un procedimiento algo más largo y que requiere de planificación para su ejecución:
Verificar situación inicial
Tomamos nota de la cantidad de filas que contiene el log de vista materializada.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_policy;
N_ROWS
------------
236,998,042
1
2
Bloquear la tabla maestra
Acá empieza la parte complicada.
Si bien el primer paso debería ser borrar las filas innecesarias del log de vista materializada, ya que tenemos cientos de millones de filas, esto puede demorar demasiado o incluso abortar por diversos problemas.
Lo que haremos será truncar el log de vista materializada, acción que es inmediata pero que requiere de planificación, pues puede ocasionar que las vistas materializadas ya no puedan refrescar en modalidad FAST, teniendo que recurrir a un refresco COMPLETE, algo que deseamos evitar.
Luego, como primer paso bloquearemos la tabla para impedir que se generen nuevas filas en el log de vista materializada. Como esto puede afectar a las aplicaciones, se debe ejecutar en un momento previamente coordinado y autorizado.
SESS_1> LOCK TABLE umaster.policy
IN EXCLUSIVE MODE;
Table(s) Locked.Esta sesión la debemos dejar intacta, ya que es la encargada de mantener la tabla maestra bloqueada.
Refrescar las vistas materializadas
Desde otra sesión refrescamos las vistas materializadas existentes, con modalidad FAST.
BEGIN
DBMS_MVIEW.REFRESH (
list => 'UMVIEW.MV_POLICY',
method => 'F' );
END;
/3
4
Truncar el log de vista materializada
Con la seguridad de que todas las vistas materializadas han sido refrescadas, sabemos que el log de vista materializada solo contiene filas asociadas a la vista materializada que ya no existe, por lo que podemos truncarla sin problemas.
Esto debemos hacerlo desde una sesión distinta a la que tiene bloqueada a la tabla maestra.
SESS_2> TRUNCATE TABLE umaster.mlog$_policy;
Table truncated.Borrar las filas innecesarias
Recurrimos al procedimiento PURGE_MVIEW_FROM_LOG del package DBMS_MVIEW, pero para identificar la vista materializada que ya no existe, usaremos en este caso su SNAPID, ya que no contamos con más datos.
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mview_id => 4506 );
END;
/5
6
Liberar bloqueo de la tabla maestra
Liberamos el bloqueo de la tabla maestra desde la misma sesión que usamos en el paso 2.
SESS_1> ROLLBACK;
Rollback complete.Verificar
Para estar seguros que no se han afectado las vistas materializadas existentes, ejecutamos un refresco en modalidad FAST.
BEGIN
DBMS_MVIEW.REFRESH (
list => 'UMVIEW.MV_POLICY',
method => 'F' );
END;
/7
Conclusiones
Es posible que algunas vistas materializadas de refresco FAST sean eliminadas o abandonadas, por lo que es conveniente que verifiques rutinariamente la cantidad de filas de los logs de vista materializada, así como las fechas de actualización de las vistas materializadas.
Si lo haces, evitarás llegar a situaciones tan extremas como las del caso que he presentado para la tabla POLICY, pero si llegas a ello, ya sabes cómo resolverlo de forma segura y eficiente.
Y ya que usas vistas materializadas, dale una leída a mi artículo
¿Cómo refrescar más rápido las vistas materializadas en 19c?, te será de gran ayuda.
Para complementar lo aquí expuesto, te recomiendo la lectura de las notas:
| 1031924.6 | Snapshot/MView Log Grows Due To Missing/Invalid Snapshot |
| 1539298.1 | How to Purge a Large MView Log And Avoid Full Refresh |
2 Responses
Que util, sobretodo para quienes trabajamos con vistas materializadas
Hola Enrique, excelente articulo.