Re: PL/SQL: cursors and loops, hang issue

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu> wrote:

>Anyway, like I said earlier, I am hoping, for my own curiosity, someone
>here can provide some under-the-hood insight into how oracle processes
>cursors+loops (or whatever else) that would cause a previously-working
>procedure to hang just by changing an exterior (to the loops) SELECT
>statement that is only run once per procedure invocation.

You are guilty of two cardinal sins in your second version

1 You are using outer joins all over the place 2 You are processing records instead of sets.

Ad 1:
an outer join should really be an exception. If you have outer joins all over the place, likely your design is utterly wrong.
Let's take for a moment a very simple example.

select e.deptno, e.empname, d.deptname
from emp left outer join dept

What is going to happen? Emp is going to be the driving table, which is joined to dept by a nested loops join or *worse* a MERGE JOIN CARTESIAN.
You should have reviewed the EXPLAIN PLAN for this particular statement, and you would probably have noticed it uses MERGE JOIN CARTESIAN all over the place (and heaps of temporary segment and 100 percent of your CPU).
THIS is what you perceive as a 'hang' and your server is experiencing this as a full blown knockout.
A result of using OUTER JOINs everywhere. Outer joins are EVIL.

2 Oracle has a seperate SQL engine and a separate PL/SQL engine. This means: for every SQL statement you execute inside PL/SQL you get a *context switch*.
So this is why you should not code procedurally (using records) what you could have done non-procedurally (using INSERT SELECT processing a set).
If you insist on doing it procedurally you should at least have used BULK COLLECT and BULK INSERT.
Both BULK COLLECT and BULK-INSERT have been there since 8i, however as many developers didn't read the books of Steven Feuerstein, because they think PL/SQL is equally 'smart' as T-SQL, BULK COLLECT and BULK INSERT are under used.

What you have now is
1 a 'hang' caused by outer join
2 a completely unscalable procedure.

Happens all the time. People like myself earn their money from eradicating those procedures.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA