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

sybrandb_at_hccnet.nl wa:
>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

Guess I better not die until I've gone to confession, then...

>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.

The design of the tables is fine. Unfortunately you're just going to have to trust me on that one (even though I know you don't want to :). The database models a very complex system that is rather proprietary (we're not talking commercial-proprietary here either), so I can't really give sufficient insight into it to allow you to believe me.

Anyway, I can eliminate 4 of the outer joins from that SELECT statement. Go ahead and roar at me for that.

>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).

I looked at the xplan.
With 4 outers it is 10% of the CPU
With 8 outers it is 11% of the CPU

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
| 0  | SELECT STATEMENT |      | 1    | 347   |   31 (10)  | 00:00:01 | 
| 0  | SELECT STATEMENT |      | 34   | 11798 |   39 (11)  | 00:00:01 | 

On a larger dataset, I expect the CPU may be used more. However, I don't expect the dataset to get very large (less than 5 people will be using the apps that talk to this db sporatically in a production environment, and they historically have only needed to once every few months). Yes, I do know the adage about assumptions. However if I were to place a bet, I'm betting that I'm right in this case.

>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.

Well, they are also sometimes necessary. Filling in the four ENTITY fields is not required, and I still want to grab the GROUP entries even if none of them are filled in. However the e1/e2/e3/e4 can be INNER joins.

So anyway, I can agree that outer joins are bad where they can be avoided. I'll see if the money man will let me change the procedures again even though I believe that particular bug has been closed out.

>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*.

Okay, that is interesting. I am assuming this has significant overhead, or at least enough that when processing large amounts of data row-by-row it can really, really increase the run time unnecessarily.

>So this is why you should not code procedurally (using records) what
>you could have done non-procedurally (using INSERT SELECT processing a
>set).

Well like I said, I was tasked to fix the bug. The cursor/loop stuff was already there. And I ended up replacing it all with a INSERT SELECT (like you can see in the original post).

>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.

Okay, so let me see if I understand you correctly here. If I'm going to keep the loops instead of INSERT INTO SELECT FROM, then I should've used the BULK COLLECT/BULK INSERT and thrown it into an "array" and then processed the array from there? But otherwise, it's better to use INSERT INTO SELECT FROM in all cases.

I did make a change that re-introduced another cur/loop (not nested though) in the final fix that I didn't list in the posted pseudo-code. It doesn't use the BULK COLLECT/BULK INSERT though. I'll see if I can still change that about the procedures, if the money man says yes to the above changes too.

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

Okay. Well, Oracle is a learning process. Every little bit of insight helps. Thank you for your comments.

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

You forgot to work "lazy programmers" in there somewhere. :)

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator