Home » RDBMS Server » Performance Tuning » SQL statement tuning (Oracle 8i)
| SQL statement tuning [message #317431] |
Thu, 01 May 2008 03:44  |
chinmayikkalki
Messages: 11
Registered: April 2008
|
Junior Member |
|
|
Hi Experts,
Plaese help in reducing elapsed time of the queries.
SELECT NVL(DHA_ORDER_ARBITRAGE.SALEABLE_QTY_FOR_UND_SEC('0000000202',:b1),0),
NVL(DHA_ORDER_ARBITRAGE.AVAILABLE_LIMIT('0000000202',:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.LIMIT_FOR_THE_DAY(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_MARGIN.SALEABLE_QTY_FROM_HOLDING('0000000202',:b1),0),
NVL(DHA_ORDER_ARBITRAGE.FUNDHOUSE_LIMIT(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.CLIENTWISE_LIMIT(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.CORPUS_LIMIT('0000000202',:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','R'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','P'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','MR'),0)
,NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','MP'),
0),NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','ME')
,0)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.06 0 0 1 0
Execute 109 0.01 0.01 0 0 0 0
Fetch 109 14.78 15.66 0 9497 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 14.82 15.73 0 9497 437 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'DUAL'
********************************************************************************
SELECT NVL(DHA_ORDER_MARGIN.POSITION_QTY_FROM_HOLDING(:b1,:b2),0) -
NVL(DHA_ORDER_MARGIN.TOTAL_ORDER_QTY_FOR_DATE(:b3,:b1,:b2,'P'),0)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.12 0 0 1 0
Execute 109 0.02 0.02 0 0 0 0
Fetch 109 2.86 3.03 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 2.95 3.17 0 109 437 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'DUAL'
********************************************************************************
SELECT SUM(A.QUANTITY)
FROM
DHT_EXECUTION A WHERE A.EXECUTION_DATE < :b1 AND A.ORDER_ID IN (SELECT
ORDER_ID FROM DHT_ORDER_MASTER WHERE ORDER_DATE < :b1 AND PORTFOLIO_ID =
:b3 AND SECURITY_ID = :b4 AND REC_PAY = :b5 AND ORDER_STATUS NOT IN (
'REJO','CANC' ))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.14 0 0 1 0
Execute 109 0.07 0.07 0 0 0 0
Fetch 109 2.16 2.41 0 77291 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 2.37 2.62 0 77291 1 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_ORDER_MASTER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_ORDER_DATE'
(NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_EXECUTION'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_EXEC_DATE'
(NON-UNIQUE)
********************************************************************************
SELECT /*+ KC_FUTURES_POSITION */NVL(TOTAL_QUANTITY,0)
FROM
DHT_FUTURES_POSITION WHERE PORTFOLIO_ID = :b1 AND SECURITY_ID = :b2 AND
VALUE_DATE = :b3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.10 0 0 1 0
Execute 327 0.06 0.07 0 0 0 0
Fetch 327 0.02 2.15 0 1368 0 327
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 655 0.16 2.32 0 1368 1 327
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_FUTURES_POSITION'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PKC_FUTURE_POSITION'
(UNIQUE)
********************************************************************************
SELECT DHA_ORDER_ARBITRAGE.CORPUS_LIMIT(:b1,:b2,:b3),
DHA_ORDER_ARBITRAGE.FUNDHOUSE_LIMIT(:b2,:b3),
DHA_ORDER_ARBITRAGE.CLIENTWISE_LIMIT(:b2,:b3)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 109 0.02 0.02 0 0 0 0
Fetch 109 1.61 1.77 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 1.64 1.80 0 109 436 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'DUAL'
********************************************************************************
SELECT NVL(POSITION_LIMIT,0)
FROM
DHT_FUND_HOUSE_LIMIT WHERE SECURITY_ID = :b1 AND VALUE_DATE = (SELECT /*+
KC_FUND_HOUSE_LIMIT */MAX(VALUE_DATE) FROM DHT_FUND_HOUSE_LIMIT WHERE
SECURITY_ID = :b1 AND VALUE_DATE <= :b3 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.16 0 0 1 0
Execute 327 0.45 0.48 0 6540 0 0
Fetch 327 0.00 0.01 0 972 0 324
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 655 0.58 0.65 0 7512 1 324
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_FUND_HOUSE_LIMIT'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PKC_FUND_HOUSE_LIMIT'
(UNIQUE)
0 SORT (AGGREGATE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PKC_FUND_HOUSE_LIMIT' (UNIQUE)
********************************************************************************
SELECT NVL(POSITION_LIMIT,0)
FROM
DHT_CLIENT_POSITION_LIMIT WHERE SECURITY_ID = :b1 AND VALUE_DATE =
(SELECT /*+ KC_CLIENT_POSITION_LIMIT */MAX(VALUE_DATE) FROM
DHT_CLIENT_POSITION_LIMIT WHERE SECURITY_ID = :b1 AND VALUE_DATE <= :b3 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.14 0 0 1 0
Execute 218 0.64 0.56 0 7194 0 0
Fetch 218 0.02 0.03 0 654 0 218
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 437 0.78 0.73 0 7848 1 218
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_CLIENT_POSITION_LIMIT'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PKC_CLIENT_POSITION_LIMIT' (UNIQUE)
0 SORT (AGGREGATE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PKC_CLIENT_POSITION_LIMIT' (UNIQUE)
*******************************************************************************
SELECT SUM(NVL(QUANTITY,0))
FROM
DHT_ORDER_ARBITRAGE_MASTER WHERE ORDER_STATUS NOT IN ( 'REJO','CANC' ) AND
PORTFOLIO_ID = :b1 AND UNDERLYING_SECURITY_ID = :b2 AND ORDER_DATE = :b3
AND REC_PAY = :b4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.10 0 0 1 0
Execute 436 0.03 0.02 0 0 0 0
Fetch 436 0.54 0.51 0 15696 1744 436
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 873 0.59 0.63 0 15696 1745 436
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'DHT_ORDER_ARBITRAGE_MASTER'
********************************************************************************
SELECT NVL(SUM(QUANTITY),0)
FROM
DHT_ORDER_MASTER WHERE ORDER_DATE < :b1 AND PORTFOLIO_ID = :b2 AND
SECURITY_ID = :b3 AND REC_PAY = :b4 AND ORDER_STATUS NOT IN ( 'REJO',
'CANC' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.12 0 0 1 0
Execute 109 0.03 0.04 0 0 0 0
Fetch 109 0.74 0.65 0 17876 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.87 0.81 0 17876 1 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_ORDER_MASTER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_ORDER_DATE'
(NON-UNIQUE)
********************************************************************************
SELECT NVL(SUM(A.QUANTITY),0)
FROM
DHT_EXECUTION A,DHT_ORDER_MASTER B WHERE A.EXECUTION_DATE = :b1 AND
A.ORDER_ID = B.ORDER_ID AND B.PORTFOLIO_ID = :b2 AND B.SECURITY_ID = :b3
AND B.REC_PAY = :b4 AND ORDER_STATUS IN ( 'CANC' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.12 0 0 1 0
Execute 109 0.07 0.07 0 0 0 0
Fetch 109 0.29 0.35 0 16541 545 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.49 0.54 0 16541 546 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (FULCRUM) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DHT_ORDER_MASTER'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DHT_EXECUTION'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_EXEC_DATE'
(NON-UNIQUE)
**************************************************************** |
|
|
|
| Re: SQL statement tuning [message #317433 is a reply to message #317431] |
Thu, 01 May 2008 03:54  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, you can replace all of the queries that select from DUAL with simple Pl/Sql assignations (although this won't help much if this is a SQL script). Without knowing the detals of the funtions they're calling, there's nothing more we can do for them.
Most of the rest of the queries look like they've executed 109 times each, and none of them took more than 2.62 seconds for 109 executions - is an execution time of 0.026 seconds too slow?
|
|
|
|
| Re: SQL statement tuning [message #317439 is a reply to message #317433] |
Thu, 01 May 2008 05:50 |
chinmayikkalki
Messages: 11
Registered: April 2008
|
Junior Member |
|
|
This is the query which take time of 15 seconds:>
[B]SELECT NVL(DOA.SQFUS('0000000202', '00000122'), 0),
NVL(DOA.AL('0000000202', '00000122', '01-apr-08'), 0),
NVL(DOA.LFTD('00000122', '01-apr-08'), 0),
NVL(DOM.SQFH('0000000202', '00000122'), 0),
NVL(DOA.FL('00000122', '01-apr-08'), 0),
NVL(DOA.CL('00000122', '01-apr-08'), 0),
NVL(DOA.COL('0000000202', '00000122', '01-apr-08'), 0),
NVL(DOA.UL('0000000202', '00000122', '01-apr-08', 'R'), 0),
NVL(DOA.UL('0000000202', '00000122', '01-apr-08', 'P'), 0),
NVL(DOA.UL('0000000202', '00000122', '01-apr-08', 'MR'), 0),
NVL(DOA.UL('0000000202', '00000122', '01-apr-08', 'MP'), 0),
NVL(DOA.UL('0000000202', '00000122', '01-apr-08', 'ME'), 0)
FROM DUAL;[/B]
[U][/U][I][/I][B]NOW DETAILS OF ALL THE ABOVE DOA PACKAGED FUNCTIONS---->>[/B]
FUNCTION SQFUS(P_PI VARCHAR2, P_UND_SEC_ID VARCHAR2)
RETURN NUMBER
AS
V_RET NUMBER;
BEGIN
SELECT NVL(TOTAL_QUANTITY, 0)
INTO V_RET
FROM DFP
WHERE PI = P_PI
AND SI = P_UND_SEC_ID
AND VALUE_DATE = DGAD(1);
RETURN V_RET;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END SQFUS;
FUNCTION AL(P_PI VARCHAR2, P_SI VARCHAR2, P_DATE DATE)
RETURN NUMBER
AS
V_RET NUMBER;
V_FUT_POS_LMT NUMBER;
V_CLIENT_POS_LMT NUMBER;
V_FHPL NUMBER;
BEGIN
SELECT COL(P_PI, P_SI, P_DATE),
FL(P_SI, P_DATE),
CL(P_SI, P_DATE)
INTO V_FUT_POS_LMT, V_FHPL, V_CLIENT_POS_LMT
FROM DUAL;
DIS('V_FUT_POS_LMT : ' || V_FUT_POS_LMT);
DIS('V_FHPL : ' || V_FHPL);
DIS('V_CLIENT_POS_LMT : ' || V_CLIENT_POS_LMT);
If V_FUT_POS_LMT = 0 or V_FUT_POS_LMT is Null Then
V_RET := LEAST(V_CLIENT_POS_LMT, V_FHPL);
Else
SELECT LEAST(V_FUT_POS_LMT, V_CLIENT_POS_LMT, V_FHPL)
INTO V_RET
FROM DUAL;
End If;
RETURN V_RET;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END AL;
FUNCTION LFTD(P_UND_SEC_ID VARCHAR2, P_DATE DATE)
RETURN NUMBER
AS
V_RET NUMBER;
V_FH_LIMIT NUMBER;
V_QTY NUMBER;
V_TOT_QTY NUMBER;
V_UTL_QTY NUMBER;
V_M_BUY_UTL_QTY NUMBER;
V_TOT_UTL_QTY NUMBER;
BEGIN
V_FH_LIMIT := FL(P_UND_SEC_ID, P_DATE);
V_TOT_QTY := 0;
V_TOT_UTL_QTY := 0;
FOR I IN (SELECT PI
FROM DHT_ARBITRAGE_RATIO
WHERE VALID_END_DATE IS NULL
OR VALID_END_DATE >= P_DATE)
LOOP
V_QTY := SQFUS(I.PI, P_UND_SEC_ID);
V_UTL_QTY := NVL(UL(I.PI, P_UND_SEC_ID, P_DATE, 'R'), 0);
V_M_BUY_UTL_QTY := NVL(UL(I.PI, P_UND_SEC_ID, P_DATE, 'MR'), 0);
V_TOT_QTY := V_TOT_QTY + V_QTY;
V_TOT_UTL_QTY := V_TOT_UTL_QTY + V_UTL_QTY + V_M_BUY_UTL_QTY;
END LOOP;
V_RET := V_FH_LIMIT - V_TOT_QTY - V_TOT_UTL_QTY;
RETURN V_RET;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END LFTD;
FUNCTION FL(P_UND_SEC_ID VARCHAR2, P_DATE DATE)
RETURN NUMBER
AS
V_RET NUMBER;
BEGIN
SELECT NVL(POSITION_LIMIT, 0)
INTO V_RET
FROM DFHL
WHERE SI = P_UND_SEC_ID
AND VALUE_DATE=(SELECT MAX(VALUE_DATE)
FROM DFHL
WHERE SI = P_UND_SEC_ID
AND VALUE_DATE <= P_DATE);
RETURN V_RET;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END FL;
FUNCTION CL(P_UND_SEC_ID VARCHAR2, P_DATE DATE)
RETURN NUMBER
AS
V_RET NUMBER;
BEGIN
SELECT NVL(POSITION_LIMIT, 0)
INTO V_RET
FROM DCPL
WHERE SI = P_UND_SEC_ID
AND VALUE_DATE = (SELECT MAX(VALUE_DATE)
FROM DCPL
WHERE SI = P_UND_SEC_ID
AND VALUE_DATE <= P_DATE);
RETURN V_RET;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END CL;
------------------------------------------------------
FUNCTION COL(P_PI VARCHAR2, P_SI VARCHAR2, P_DATE DATE)
RETURN NUMBER
AS
V_RET NUMBER;
BEGIN
SELECT COL
INTO V_RET
FROM DFP
WHERE PI = P_PI
AND SI = P_SI
AND VALUE_DATE = P_DATE;
RETURN V_RET;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END COL;
------------------------------------------------------
FUNCTION UL(P_PI VARCHAR2, P_SI VARCHAR2, P_DATE DATE, P_TRAN_TYPE VARCHAR2)
RETURN NUMBER
AS
V_RET NUMBER;
V_MULT_SIGN NUMBER;
BEGIN
IF SUBSTR(P_TRAN_TYPE, 1, 1) = 'M' THEN
IF SUBSTR(P_TRAN_TYPE, 2, 1) = 'R' THEN
V_MULT_SIGN := 1;
ELSIF SUBSTR(P_TRAN_TYPE, 2, 1) = 'P' THEN
V_MULT_SIGN := -1;
ELSIF SUBSTR(P_TRAN_TYPE, 2, 1) = 'E' THEN
V_MULT_SIGN := 0;
END IF;
SELECT SUM(DECODE(V_MULT_SIGN, 0, NVL(A.EQUITY_QTY, 0), ABS(DECODE(SIGN(A.QUANTITY1), V_MULT_SIGN,
0, A.QUANTITY1) + DECODE(SIGN(A.QUANTITY2), V_MULT_SIGN, 0, A.QUANTITY2) + DECODE(SIGN(A.QUANTITY3),
V_MULT_SIGN, 0, A.QUANTITY3))))
INTO V_RET
FROM DHT_ORDER_MULTIPLE A,
DOAM B
WHERE ORDER_DATE = P_DATE
AND B.ORDER_STATUS NOT IN ('REJO', 'CANC')
AND B.PI = P_PI
AND B.UNDERLYING_SI = P_SI
AND REC_PAY = SUBSTR(P_TRAN_TYPE, 1, 1)
AND A.TRANSACTION_TYPE = 'T'
AND A.ARBITRAGE_ORDER_ID = B.ORDER_ID;
ELSE
SELECT SUM(NVL(QUANTITY,0))
INTO V_RET
FROM DOAM
WHERE ORDER_STATUS NOT IN ('REJO', 'CANC')
AND PI = P_PI
AND UNDERLYING_SI = P_SI
AND ORDER_DATE = P_DATE
AND REC_PAY = P_TRAN_TYPE;
END IF;
RETURN V_RET;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END UL;
--------------------------------------------------------------------------
[U][I][B]NOW DETAILS OF DOM PACKAGED FUNCTIONS---->>[/B][/I][/U]
FUNCTION SQFH(P_PORTFOLIO VARCHAR2, P_SECURITY VARCHAR2)
RETURN NUMBER
AS
V_QTY NUMBER;
V_POS_QTY NUMBER;
V_PENR NUMBER;
V_PERC NUMBER;
V_APP_DATE DATE;
BEGIN
SELECT APPLICATION_DATE
INTO V_APP_DATE
FROM DQT_EOD_PARAMETER;
SELECT NVL(PQFH(P_PORTFOLIO, P_SECURITY), 0) - NVL(TOTAL_ORDER_QTY_FOR_DATE(V_APP_DATE, P_PORTFOLIO, P_SECURITY, 'P'), 0)
INTO V_QTY
FROM DUAL;
RETURN V_QTY;
END SQFH;
---------------------------------------------------------
FUNCTION SQFH(P_PORTFOLIO VARCHAR2, P_SECURITY VARCHAR2)
RETURN NUMBER
AS
V_QTY NUMBER;
V_POS_QTY NUMBER;
V_PENR NUMBER;
V_PERC NUMBER;
V_APP_DATE DATE;
BEGIN
SELECT APPLICATION_DATE
INTO V_APP_DATE
FROM DQT_EOD_PARAMETER;
-- SALEABLE QTY AT BOD MINUS THE TOTAL EXECUTED SELL ORDERS (13.07.2007)
-- SELECT NVL(PQFH(P_PORTFOLIO, P_SECURITY), 0) - NVL(TOTAL_EXEC_QTY_FOR_DATE(V_APP_DATE, P_PORTFOLIO, P_SECURITY, 'P'), 0)
SELECT NVL(PQFH(P_PORTFOLIO, P_SECURITY), 0) - NVL(TOTAL_ORDER_QTY_FOR_DATE(V_APP_DATE, P_PORTFOLIO, P_SECURITY, 'P'), 0)
INTO V_QTY
FROM DUAL;
RETURN V_QTY;
END SQFH;
---------------------------------------------------------
FUNCTION PQFH(P_PORTFOLIO VARCHAR2, P_SECURITY VARCHAR2)
RETURN NUMBER
AS
V_QTY NUMBER;
V_POS_QTY NUMBER;
V_PENR NUMBER;
V_PERC NUMBER;
BEGIN
BEGIN
SELECT nvl(SALEABLE_QUANTITY,0)
INTO V_POS_QTY
FROM DBOSQ
WHERE PI = P_PORTFOLIO
AND SI = P_SECURITY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_POS_QTY := 0;
END;
RETURN V_POS_QTY;
END PQFH;
|
|
|
|
| Re: SQL statement tuning [message #317440 is a reply to message #317439] |
Thu, 01 May 2008 06:02 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There's no reason at all to have a SELECT ... FROM DUAL in a function - you should replace things like SELECT COL(P_PI, P_SI, P_DATE),
FL(P_SI, P_DATE),
CL(P_SI, P_DATE)
INTO V_FUT_POS_LMT, V_FHPL, V_CLIENT_POS_LMT
FROM DUAL; v_fut_pos := COL(P_PI, P_SI, P_DATE);
v_fhpl := FL(P_SI, P_DATE);
v_client_pos_lmt := CL(P_SI, P_DATE)
Other than that, the only improvements I can see are to extract the code from the functions and merge them into the main query.
You could run a Statspack while this process is running - that might tell you if any of the SQL in the functions is being particularly troublesome.
This query will execute in about 0.13 seconds, which isn't bad going given the amount of context switches (going from Sql - Pl/Sql or vice versa) you're asking it to do.
|
|
|
|
| Re: SQL statement tuning [message #317601 is a reply to message #317440] |
Fri, 02 May 2008 02:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Received by PM:
| Quote: | Yes sir but the application is running the following query so
many times and because of that performance is down in the peak period(high workload).
There are so many NVLs and bind variables.
How to replace these NVLs and bind variables to increase the performance?
What do u think about it? (cursor_sharing,histogram etc.)
|
NVL is (I believe) supported as part of SQL, and does not force a context switch - it is not going to be causing you a noticable problem.
If you get rid of the bind variables, you will make your problems far far worse. At the moment, you are parsing the query once, and then executing it over 100 times. If you removed the bind variables, you would add an extra 108 parses into the equation.
Try doing what I suggested, and getting rid of all the queries that are doing SELECT ... FROM DUAL.
Your underlying problem is that you have got functions executing queries that call other functions that execut queries.....
If a Statspack report doesn't indicate that there are any query tuning improvements that can be made, then the only solution I can think of is to rewrite your queries to get as much data as possible in a single piece of SQL and reduce the depth of your function nesting.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 01:23:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
