| Performance Tunning issue 9i [message #342118] |
Thu, 21 August 2008 07:06  |
akkumar81
Messages: 144
Registered: February 2007
Location: india
|
Senior Member |
|
|
HI ,
Below query taken more 25 minutes to execute .Please kindly help me reduce execution time.
View:
CREATE OR REPLACE VIEW V_OES_OFR_CHK_INFO AS SELECT offer_id,
check_id,
prfl_chk_amt_ind
FROM (SELECT o.offer_idseq offer_id,
s.source_code check_id,
DECODE(o.conv_chk_prfl_in,-1,'Y','N') prfl_chk_amt_ind,
pkg_promo_bt_cc_dal.get_cc_rate_tp_val(s.source_code) rate_type
FROM offer o,
source s,
source_offer_link sol,
program p,
business_unit bu
WHERE o.offer_idseq = sol.offer_idseq
AND s.source_idseq = sol.source_idseq
AND s.fetr_set_type_cd = pkg_cpln_fetr_set_cnst.FETR_SET_TP_CHECK
AND o.program_idseq = p.program_idseq
AND p.business_unit_idseq = bu.business_unit_idseq
AND bu.portfolio_subtype_id = 'CIS'
ORDER BY offer_id ASC, rate_type DESC,check_id ASC);
Function:
----------
FUNCTION get_cc_rate_tp_val(Chk_Id_In IN source.source_code%TYPE)
RETURN VARCHAR2
IS
v_Request_Status VARCHAR2(20);
v_Check_Num_In v_cc_offer.pcc_check_num%type;
v_Rate_Tp VARCHAR2(200);
PROC_NAME CONSTANT VARCHAR2(30):= 'get_cc_rate_tp_val';
BEGIN
-- Get the pointee Check if the check is in a Range
v_Request_Status := get_request_status(Chk_Id_In,
pkg_cpln_fetr_set_cnst.FETR_SET_TP_CHECK);
IF v_Request_Status = pkg_cpln_workflow_cnst.FETR_RQST_PEND THEN
SELECT vfr.rate_tp
INTO v_Rate_Tp
FROM v_fetr_rqst vfr,
source S,
fetr_set_dtl fsd
WHERE vfr.fetr_id = fsd.fetr_id
AND s.source_idseq = fsd.fetr_set_id
AND s.fetr_set_type_cd = pkg_cpln_fetr_set_cnst.FETR_SET_TP_CHECK
AND s.Source_code = Chk_Id_In;
ELSE
SELECT v.pcc_rate_tp
INTO v_Rate_Tp
FROM v_cc_offer v
WHERE v.pcc_check_num = Chk_Id_In;
END IF;
RETURN v_Rate_Tp;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN EMPTY_STRING;
WHEN OTHERS THEN
sp_dbug_insert(package_name || '.' || PROC_NAME ||'|' || Chk_Id_In || '|'|| SQLERRM);
RAISE_APPLICATION_ERROR (-20000,package_name || '.' || PROC_NAME || '|' || SQLERRM);
END get_cc_rate_tp_val;
Thanks and regards
Akkumar
|
|
|
|
| Re: Performance Tunning issue 9i [message #342132 is a reply to message #342118] |
Thu, 21 August 2008 08:04  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
