Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have following simplified query

WITH Report1 AS (
   SELECT SUM(t.X), SUM(t.Y) 
   FROM Table1 t
   WHERE t.Date1 > :dateStart  and t.Date1 < :dateFinish
)
SELECT * FROM Report1

This query executes in 6-7 seconds.

When I put this query to stored procedure

create or replace PROCEDURE       SPReport1
(
 pCURSOR OUT Geneltipler.REFCURSOR,
 pDateStart Table1.Date1%TYPE,
 pDateFinish Table1.Date1%TYPE,
 ) AS
BEGIN

  OPEN pCURSOR FOR
    WITH Report1 AS (
       SELECT SUM(t.X), SUM(t.Y) 
       FROM Table1 t
       WHERE t.Date1 > :dateStart  and t.Date1 < :dateFinish
    )
    SELECT * FROM Report1
 ;
END;

and execute in the following fashion in Toad and Sql Developer:

DECLARE
  PCURSOR Geneltipler.REFCURSOR;
  pDateStart DATE;
  pDateFinish DATE;
BEGIN

  pDateStart := TO_DATE('01/01/2014', 'DD/MM/RRRR');
  pDateFinish := TO_DATE('01/03/2014', 'DD/MM/RRRR');

  SPReport1(
    PCURSOR => PCURSOR,
    pDateStart => pDateStart,
    pDateFinish => pDateFinish,
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('PCURSOR = ' || PCURSOR);
*/ 
  :PCURSOR := PCURSOR; --<-- Cursor
--rollback; 
END;

This execution takes 4 minutes, that is 240 seconds. This is almost 40 times normal. How can I find what is the problem? I know that stored procedures does not have execution plans? How can I investigate what is the problem?

I am using Oracle 10gR2 RAC.

share|improve this question
1  
When you say it takes 6-7 seconds, I think you are referring to time taken by your GUI client to return first set of N rows. (N might be 100 rows which is default). Whereas in your procedure you are pulling in all data, not just first 100 rows, hence it takes more time. If you retrieve all rows in your client, you will probably see that it takes about same amount of time as procedure (a bit less actually). –  Raj Mar 25 at 13:26
    
Actually this query only returns 10 rows, therefore I do not think what you say apply to this situation. –  Atilla Ozgur Mar 25 at 15:11
    
So, how did you know it is 4 minutes? Run it with sql_trace on or with event 10046 set in your session and it will tell you why it is taking long, if it is. Even 6-7 seconds is too long for 10 rows. Did you validate it returns 10 rows by running "WITH Report1 AS (SELECT SUM(t.X), SUM(t.Y) FROM Table1 t WHERE t.Date1 > :dateStart and t.Date1 < :dateFinish) SELECT count(*) FROM Report1" ? using the exact same values for start and end dates? Just curious because as written, I think it should come back with 1 row since there is no group by. –  Raj Mar 25 at 19:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.