LOG en Vista materializada

[samotin]

Hola a todos,


tengo un problema con una vista materializada que va muy lenta y no sé que puedo mirar más. Esto es lo que he visto:

El modo de refresco es FAST, he creado los LOG, se hacen estadísticas, el parametro STAR_TRANSFORMATION = TRUE (es un DWH).

La select es un inner join entre dos tablas de diferentes esquemas con un group by.

Tengo una duda:

Si tengo varias vistas que apuntan a una misma tabla ¿sólo necesito una tabla de LOG?

Les doy las gracias de antemano

[matanga]

Hola,

Para analizar la consulta sobre una vista materializada puedes ver el package DBMS_MVIEW.EXPLAIN_REWRITE donde expone el plan de ejecucion y como utiliza o no la vista.

Estas utilizando Query Rewrite?, o haces un SELECT directo sobre la vista materializada?

Tengo una duda:

Si tengo varias vistas que apuntan a una misma tabla ¿sólo necesito una tabla de LOG?

Buena pregunta, nunca me habia pasado, asi que lo tuve que probar :)

Lo que hice fue:

1. Cree una tabla cualquiera T1.
2. Cree un LOG sobre la tabla T1.
3. Cree dos vistas materializadas, T1_VM1 y T1_VM2, con BUILD IMMEDIATE Y FAST REFRESH ON COMMIT.
4. Inserte datos en la tabla original y despues del commit Oracle actualizo las dos VMs, asi que vale suponer que es un LOG por tabla.

Saludos

[samotin]

Gracias por tu respuesta.

Estoy utilizando Query Rewrite, crees que es mejor hacerlo de otra forma?

Saludos.

[matanga]

Hola,

Depende de como utilices las vistas materializadas, la frecuencia con que cambien los datos y los eventos que disparan el refresh.

Query Rewrite es muy util pero una vez tuve un caso en que las vistas se refrescaban 1 vez por mes que era la frecuencia en que se realizaba la carga externa de datos, en este escenario puntual tuve mejores resultados haciendo las consultas directamente sobre la vista, y no dejar al optimizador la tarea de evaluar el Query Rewrite. Pero si los datos cambian con frecuencia, puedes caer un una consulta sobre datos desactualizados, eso depende del modelo que tengas.

La mejor herramienta que tienes en estos casos es el Explain Plan, ya sea sobre la vista materializada o el Query Rewrite, y ahi veras las formas de acceso y cual es la mas optima.

Saludos

[samotin]

Hola,

he hecho el explain plan pero la verdad es que no consigo entender que puedo mejorar despues de ver lo que me devuelve:
----------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
0 | SELECT STATEMENT | |
1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | PX BLOCK ITERATOR | |
| 4 | MAT_VIEW REWRITE ACCESS FULL| VM_PARTE_SEC_SEMANAL |


--------------------------------------------------------------------
Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------
67M| 10G| 15581 (2)| 00:03:07 | | | |
67M| 10G| 15581 (2)| 00:03:07 | Q1,00 | P->S | QC (RAND) |
67M| 10G| 15581 (2)| 00:03:07 | Q1,00 | PCWC | |
67M| 10G| 15581 (2)| 00:03:07 | Q1,00 | PCWP | |
--------------------------------------------------------------------



Ves algo o se te ocurre que más puedo probar?

Gracias

[matanga]

Hola,

Postea la consulta, y formatea el post con las etiquetas [CODE] asi sera mas facil del leer :)

Editado:

Tambien la version de Oracle en 4 digitos, ej 10.2.0.1

Saludos

[samotin]

Hola,

a ver que te parece, es un poco larga. La version es:

10g 10.2.0.3

Ahí va la consulta:

Código:

  CREATE MATERIALIZED VIEW "RECOR"." VM_PARTE_SEC_SEMANAL "
  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_SEC"
  PARALLEL (DEGREE 10 INSTANCES 2)
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_SEC"
  REFRESH FAST ON COMMIT
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  ENABLE QUERY REWRITE
  AS select t.DATOS_SEMANALES SEMANALES,
t.NOMBRE,
t.DATOS_ANY,
to_char(t.DATOS_ANY) NOM_ANYO_SEMANAL,
ID_SECI,
ID_NUMERO,
ID_VERSION,
ID_BIT,
ID_ZONA,
CONTAB,
ID_BANDERA,
ID_SUBAREA,
ID_SECCION,
MARCA,
HARD,
ID_HOJA,
ID_PRODUC,
sum(IMPORTE_PUNTO) IMPORTE_PUNTO,
sum(decode(ID_ZONA, 40, 1, 50, 1, 0)*IMPORTE_PUNTO) IMPORTE_ACT,
sum(IMPORTE_PUNTO_PIG) IMPORTE_PUNTO_PIG,
sum(decode(ID_ZONA, 40, 1, 50, 1, 0)*IMPORTE_PUNTO_PIG) IMPORTE_ACT_PUNTO,
sum(IMPORTE_PP_HIST) IMPORTE_PP_HIST,
sum(decode(ID_ZONA, 40, 1, 50, 1, 0)*IMPORTE_PP_HIST) IMPORTE_HISTORICO,
sum(IMPORTE_AA) IMPORTE_AA,
sum(decode(ID_ZONA, 40, 1, 50, 1, 0)*IMPORTE_AA) IMPORTE_VUELTA
from comercial_margon c,
trimestral t
where c.DAT_CONTAB = t.DAT_DIA
group by t.DAT_SEMANA ,
t.NOMBRE,
t.DATOS_ANY,
to_char(t.DATOS_ANY),
ID_SECI,
ID_NUMERO,
ID_VERSION,
ID_BIT,
ID_ZONA,
CONTAB,
ID_BANDERA,
ID_SUBAREA,
ID_SECCION,
MARCA,
HARD,
ID_HOJA,
ID_PRODUC

Muchas gracias

[matanga]

Hola,

Esto es un problema

Código:

from comercial_margon c,
trimestral t
where c.DAT_CONTAB = t.DAT_DIA

No hay ningun filtro por valor, supongo que se esta haciendo un FULL SCAN sobre las dos tablas.

1. ¿De que indices dispones?
2. ¿Cual es el plan de ejecucion de?, por ejemplo

Código:

select t.DAT_DIA
from comercial_margon c,
trimestral t
where c.DAT_CONTAB = t.DAT_DIA

Por otro lado veo que estas utilizando Parallel Query, ten cuidado de que el grado de paralelismo no supere los procesadores o nucleos, y si lo hace, que no sea por mucho, esto puede ser contraproducente.

Saludos

[samotin]

Hola,

haré lo que me indicas, lástima que ahora no me sea posible.

El explain plan es el que te envié, está 4 mensajes más arriba, dice algo tipo MAT_VIEW REWRITE ACCESS FULL, supongo que, tal como tú decías, está haciendo un FULL SCAN.

Miraré si tiene índices en los campos que utiliza para hacer el join.

Te mantengo informado de los progresos.

Mil gracias y un saludo.

[samotin]

Hola,

el tema de los índices que me comentabas podría ser el problema?

la tabla COMERCIAL_MARGON tiene un Primary Key con 12 campos y un índice con dos campos. Es la tabla más grande, aprox. 450 millones de filas

La otra tabla tiene sólo un índice, con dos campos. Esta tabla sólo tiene aprox. 1500 filas

Los campos que están en el WHERE de la consulta están indexados.

Crees que iría más rápido si utilizase un "hint" tipo SELECT * /*+ INDEX(tabla nombre_indice) */? He leido algo sobre eso aunque no tengo muy claro como hacerlo. Tu que crees?

Gracias de nuevo,

[matanga]

Hola,

Creo que hay varias cosas a considerar, por ejemplo:

1. Intentar un FULL SCAN sobre la tabla mas pequeña, y que el JOIN entre por indice a la tabla grande, para esto tienes que tener indexado el campo del WHERE de la tabla grande, siempre tienes que ir de la mano del Explain Plan para ver los resultados.

2. Intenta ver que sucede con el hint /*+ USE_NL(tabla_grande tabla_pequeña) */ y asi hacer un FULL SCAN sobre la pequeña y un acceso por indice a la grande.

3. Parallel Query, la vista utiliza un grado de paralelismo de 10, desconozco la dimesion del servidor, pero parece un poco alto, puedes intentar que el grado de paralelismo lo administre Oracle en forma automatica.

4. Tambien desconozco el tamaño en megas de la tabla, pero con esa cantidad de registros, y si tienes licenciada la version Enterprise, puedes ver como utilizar particionamiento.

Saludos

[samotin]

Hola,

bueno, pues parece que tengo bastantes cosas que probar. Eso es bueno, por lo menos tengo varias opciones.

Cuando lo haya probado todo te diré si se acelera el tema y como me ha ido.

Gente como tú son las que hacen que esto funcione. Muchas muchas gracias.

Saludos.