Home » SQL & PL/SQL » SQL & PL/SQL » tune this procedure
tune this procedure [message #229748] Mon, 09 April 2007 22:46 Go to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
I have to tune this following proc,it takes 1 & half hr.
Can any body tell me what i do for tuning of that.
Give me reply as soon as possible ,it is very urgent.


CREATE OR REPLACE PROCEDURE FC_ASSET_Daily_Xlation_Test(pv_batch_type_num_i IN NUMBER)
IS
TYPE gtyp_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
--
lv_counter NUMBER := 0;
gv_rowid gtyp_rowid;
--
lv_batch_min_run_dt DATE;
lv_batch_max_run_dt DATE;
--
lv_row_count NUMBER := 0;
lv_commit_size varchar2(100);-------------Datatype changed By i-flex\SD----------------------
lv_curr_val NUMBER := 0;
--
lv_daily_run_id varchar2(100);-------------Datatype changed By i-flex\SD----------------------

lv_xsl_run_id NUMBER := 0;
lv_xfmn_run_id NUMBER := 0;
--
lv_min_batch_num NUMBER := 0;
lv_max_batch_num NUMBER := 0;
--
lv_parameters VARCHAR2(100);
lv_comma1 NUMBER := 0;
lv_comma2 NUMBER := 0;
lv_comma3 NUMBER := 0;
lv_comma4 NUMBER := 0;
lv_comma5 NUMBER := 0;
--
lv_status BOOLEAN;
--
lv_product_batch BCL_log.product_batch%TYPE;
--
lv_prog_name VARCHAR2(50) := 'FC_ASSET_Daily_Xlation.spr';
--
Parameters_Not_Found EXCEPTION;
Remove_Dup_Failed EXCEPTION;
lv_return_status NUMBER(10) := 0;


BEGIN

-- Parameters for this program will be taken from a function called get_xsl_parameters

SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;
Common.Get_Product_batch(pv_batch_type_num_i, lv_product_batch); -- Get product batch name E.g.: FC_DAILY.

Common.get_xsl_parameters(pv_batch_type_num_i, lv_parameters);

IF (lv_parameters = 'ERROR')
THEN
RAISE Parameters_Not_found;
END IF;

lv_comma1 := INSTR(lv_parameters, ',', 1, 1);
lv_comma2 := INSTR(lv_parameters, ',', 1, 2);
lv_comma3 := INSTR(lv_parameters, ',', 1, 3);
lv_comma4 := INSTR(lv_parameters, ',', 1, 4);
lv_comma5 := INSTR(lv_parameters, ',', 1, 5);

-- Get 1st parameter

lv_daily_run_id := SUBSTR(lv_parameters, 1, (lv_comma1 - 1) );

Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,lv_prog_name||' : Before Checking End dt.');

IF (Common.Chk_Daily_Run_End_Dt(lv_daily_run_id))
THEN

lv_xsl_run_id := SUBSTR(lv_parameters, (lv_comma1 + 1), (lv_comma2 - lv_comma1 - 1));
lv_xfmn_run_id := SUBSTR(lv_parameters, (lv_comma2 + 1), (lv_comma3 - lv_comma2 - 1));
lv_batch_min_run_dt := TO_DATE(SUBSTR(lv_parameters, (lv_comma3 + 1), (lv_comma4 - lv_comma3 - 1)), 'DD-MON-YYYY HH24:MI:SS');
lv_batch_max_run_dt := TO_DATE(SUBSTR(lv_parameters, (lv_comma4 + 1), (lv_comma5 - lv_comma4 - 1)), 'DD-MON-YYYY HH24:MI:SS');
lv_commit_size := SUBSTR(lv_parameters, (lv_comma5 + 1), LENGTH(lv_parameters));

Common.bcl_update_stage(pv_batch_type_num_i,NULL,0,lv_xsl_run_id,'TRANSLATION_INPROGRESS');

lv_min_batch_num := pv_batch_type_num_i||Common.Ods_Id_Seq_Next_Val;

Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Min Batchnum : '||lv_min_batch_num||' : Before Insert Txn Info.');

--To get records for which only asset level information is changed.
-------------------------------------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO ODS_ASSET -------------------Append Modified by I-flex\SD\05-04-07-------------------
(ods_batch_id,
ast_asset_num,
ast_rev_num,
ast_start_dt,
ast_end_dt,
ast_extension_cd,
ast_value_basis_cd,
ast_version,
ast_comments,
ast_prod_name,
ast_prod_bu,
ast_ship_dt,
ast_inception_dt,
fc_detail_type_cd,
ast_compundwrntyfl,
ast_undmfgwrntyflg,
ast_produndwrntyfl,
ast_type_cd,
ast_pr_con,
con_con_priv_flg,
ast_pr_postn,
x_pos_type_cd,
con_relationtypecd,
ext_attrib_09,
verify_flag,
uk_flag)
SELECT
pv_batch_type_num_i||DECODE(MOD(ROWNUM,lv_commit_size), 0, Common.ODS_Id_Seq_Next_Val, LPAD(ODS_Id_Seq.CURRVAL,6,'0')),
custmast.cod_cust_natl_id,
SUBSTR(custmast.cod_cust_natl_id,1,3),
custmast.dat_cust_open,
custmast.dat_cust_close,
DECODE(custmast.cod_cnt,NULL, NULL,Get_LOVPROD.Get_Fclov('SB_CONACT_WAY', Common_File_Func.get_lang, custmast.cod_cnt)),
DECODE(custmast.cod_card_issue_reason, NULL, NULL, Get_LOVPROD.Get_Fclov('SB_CARD_TYPE', Common_File_Func.get_lang, custmast.cod_card_issue_reason)),
Get_LOVPROD.Get_Fclov('SB_TAX_CODE', Common_File_Func.get_lang, custmast.cod_tds),
DECODE(custmast.flg_cust_memo, NULL, NULL, Get_LOVPROD.Get_Fclov('SB_SPECIAL_REMARKS', Common_File_Func.get_lang, custmast.flg_cust_memo)),
inview.fcprod,
inview.fcprodbu,
inview.dt_last_mnt,
inview.file_dt,
inview.detail_type_cd,
'N',
'N',
'N',
'Fin Account',
'Y',
'N',
'Y',
'Fin Account',
Get_LOVPROD.Get_Fclov('INS_RELATION_TO_POLICYHOLDER', Common_File_Func.get_lang, '8'),
'Y',
'Y',
'Y'
FROM cbr_custmast custmast,
------------------------------------------------------------------------------------------------------------------------------------- ---------------
(SELECT /*+ ALL_ROWS */ MAX(prodbal.dat_last_mnt) dt_last_mnt, -------------------All Rows Modified by I-flex\SD\05-04-07--------------------
MAX(prodbal.file_date) file_dt,
prodbal.cod_cust_natl_id,
Get_Lovprod.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fcprod,
Get_Lovprod.Get_Fcprodbu(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fcprodbu,
prodint.detail_type_cd
FROM cbr_prod_bal prodbal,S_PROD_INT prodint
WHERE TO_CHAR(prodbal.cod_prod) = prodint.part_num
AND prodint.detail_type_cd IN ('FC-Saving','FC-TD')
AND prodbal.cod_cust_natl_id IN( SELECT cod_cust_natl_id
FROM cbr_fw_cust_mast fwcustmast
WHERE fwcustmast.dat_last_mnt BETWEEN lv_batch_min_run_dt AND lv_batch_max_run_dt)
GROUP BY prodbal.cod_cust_natl_id, prodint.detail_type_cd,

Get_Lovprod.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd),
Get_Lovprod.Get_Fcprodbu(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd)) inview
WHERE custmast.cod_cust_natl_id = inview.cod_cust_natl_id;

COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;

-- Count total number of records inserted into ODS_ASSET

lv_row_count := SQL%ROWCOUNT;
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Inserted : '||lv_row_count||' : Records.');
------------------------------------------------------------------------------------------------------------------------------------- -----------------------
INSERT /*+ APPEND */ INTO ODS_ASSET ----------------Append Modified by I-flex\SD\05\04\07-----------------
(ods_batch_id,
ast_asset_num,
ast_rev_num,
ast_start_dt,
ast_end_dt,
ast_extension_cd,
ast_value_basis_cd,
ast_version,
ast_comments,
ast_prod_name,
ast_current_bal,
ast_prod_bu,
ast_ship_dt,
ast_inception_dt,
fc_detail_type_cd,
ast_compundwrntyfl,
ast_undmfgwrntyflg,
ast_produndwrntyfl,
ast_type_cd,
ast_pr_con,
con_con_priv_flg,
ast_pr_postn,
x_pos_type_cd,
con_relationtypecd,
ext_attrib_09,
verify_flag,
uk_flag)

SELECT
pv_batch_type_num_i||DECODE(MOD(ROWNUM,lv_commit_size), 0, Common.ODS_Id_Seq_Next_Val, LPAD(ODS_Id_Seq.currval,6,'0')),
custmast.cod_cust_natl_id,
--gets branch name from account number
SUBSTR(custmast.cod_cust_natl_id,1,3),
custmast.dat_cust_open,
custmast.dat_cust_close,
DECODE(custmast.cod_cnt,NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_CONACT_WAY', Common_File_Func.get_lang, custmast.cod_cnt)),
DECODE(custmast.cod_card_issue_reason, NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_CARD_TYPE', Common_File_Func.get_lang, custmast.cod_card_issue_reason)),
Get_LOVPROD.GET_FCLOV('SB_TAX_CODE', Common_File_Func.get_lang, custmast.cod_tds),
DECODE(custmast.flg_cust_memo, NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_SPECIAL_REMARKS', Common_File_Func.get_lang, custmast.flg_cust_memo)),
inview.fc_asset_name,
inview.fc_asset_bal,
Get_LOVPROD.Get_Fcprodbu(inview.fc_product, inview.fc_detail_type_cd),
inview.fc_last_bal_dt,
inview.fc_last_batch_dt,
inview.fc_detail_type_cd,
'N',
'N',
'N',
'Fin Account',
'Y',
'N',
'Y',
'Fin Account',
--gets the value from S_LST_OF_VAL;
Get_LOVPROD.GET_FCLOV('INS_RELATION_TO_POLICYHOLDER', Common_File_Func.get_lang, '8'),
'Y',
'Y',
'Y'
FROM cbr_custmast custmast,cbr_fw_cust_mast fwcustmast,
(SELECT inview1.ast_asset_num,
inview1.fc_asset_bal,
inview1.fc_product,
inview1.fc_last_bal_dt,
inview1.fc_last_batch_dt,
inview1.fc_detail_type_cd,
inview1.fc_asset_name
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
FROM (SELECT /*+ ALL_ROWS */ odshldng.ast_asset_num ast_asset_num, -----------------All Rows modified by I-flex\SD\05\04\07-------------------
/*NVL(SUM(odshldng.hld_accrued_intr),0) */999 fc_asset_bal,
DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001') fc_product,
MAX(odshldng.hld_as_of_dt) fc_last_bal_dt,
MAX(odshldng.fc_file_date) fc_last_batch_dt,
prodint.detail_type_cd fc_detail_type_cd,
Get_LOVPROD.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fc_asset_name,
MAX(odshldng.verify_flag) verify_flag
FROM ODS_HLDNG odshldng,
S_PROD_INT prodint
WHERE odshldng.fc_product = prodint.part_num
AND prodint.detail_type_cd IN ('FC-Saving', 'FC-TD')
AND odshldng.ods_batch_id LIKE Common.Get_BTN(pv_batch_type_num_i, 'HOLDING')||'%'
AND odshldng.verify_flag ='Y'
GROUP BY ast_asset_num,prodint.detail_type_cd) inview1,
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------
(SELECT /*+ LEADING(custmat fwcustmast) */ /*+ Merge(inview) */ ast_asset_num ---------------'Leading' for joins modified by I-flex\SD\05\04\07-------------
FROM ODS_ASSET ast
WHERE ast.ODS_BATCH_ID LIKE pv_batch_type_num_i||'%'
AND ast.verify_flag = 'Y') inview2
WHERE inview1.ast_asset_num = inview2.AST_ASSET_NUM (+)
AND inview2.AST_ASSET_NUM IS NULL ) inview -- ******* 'inview2.AST_ASSET_NUM IS NULL' IS SAME AS "DECODE(inview2.AST_ASSET_NUM,NULL,'ACCEPT','REJECT') = 'ACCEPT' "
WHERE custmast.cod_cust_natl_id = fwcustmast.cod_cust_natl_id
AND custmast.cod_cust_natl_id = inview.ast_asset_num;

-- Count total number of records inserted into ODS_ASSET

lv_row_count := lv_row_count + SQL%ROWCOUNT;
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Inserted : '||lv_row_count||' : Records.');
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;


--
BEGIN
SELECT ODS_Id_Seq.currval
INTO lv_curr_val
FROM dual;
END;

lv_max_batch_num := pv_batch_type_num_i||LPAD(lv_curr_val, 6, '0');

-- Delete the old data from ODS for which new data is coming in this batch

Remove_Duplicates.Asset(lv_product_batch, pv_batch_type_num_i, lv_commit_size, lv_return_status);

IF lv_return_status = -1
THEN
RAISE Remove_Dup_Failed;
END IF;

Common.Reset_Ods_Stage (lv_status, pv_batch_type_num_i);

-- Call BCL_Batch_Run update function to update record in BCL_Batch_Run with end date and stage

Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,lv_row_count,lv_xsl_run_id,'TRANSLATION_COMPLETED');

-- Insert a new record in BCL_Batch_Run table for next process, in this case 'xformation started'

Common.bcl_insert_stage(lv_daily_run_id,pv_batch_type_num_i,'A',SYSDATE,'TRANSFORMATION_STARTED',lv_xfmn_run_id);

IF (lv_row_count >= 0)
THEN
-------------------------------------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO bcl_ods_batch_dtl (run_id, --------------Append Modified by I-flex\SD\05\04\07------------
ods_batch_id,
xslation_end_dt,
total_no_of_rows_processed,
no_of_error_rows)
SELECT lv_xsl_run_id,ods_batch_id,SYSDATE,count(*),NULL
FROM ODS_ASSET
WHERE ods_batch_id BETWEEN lv_min_batch_num AND lv_max_batch_num
GROUP BY lv_xsl_run_id,ods_batch_id,SYSDATE;

END IF;
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;


ELSE

Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Cannot proceed because of End_dt is Not Null.');

END IF;

EXCEPTION
WHEN Parameters_Not_Found THEN
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.RunId : '||TO_CHAR(lv_xsl_run_id)||' : Error returned in parameters for Asset Daily Translation program.');

Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');

WHEN Remove_Dup_Failed
THEN
Common.log_message (lv_product_batch,lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.Run_Id : '||TO_CHAR(lv_xsl_run_id)||' : Error returned in Remove_Duplicates.Asset');

Common.bcl_update_stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');

WHEN OTHERS THEN
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.RunId : '||TO_CHAR(lv_xsl_run_id)||' : '||SUBSTR(Sqlerrm, 1, 400));
ROLLBACK;

-- If an exception caught, update corresponding record in BCL_batch_run table with stage failed

Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');

END FC_Asset_Daily_Xlation_Test;
/
Re: tune this procedure [message #229752 is a reply to message #229748] Mon, 09 April 2007 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>,it is very urgent.
why is it urgent for free volunteers to answer your question(s).

When are you going to learn & use CODE tags?

[Updated on: Mon, 09 April 2007 22:50] by Moderator

Report message to a moderator

Re: tune this procedure [message #229765 is a reply to message #229752] Mon, 09 April 2007 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And send us money to make your job. Frown
Re: tune this procedure [message #229844 is a reply to message #229765] Tue, 10 April 2007 02:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
- Read the first topic (tips and tricks)
- Make sure you understand what is said and apply this to your message.
- And let it make the last thread on this subject! One more and see it removed.

MHE

[Updated on: Tue, 10 April 2007 03:01]

Report message to a moderator

Previous Topic: COMMIT IN TRIGGERS
Next Topic: Inserting multiple varchar2 values into a single CLOB row
Goto Forum:
  


Current Time: Wed Dec 07 22:20:06 CST 2016

Total time taken to generate the page: 0.25834 seconds