SQL Query Perfomance Issue.
Oracle, Windows, ERP, Software testing, Automated, Performance/Load, Software Testing Tools
Hi All,This is Wasim Ahmed. I basically wanted a kick start to tune my SQL Queries for performance.
I have a huge query which is taking an average of 30seconds for records in thousands. I want to improve the efficiency of the Query. Can any one help me in this.
I am pasting my SQL query.
I would be greatfull if you can mail me your personal email id to wase_ahmed@hotmail.com so that i can share my PLSql procedure..
Thank you.
Best Regards
Wasim Ahmed.
SELECT *
FROM (SELECT ohs.ohs_order_no "SOH_ORDER_NO", ohs.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME",
ohs.ohs_doc_ref "SOH_CUSTOMER_DOC_REF",
NVL (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format), ' ') "SOH_CUSTOMER_ORDER_DATE",
ohs.rep_entity_id "REP_ENTITY_ID", ohs.rev_entity_id "REV_ENTITY_ID", ohs.ohs_status "SOH_STATUS",
cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID", prm.prm_name "PRM_NAME",
ohs.ohs_transaction_type "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", ods.sup_code "SUP_CODE",
ohs.ohs_order_classification "SOH_ORDER_CLASSIFICATION"
FROM ORDER_HEADER_SCRATCH ohs,
CUSTOMER_MASTER cus,
CODE_MASTER cm,
(SELECT MAX (prm_id) AS prm_id, pos_order_no
FROM PROCESS_ORDER_STATUS
GROUP BY pos_order_no) pos,
PROCESS_MASTER prm,
(SELECT ods.ohs_order_no, ods.sup_code
FROM ORDER_HEADER_SCRATCH ohs, ORDER_DETAIL_SCRATCH ods
WHERE ohs.ohs_order_no(+) = ods.ohs_order_no
AND ohs.ohs_status <> 1
GROUP BY ods.ohs_order_no, ods.sup_code) ods
WHERE ohs.cus_code_dlvy = cus.cus_code
AND ohs.ohs_status LIKE l_orderstatus
AND ohs.ohs_order_no LIKE l_orderno
AND UPPER (ohs.ohs_transaction_type) = 'SO'
AND ohs.ohs_status = cm.acm_code_value
AND cm.acm_code_fldname = 'SO_HEADER_STATUS'
AND ohs.ohs_order_no = pos.pos_order_no(+)
AND prm.prm_id(+) = pos.prm_id
-- Dummy order number to suppress the order when despatch note number is given
AND ohs.ohs_order_no LIKE l_dummyordernumber
-- New Requirment for Simple Search for Sup Code
AND ohs.ohs_order_no = ods.ohs_order_no(+)
AND UPPER (NVL (ods.sup_code, ' ')) LIKE UPPER (l_supcode)
AND ohs.rev_entity_id IN (SELECT t_reventityid
FROM TEMP_REVENTITY)
AND ohs.rep_entity_id IN (SELECT t_repentityid
FROM TEMP_REPENTITY)
AND UPPER (NVL (ohs.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode)
AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname)
AND UPPER (NVL (ohs.ohs_doc_ref, ' ')) LIKE UPPER (l_cusrefno)
AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom
AND ohs.ohs_order_date IS NOT NULL
)
AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto
AND ohs.ohs_order_date IS NOT NULL
)
UNION ALL
(SELECT DISTINCT soh.soh_order_no "SOH_ORDER_NO", soh.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME",
soh.soh_customer_doc_ref "SOH_CUSTOMER_DOC_REF",
NVL (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format),
' '
) "SOH_CUSTOMER_ORDER_DATE",
soh.rep_entity_id_orig "REP_ENTITY_ID", soh.rev_entity_id "REV_ENTITY_ID",
soh.soh_status "SOH_STATUS", cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID",
prm.prm_name "PRM_NAME", 'SO' "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", sod.sup_code "SUP_CODE",
soh.soh_order_classification "SOH_ORDER_CLASSIFICATION"
FROM SO_HEADER soh,
CUSTOMER_MASTER cus,
CODE_MASTER cm,
(SELECT MAX (prm_id) AS prm_id, pos_order_no
FROM PROCESS_ORDER_STATUS
GROUP BY pos_order_no) pos,
PROCESS_MASTER prm,
(SELECT sod.soh_order_no, sod.sup_code, sod.rev_entity_id
FROM SO_HEADER soh, SO_DETAIL sod
WHERE soh.soh_order_no(+) = sod.soh_order_no
AND soh.rev_entity_id = sod.rev_entity_id
AND soh.soh_status <> 1
GROUP BY sod.soh_order_no, sod.sup_code, sod.rev_entity_id) sod,
(SELECT pic.soh_order_no, pic_picklist, pic.rev_entity_id
FROM PICKLIST_MASTER pic
WHERE (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM')) IN (
SELECT MAX (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM'))
FROM PICKLIST_MASTER pic1
WHERE pic1.soh_order_no = pic.soh_order_no
AND pic1.sod_line_number = pic.sod_line_number
AND pic1.rev_entity_id = pic.rev_entity_id
GROUP BY pic1.pic_picklist)
GROUP BY pic.pic_picklist, pic.soh_order_no, pic.rev_entity_id) pic
WHERE soh.cus_code_dlvy = cus.cus_code
AND soh.soh_status LIKE l_orderstatus
AND soh.soh_order_no LIKE l_orderno
AND UPPER (soh.soh_transaction_type) = 'SO'
AND soh.soh_status = cm.acm_code_value
AND cm.acm_code_fldname = 'SO_HEADER_STATUS'
AND soh.soh_order_no = pos.pos_order_no(+)
AND pos.prm_id = prm.prm_id(+)
-- Despatch Note Conditions
AND pic.soh_order_no(+) = sod.soh_order_no
-- New Simple Search change Sup Code
AND soh.soh_order_no = sod.soh_order_no(+)
AND soh.rev_entity_id = sod.rev_entity_id
AND UPPER (NVL (sod.sup_code, ' ')) LIKE UPPER (l_supcode)
AND soh.rev_entity_id IN (SELECT t_reventityid
FROM TEMP_REVENTITY)
AND soh.rep_entity_id_orig IN (SELECT t_repentityid
FROM TEMP_REPENTITY)
AND UPPER (NVL (soh.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode)
AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname)
AND UPPER (NVL (soh.soh_customer_doc_ref, ' ')) LIKE UPPER (l_cusrefno)
-- Despatch note
AND NVL (pic.pic_picklist, ' ') LIKE l_despatchnotenumber
AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom
AND soh.soh_customer_order_date IS NOT NULL
)
AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto
AND soh.soh_customer_order_date IS NOT NULL
))
ORDER BY soh_order_no)
WHERE ROWNUM < Agsoracleconstant.l_record_count;
0
