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

On Oct 22, 5:37 pm, Aya the Vampire Slayer <ry..._at_gatech.rmv.this.part.edu> wrote:
> sybra..._at_hccnet.nl wa:
>
> >On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
> ><ry..._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.

The rule of thumb is that row-by-row can be expected to take around 4 times as long as the equivalent single SQL statement.

The CPU estimate is not the only thing to look at in the execution plan. Perhaps the optimizer has picked the wrong join order or access path, possibly indicating an issue in the stats or an inefficiency in the SQL, such as a NOT IN predicate against a nullable key.

For non-obvious PL/SQL performance issues the first thing I would do is run the procedure using DBMS_PROFILER. (IDEs such as PL/SQL Developer have a button for this but you can work it from the command line if you have to.) Also while it appears to be hanging, query v $session, v$session_wait, v$session_wait_history and v$session_longops to see what it's actually up to. Received on Thu Oct 23 2008 - 01:35:53 CDT