Home » RDBMS Server » Performance Tuning » Performance Tunning issue 9i (9i)
Performance Tunning issue 9i [message #342118] Thu, 21 August 2008 07:06 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Query Taking very long time
Next Topic: Are system procedures using Rule hint in Oracle 10g?
Goto Forum:
  


Current Time: Mon Dec 05 03:12:04 CST 2016

Total time taken to generate the page: 0.21095 seconds