The below code is having a performance issue. I am new to perf tuning, and not able to figure out the exact cause of the performance issue. Ran SQL Tuning Advisor and created a SQL profile for the sql id. But every time I run the query with diff parameter the SQL id itself is getting changed and hence the SQL profile is not being used.
Any help on how to fix the perf issue will be highly useful and appreciated :
Code:
SELECT ooh.order_number order_number
,ooh.transactional_curr_code currency
,ooh.org_id org_id
,ooh.attribute14 lens_letter_code
,(SELECT directory_path||'/'
FROM dba_directories
WHERE directory_name = 'XXSSOM_CL_REPORT') dba_path
,(SELECT flv.attribute10
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) ecomm_email
,(SELECT flv.attribute11
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) ecomm_phone
,(SELECT flv.description
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) template_code
,ooh.tp_attribute2 ecomm_order_no
,(SELECT oola.attribute8
FROM oe_order_lines_all oola
WHERE oola.header_id = ooh.header_id
AND ROWNUM=1
) ecomm_cust_no
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 1), '~') cust_info1
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 2), '~') cust_info2
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 3), '~') cust_info3
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 4), '~') cust_info4
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 5), '~') cust_info5
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 6), '~') cust_info6
,RTRIM (REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 7), '~') cust_info7
,LTRIM (REGEXP_SUBSTR (ooh.attribute5, '~[^~]*', 1, 7), '~') cust_info8
,ship_loc.address1 store_address1
,ship_loc.address2 store_address2
,ship_loc.address3 store_address3
,ship_loc.address4 store_address4
,ship_loc.postal_code store_postal_code
,org.address_line_1 ou_address1
,org.address_line_2 ou_address2
,org.address_line_3 ou_address3
,org.town_or_city ou_town_or_city
,org.country ou_country
,org.postal_code ou_postal_code
,ooh.attribute5 customer_details
,regexp_replace(hca.account_number,'[^0-9]') epos_number
,ooh.attribute1 order_reference
,ooh.tp_attribute9 scheme_epos_number
,ooh.tp_attribute2 scheme_order_ref
,(SELECT oola.attribute8
FROM oe_order_lines_all oola
WHERE oola.header_id = ooh.header_id
AND ROWNUM=1
) ecomm_order_ref
,RTRIM(REGEXP_SUBSTR (ooh.attribute5, '[^~]*~', 1, 1), '~') customer_title
,NULL customer_first_name
,NULL customer_last_name
,TO_CHAR(ooh.ordered_date,'dd/mm/yy') order_date
,TO_CHAR(ooh.ordered_date,'dd.mm.yyyy') order_date_finland
,TO_CHAR(ooh.ordered_date,'yyyy-mm.dd') order_date_SE
,TO_CHAR(ooh.ordered_date,'dd. month yyyy') xal_order_date
,TO_CHAR(ooh.request_date,'dd-mon-yyyy') xal_request_date
,ooh.attribute10 total_for_product
,ooh.attribute11 delivery_charge
,ooh.attribute13 total_paid
,stocust.address1 store_registerd_add1
,stocust.address2 store_registerd_add2
,stocust.address3 store_registerd_add3
,stocust.address4 store_registerd_add4
,stocust.address5 store_registerd_add5
,stocust.legal_name legal_name
,stocust.vat_number vat_number
,stocust.company_number company_number
,stocust.phone_number phone_number
,NVL((SELECT xxssom_cl_cust_report_pkg.cl_conv_to_clob(fl.file_data)
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd
WHERE 1 = 1
AND fad.pk1_value = stocust.site_id
AND fad.document_id = fd.document_id
AND fd.file_name = fl.file_name
AND fad.category_id = 1000474
AND ROWNUM = 1),'XX') image_clob
,TO_CHAR(to_date(ooh.attribute17,'DD-MON-RRRR'),'DD/MM/YYYY') next_pack_due_date
,TO_CHAR(to_date(ooh.attribute17,'DD-MON-RRRR'),'DD.Mon YYYY') next_pack_due_date_xal67
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute1
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg1
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute2
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg2
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute3
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg3
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute4
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg4
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute5
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg5
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute6
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg6
,(SELECT distinct message_text
FROM fnd_new_messages fnm,
fnd_lookup_values flv
WHERE fnm.message_name = flv.attribute7
AND lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) scheme_msg7
,(SELECT flv.tag
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) report_type
,(SELECT flv.attribute8
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) country_code
,(SELECT flv.attribute9
FROM fnd_lookup_values flv
WHERE lookup_type = 'XXSS_CL_LETTER_CODE_LKP'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( flv.start_date_active, SYSDATE-1) AND NVL(flv.end_date_active,SYSDATE+1)
AND language = 'US'
AND lookup_code = ooh.attribute14) sequence
,'N'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
FROM oe_order_headers_all ooh
,hz_parties hp
,hz_cust_accounts hca
,hz_cust_acct_sites_all hcasas
,hz_cust_site_uses_all hzsuas
,hz_party_sites hps
,hz_locations ship_loc
,(SELECT ood.operating_unit,
organization_name,
organization_code,
address_line_1,
address_line_2,
address_line_3,
town_or_city,
country,
postal_code
FROM org_organization_definitions ood,
hr_all_organization_units haou,
hr_locations hl
WHERE hl.location_id = haou.location_id
AND ood.organization_id = haou.organization_id
) org
,(SELECT DISTINCT INITCAP(rx.legal_name) legal_name ,
rxt.tax_registration_number vat_number ,
rx.company_number company_number ,
rxc.phone_number phone_number ,
SUBSTR(site_identification_number, 1, 8 ) site_identification_number,
rrb.site_id site_id,
TRIM(REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 1)) AS Address1 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 2)) AS Address2 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 3)) AS Address3 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 4)) AS Address4 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 5)) AS Address5 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 6)) AS Address6 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 7)) AS Address7 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 8)) AS Address8 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 9)) AS Address9 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 10)) AS Address10 ,
TRIM( REGEXP_SUBSTR (REGISTERED_ADDRESS, '[^,]+', 1, 11)) AS Address11
FROM rrs_xx_legal_basic_agv rx ,
rrs.rrs_sites_b rrb,
rrs.rrs_sites_ext_b rre,
rrs_xx_tax_basic_agv rxt,
rrs_xx_communication_agv rxc
WHERE rx.site_id(+) = rrb.site_id
AND rxt.site_id(+) = rrb.site_id
AND rxc.site_id(+) = rrb.site_id
AND rre.site_id(+) = rrb.site_id
AND rre.c_ext_attr1 = 'YES'
AND rre.d_ext_attr2 IS NULL
AND rre.attr_group_id = 702
AND SYSDATE BETWEEN NVL( rx.start_date, SYSDATE -1 ) AND NVL( rx.end_date,SYSDATE+1)
AND SYSDATE BETWEEN NVL( rrb.start_date, SYSDATE-1 ) AND NVL( rrb.end_date,SYSDATE)
AND rrb.site_status_code ='A'
AND rx.legal_status ='Active'
)stocust
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = ooh.sold_to_org_id
AND org.operating_unit = ooh.org_id
AND stocust.site_identification_number(+) = hca.account_number
AND hcasas.cust_account_id = hca.cust_account_id
AND hps.location_id = ship_loc.location_id
AND hcasas.party_site_id = hps.party_site_id
AND hcasas.cust_acct_site_id = hzsuas.cust_acct_site_id
AND ooh.ship_to_org_id = hzsuas.site_use_id
AND ooh.booked_flag = 'Y'
AND(ooh.order_number IN(SELECT order_number
FROM xxssso_outbound_jda_batch_stg
WHERE parent_request_id = pn_request_id (passing a different value everytime)
AND letter_code IS NOT NULL
AND UPPER(order_source) IN ('LENSMAIL'
,'XAL-DK'
,'MPP'
,'ECOMMERCE-CL-FI'
,'ECOMMERCE-CL-NL'
,'ECOMMERCE-CL-NO'
,'ECOMMERCE-CL-ROI'
,'ECOMMERCE-CL-SE'
,'ECOMMERCE-CL-SOG'
,'ECOMMERCE-CL-UK'
,'COPY' -- Added new sources for LOL725
,'ONLINE' -- Added new sources for LOL725
,'ECOMMERCE-CL-DK' -- Added for version 2.20
)
)
)
GROUP BY ooh.order_number
,ooh.transactional_curr_code
,ooh.org_id
,ooh.attribute14
,TRIM(REGEXP_SUBSTR (ooh.attribute1, '[^-]+', 1, 2))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 1))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 2))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 3))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 4))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 5))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '[^,]+', 1, 6))
,ship_loc.address1
,ship_loc.address2
,ship_loc.address3
,ship_loc.address4
,ship_loc.postal_code
,org.address_line_1
,org.address_line_2
,org.address_line_3
,org.town_or_city
,org.country
,org.postal_code
,ooh.attribute5
,regexp_replace(hca.account_number,'[^0-9]')
,ooh.attribute1
,TRIM(REGEXP_SUBSTR (ooh.attribute1, '[^-]+', 1, 3))
,TRIM(REGEXP_SUBSTR (ooh.attribute1, '[^-]+', 1, 2))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '(\S*)(\s)', 1, 1))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '(\S*)(\s)', 1, 2))
,TRIM(REGEXP_SUBSTR (ooh.attribute5, '(\S*)(\s)', 1, 3))
,TO_CHAR(ooh.ordered_date,'dd/mm/yy')
,TO_CHAR(ooh.ordered_date,'dd.mm.yyyy')
,TO_CHAR(ooh.ordered_date,'yyyy-mm.dd')
,TO_CHAR(ooh.ordered_date,'dd-mon-yyyy')
,TO_CHAR(ooh.request_date,'dd-mon-yyyy')
,ooh.attribute10
,ooh.attribute11
,ooh.attribute13
,ooh.tp_attribute2
,ooh.tp_attribute9
,ooh.header_id
,stocust.address1
,stocust.address2
,stocust.address3
,stocust.address4
,stocust.address5
,stocust.legal_name
,stocust.vat_number
,stocust.company_number
,stocust.phone_number
,stocust.site_id
,TO_CHAR(ooh.ordered_date,'dd. month yyyy')
,TO_CHAR(TO_DATE(ooh.attribute17,'DD-MON-RRRR'),'DD/MM/YYYY')
,TO_CHAR(TO_DATE(ooh.attribute17,'DD-MON-RRRR'),'DD.Mon YYYY');
Regards,