How do I Fine Tune the below two queries which is cosuming lots of time to execute. This is being used in OTM application.
i) The buffer gets is as high as 154,326.88 per execution for the first query
SELECT COUNT(tfa.shipment_gid) FROM shipment s, tesco_fresh_appointments tfa, shipment_refnum r WHERE s.shipment_gid=r.shipment_gid AND s.shipment_gid=tfa.shipment_gid AND r.shipment_refnum_qual_gid=:"SYS_B_0" AND r.shipment_refnum_value=:1 AND s.is_to_be_held=:"SYS_B_1" AND tfa.is_approved!=:"SYS_B_2" AND tfa.status=:"SYS_B_3" AND tfa.appointment_time BETWEEN to_date
2, :"SYS_B_4") -:"SYS_B_5" AND to_date3, :"SYS_B_6") +:"SYS_B_7" AND tfa.wk_day=(SELECT wk_day FROM tesco_fresh_appointments WHERE shipment_gid=:4) AND s.servprov_gid=(SELECT servprov_gid FROM shipment WHERE shipment_gid=:5) AND s.dest_location_gid=(SELECT dest_location_gid FROM shipment WHERE shipment_gid=:6)
ii) For the below query the cpu time is high as 29.47 second time per execution and Disk read is 1,856.14 per execution
select COUNT(V.VOUCHER_GID) from voucher v, invoice I, INVOICE_SHIPMENT SI, SHIPMENT S where v.invoice_gid = i.invoice_gid AND SI.INVOICE_GID=I.INVOICE_GID AND SI.SHIPMENT_GID=S.SHIPMENT_GID AND v.amount_to_pay_gid in"SYS_B_00", :"SYS_B_01") and v.domain_name = :"SYS_B_02" and v.amount_to_pay != :"SYS_B_03" and EXISTS (select sa.servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_04") and NOT EXISTS (select servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_05" and sa.ALIAS IN "SYS_B_06", :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11", :"SYS_B_12", :"SYS_B_13")) and TO_CHAR(utc.get_local_date(V.INSERT_DATE, s.dest_location_gid), :"SYS_B_14")=:"SYS_B_15"
i) The buffer gets is as high as 154,326.88 per execution for the first query
SELECT COUNT(tfa.shipment_gid) FROM shipment s, tesco_fresh_appointments tfa, shipment_refnum r WHERE s.shipment_gid=r.shipment_gid AND s.shipment_gid=tfa.shipment_gid AND r.shipment_refnum_qual_gid=:"SYS_B_0" AND r.shipment_refnum_value=:1 AND s.is_to_be_held=:"SYS_B_1" AND tfa.is_approved!=:"SYS_B_2" AND tfa.status=:"SYS_B_3" AND tfa.appointment_time BETWEEN to_date
2, :"SYS_B_4") -:"SYS_B_5" AND to_date3, :"SYS_B_6") +:"SYS_B_7" AND tfa.wk_day=(SELECT wk_day FROM tesco_fresh_appointments WHERE shipment_gid=:4) AND s.servprov_gid=(SELECT servprov_gid FROM shipment WHERE shipment_gid=:5) AND s.dest_location_gid=(SELECT dest_location_gid FROM shipment WHERE shipment_gid=:6) ii) For the below query the cpu time is high as 29.47 second time per execution and Disk read is 1,856.14 per execution
select COUNT(V.VOUCHER_GID) from voucher v, invoice I, INVOICE_SHIPMENT SI, SHIPMENT S where v.invoice_gid = i.invoice_gid AND SI.INVOICE_GID=I.INVOICE_GID AND SI.SHIPMENT_GID=S.SHIPMENT_GID AND v.amount_to_pay_gid in
"SYS_B_00", :"SYS_B_01") and v.domain_name = :"SYS_B_02" and v.amount_to_pay != :"SYS_B_03" and EXISTS (select sa.servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_04") and NOT EXISTS (select servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_05" and sa.ALIAS IN "SYS_B_06", :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11", :"SYS_B_12", :"SYS_B_13")) and TO_CHAR(utc.get_local_date(V.INSERT_DATE, s.dest_location_gid), :"SYS_B_14")=:"SYS_B_15"
