Wednesday, August 26, 2009

Why I use the Resource Manager on apex.oracle.com

A few weeks ago, I blogged about the use of the Oracle Database Resource Manager and apex.oracle.com, and how it is essential to keep this service up and running.

Well, today, I stumbled across 10 active database sessions, all from the same user, all running an anonymous PL/SQL block issued within SQL Commands on apex.oracle.com. All 10 sessions had been running for hundreds of seconds and all 10 were in the APEX_LOW resource consumer group. By using the built-in-to-APEX Utilities -> Database Monitor -> Sessions reports, I was able to determine exactly what was executing in these sessions.

What's wrong with this user's code?


DECLARE
i NUMBER(3):=1;
BEGIN
WHILE (i<5) LOOP
IF( mod(i,2)=0) THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;


I am NOT suggesting you run this on apex.oracle.com nor your own instance of Application Express.

8 comments:

Chandu said...

The value "i" is not incremented.
So this is results in infinite loop.

--Chandra.

Stew said...

If I had a nickel for every time I forgot to increment a loop... I'd have a couple dollars anyway! Let's just say that I've made this mistake often enough that it was easy to recognize...

Thanks for the tip about the Resource Manager.

Gary Myers said...

There was a time when the DBMS_OUTPUT buffer was small enough that it would fall over. Then they go and 'enhance' it :)

Andreas said...

As Chandu already pointed to, your variable i isn't de/incremented. So your loop is indefinite.

Joel R. Kallman said...

Andreas,

This is definitely *not* "my variable" nor "my loop".

Joel

Scott said...

I've tried running this code 5-6 times now, and can't figure out what's wrong, other than it broke my database and my internets. :)

- Scott -

Doug said...

Scott is a smart-@$$

Stew said...

Scott - You too??? My DBAs are really mad at me.

Doug - What do you mean?

;-)