Home » RDBMS Server » Performance Tuning » Procedure runs slow whereas (11.2.0.1)
Procedure runs slow whereas [message #614028] Wed, 14 May 2014 10:21 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Procedure called from a PL/SQL package runs slow, whereas the individual sql statements in the procedure are pretty fast.
Also when i run the same procedure logic in an anonymous block, it gets executed fastly.

What could be the possible reasons?
Re: Procedure runs slow whereas [message #614029 is a reply to message #614028] Wed, 14 May 2014 10:24 Go to previous messageGo to next message
Roachcoach
Messages: 1224
Registered: May 2010
Location: UK
Senior Member
3:1 odds on processing row by row.

Need more data.
Re: Procedure runs slow whereas [message #614030 is a reply to message #614028] Wed, 14 May 2014 10:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2513
Registered: May 2013
Location: World Wide on the Web
Senior Member
SQL directly interacts with DB.
SQL and PLSQL engines are different.
You cannot compare the execution times between SQL and PLSQL. Context switchingbis one of the reasons.

What exactly is the issue? Please post complete details. Would you expect Oracle support to help with a TAR/SR with such minimal information? Please read the sticky on top of performance tuning forum. It will help you as well as us to get the required information.
Re: Procedure runs slow whereas [message #614031 is a reply to message #614030] Wed, 14 May 2014 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 23025
Registered: January 2009
Senior Member
You choose to repeatedly ignore Posting Guideline, we can choose to not answer your questions.

BlackSwan wrote on Wed, 19 October 2011 14:22
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Fri, 13 June 2008 23:45
More than 100 posts and you don't know to:
- give a meaningul title
- post with code tags
- post a test case: create tables and insert statements
- give the result for the test case.

Correct that.

Regards
Michel


Michel Cadot wrote on Mon, 02 July 2007 22:32
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel


Re: Procedure runs slow whereas [message #614034 is a reply to message #614031] Wed, 14 May 2014 14:33 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Test the output of the procedure with the parameters
---------------------------------------------------------

DECLARE
P_RUNDATE DATE;
P_ACCT_SRC_ID NUMBER;
P_ACCT_SRC_TYPE VARCHAR2(32767);
P_PRODUCT_ID VARCHAR2(32767);
P_RPT_CCY VARCHAR2(32767);
P_STREAM_ID NUMBER;
P_REFCUR CPORTDWDBO.PKG_SWAP_CUSTOM_FIN_REP.REF_CUR;
P_ERRCODE NUMBER;
P_ERRMESG VARCHAR2(32767);

BEGIN
P_RUNDATE := '19-FEB-14';
P_ACCT_SRC_ID := 727523;
P_ACCT_SRC_TYPE := 'G';
P_PRODUCT_ID := '2,6,7';
P_RPT_CCY := 'USD';
P_STREAM_ID := 2;
-- P_REFCUR := NULL; Modify the code to initialize this parameter
P_ERRCODE := NULL;
P_ERRMESG := NULL;

CPORTDWDBO.PKG_SWAP_CUSTOM_FIN_REP.PRC_CUST_SHORT_POSITIONS ( P_RUNDATE, P_ACCT_SRC_ID, P_ACCT_SRC_TYPE, P_PRODUCT_ID, P_RPT_CCY, P_STREAM_ID, P_REFCUR, P_ERRCODE, P_ERRMESG );

DBMS_OUTPUT.Put_Line('P_ERRCODE = ' || TO_CHAR(P_ERRCODE));
DBMS_OUTPUT.Put_Line('P_ERRMESG = ' || P_ERRMESG);


:rc0_P_REFCUR := P_REFCUR;

DBMS_OUTPUT.Put_Line('');

COMMIT;
END;

Actual procedure
-------------------

PROCEDURE PRC_CUST_SHORT_POSITIONS (p_Rundate IN DATE,
p_Acct_Src_Id IN NUMBER,
p_Acct_Src_Type IN VARCHAR2,
p_Product_Id IN VARCHAR2,
p_Rpt_Ccy IN VARCHAR2,
p_Stream_Id IN NUMBER,
p_Refcur OUT REF_CUR,
p_Errcode OUT NUMBER,
p_Errmesg OUT VARCHAR2)
IS
v_BusDateID_Num NUMBER;
v_CurrDate_Dt DATE;
v_PrevDateID_Num NUMBER;
v_PrevDate_Dt DATE;
v_MaxDate_Dt DATE;
v_Todate DATE;
V_Fromdate DATE;
BEGIN
BEGIN
processing.Dm_Proc_Truncate_Table ('TMP_OTHER_ACCOUNTS_TAB');
END;
BEGIN
processing.Dm_Proc_Truncate_Table ('TMP_SWAP_MTM_SUMM_ACCOUNTS');
END;


Pkg_Global_Report_Library.Prc_Load_Accounts ('TMP_OTHER_ACCOUNTS_TAB',
p_Acct_Src_Id,
p_Acct_Src_Type,
'51,58',
p_ErrCode,
p_ErrMesg);


Pkg_Global_Report_Library.Prc_Load_Swap_Accounts (
'TMP_SWAP_MTM_SUMM_ACCOUNTS',
p_Acct_Src_Id,
p_Acct_Src_Type,
'2,6,7',
p_ErrCode,
p_ErrMesg
);

-- To get Previous day column
SELECT D.DATE_ID
INTO v_BusDateID_Num
FROM D_DATE D
WHERE D.DATE_VALUE = p_Rundate;

SELECT PREVBUSDAY_DATE_ID
INTO v_PrevDateID_Num
FROM D_Business_Days
WHERE DATE_ID = v_BusDateID_Num;

SELECT D.DATE_VALUE
INTO v_PrevDate_Dt
FROM D_DATE D
WHERE D.DATE_ID = v_PrevDateID_Num;


--To Enable reports to run for a backdate

SELECT D.DATE_VALUE
INTO v_CurrDate_Dt
FROM D_DATE D
WHERE D.DATE_VALUE = p_Rundate;

IF v_CurrDate_Dt > p_Rundate
THEN
IF TO_NUMBER (TO_CHAR (p_Rundate, 'mm')) <>
TO_NUMBER (TO_CHAR (v_CurrDate_Dt, 'mm'))
THEN
SELECT D1.Date_Value
INTO v_MaxDate_Dt
FROM D_DATE D, D_BUSINESS_DAYS B, D_DATE D1
WHERE D.Date_Value = p_Rundate
AND D.Date_Id = B.Date_Id
AND B.Curmon_Lastbusdate_Id = D1.Date_Id;
ELSE
v_Maxdate_Dt := v_CurrDate_Dt;
END IF;
ELSE
v_Maxdate_Dt := p_Rundate;
END IF;



IF p_Stream_Id = 1
THEN -- COB Data
OPEN p_Refcur FOR
SELECT Account_Id,
Account_Name, -- added as per the artifact artf939755
Value_Date,
Local_Ccy,
Secu_Type,
Cusip,
Sec_Description,
Sedol,
Isin,
Ric_Code,
Symbol,
Country_Name,
Sdayqty,
Tdayqty,
Excessborrowqty,
Coll_Price,
Gc_Nongc,
Fee_Rebate_Indr,
Base_Rate AS Base_Rate,
Stock_Loan_Fee,
Spread,
Vdcv,
Base_Rt_Bench,
Gross_Rebate,
(St_Loan_Cost) / 365 AS Stock_Loan_Cost,
Net_Rebate_Rate,
( (Net_Rebate_Rate * Vdcv * -1) / 365) / 100
AS Net_Rebate,
Local_Price,
Lmv,
Swap_Ind,
Legalentity_Name -- added as per the artifact artf939755
FROM ( SELECT Account_Id,
Account_Name,
Value_Date,
Local_Ccy,
Secu_Type,
Cusip,
Sec_Description,
Sedol,
Isin,
Ric_Code,
Symbol,
Country_Name,
Sdayqty,
Tdayqty,
SUM (Quantity_Faq + Quantity_Pos)
AS Excessborrowqty,
Coll_Price,
Gc_Nongc,
Fee_Rebate_Indr,
Base_Rate AS Base_Rate,
Stock_Loan_Fee,
Spread,
Vdcv,
Base_Rt_Bench,
( (Grss_Rebate * -1) / 365) / 100
AS Gross_Rebate,
St_Loan_Cost,
Net_Rebate_Rate,
Local_Price,
Lmv,
Swap_Ind,
Legalentity_name
FROM (SELECT ACC.SOURCE_ID AS ACCOUNT_ID,
ACC.ORIGINAL_NAME As Account_Name,
v_PrevDate_Dt AS VALUE_DATE,
POS.LOCAL_CCY AS Local_Ccy,
-- CASE
-- WHEN I.INSTRUMENT_TYPE_ID = 0
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 1
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 4
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 5
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 6
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 7
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 8
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 10
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 11
-- THEN
-- 'CV'
-- WHEN I.INSTRUMENT_TYPE_ID = 12
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 13
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 15
-- THEN
-- 'W'
-- WHEN I.INSTRUMENT_TYPE_ID = 16
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 18
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 19
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 20
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 21
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 22
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 24
-- THEN
-- 'W'
-- WHEN I.INSTRUMENT_TYPE_ID = 25
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 26
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 28
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 30
-- THEN
-- 'CV'
-- WHEN I.INSTRUMENT_TYPE_ID = 42
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 46
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 97
-- THEN
-- 'ABS'
-- WHEN I.INSTRUMENT_TYPE_ID = 98
-- THEN
-- 'FX'
-- WHEN I.INSTRUMENT_TYPE_ID = 113
-- THEN
-- 'MBS'
-- ELSE
-- 'EQ'
-- END
INSTYPE.INSTRUMENT_TYPE_DESC AS SECU_TYPE,
I.CUSIP AS CUSIP,
I.instrument_name AS SEC_DESCRIPTION,
I.SEDOL AS SEDOL,
I.ISIN AS ISIN,
I.RIC_CODE AS RIC_CODE,
I.INSTRUMENT_ID AS SYMBOL,
I.COUNTRY_CODE AS COUNTRY_NAME,
POS.SD_QUANTITY AS SDAYQTY,
POS.TD_QUANTITY AS TDAYQTY,
AR.PRICE * FX.FX_RATE AS Coll_Price,
AR.GC_NONGC,
AR.FEE_REBATE_INDR
AS FEE_REBATE_INDR,
AR.BASE_RATE,
ar.STOCK_LOAN_FEE,
AR.QUANTITY_FAQ,
CASE
WHEN POS.FACTOR < 1
THEN
POS.S_D_ORIGINAL_FACE
WHEN POS.FACTOR > 1
THEN
COALESCE (
POS.S_D_ORIGINAL_FACE,
POS.SD_QUANTITY,
0
)
ELSE
NVL (POS.SD_QUANTITY, 0)
END
AS QUANTITY_POS,
0 AS spread,
POS.SD_MKT_VALUE * FX.FX_RATE
AS VDCV,
ar.STOCK_LOAN_FEE
* POS.SD_MKT_VALUE
* FX.FX_RATE
AS ST_LOAN_COST,
AD.DRBASERATE_CODE AS BASE_RT_BENCH,
POS.SD_MKT_VALUE
* BASE_RATE
* FX.FX_RATE
AS GRSS_REBATE,
POS.SD_MKT_PRICE AS LOCAL_PRICE,
(POS.SD_MKT_VALUE * -1) AS LMV,----MODIFIED AS PER ARTF95112
'PB' AS SWAP_IND,
AR.NET_REBATE_RATE,
LEGE.NAME_LONG AS Legalentity_Name
FROM F_POSITION_COB Pos,
TMP_OTHER_ACCOUNTS_TAB TEMP,
D_ACCOUNT ACC,
D_INSTRUMENT I,
D_INSTRUMENT_TYPE INSTYPE,
D_ACCOUNT_IRDETAIL IR,
F_FX_CONVERSION_RATE fx,
F_GFC_ASSET_DETAIL_COB AD,
D_LEGALENTITY LEGE, -- added as per the artifact artf939755
( SELECT AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
AVG (AR.PRICE) AS price,
AR.CURRENCY,
AR.FEE_REBATE_INDR,
DECODE (AR.HOT_STOCK_INDR,
'Y', 'Non-GC',
'GC')
AS GC_NONGC,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
ELSE
NULL
END
AS BASE_RATE,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
- AVG(NVL (
AR.REBATE_RATE,
0
))
ELSE
AVG(NVL (
AR.REBATE_RATE,
0
))
END
AS STOCK_LOAN_FEE,
SUM(NVL (
(AR.QUANTITY * -1),
0
))
AS QUANTITY_FAQ,
AR.Business_Date,
AVG (
NVL (AR.REBATE_RATE, 0)
)
AS NET_REBATE_RATE
FROM F_GFC_ACCOUNT_REBATE_COB AR,
D_ACCOUNT_IRDETAIL IR,
TMP_OTHER_ACCOUNTS_TAB TEMP
WHERE AR.Effective_Date BETWEEN p_rundate
AND v_Maxdate_Dt
AND AR.TYPE = 'BOR'
AND IR.Account_id =
AR.Account_Id
AND AR.Account_id=TEMP.Account_id
GROUP BY AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
FEE_REBATE_INDR,
AR.CURRENCY,
AR.HOT_STOCK_INDR,
IR.GENEVA_PRODUCT,
AR.Business_Date) AR,
D_ACCOUNT_ROLLUP RLP
WHERE Pos.Business_Date = p_rundate
AND ad.business_date = p_rundate
AND ar.business_date = p_rundate
AND fx.business_date = p_rundate
AND ar.business_date = p_rundate
AND I.INSTRUMENT_TYPE_ID <> 28
AND I.INSTRUMENT_TYPE_ID = INSTYPE.INSTRUMENT_TYPE_ID
AND Pos.Account_id = RLP.ACCOUNT_ID
AND IR.Account_id = RLP.ACCOUNT_ID
AND RLP.ACCOUNT_ID = AR.ACCOUNT_ID
AND AD.Account_id =
ROLLUP_ACCOUNT_ID
AND RLP.ROLLUP_ACCOUNT_ID =
temp.ACCOUNT_ID
AND Pos.Account_id = ACC.Account_Id
AND IR.Account_id = ACC.Account_Id
AND ACC.ACCOUNT_ID = AR.ACCOUNT_ID
AND Pos.Account_id = IR.Account_Id
AND Pos.Account_Id = AR.Account_Id
AND IR.Account_id = AR.Account_Id
AND Pos.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND Pos.INSTRUMENT_ID =
AR.INSTRUMENT_ID
AND AR.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND AD.BUSINESS_DATE =
FX.BUSINESS_DATE
AND fx.business_date =
Pos.Business_Date
AND pos.local_ccy = fx.from_ccy
AND ad.ccy = ar.currency
AND ad.ccy = pos.local_ccy
AND ad.ccy = fx.from_ccy
AND FX.TO_CCY = p_Rpt_Ccy
AND NVL (POS.SD_QUANTITY, 0) <= 0
AND LEGE.LEGALENTITY_ID=ACC.LEGALENTITY_ID)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
VDCV,
SPREAD,
GRSS_REBATE,
BASE_RATE,
STOCK_LOAN_FEE,
LOCAL_PRICE,
LMV,
BASE_RT_BENCH,
Coll_Price,
SWAP_IND,
QUANTITY_FAQ,
QUANTITY_POS,
NET_REBATE_RATE,
ST_LOAN_COST,
Legalentity_Name)
UNION ALL
SELECT ACCOUNT_ID,
Account_Name, -- added as per the artifact artf939755
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
BASE_RT_BENCH,
GROSS_REBATE,
(ST_LOAN_COST) / 365 AS STOCK_LOAN_COST,
NET_REBATE_RATE,
( (NET_REBATE_RATE * VDCV * -1) / 365) / 100
AS NET_REBATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_Name -- added as per the artifact artf939755
FROM ( SELECT ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
( ( (VDCV * BASE_RATE * -1) / 365) / 100)
AS GROSS_REBATE,
VDCV * STOCK_LOAN_FEE AS ST_LOAN_COST,
NET_REBATE_RATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_name
FROM ( SELECT TO_CHAR (BSUM.SWAP_NUM)
AS ACCOUNT_ID,
ACC.ORIGINAL_NAME As Account_Name,
v_PrevDate_Dt AS VALUE_DATE,
I.CUSIP AS CUSIP,
I.instrument_name AS SEC_DESCRIPTION,
I.SEDOL AS SEDOL,
I.ISIN AS ISIN,
I.RIC_CODE AS RIC_CODE,
BSUM.Swift_Code AS Local_Ccy,
IT.instrument_type_desc AS Secu_Type,
I.INSTRUMENT_ID AS SYMBOL,
I.COUNTRY_CODE AS COUNTRY_NAME,
(BSUM.SETTLED_QUANTITY*-1) AS SDAYQTY,--------AS PER ARTF 951112
SUM (BSUM.QUANTITY * -1) AS TDAYQTY,
0 AS ExcessBorrowQty,
BSUM.MARKET_PRICE * BSUM.FX_RATE
AS Coll_Price,
BASKET.base_rate AS BASE_RT_BENCH,
NULL AS GC_NONGC,
intdet.interest_rate AS BASE_RATE,
0 AS STOCK_LOAN_FEE,
'F' AS FEE_REBATE_INDR,--modified as per artf951112
intdet.notional * -1 AS VDCV,
BSUM.MARKET_PRICE AS LOCAL_PRICE,
CASE
WHEN (BSUM.LONG_SHORT_IND = 1)
THEN
(BSUM.SETTLED_QUANTITY
* BSUM.MARKET_PRICE * -1) -------MODIFIED AS PER ARTF95112
ELSE
NULL
END
AS LMV,
INTDET.spread AS SPREAD,
intdet.net_rate AS NET_REBATE_RATE,
'SWAP' AS SWAP_IND,
LEGE.NAME_LONG AS Legalentity_Name
FROM F_SWAP_BASKET_SUMMARY_COB BSUM,
F_SWAP_BASKET_SWAP_COB BASKET,
TMP_SWAP_MTM_SUMM_ACCOUNTS Tmp,
F_SWAP_INTEREST_DETAIL_COB INTDET,
F_FX_CONVERSION_RATE FX,
D_INSTRUMENT I,
D_INSTRUMENT_TYPE IT,
D_ACCOUNT ACC, -- added as per the artifact artf939755
D_LEGALENTITY LEGE, -- added as per the artifact artf939755
( SELECT Business_date,
INTDET.ACCOUNT_ID,
INTDET.INSTRUMENT_ID,
MAX (End_Date) AS End_Date
FROM F_SWAP_INTEREST_DETAIL_COB INTDET,
TMP_SWAP_MTM_SUMM_ACCOUNTS Tmp
WHERE Business_date = p_rundate
AND (INTDET.ACCOUNT_ID =
TMP.ACCOUNT_ID
OR INTDET.PARENT_ID =
TMP.PARENT_ID
OR INTDET.LEGALENTITY_ID =
TMP.LEGALENTITY_ID)
GROUP BY Business_date,
INTDET.ACCOUNT_ID,
INTDET.INSTRUMENT_ID)
IND
WHERE BSUM.BUSINESS_DATE = p_rundate
AND intdet.business_date = p_rundate
AND basket.business_date =p_rundate
AND BSUM.LONG_SHORT_IND = 1
AND BSUM.ACCOUNT_ID =
NVL (Tmp.Account_Id,
BSUM.Account_Id)
AND BSUM.Account_Id =
INTDET.ACCOUNT_ID
AND BSUM.Account_Id =
BASKET.ACCOUNT_ID
AND INTDET.ACCOUNT_ID =
BASKET.ACCOUNT_ID
AND fx.business_date = p_rundate
AND INTDET.INTEREST_TYPE IN
('Position Interest',
'Intra Period Interest')
AND BSUM.BUSINESS_DATE =
fx.business_date
AND BSUM.Business_Date =
INTDET.Business_Date
AND fx.business_date =
INTDET.Business_Date
AND fx.business_date =
BSUM.Business_Date
AND FX.FROM_CCY = BSUM.SWIFT_CODE
AND FX.TO_CCY =p_Rpt_Ccy
AND BSUM.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND BSUM.INSTRUMENT_ID =
INTDET.INSTRUMENT_ID
AND INTDET.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND IT.INSTRUMENT_TYPE_ID =
I.INSTRUMENT_TYPE_ID
AND intdet.swap_num =
basket.swap_num
AND intdet.Parent_Id =
basket.Parent_Id
AND intdet.LegalEntity_Id =
basket.LegalEntity_Id
AND fx.business_date =
basket.business_date
AND NVL (tmp.parent_id,
intdet.Parent_Id) =
basket.Parent_Id
AND ind.account_id =
INTDET.account_ID
AND IND.INSTRUMENT_ID =
Intdet.INSTRUMENT_ID
AND intdet.end_date = ind.end_date
AND ind.account_id =
basket.account_ID
AND ind.account_id = bsum.account_ID
AND BSUM.ACCOUNT_ID=ACC.ACCOUNT_ID
AND LEGE.LEGALENTITY_ID=ACC.LEGALENTITY_ID -- added as per the artifact artf939755

GROUP BY ACC.ORIGINAL_NAME,
BSUM.Business_Date,
BSUM.SWAP_NUM,
BSUM.SWIFT_CODE,
I.CUSIP,
I.instrument_name,
I.SEDOL,
I.ISIN,
I.RIC_CODE,
I.INSTRUMENT_ID,
I.COUNTRY_CODE,
BSUM.SWAP_NUM,
BSUM.Swift_Code,
IT.instrument_type_desc,
BSUM.SETTLED_QUANTITY,
BSUM.QUANTITY,
BASKET.base_rate,
INTDET.spread,
BSUM.LONG_SHORT_IND,
BSUM.MARKET_PRICE,
BSUM.FX_RATE,
intdet.interest_rate,
intdet.notional,
intdet.net_rate,
LEGE.NAME_LONG)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
LOCAL_PRICE,
LMV,
SWAP_IND,
NET_REBATE_RATE,
Legalentity_name)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
GROSS_REBATE,
ST_LOAN_COST,
NET_REBATE_RATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_name;
ELSE --ACOB data
OPEN P_REFCUR FOR
SELECT ACCOUNT_ID,
Account_Name, -- added as per the artifact artf939755
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE AS BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
BASE_RT_BENCH,
GROSS_REBATE,
(ST_LOAN_COST) / 365 AS STOCK_LOAN_COST,
NET_REBATE_RATE,
( (NET_REBATE_RATE * VDCV * -1) / 365) / 100
AS NET_REBATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_Name -- added as per the artifact artf939755
FROM ( SELECT ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
SUM (QUANTITY_FAQ + QUANTITY_POS)
AS ExcessBorrowQty,
Coll_Price,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE AS BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
BASE_RT_BENCH,
( (GRSS_REBATE * -1) / 365) / 100
AS GROSS_REBATE,
ST_LOAN_COST,
NET_REBATE_RATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_name
FROM (SELECT ACC.SOURCE_ID AS ACCOUNT_ID,
ACC.ORIGINAL_NAME As Account_Name,
v_PrevDate_Dt AS VALUE_DATE,
POS.LOCAL_CCY AS Local_Ccy,
-- CASE
-- WHEN I.INSTRUMENT_TYPE_ID = 0
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 1
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 4
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 5
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 6
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 7
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 8
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 10
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 11
-- THEN
-- 'CV'
-- WHEN I.INSTRUMENT_TYPE_ID = 12
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 13
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 15
-- THEN
-- 'W'
-- WHEN I.INSTRUMENT_TYPE_ID = 16
-- THEN
-- 'PS'
-- WHEN I.INSTRUMENT_TYPE_ID = 18
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 19
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 20
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 21
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 22
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 24
-- THEN
-- 'W'
-- WHEN I.INSTRUMENT_TYPE_ID = 25
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 26
-- THEN
-- 'B'
-- WHEN I.INSTRUMENT_TYPE_ID = 28
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 30
-- THEN
-- 'CV'
-- WHEN I.INSTRUMENT_TYPE_ID = 42
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 46
-- THEN
-- 'EQ'
-- WHEN I.INSTRUMENT_TYPE_ID = 97
-- THEN
-- 'ABS'
-- WHEN I.INSTRUMENT_TYPE_ID = 98
-- THEN
-- 'FX'
-- WHEN I.INSTRUMENT_TYPE_ID = 113
-- THEN
-- 'MBS'
-- ELSE
-- 'EQ'
-- END
INSTYPE.INSTRUMENT_TYPE_DESC AS SECU_TYPE,
I.CUSIP AS CUSIP,
I.instrument_name AS SEC_DESCRIPTION,
I.SEDOL AS SEDOL,
I.ISIN AS ISIN,
I.RIC_CODE AS RIC_CODE,
I.INSTRUMENT_ID AS SYMBOL,
I.COUNTRY_CODE AS COUNTRY_NAME,
POS.SETTLED_QUANTITY AS SDAYQTY,
POS.TRADED_QUANTITY AS TDAYQTY,
AR.PRICE * FX.FX_RATE AS Coll_Price,
AR.GC_NONGC,
AR.FEE_REBATE_INDR
AS FEE_REBATE_INDR,
AR.BASE_RATE,
ar.STOCK_LOAN_FEE,
AR.QUANTITY_FAQ,
CASE
WHEN POS.FACTOR < 1
THEN
POS.S_D_ORIGINAL_FACE
WHEN POS.FACTOR > 1
THEN
COALESCE (
POS.S_D_ORIGINAL_FACE,
POS.SETTLED_QUANTITY,
0
)
ELSE
NVL (POS.SETTLED_QUANTITY, 0)
END
AS QUANTITY_POS,
0 AS spread,
POS.SETTLED_MKT_VALUE * FX.FX_RATE
AS VDCV,
STOCK_LOAN_FEE
* POS.SETTLED_MKT_VALUE
* FX.FX_RATE
AS ST_LOAN_COST,
AD.DRBASERATE_CODE AS BASE_RT_BENCH,
POS.SETTLED_MKT_VALUE
* BASE_RATE
* FX.FX_RATE
AS GRSS_REBATE,
POS.SETTLE_MKT_PRICE AS LOCAL_PRICE,
(POS.SETTLED_MKT_VALUE * -1) AS LMV,--------MODIFIED AS PER ARTF95112
'PB' AS SWAP_IND,
AR.NET_REBATE_RATE,
LEGE.NAME_LONG AS Legalentity_Name
FROM F_POSITIONS Pos,
TMP_OTHER_ACCOUNTS_TAB TEMP,
D_ACCOUNT ACC,
D_INSTRUMENT I,
D_INSTRUMENT_TYPE INSTYPE,
D_ACCOUNT_IRDETAIL IR,
F_FX_CONVERSION_RATE fx,
F_GFC_ASSET_DETAIL AD,
D_LEGALENTITY LEGE, -- added as per the artifact artf939755
( SELECT AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
AVG (AR.PRICE) AS price,
AR.CURRENCY,
AR.FEE_REBATE_INDR,
DECODE (AR.HOT_STOCK_INDR,
'Y', 'Non-GC',
'GC')
AS GC_NONGC,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
ELSE
NULL
END
AS BASE_RATE,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
- AVG(NVL (
AR.REBATE_RATE,
0
))
ELSE
AVG(NVL (
AR.REBATE_RATE,
0
))
END
AS STOCK_LOAN_FEE,
SUM(NVL (
(AR.QUANTITY * -1),
0
))
AS QUANTITY_FAQ,
AR.Business_Date,
AVG (
NVL (AR.REBATE_RATE, 0)
)
AS NET_REBATE_RATE
FROM F_GFC_ACCOUNT_REBATE AR,
D_ACCOUNT_IRDETAIL IR,
TMP_OTHER_ACCOUNTS_TAB TEMP
WHERE AR.Effective_Date BETWEEN p_rundate
AND v_Maxdate_Dt
AND AR.TYPE = 'BOR'
AND IR.Account_id =
AR.Account_Id
AND AR.Account_id=Temp.Account_Id
GROUP BY AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
FEE_REBATE_INDR,
AR.CURRENCY,
AR.HOT_STOCK_INDR,
IR.GENEVA_PRODUCT,
AR.Business_Date) AR,
D_ACCOUNT_ROLLUP RLP
WHERE Pos.Business_Date = p_rundate
AND ad.business_date = p_rundate
AND ar.business_date = p_rundate
AND fx.business_date = p_rundate
AND ar.business_date = p_rundate
AND I.INSTRUMENT_TYPE_ID <> 28
AND I.INSTRUMENT_TYPE_ID = INSTYPE.INSTRUMENT_TYPE_ID--added
AND Pos.Account_id = RLP.ACCOUNT_ID
AND IR.Account_id = RLP.ACCOUNT_ID
AND RLP.ACCOUNT_ID = AR.ACCOUNT_ID
AND AD.Account_id =
ROLLUP_ACCOUNT_ID
AND RLP.ROLLUP_ACCOUNT_ID =
temp.ACCOUNT_ID
AND Pos.Account_id = ACC.Account_Id
AND IR.Account_id = ACC.Account_Id
AND ACC.ACCOUNT_ID = AR.ACCOUNT_ID
AND Pos.Account_id = IR.Account_Id
AND Pos.Account_Id = AR.Account_Id
AND IR.Account_id = AR.Account_Id
AND Pos.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND Pos.INSTRUMENT_ID =
AR.INSTRUMENT_ID
AND AR.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND AD.BUSINESS_DATE =
FX.BUSINESS_DATE
AND fx.business_date =
Pos.Business_Date
AND pos.local_ccy = fx.from_ccy
AND ad.ccy = ar.currency
AND ad.ccy = pos.local_ccy
AND ad.ccy = fx.from_ccy
AND FX.TO_CCY = p_Rpt_Ccy
AND NVL (POS.SETTLED_QUANTITY, 0) <=
0
AND LEGE.LEGALENTITY_ID=ACC.LEGALENTITY_ID)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
VDCV,
SPREAD,
GRSS_REBATE,
BASE_RATE,
STOCK_LOAN_FEE,
LOCAL_PRICE,
LMV,
BASE_RT_BENCH,
Coll_Price,
SWAP_IND,
QUANTITY_FAQ,
QUANTITY_POS,
NET_REBATE_RATE,
ST_LOAN_COST,
Legalentity_Name)
UNION ALL
SELECT ACCOUNT_ID,
Account_Name, -- added as per the artifact artf939755
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
BASE_RT_BENCH,
GROSS_REBATE,
(ST_LOAN_COST) / 365 AS STOCK_LOAN_COST,
NET_REBATE_RATE,
( (NET_REBATE_RATE * VDCV * -1) / 365) / 100
AS NET_REBATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_Name -- added as per the artifact artf939755
FROM ( SELECT ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
( ( (VDCV * BASE_RATE * -1) / 365) / 100)
AS GROSS_REBATE,
VDCV * STOCK_LOAN_FEE AS ST_LOAN_COST,
NET_REBATE_RATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_name
FROM ( SELECT TO_CHAR (BSUM.SWAP_NUM)
AS ACCOUNT_ID,
ACC.ORIGINAL_NAME As Account_Name,
v_PrevDate_Dt AS VALUE_DATE,
I.CUSIP AS CUSIP,
I.instrument_name AS SEC_DESCRIPTION,
I.SEDOL AS SEDOL,
I.ISIN AS ISIN,
I.RIC_CODE AS RIC_CODE,
BSUM.Swift_Code AS Local_Ccy,
IT.instrument_type_desc AS Secu_Type,
I.INSTRUMENT_ID AS SYMBOL,
I.COUNTRY_CODE AS COUNTRY_NAME,
(BSUM.SETTLED_QUANTITY * -1) AS SDAYQTY,----AS PER ARTF95112
SUM (BSUM.QUANTITY * -1) AS TDAYQTY,
0 AS ExcessBorrowQty,
BSUM.MARKET_PRICE * BSUM.FX_RATE
AS Coll_Price,
BASKET.base_rate AS BASE_RT_BENCH,
NULL AS GC_NONGC,
intdet.interest_rate AS BASE_RATE,
0 AS STOCK_LOAN_FEE,
'F' AS FEE_REBATE_INDR,
intdet.notional * -1 AS VDCV,
BSUM.MARKET_PRICE AS LOCAL_PRICE,
CASE
WHEN (BSUM.LONG_SHORT_IND = 1)
THEN
(BSUM.SETTLED_QUANTITY
* BSUM.MARKET_PRICE*-1) ----------MODIFIED AS PER ARTF95112
ELSE
NULL
END
AS LMV,
INTDET.spread AS SPREAD,
intdet.net_rate AS NET_REBATE_RATE,
'SWAP' AS SWAP_IND,
LEGE.NAME_LONG AS Legalentity_Name
FROM F_BASKET_SUMMARY BSUM,
F_ESS_BASKET_SWAP BASKET,
TMP_SWAP_MTM_SUMM_ACCOUNTS Tmp,
F_SWAP_INTEREST_DETAIL INTDET,
F_FX_CONVERSION_RATE FX,
D_INSTRUMENT I,
D_INSTRUMENT_TYPE IT,
D_ACCOUNT ACC, -- added as per the artifact artf939755
D_LEGALENTITY LEGE, -- added as per the artifact artf939755

( SELECT Business_date,
INTDET.ACCOUNT_ID,
INTDET.INSTRUMENT_ID,
MAX (End_Date) AS End_Date
FROM F_SWAP_INTEREST_DETAIL INTDET,
TMP_SWAP_MTM_SUMM_ACCOUNTS Tmp
WHERE Business_date = p_rundate
AND (INTDET.ACCOUNT_ID =
TMP.ACCOUNT_ID
OR INTDET.PARENT_ID =
TMP.PARENT_ID
OR INTDET.LEGALENTITY_ID =
TMP.LEGALENTITY_ID)
GROUP BY Business_date,
INTDET.ACCOUNT_ID,
INTDET.INSTRUMENT_ID)
IND
WHERE BSUM.BUSINESS_DATE = p_rundate
AND intdet.business_date = p_rundate
AND basket.business_date = p_rundate
AND BSUM.LONG_SHORT_IND = 1
AND BSUM.ACCOUNT_ID =
NVL (Tmp.Account_Id,
BSUM.Account_Id)
AND BSUM.Account_Id =
INTDET.ACCOUNT_ID
AND BSUM.Account_Id =
BASKET.ACCOUNT_ID
AND INTDET.ACCOUNT_ID =
BASKET.ACCOUNT_ID
AND INTDET.INTEREST_TYPE IN
('Position Interest',
'Intra Period Interest')
AND fx.business_date = p_rundate
AND BSUM.BUSINESS_DATE =
fx.business_date
AND BSUM.Business_Date =
INTDET.Business_Date
AND fx.business_date =
INTDET.Business_Date
AND fx.business_date =
BSUM.Business_Date
AND FX.BUSINESS_DATE = p_rundate
AND FX.FROM_CCY = BSUM.SWIFT_CODE
AND FX.TO_CCY = p_Rpt_Ccy
AND BSUM.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND BSUM.INSTRUMENT_ID =
INTDET.INSTRUMENT_ID
AND INTDET.INSTRUMENT_ID =
I.INSTRUMENT_ID
AND IT.INSTRUMENT_TYPE_ID =
I.INSTRUMENT_TYPE_ID
AND intdet.swap_num =
basket.swap_num
AND intdet.Parent_Id =
basket.Parent_Id
AND fx.business_date =
basket.business_date
AND NVL (tmp.parent_id,
intdet.Parent_Id) =
basket.Parent_Id
AND intdet.LegalEntity_Id =
basket.LegalEntity_Id
AND ind.account_id =
INTDET.account_ID
AND IND.INSTRUMENT_ID =
Intdet.INSTRUMENT_ID
AND intdet.end_date = ind.end_date
AND ind.account_id =
basket.account_ID
AND ind.account_id = bsum.account_ID
AND BSUM.ACCOUNT_ID=ACC.ACCOUNT_ID
AND LEGE.LEGALENTITY_ID=ACC.LEGALENTITY_ID -- added as per the artifact artf939755
GROUP BY ACC.ORIGINAL_NAME,
BSUM.Business_Date,
BSUM.SWAP_NUM,
BSUM.SWIFT_CODE,
I.CUSIP,
I.instrument_name,
I.SEDOL,
I.ISIN,
I.RIC_CODE,
I.INSTRUMENT_ID,
I.COUNTRY_CODE,
BSUM.SWAP_NUM,
BSUM.Swift_Code,
IT.instrument_type_desc,
BSUM.SETTLED_QUANTITY,
BSUM.QUANTITY,
BASKET.base_rate,
INTDET.spread,
BSUM.LONG_SHORT_IND,
BSUM.MARKET_PRICE,
BSUM.FX_RATE,
intdet.interest_rate,
intdet.notional,
intdet.net_rate,
LEGE.NAME_LONG)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
LOCAL_PRICE,
LMV,
SWAP_IND,
NET_REBATE_RATE,
Legalentity_name)
GROUP BY ACCOUNT_ID,
Account_Name,
VALUE_DATE,
Local_Ccy,
SECU_TYPE,
CUSIP,
SEC_DESCRIPTION,
SEDOL,
ISIN,
RIC_CODE,
SYMBOL,
COUNTRY_NAME,
SDAYQTY,
TDAYQTY,
ExcessBorrowQty,
Coll_Price,
BASE_RT_BENCH,
GC_NONGC,
FEE_REBATE_INDR,
BASE_RATE,
STOCK_LOAN_FEE,
spread,
VDCV,
GROSS_REBATE,
ST_LOAN_COST,
NET_REBATE_RATE,
LOCAL_PRICE,
LMV,
SWAP_IND,
Legalentity_name;
END IF;
EXCEPTION
WHEN OTHERS
THEN
P_ERRCODE := SQLCODE;
P_ERRMESG := SQLERRM;
END PRC_CUST_SHORT_POSITIONS;



Re: Procedure runs slow whereas [message #614040 is a reply to message #614034] Wed, 14 May 2014 18:38 Go to previous messageGo to next message
BlackSwan
Messages: 23025
Registered: January 2009
Senior Member
compare & contrast results from SQL_TRACE=TRUE for both named & anonymous procedures.
compare & contrast EXPLAIN PLAN for each SQL statement

If you used Global Temporary Tables, you would not need the TRUNCATE procedures.
Re: Procedure runs slow whereas [message #614071 is a reply to message #614040] Thu, 15 May 2014 09:36 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member

'TMP_OTHER_ACCOUNTS_TAB' and 'TMP_SWAP_MTM_SUMM_ACCOUNTS' are temporary tables. But i dont see any issues in truncating them. I tested a seperate block until some of the code and it works fine.

declare
P_ACCT_SRC_ID NUMBER;
P_ACCT_SRC_TYPE VARCHAR2(32767);
P_PRODUCT_ID VARCHAR2(32767);
P_ERRCODE NUMBER:=null;
P_ERRMESG VARCHAR2(32767):=null;


begin
P_ACCT_SRC_ID := 727523;
P_ACCT_SRC_TYPE := 'G';
P_PRODUCT_ID := '2,6,7';


processing.Dm_Proc_Truncate_Table ('TMP_OTHER_ACCOUNTS_TAB');


processing.Dm_Proc_Truncate_Table ('TMP_SWAP_MTM_SUMM_ACCOUNTS');


Pkg_Global_Report_Library.Prc_Load_Accounts ('TMP_OTHER_ACCOUNTS_TAB',
p_Acct_Src_Id,
p_Acct_Src_Type,
'51,58',
p_ErrCode,
p_ErrMesg);


Pkg_Global_Report_Library.Prc_Load_Swap_Accounts (
'TMP_SWAP_MTM_SUMM_ACCOUNTS',
p_Acct_Src_Id,
p_Acct_Src_Type,
'2,6,7',
p_ErrCode,
p_ErrMesg
);
end;
Re: Procedure runs slow whereas [message #614073 is a reply to message #614071] Thu, 15 May 2014 09:43 Go to previous message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Here is the count of code from each table

select count(*) from F_POSITION_COB Pos --64182397

select count(*) from TMP_OTHER_ACCOUNTS_TAB --106( after truncate and load)

select count(*) from D_ACCOUNT ---228887

select count(*) from D_INSTRUMENT ---2517347

select count(*) from D_INSTRUMENT_TYPE --112

select count(*) from D_ACCOUNT_IRDETAIL --16392

select count(*) from F_FX_CONVERSION_RATE ---2555272

select count(*) from F_GFC_ASSET_DETAIL_COb ---2318641

select count(*) from D_LEGALENTITY LEGE --49302

select count(*) from F_GFC_ACCOUNT_REBATE_COB AR ---82270723

select count(*) from D_ACCOUNT_IRDETAIL --16392


The inline subquery takes a lot of time when run seperately (around 30 minutes)

SELECT AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
AVG (AR.PRICE) AS price,
AR.CURRENCY,
AR.FEE_REBATE_INDR,
DECODE (AR.HOT_STOCK_INDR,
'Y', 'Non-GC',
'GC')
AS GC_NONGC,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
ELSE
NULL
END
AS BASE_RATE,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
- AVG(NVL (
AR.REBATE_RATE,
0
))
ELSE
AVG(NVL (
AR.REBATE_RATE,
0
))
END
AS STOCK_LOAN_FEE,
SUM(NVL (
(AR.QUANTITY * -1),
0
))
AS QUANTITY_FAQ,
AR.Business_Date,
AVG (
NVL (AR.REBATE_RATE, 0)
)
AS NET_REBATE_RATE
FROM F_GFC_ACCOUNT_REBATE_COB AR,
D_ACCOUNT_IRDETAIL IR,
TMP_OTHER_ACCOUNTS_TAB TEMP
WHERE AR.Effective_Date BETWEEN '03-FEB-13'
AND '03-FEB-13'
AND AR.TYPE = 'BOR'
AND IR.Account_id =
AR.Account_Id
AND AR.Account_id=TEMP.Account_id
GROUP BY AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
FEE_REBATE_INDR,
AR.CURRENCY,
AR.HOT_STOCK_INDR,
IR.GENEVA_PRODUCT,
AR.Business_Date



When i changed it to the below joining order, it ran in few milliseconds


SELECT AR.ACCOUNT_ID, AR.INSTRUMENT_ID,
AVG (AR.PRICE) AS price,
AR.CURRENCY,
AR.FEE_REBATE_INDR,
DECODE (AR.HOT_STOCK_INDR,
'Y', 'Non-GC',
'GC')
AS GC_NONGC,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
ELSE
NULL
END
AS BASE_RATE,
CASE
WHEN (IR.GENEVA_PRODUCT =
'PB US'
AND AR.FEE_REBATE_INDR =
'R')
THEN
AVG(NVL (
AR.BASE_RATE,
0
))
- AVG(NVL (
AR.REBATE_RATE,
0
))
ELSE
AVG(NVL (
AR.REBATE_RATE,
0
))
END
AS STOCK_LOAN_FEE,
SUM(NVL (
(AR.QUANTITY * -1),
0
))
AS QUANTITY_FAQ,
AR.Business_Date,
AVG (
NVL (AR.REBATE_RATE, 0)
)
AS NET_REBATE_RATE
FROM TMP_OTHER_ACCOUNTS_TAB TEMP, D_ACCOUNT_IRDETAIL ir,
F_GFC_ACCOUNT_REBATE_COB ar
WHERE temp.account_id= ir.account_id
and ir.account_id=ar.account_id and
AR.Effective_Date BETWEEN '03-FEB-13'
AND '03-FEB-13'
AND AR.TYPE = 'BOR'

GROUP BY AR.ACCOUNT_ID,
AR.INSTRUMENT_ID,
FEE_REBATE_INDR,
AR.CURRENCY,
AR.HOT_STOCK_INDR,
IR.GENEVA_PRODUCT,
AR.Business_Date



So, can that be a reason for slow running of the procedure when called?

When i run the whole sql statement including the slow inline subquery, it works fine. But when i run that inline subquery separately, it takes a lot of time(30 minutes). Will that affect when i run the procedure as a whole??
Previous Topic: LOV taking long time
Next Topic: % cpu used by a session
Goto Forum:
  


Current Time: Thu Nov 20 20:45:27 CST 2014

Total time taken to generate the page: 0.18396 seconds