Re: PL/SQL: cursors and loops, hang issue
From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 25 Oct 2008 07:57:36 -0700
Message-ID: <1224946649.988287@bubbleator.drizzle.com>
>> 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.
Date: Sat, 25 Oct 2008 07:57:36 -0700
Message-ID: <1224946649.988287@bubbleator.drizzle.com>
William Robertson wrote:
> 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.
Unless an exception is generated or the table has constraints, indexes, triggers, etc. Then it can 10:1 or more.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sat Oct 25 2008 - 09:57:36 CDT