Había terminado el upgrade de una base de datos desde Oracle 11.2 hacia Oracle 19.24, cuando se empezaron a presentar demoras en las rutinas de refresco rápido de vistas materializadas.
Lo que antes tomaba menos de 30 minutos ahora demoraba horas y horas.
Se validó que las estadísticas estuvieran actualizadas, que los materialized view logs no fueran inmensos, que todo estuviera indexado y nada; los tiempos seguían sin reducirse.
Ya se hablaba de hacer un downgrade para regresar a Oracle 11.2, pero afortunadamente se logró resolver el problema, y ¡con un solo comando!
¿Quieres saber cuál es? Entonces devora los siguiente párrafos, te puede salvar la vida si pasas por un escenario similar.
El problema
Revisando el código de la aplicación encargada de refrescar las cerca de 400 vistas materializadas, encuentro que para algunas de ellas modifican un parámetro oculto.
DECLARE
. . .
BEGIN
EXECUTE IMMEDIATE
'alter session set "_mv_refresh_use_stats"=FALSE';
. . .
DBMS_MVIEW.REFRESH ( 'INS00.MV_POLICY', 'F' );
. . .
END;
Consulto a los programadores sobre la justificación para fijar _mv_refresh_use_stats=false, pero sus origenes se han perdido.
Investigo en My Oracle Support y encuentro varias notas que recomiendan ejecutar este cambio, con el objetivo de mejorar el desempeño de los refrescos:
| 420040.1 | Materialized View Fast Refresh Is Slow Due to a Poor Execution Plan During the Insert Phase With Hints Such As CARDINALITY, NO_SEMI_JOIN, and HASH_SJ |
| 981948.1 | MSRFWOR module: refresh collection snapshots runs longer than 3 hours |
| 145419.1 | How to Setup and Run Data Collections |
| 1067892.1 | Performance issue - refresh collections snapshot when run for all |
| 1063953.1 | Refresh Collection Snapshots Performance - Managing MLOG$ Tables and Snapshots for Data Collections |
El misterio se ha develado: cuando implementaron la aplicación para el refresco de vistas materializadas se toparon con problemas de desempeño, los cuales resolvieron al fijar el parámetro oculto.
Pero esto era válido una década atrás, cuando usaban Oracle 11.2, ¿lo seguirá siendo ahora que ya se concretó el upgrade a Oracle 19.24?
La solución
INSERT /*+ NOAPPEND */ INTO "INS00"."MV_POLICY"
SELECT /*+ NO_MERGE("JV$")*/
"MAS$4"."ID","MAS$4"."INITDATE","MAS$4"."ENDDATE",
"MAS$4"."POLICYNUM","MAS$4"."PREMIUM","MAS$4"."IDAGENT",
"MAS$2"."IDMOV","MAS$2"."AVISOCOBRANZA",
"MAS$0"."FIRSTNAME","MAS$0"."LASTNAME"
"MAS$4".ROWID,"MAS$1".ROWID,"MAS$2".ROWID, "JV$"."RID$","MAS$0".ROWID
FROM ( SELECT "MAS$"."ROWID" "RID$",
"MAS$".*
FROM "INS00"."MV_POLICY_LAST_MOV" "MAS$" W
HERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$
FROM "INS00"."MLOG$_VM_POLICY_LAST_MOV" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :B_ST3
)
) AS OF SNAPSHOT(:B_SCN) "JV$",
"INS00"."CUSTOMER" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"INS00"."AGENT" AS OF SNAPSHOT(: B_SCN) "MAS$1",
"INS00"."POLICY_MOV" AS OF SNAPSHOT(:B_SCN) "MAS$2",
"INS00"."POLICY" AS OF SNAPSHOT(:B_SCN) "MAS$4"
WHERE "MAS$4"."ID"="JV$"."ID"
AND "MAS$2"."IDMOV"="JV$"."MOV"
AND "MAS$2"."ID"="JV$"."ID"
AND "MAS$4"."IDAGENTE"="MAS$1" ."IDAGENT"(+)
AND "MAS$4"."IDCUSTOMER"="MAS$0"."IDCUSTOMER"(+)My Oracle Support tiene varias notas en las que se indica que el uso del hint HASH_SJ ha estado asociado con problemas de desempeño en el refresco de vistas materializadas:
| 1388637.1 | How To Troubleshoot Materialized View Fast Log Timestamp-based Refresh Performance Issues |
| 733054.1 | Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table |
| 2862145.1 | Refresh Collection Snapshot Is Taking Very Long To Complete - Performance Issue with Large Base Table Row Count |
La nota 2862145.1 es la más explícita en señalarnos el problema y su solución:
DB Optimizer elige cómo se actualizan las vistas materializadas.
En la versión 19c, se descubrió que el optimizador estaba optando por utilizar el hint /*+ HASH_SJ */, lo cual es un problema de rendimiento conocido.
En el pasado, recomendábamos configurar el parámetro de la base de datos _mv_refresh_use_stats en "false", y esta era la configuración correcta para las versiones anteriores de la base de datos.
En 19c, debemos establecer este parámetro en "true" para evitar que DB Optimizer elija utilizar /*+ HASH_SJ */ para actualizar las vistas materializadas.
¡Bingo! Lo que era recomendado en los tiempos de Oracle 11.2 ya no lo es con 19.24, por lo que procedemos a hacer el cambio a nivel de instancia, y solo resta ver qué pasa con los queries.
SQL> ALTER SYSTEM SET "_mv_refresh_use_stats"=TRUE SCOPE=BOTH SID='*';INSERT /*+ NOAPPEND */ INTO "INS00"."MV_POLICY"
SELECT /*+ NO_MERGE("JV$") */
"MAS$4"."ID","MAS$4"."INITDATE","MAS$4"."ENDDATE",
"MAS$4"."POLICYNUM","MAS$4"."PREMIUM","MAS$4"."IDAGENT",
"MAS$2"."IDMOV","MAS$2"."AVISOCOBRANZA",
"MAS$0"."FIRSTNAME","MAS$0"."LASTNAME"
"MAS$4".ROWID,"MAS$1".ROWID,"MAS$2".ROWID, "JV$"."RID$","MAS$0".ROWID
FROM ( SELECT "MAS$"."ROWID" "RID$",
"MAS$".*
FROM "INS00"."MV_POLICY_LAST_MOV" "MAS$"
WHERE ROWID IN (SELECT /*+ CARDINALITY(MAS$ 294) NO_SEMIJOIN */
CHARTOROWID("M AS$"."M_ROW$$") RID$
FROM "INS00"." MLOG$_VM_POLICY_LAST_MOV" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :B_ST3
)
) AS OF SNAPSHOT(:B_SCN) "JV$",
"INS00"."CUSTOMER" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"INS00"."AGENT" AS OF SNAPSHOT(:B_SCN) "MAS$1",
"INS00"."POLICY_MOV" AS OF S NAPSHOT(:B_SCN) "MAS$2",
"INS00"."POLICY" AS OF SNAPSHOT(:B_SCN) "MAS$4"
WHERE "MAS$4"."ID"="JV$"."ID"
AND "MAS$2"."IDMOV"="JV$"."MOV"
AND "MAS$2"."ID"="JV$"."ID"
AND "MAS$4"."CODSISTEMA"="MAS$1"."CODSISORI"(+)
AND "MAS$4"."IDAGENTE"="MAS$1" ."IDAGENT"(+)
AND "MAS$4"."IDCUSTOMER"="MAS$0"."IDCUSTOMER"(+)Luego de varios días de aplicado el cambio de parámetro, los tiempos de ejecución han mejorado y nuevamente es posible refrescar las vistas materializadas en menos de 30 minutos, por lo que podemos dar el caso por cerrado.
Con esta experiencia, si tienes bases de datos con Oracle 19c que usan vistas materializadas, no dudes en fijar _mv_refresh_use_stats=true, luego me lo agradeces …
Una respuesta
Hola Enrique, interesente nota, buen dato. Gracias por la actualización!