Loop one time takes more time than loop 200 times ...

[htanga]

I all !
I've encountered a strange behavior with a loop in PL/SQL block.
Here is the PL/SQL code :

set serveroutput on size 1000000

DECLARE

TYPE fdv_type is REF CURSOR return view_test%rowtype;
PIO_CURSOR fdv_type;
PI_DATE DATE;
PI_PDT_NIV NUMBER;
FETCH_ROW PIO_CURSOR%ROWTYPE;

BEGIN

DBMS_OUTPUT.ENABLE(32768) ;

PI_DATE := '20/06/03';
PI_PDT_NIV := 1;

P_TEST( PIO_CURSOR, PI_DATE, PI_PDT_NIV);

dbms_output.put_line('before loop : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));

LOOP
FETCH PIO_CURSOR INTO FETCH_ROW ;
EXIT WHEN PIO_CURSOR%NOTFOUND ;
END LOOP;

dbms_output.put_line('after loop : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));

DBMS_OUTPUT.PUT_LINE(PIO_CURSOR%ROWCOUNT);

END;
/


When the PI_PDT_NIV=1, the p_test procedure retrieves one row, when the PI_PDT_NIV=5, the p_test procedure retrieves 200 rows.

The p_test() procedure always takes less than one second to execute(for PI_PDT_NIV=1 or PI_PDT_NIV=5).

But the loop takes 5 seconds when the cursor contains one row, and less than one second when the cursor contains 200 rows ...

How could this happen ?

Thanks a lot for your help ...

[tombest]

I'm guessing that you are ending up with 2 different execution plans in thes 2 cases. P_TEST defines a query and opens the cursor, but it does not fetch the first row. So, P_TEST will pretty much be the same no matter how many rows are in the result set.

So, that points to the fetch loop taking longer to find the single row, which means it has a non-optimal plan, probably.

Capture the sql that p_test is opening the cursor with, and EXPLAIN each case and compare.

Hard to say more without actually seeing the SQL you are running.

[htanga]

Thanks for you reply ...
I compared the two explain plans, and there are exactly the same (except for the number of rows returned).
The strange thing is that when I launch the SELECT for pi_pdt_niv=1 (which returns one rows) with SQL*Plus in trace mode, I can see that it takes the same time that with pi_pdt_niv=5 (which returns 200 rows).
I use sql_trace, and I compare "cpu" and "elapsed" variables.
Any idea ?

[htanga]

I finally found out the problem ...
I was wrong about the explain plans !
There were different !
I modify the query in the procedure and now, it works fine !
Thanks a lot !