SQL & PL/SQL

Hi all,

Please consider the block of PL/SQL below:

DECLARE
ts_start TIMESTAMP;
ts_end TIMESTAMP;
ts_diff INTERVAL DAY TO SECOND;
v_usageCode VARCHAR2(1) := '';

CURSOR curUsageCode IS
SELECT 'a' usage_code FROM dual where 1 = 0;

BEGIN
ts_start := systimestamp;

FOR slot IN 1 .. 200 LOOP
OPEN curUsageCode();
FETCH curUsageCode INTO v_usageCode;
CLOSE curUsageCode;
END LOOP;

ts_end := systimestamp;
ts_diff := ts_end - ts_start;
Dbms_Output.put_line(EXTRACT(SECOND FROM ts_diff) || ' secs');
END;
/

(I know it's an artificial situation, but it demonstrates nicely a problem I'm having in some real pl/sql).

On one of my customers' databases, and on just one of my internal dev databases, the above block of pl/sql can take up to _10 seconds_ to execute. On other databases, it executes instantaneously. Late at night when no-one's on the affected dev database, it can execute instantaneously too.

The customer is on 10.2.0.4.0, and the dev database is on 10.2.0.3.0.

If I change the above to "... FROM dual where 1 = 1;", it ALWAYS runs instantaneously.

Therefore, it seems that the problem only occurs when the cursor returns no rows. I've put some extra timings on, and can confirm that the delay is in the FETCH step.

I can only assume there is some environmental problem going on here - a shortage of some resource or other, or an optimization that only affects empty resultsets, for example? The dev database is used frequently, but it's not used particularly heavily (very few resource-hungry queries, etc.) most of the time.

If anyone can make any suggestions, they would be very gratefully received.

Here are the cursor-related parameters from v$parameter. Sorry the table below isn't very readable, but I think it's saying they're all on default settings:

NAME VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED
cursor_space_for_time FALSE TRUE FALSE FALSE FALSE FALSE FALSE
session_cached_cursors 20 TRUE TRUE FALSE FALSE FALSE FALSE
cursor_sharing EXACT TRUE TRUE IMMEDIATE TRUE FALSE FALSE
open_cursors 300 FALSE FALSE IMMEDIATE TRUE FALSE FALSE

Thanks in advance,

Neil