Home » RDBMS Server » Performance Tuning » SQL statement tuning (Oracle 8i)
SQL statement tuning [message #317431] Thu, 01 May 2008 03:44 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
with
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 Go to previous message
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.
Previous Topic: tuning time consuming queries
Next Topic: How to make inserts faster
Goto Forum:
  


Current Time: Sun Dec 11 02:27:10 CST 2016

Total time taken to generate the page: 0.15329 seconds