Huge performance difference between direct sql text execution and stored procedure execution

Hi All,

I have a stored procedure (in a package) which contains only 1 query string. If I execute the string directly in SQL*+, it finishes in 2~3 minutes. But if I execute the stored procedure, it takes 30 minutes!

Here is the stored procedure:

*****************************
PROCEDURE PR_LOADEVE(
      in_FROM_DATE IN DATE,
      in_TO_DATE IN DATE,
      out_RET_CODE OUT INT
)
AS
        USD CHAR(3) := '001';
  SGD CHAR(3) := '022';
BEGIN
      out_RET_CODE := 0;
      -- CTR
      INSERT INTO DC_EVE_CTR (
            SELECT JOB_ID, E.VALUATION_DATE, E.CLIENT_CODE, SECURITY_ID, SUM(MKT_VAL3), SUM(MKT_VAL2), SUM(MKT_VAL1)
            FROM EVE.VIEW_EVE_VAL_CTR_JOB E,
            (SELECT VALUATION_DATE, CLIENT_CODE, MAX(JOB_ID) MAX_JOB_ID FROM EVE.VIEW_EVE_VAL_CTR_JOB E1
            WHERE VALUATION_DATE BETWEEN in_FROM_DATE AND in_TO_DATE
            GROUP BY VALUATION_DATE, CLIENT_CODE) E1
            WHERE E.VALUATION_DATE BETWEEN in_FROM_DATE AND in_TO_DATE
            AND E.VALUATION_DATE = E1.VALUATION_DATE
            AND E.CLIENT_CODE = E1.CLIENT_CODE
            AND E.JOB_ID = E1.MAX_JOB_ID
            GROUP BY SECURITY_ID, E.VALUATION_DATE, E.CLIENT_CODE, JOB_ID
      );

      out_RET_CODE := 1;
END;

*****************************
you can ignore the USD and SGD as they are just two constants.

When I execute the stored proc, I use
*****************************
DECLARE
  IN_FROM_DATE DATE;
  IN_TO_DATE DATE;
  OUT_RET_CODE NUMBER;

BEGIN
  IN_FROM_DATE := '30-Apr-2004';
  IN_TO_DATE := '30-Apr-2004';
  OUT_RET_CODE := NULL;

  DPMS.PKG_DPMSCHECKS.PR_LOADEVE ( IN_FROM_DATE, IN_TO_DATE, OUT_RET_CODE );
  COMMIT;
END;
****************************

It takes 30+ minutes.

and here is the query text that I executed in sql*+:

****************************
INSERT INTO DC_EVE_CTR (
      SELECT JOB_ID, E.VALUATION_DATE, E.CLIENT_CODE, SECURITY_ID, SUM(MKT_VAL3), SUM(MKT_VAL2), SUM(MKT_VAL1)
      FROM EVE.VIEW_EVE_VAL_CTR_JOB E,
            (SELECT VALUATION_DATE, CLIENT_CODE, MAX(JOB_ID) MAX_JOB_ID FROM EVE.VIEW_EVE_VAL_CTR_JOB E1
            WHERE VALUATION_DATE BETWEEN '30-Apr-2004' AND '30-Apr-2004'
            GROUP BY VALUATION_DATE, CLIENT_CODE) E1
      WHERE E.VALUATION_DATE BETWEEN '30-Apr-2004' AND '30-Apr-2004'
      AND E.VALUATION_DATE = E1.VALUATION_DATE
      AND E.CLIENT_CODE = E1.CLIENT_CODE
      AND E.JOB_ID = E1.MAX_JOB_ID
      GROUP BY SECURITY_ID, E.VALUATION_DATE, E.CLIENT_CODE, JOB_ID
);

****************************

and it takes only less than 3 minutes.

Any help is highly appreciated.Start Free Trial