Preciso de um help para melhorar desempenho dessa consulta..
Qtde de Registros:
works_op_char 2.339.863
works_order_char 1.701.925
works_operation 433.939
schedule 229.050
gbi_int_gsb_produtos 157.373
works_order 65.305
aep_schedule 57.364
gbi_int_gsb_produtos2 55.168
pps_resource 312
SQL:
SELECT to_char(MIN(s.sched_start_time), 'YYYY MM') mm,
to_char(MIN(s.sched_start_time), 'DD') dd,
to_char(min(s.sched_start_time), 'HH24') hr,
NVL(TRIM(WOC.OPERATION_CHAR_VAL),substr(s.works_order_no,
2, 7)) OP,
substr(s.product_id, 3, 8) material,
substr(s.cast_no, 1, 10) lote,
to_char(MIN(w.due_date), 'DD/MM/YY') prz_conf,
sum(s.planned_quantity) peso,
sum(round((s.sched_fin_time - s.sched_start_time) * 24 *
(r.disturbance_eff / 1000),
2)) horas,
s.round_id fam,
MAX(s.sched_lock_flag) C,
MAX(a2.bit_lam1) bit_lam,
MAX(A.bitola1) bit,
max(A.bitola2) larg,
A.qual_afp qual,
A.TP TP,
A.toto tt,
A.acabamento ac,
substr(A.norma, 1, 7) norma,
max(A.comp_max) comp_max,
u.production_centre EQ_ANT,
to_char(min(u.sched_start_time), 'DDHH24') DT_AN,
d.production_centre EQ_POS,
to_char(min(d.sched_start_time), 'DDHH24') DT_PO,
wpc_38.order_char_val apl_final
FROM aep_schedule s,
(select x.version_no,
x.works_order_no,
to_number(x.works_op_no || x.op_proc_no) OPER,
x.production_centre,
x.sched_start_time
from schedule x) u,
(select y.version_no,
y.works_order_no,
to_number(y.works_op_no || y.op_proc_no) OPER,
y.production_centre,
y.sched_start_time
from schedule y) d,
works_order w,
pps_resource r,
gbi_int_gsb_produtos A,
gbi_int_gsb_produtos2 a2,
schedule sch,
works_operation wo,
works_op_char woc,
works_order_char wpc_38
WHERE s.sched_fin_time < sysdate + 5
and s.equipamento = '&equipamento'
AND S.WORKS_ORDER_NO NOT LIKE 'PPS%'
AND S.WORKS_ORDER_NO NOT LIKE 'D%'
AND S.WORKS_ORDER_NO NOT LIKE 'Q%'
AND S.WORKS_ORDER_NO NOT LIKE 'R%'
and s.works_order_no = wo.works_order_no
and s.works_op_no = wo.works_op_no
and s.op_proc_no = wo.op_proc_no
and wo.version_no = 1
and w.works_order_no = wo.works_order_no
and w.version_no = wo.version_no
and sch.works_order_no = wo.works_order_no
and sch.works_op_no = wo.works_op_no
and sch.version_no = wo.version_no
and sch.alternate_pref = wo.alternate_pref
and sch.sched_resource_1 = s.equipamento
and sch.sched_start_time = s.sched_start_time
AND r.resource_id(+) = s.equipamento
AND a.product_id(+) = trim(s.product_id)
AND A.werks(+) = 'AFP'
AND A.matnr = a2.matnr(+)
and A.werks = a2.werks(+)
and u.works_order_no(+) = s.works_order_no
AND u.OPER(+) = s.up_op_no
AND d.works_order_no(+) = s.works_order_no
AND d.OPER(+) = s.down_op_no
and wo.works_order_no = woc.works_order_no
and wo.works_op_no = woc.works_op_no
and wo.version_no = woc.version_no
and wo.alternate_pref = woc.alternate_pref
and wo.op_proc_no = woc.op_proc_no
and woc.product_char_id = 202
and wo.works_order_no = wpc_38.works_order_no (+)
and wo.version_no = wpc_38.version_no (+)
and 38 = wpc_38.product_char_id (+)
GROUP BY s.equipamento,
NVL(TRIM(WOC.OPERATION_CHAR_VAL),substr(s.works_order_n
o, 2, 7)),
substr(s.product_id, 3, 8),
substr(s.cast_no, 1, 10),
s.round_id,
A.qual_afp,
A.tp,
A.toto,
A.acabamento,
substr(A.norma, 1, 7),
u.production_centre,
d.production_centre,
wpc_38.order_char_val
order by s.equipamento, 1, 2, 3