10 Replies Latest reply: Feb 2, 2010 9:53 PM by Hemant K Chitale RSS

    Refreshing Materialized view create a lot of logs file..

    750260
      Hi,

      I'm facing a problem refreshing materialized view in 10.2.0.4... when executing the complete refresh every night, Oracle create a lot of logs file that make our dba like crazy... I have to mention that we have spatial index on data and that if I eliminate this spatial index the problem go away... We have tried a lot of thing like putting nologging parameter in the spatial index creation, in the materialized view creation or in the tablespace but nothing is working...

      How can we get rid of this log files!?

      Thank a lot

      Etienne
        • 1. Re: Refreshing Materialized view create a lot of logs file..
          Robert Geier
          Why are you running a complete refresh instead of a fast refresh ?

          How big is the MVIEW and index ? How much log is created when you refresh ?

          Can you show the mveiw create script ?

          Edited by: Robert Geier on Feb 1, 2010 3:50 PM
          • 2. Re: Refreshing Materialized view create a lot of logs file..
            750260
            Because it seem that we can't use the fast refresh when you have an sdo_geometry column and a spatial index...
            • 3. Re: Refreshing Materialized view create a lot of logs file..
              Robert Geier
              Can you show the mveiw create script ?
              • 4. Re: Refreshing Materialized view create a lot of logs file..
                750260
                This is the script

                CREATE MATERIALIZED VIEW "DGM"."MV_REGS_SUELTA_UEA_SITUACION"
                ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                TABLESPACE "TS_DATA"
                BUILD IMMEDIATE
                USING INDEX
                REFRESH FORCE ON DEMAND
                USING DEFAULT LOCAL ROLLBACK SEGMENT
                DISABLE QUERY REWRITE
                AS SELECT
                un.ID_UNIDAD,
                un.NOMBRE,
                un.ID_TIPO_EXPEDIENTE,
                te.DESCRIPCION TIPO_EXPEDIENTE,
                un.ID_SITUACION_EXPEDIENTE,
                se.DESCRIPCION SITUACION_EXPEDIENTE,
                un.ID_CLASE_SUSTANCIA,
                cs.DESCRIPCION CLASE_SUSTANCIA,
                un.HECTAREAS_DISPONIBLES,
                un.HECTAREAS_OTORGADAS,
                un.FEC_FORMULACION,
                un.FEC_CONSENTIDA,
                un.FEC_RESOLUCION,
                es.ID_SITUACIONUP,
                es.ANOPRO,
                es.MES,
                NVL (su.DESCRIPCION, 'SIN DECLARACIÓN') SITUACIONUP,
                TI.ID_CLIENTE,
                TI.ID_INACC_T1 ID_INACC,
                TI.NOMBRE_TITULAR,
                TI.PAGINA_WEB,
                TI.RUC,
                un.GEOMETRY
                FROM
                DGM.MV_REGS_SUELTA_CON_UEA un
                LEFT JOIN --Filtrar unicamente las unidades con ESTAMIN declarada en fecha de hoy
                (SELECT
                eu.ID_UNIDAD,
                eu.ID_CLIENTE,
                eu.ID_SITUACIONUP,
                eu.ANOPRO,
                eu.MES
                FROM
                DGM.T_EMMD_UNIDAD eu
                WHERE
                ((eu.ANOPRO = TO_CHAR (ADD_MONTHS (SYSDATE - 20, -1), 'YYYY')
                AND eu.MES = TO_CHAR (ADD_MONTHS (SYSDATE - 20, -1), 'MM'))
                OR (eu.ANOPRO IS NULL AND eu.MES IS NULL))) es
                ON es.ID_UNIDAD = un.ID_UNIDAD
                LEFT JOIN DGM.T_MAE_SITUACIONUP su ON SU.ID_SITUACIONUP = es.ID_SITUACIONUP
                LEFT JOIN(
                --Todo eso es para asegurarse que tenemos solo una fila (titular) por unidad porque sino vamos a duplicar los poligonos de unidades
                SELECT
                T1.ID_UNIDAD ID_UNIDAD,
                T1.MAX_PORCENTAJE,
                T2.ID_INACC ID_INACC_T1,
                V_GENM_CLIENTE.ID_CLIENTE,
                V_GENM_CLIENTE.ID_INACC,
                V_GENM_CLIENTE.NOMBRE NOMBRE_TITULAR,
                V_GENM_CLIENTE.PAGINA_WEB,
                V_GENM_CLIENTE.RUC
                FROM(
                SELECT ID_UNIDAD, MAX(PORCENTAJE_PARTICIPACION) MAX_PORCENTAJE --Obtener el titular con % mas alto
                FROM DGM.T_REGJ_TITULAR_UNIDAD
                WHERE FLG_ESTADO = '1'
                GROUP BY ID_UNIDAD)T1,
                --Escoger cualquier titular en el caso de varios % eguales
                (SELECT ID_UNIDAD, PORCENTAJE_PARTICIPACION, MIN(ID_INACC || TIPO_PERSONA) ID_INACC FROM DGM.T_REGJ_TITULAR_UNIDAD WHERE FLG_ESTADO = '1' GROUP BY ID_UNIDAD, PORCENTAJE_PARTICIPACION)T2
                --Sacar los clientes duplicados (errores) de la tabla de cliente
                LEFT JOIN (SELECT ID_INACC, TIPO_PERSONA, MIN(ID_CLIENTE) MIN_ID_CLIENTE FROM SIMEM.T_GENM_CLIENTE WHERE FLG_ESTADO = '1' AND ID_INACC IS NOT NULL GROUP BY ID_INACC, TIPO_PERSONA) CLIENTE1 ON SUBSTR(T2.ID_INACC,0,6) = CLIENTE1.ID_INACC AND SUBSTR(T2.ID_INACC,7,1) = CLIENTE1.TIPO_PERSONA
                LEFT JOIN SIMEM.V_GENM_CLIENTE ON CLIENTE1.MIN_ID_CLIENTE = V_GENM_CLIENTE.ID_CLIENTE
                WHERE
                T1.ID_UNIDAD || T1.MAX_PORCENTAJE = T2.ID_UNIDAD || T2.PORCENTAJE_PARTICIPACION--Se Obtene asi el titular con el % mas alto (en caso de % duplicados se escogio cualquier de ellos
                )TI ON TI.ID_UNIDAD = un.ID_UNIDAD
                LEFT JOIN DGM.T_MAE_TIPO_EXPEDIENTE te ON TE.ID_TIPO_EXPEDIENTE = UN.ID_TIPO_EXPEDIENTE
                LEFT JOIN DGM.T_MAE_SITUACION_EXPEDIENTE se ON SE.ID_SITUACION_EXPEDIENTE = un.ID_SITUACION_EXPEDIENTE
                LEFT JOIN DGM.T_MAE_CLASE_SUSTANCIA cs ON CS.ID_CLASE_SUSTANCIA = UN.ID_CLASE_SUSTANCIA
                WHERE
                un.ID_SITUACION_EXPEDIENTE = 'V' AND UN.ID_ESTADO_EXPEDIENTE IN ('T','C')
                AND un.GEOMETRY.GET_GTYPE() <> 4
                ;
                CREATE INDEX DGM.IS_REGS_SUELTA_CON_INTEGRANTE ON DGM.MV_REGS_SUELTA_CON_INTEGRANTE
                NOLOGGING (GEOMETRY)
                INDEXTYPE IS MDSYS.SPATIAL_INDEX
                PARAMETERS ('TABLESPACE=TS_GIS SDO_RTR_PCTFREE=10 INITIAL=64K ')
                NOPARALLEL;

                CREATE INDEX "DGM"."I_REGS_SUELTA_UEA_SITU_CLI" ON "DGM"."MV_REGS_SUELTA_UEA_SITUACION" NOLOGGING ("ID_CLIENTE")
                PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                TABLESPACE "TS_DATA" ;

                CREATE INDEX "DGM"."I_REGS_SUELTA_UEA_SITU_UNI" ON "DGM"."MV_REGS_SUELTA_UEA_SITUACION" NOLOGGING ("ID_UNIDAD")
                PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                TABLESPACE "TS_DATA" ;

                Thanks for your help
                • 5. Re: Refreshing Materialized view create a lot of logs file..
                  530453
                  If you envronment allows ,cann't you drop the index before refresh and re-build after refresh is done.
                  • 6. Re: Refreshing Materialized view create a lot of logs file..
                    Mohamed Houri
                    Dear,

                    What are you using for the atomic_refresh parameter? false or true?

                    in 10g release the default refresh of a materialized view is

                    (a) delete
                    (b) insert

                    But if you use atomic_refresh => false you will be doing

                    a) truncate
                    (b) insert /*+ append */

                    regards

                    Mohamed Houri
                    • 7. Re: Refreshing Materialized view create a lot of logs file..
                      Hemant K Chitale
                      Note that changing atomic_refresh so that the Refresh MV does a TRUNCATE and INSERT would mean that no data would be available in the MV between the TRUNCATE and the successful completion of the INSERT. Should the INSERT fail, the MV will remain empty until it is successfully refreshed.

                      So, the choice to switch to TRUNCATE, INSERT should be carefully considered before it is implemented.


                      Hemant K Chitale
                      • 8. Re: Refreshing Materialized view create a lot of logs file..
                        Mohamed Houri
                        Of course; and thanks to mention it.

                        Regards

                        Mohamed
                        • 9. Re: Refreshing Materialized view create a lot of logs file..
                          750260
                          Hi,

                          I'm using atomic_refresh=>FALSE so that Oracle do a Truncate instead of a Delete but it does not solve the problem. Oracle continue to create a lot of logs related to the Spatial Index... most probably related to the recalculation of the index during the refresh. I have read in another thread that there is a known bug relate to the LOGGING clause of a spatial index and that NOLOGGING cannot be use for this type of index and this should be the problem.

                          Any idea how to get rid of this logs when refreshing my MV?

                          Thanks a lot

                          Etienne
                          • 10. Re: Refreshing Materialized view create a lot of logs file..
                            Hemant K Chitale
                            Your issue is more with the Spatial Index than with the MV itself.

                            If you run the refresh job without creating the indexes (or stop the job at that step), you could check the amount of redo generated by the MV refresh. Since you are already using TRUNCATE and INSERT APPEND, it should be low.

                            Have you posted your question about the Spatial Indexes and Redo generation in the Spatial forum ?


                            Hemant K Chitale