IDS Forum

Re: [Fwd: Perfomance problem [547]] [548]

Posted By:
Date:

Sorry for the atachment file.
Here is in the message body:

*********************

QUERY:
------
create view "dba".sga_cursadas
(unidad_academica,carrera,legajo,comision,materia,acta_promocion,folio_promocion
,renglon_promocion,acta_regular,folio_regular,renglon_regular,plan,version,fecha
_regularidad,cond_regularidad,resultado,nota,pct_asistencia,fin_vigencia_regul,e
stado,origen) as select
x1.unidad_academica ,x1.carrera ,x1.legajo ,x0.comision ,x2.materia ,x1.acta ,x1
.folio ,x1.renglon ,0 ,0 ,0 ,x1.plan ,x1.version ,x1.fecha ,x1.cond_regularidad
,x1.resultado ,x1.nota ,x1.pct_asistencia ,x1.fecha ,'A' ,'P'
from "dba".sga_actas_promo x0 ,"dba".sga_det_acta_promo
x1 ,"dba".sga_comisiones x2 where (((((((x0.unidad_academica =
x1.unidad_academica ) AND (x0.acta = x1.acta ) ) AND (x0.estado = 'C' ) ) AND
(x0.tipo = 'P' ) ) AND (x1.rectificado = 'N' ) ) AND (x1.resultado = 'P' ) )
AND (x2.comision = x0.comision ) ) union select {+ ORDERED }
x3.unidad_academica ,x3.carrera ,x3.legajo ,x4.comision ,x6.materia ,x4.acta_ref
erencia ,x5.folio ,x5.renglon ,0 ,0 ,0 ,x3.plan ,x3.version ,x3.fecha ,x3.cond_r
egularidad ,x3.resultado ,x3.nota ,x3.pct_asistencia ,x3.fecha ,'A' ,'P'
from "dba".sga_det_acta_promo x3 ,"dba".sga_actas_promo x4 ,outer
("dba".sga_det_acta_promo x5 ) ,"dba".sga_comisiones x6 where
(((((((((((x4.unidad_academica = x3.unidad_academica ) AND (x4.acta =
x3.acta ) ) AND (x4.tipo = 'R' ) ) AND (x4.estado = 'C' ) ) AND (x3.rectificado
= 'N' ) ) AND (x3.resultado = 'P' ) ) AND (x5.unidad_academica =
x3.unidad_academica ) ) AND (x5.carrera = x3.carrera ) ) AND (x5.legajo =
x3.legajo ) ) AND (x5.acta = x4.acta_referencia ) ) AND (x6.comision =
x4.comision ) ) union select {+ ORDERED }
x7.unidad_academica ,x7.carrera ,x7.legajo ,x8.comision ,x9.materia ,0 ,0 ,0 ,x7
.acta ,x7.folio ,x7.renglon ,x7.plan ,x7.version ,x7.fecha_regularidad ,x7.cond_
regularidad ,x7.resultado ,x7.nota ,x7.pct_asistencia ,x7.fin_vigencia_regul ,'A
' ,'C' from "dba".sga_det_acta_curs x7 ,"dba".sga_actas_cursado
x8 ,"dba".sga_comisiones x9 where (((((x7.unidad_academica =
x8.unidad_academica ) AND (x7.acta = x8.acta ) ) AND (x8.estado = 'C' ) ) AND
(x7.resultado != 'P' ) ) AND (x9.comision = x8.comision ) ) union select
x10.unidad_academica ,x10.carrera ,x10.legajo ,0 ,x10.materia ,0 ,0 ,0 ,x10.equi
v_parcial ,0 ,0 ,x10.plan ,x10.version ,DATE
(x10.fecha ) ,0 ,x10.resultado ,x10.nota ,0 ,DATE
(x10.fin_de_vigencia ) ,x10.estado ,'E' from "dba".sga_equiv_parcial x10 where
((x10.fin_de_vigencia IS NULL ) OR (x10.fin_de_vigencia > TODAY ) ) union
select
x11.unidad_academica ,x11.carrera ,x11.legajo ,0 ,x12.materia ,0 ,0 ,0 ,x11.equi
v_parcial ,0 ,0 ,x11.plan ,x11.version ,DATE
(x11.fecha ) ,0 ,x12.resultado ,x12.nota ,0 ,DATE
(x12.fin_de_vigencia ) ,x11.estado ,'EE' from "dba".sga_equivpar_equiv
x11 ,"dba".sga_equiv_parcial x12 where ((x12.equiv_parcial =
x11.equiv_parcial ) AND ((x12.fin_de_vigencia IS NULL ) OR (x12.fin_de_vigencia
> TODAY ) ) ) union select {+ ORDERED }
x13.unidad_academica ,x13.carrera ,x13.legajo ,x16.comision ,x16.materia ,0 ,0 ,
0 ,0 ,x15.folio ,x15.renglon ,x13.plan ,x13.version ,x13.fecha ,x15.cond_regular
idad ,x15.resultado ,x15.nota ,x15.pct_asistencia ,x15.fin_vigencia_regul ,x13.e
stado ,'CE' from "dba".sga_cursadas_equiv x13 ,"dba".sga_actas_cursado
x14 ,"dba".sga_det_acta_curs x15 ,"dba".sga_comisiones x16 where
(((((((x13.unidad_academica = x14.unidad_academica ) AND (x13.acta =
x14.acta ) ) AND (x14.comision = x16.comision ) ) AND (x13.unidad_academica =
x15.unidad_academica ) ) AND (x13.carrera_original = x15.carrera ) ) AND
(x13.legajo_original = x15.legajo ) ) AND (x13.acta = x15.acta ) ) ;

Estimated Cost: 166923
Estimated # of Rows Returned: 13581

1) dba.sga_actas_promo: SEQUENTIAL SCAN

Filters: (dba.sga_actas_promo.estado = 'C' AND dba.sga_actas_promo.tipo
= 'P' )

2) dba.sga_comisiones: INDEX PATH

(1) Index Keys: comision (Serial, fragments: ALL)
Lower Index Filter: dba.sga_comisiones.comision =
dba.sga_actas_promo.comision
NESTED LOOP JOIN

3) dba.sga_det_acta_promo: INDEX PATH

Filters: (dba.sga_det_acta_promo.resultado = 'P' AND
dba.sga_det_acta_promo.rectificado = 'N' )

(1) Index Keys: unidad_academica acta (Serial, fragments: ALL)
Lower Index Filter: (dba.sga_actas_promo.acta =
dba.sga_det_acta_promo.acta AND dba.sga_actas_promo.unidad_academica =
dba.sga_det_acta_promo.unidad_academica )
NESTED LOOP JOIN


Union Query:
------------
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:
ORDERED Outerjoin nesting not compatible with ORDERED.


1) dba.sga_actas_promo: SEQUENTIAL SCAN

Filters: (dba.sga_actas_promo.tipo = 'R' AND dba.sga_actas_promo.estado
= 'C' )

2) dba.sga_comisiones: INDEX PATH

(1) Index Keys: comision (Serial, fragments: ALL)
Lower Index Filter: dba.sga_comisiones.comision =
dba.sga_actas_promo.comision
NESTED LOOP JOIN

3) dba.sga_det_acta_promo: INDEX PATH

Filters: (dba.sga_det_acta_promo.resultado = 'P' AND
dba.sga_det_acta_promo.rectificado = 'N' )

(1) Index Keys: unidad_academica acta (Serial, fragments: ALL)
Lower Index Filter: (dba.sga_actas_promo.acta =
dba.sga_det_acta_promo.acta AND dba.sga_actas_promo.unidad_academica =
dba.sga_det_acta_promo.unidad_academica )
NESTED LOOP JOIN

4) dba.sga_det_acta_promo: INDEX PATH

(1) Index Keys: unidad_academica carrera legajo acta (Serial, fragments:
ALL)
Lower Index Filter: (((dba.sga_det_acta_promo.legajo =
dba.sga_det_acta_promo.legajo AND dba.sga_det_acta_promo.acta =
dba.sga_actas_promo.acta_referencia ) AND dba.sga_det_acta_promo.carrera =
dba.sga_det_acta_promo.carrera ) AND dba.sga_det_acta_promo.unidad_academica =
dba.sga_det_acta_promo.unidad_academica )
NESTED LOOP JOIN


Union Query:
------------
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:


1) dba.sga_det_acta_curs: SEQUENTIAL SCAN

Filters: dba.sga_det_acta_curs.resultado != 'P'

2) dba.sga_actas_cursado: INDEX PATH

Filters: dba.sga_actas_cursado.estado = 'C'

(1) Index Keys: unidad_academica acta (Serial, fragments: ALL)
Lower Index Filter: (dba.sga_det_acta_curs.acta =
dba.sga_actas_cursado.acta AND dba.sga_det_acta_curs.unidad_academica =
dba.sga_actas_cursado.unidad_academica )
NESTED LOOP JOIN

3) dba.sga_comisiones: INDEX PATH

(1) Index Keys: comision (Serial, fragments: ALL)
Lower Index Filter: dba.sga_comisiones.comision =
dba.sga_actas_cursado.comision
NESTED LOOP JOIN


Union Query:
------------

1) dba.sga_equiv_parcial: SEQUENTIAL SCAN

Filters: (dba.sga_equiv_parcial.fin_de_vigencia IS NULL OR
dba.sga_equiv_parcial.fin_de_vigencia > TODAY )


Union Query:
------------

1) dba.sga_equivpar_equiv: SEQUENTIAL SCAN

2) dba.sga_equiv_parcial: INDEX PATH

Filters: (dba.sga_equiv_parcial.fin_de_vigencia IS NULL OR
dba.sga_equiv_parcial.fin_de_vigencia > TODAY )

(1) Index Keys: equiv_parcial (Serial, fragments: ALL)
Lower Index Filter: dba.sga_equiv_parcial.equiv_parcial =
dba.sga_equivpar_equiv.equiv_parcial
NESTED LOOP JOIN


Union Query:
------------
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:


1) dba.sga_cursadas_equiv: SEQUENTIAL SCAN

2) dba.sga_actas_cursado: INDEX PATH

(1) Index Keys: unidad_academica acta (Serial, fragments: ALL)
Lower Index Filter: (dba.sga_cursadas_equiv.acta =
dba.sga_actas_cursado.acta AND dba.sga_cursadas_equiv.unidad_academica =
dba.sga_actas_cursado.unidad_academica )
NESTED LOOP JOIN

3) dba.sga_det_acta_curs: INDEX PATH

(1) Index Keys: unidad_academica carrera legajo acta (Serial, fragments:
ALL)
Lower Index Filter: (((dba.sga_cursadas_equiv.legajo_original =
dba.sga_det_acta_curs.legajo AND dba.sga_cursadas_equiv.acta =
dba.sga_det_acta_curs.acta ) AND dba.sga_cursadas_equiv.carrera_original =
dba.sga_det_acta_curs.carrera ) AND dba.sga_cursadas_equiv.unidad_academica =
dba.sga_det_acta_curs.unidad_academica )
NESTED LOOP JOIN

4) dba.sga_comisiones: INDEX PATH

(1) Index Keys: comision (Serial, fragments: ALL)
Lower Index Filter: dba.sga_actas_cursado.comision =
dba.sga_comisiones.comision
NESTED LOOP JOIN

UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com
UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com
UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com
UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com
UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com
UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com

QUERY:
------
SELECT sga_alumnos.unidad_academica,

sga_alumnos.carrera,

sga_alumnos.legajo,

sga_comisiones.materia,

sga_comisiones.anio_academico,

sga_comisiones.periodo_lectivo,

DECODE(
sga_cursadas.resultado, 'P', 'Promovido', 'A', 'Aprobado', 'R', 'Reprobado', 'U'
, 'Ausente', 'E', 'Equivalencia', 'EE', 'Equiv. Equivalente', 'CE', 'Cursada
Equiv.' ),

sga_cursadas.fin_vigencia_regul,

sga_cursadas.fecha_regularidad,

sga_cursadas.cond_regularidad,

sga_atrib_mat_plan.nombre_reducido,

sp_docentes_com ( sga_cursadas.comision ) docentes,

sga_carreras.nombre_reducido

{ INTO vcUnidadAcademica,

vcCarrera,

vcLegajo,

vcMateria,

iAnioAcademico,

vcPeriodoLectivo,

vcResultado,

dFinVigRegularidad,

dFecRegularidad,

dCondRegularidad,

vcMateriaDesc,

vcDocentes,

vcCarreraDesc

}

FROM sga_comisiones,

sga_cursadas,

sga_atrib_mat_plan,

sga_alumnos,

sga_carreras,

sga_planes

WHERE sga_alumnos.unidad_academica = '09'

AND sga_alumnos.carrera = 'CP'

AND sga_alumnos.legajo = '45622/0'



AND sga_carreras.unidad_academica = sga_alumnos.unidad_academica

AND sga_carreras.carrera = sga_alumnos.carrera



AND sga_cursadas.unidad_academica = sga_alumnos.unidad_academica

AND sga_cursadas.carrera = sga_alumnos.carrera

AND sga_cursadas.legajo = sga_alumnos.legajo

AND sga_cursadas.fin_vigencia_regul >= TODAY

AND sga_cursadas.resultado = 'A'



AND sga_planes.unidad_academica = sga_alumnos.unidad_academica

AND sga_planes.carrera = sga_alumnos.carrera

AND sga_planes.plan = sga_alumnos.plan



AND sga_atrib_mat_plan.unidad_academica = sga_alumnos.unidad_academica

AND sga_atrib_mat_plan.carrera = sga_alumnos.carrera

AND sga_atrib_mat_plan.plan = sga_alumnos.plan



-- AND sga_atrib_mat_plan.version = sga_planes.version_actual

AND sga_atrib_mat_plan.version = sga_cursadas.version



AND sga_atrib_mat_plan.materia = sga_comisiones.materia

AND sga_comisiones.comision = sga_cursadas.comision



ORDER BY sga_comisiones.anio_academico, sga_comisiones.periodo_lectivo,
sga_comisiones.materia

Estimated Cost: 11
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) dba.sga_carreras: SEQUENTIAL SCAN

Filters: (dba.sga_carreras.unidad_academica = '09' AND
dba.sga_carreras.carrera = 'CP' )

2) (Temp Table For View): SEQUENTIAL SCAN

Filters:
Table Scan Filters: (((((Temp Table For View).resultado = 'A' AND (Temp
Table For View).fin_vigencia_regul >= TODAY ) AND (Temp Table For
View).unidad_academica = '09' ) AND (Temp Table For View).carrera = 'CP' ) AND
(Temp Table For View).legajo = '45622/0' )


DYNAMIC HASH JOIN
Dynamic Hash Filters: (dba.sga_carreras.carrera = (Temp Table For
View).carrera AND dba.sga_carreras.unidad_academica = (Temp Table For
View).unidad_academica )

3) dba.sga_alumnos: INDEX PATH

(1) Index Keys: unidad_academica carrera legajo (Serial, fragments: ALL)
Lower Index Filter: (((Temp Table For View).legajo =
dba.sga_alumnos.legajo AND dba.sga_carreras.carrera = dba.sga_alumnos.carrera )
AND dba.sga_carreras.unidad_academica = dba.sga_alumnos.unidad_academica )
NESTED LOOP JOIN

4) dba.sga_planes: INDEX PATH

(1) Index Keys: unidad_academica carrera plan (Serial, fragments: ALL)
Lower Index Filter: ((dba.sga_planes.carrera = dba.sga_alumnos.carrera
AND dba.sga_planes.plan = dba.sga_alumnos.plan ) AND
dba.sga_planes.unidad_academica = dba.sga_alumnos.unidad_academica )
NESTED LOOP JOIN

5) dba.sga_comisiones: INDEX PATH

(1) Index Keys: comision (Serial, fragments: ALL)
Lower Index Filter: dba.sga_comisiones.comision = (Temp Table For
View).comision
NESTED LOOP JOIN

6) dba.sga_atrib_mat_plan: INDEX PATH

(1) Index Keys: unidad_academica carrera plan version materia (Serial,
fragments: ALL)
Lower Index Filter: ((((dba.sga_atrib_mat_plan.materia =
dba.sga_comisiones.materia AND dba.sga_atrib_mat_plan.version = (Temp Table For
View).version ) AND dba.sga_atrib_mat_plan.unidad_academica =
dba.sga_alumnos.unidad_academica ) AND dba.sga_planes.plan =
dba.sga_atrib_mat_plan.plan ) AND (Temp Table For View).carrera =
dba.sga_atrib_mat_plan.carrera )
NESTED LOOP JOIN

UDRs in query:
--------------
UDR id : 1341
UDR name: sp_docentes_com


************************************

Thanks again.



Quoting Martin Fuerderer <MARTINFU@de.ibm.com>:

> Hi Paola,
>
> the attachment is still missing ...
> This may be due to the e-mail processing of the IIUG (don't know).
>
> Maybe you should use simple cut&paste to put the text directly into
> the e-mails text body ...
>
> Cheers,
> Martin
> --
>
>
>
>
> "Ana Paola A...." <pamadeo@info.unlp.edu.ar>
> Sent by: forum.subscriber@iiug.org
> 03.03.2003 13:05
>
>
> To: ids@iiug.org
> cc:
> Subject: [Fwd: Perfomance problem [547]] [548]
>
>
>
>
> Sorry, the attachment is here...
>
> > Hi everybody,
> > I have performance problem. A query have time proccesing very high but
> > in another machine this is not. I send you the query plan.
> > Thanks
> Paola
>
>
>
>
>
>
>
>
>




-------------------------------------------------
This mail sent through IMP: http://mail.info.unlp.edu.ar/

IDS Forum is maintained by Administrator with WebBBS 5.12.