PL/SQL: cursors and loops, hang issue

#21

On Oct 22, 5:37*pm, Aya the Vampire Slayer
<ry... (AT) gatech (DOT) rmv.this.part.edu> wrote:

Quote:

sybra... (AT) hccnet (DOT) nl wa:

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
ry... (AT) gatech (DOT) 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.

#22

William Robertson wrote:

Quote:

On Oct 22, 5:37 pm, Aya the Vampire Slayer
ry... (AT) gatech (DOT) rmv.this.part.edu> wrote:
sybra... (AT) hccnet (DOT) nl wa:

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
ry... (AT) gatech (DOT) 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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#23

William Robertson wrote:

Quote:

On Oct 22, 5:37 pm, Aya the Vampire Slayer
ry... (AT) gatech (DOT) rmv.this.part.edu> wrote:
sybra... (AT) hccnet (DOT) nl wa:

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
ry... (AT) gatech (DOT) 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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#24

William Robertson wrote:

Quote:

On Oct 22, 5:37 pm, Aya the Vampire Slayer
ry... (AT) gatech (DOT) rmv.this.part.edu> wrote:
sybra... (AT) hccnet (DOT) nl wa:

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
ry... (AT) gatech (DOT) 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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#25

William Robertson wrote:

Quote:

On Oct 22, 5:37 pm, Aya the Vampire Slayer
ry... (AT) gatech (DOT) rmv.this.part.edu> wrote:
sybra... (AT) hccnet (DOT) nl wa:

On Tue, 21 Oct 2008 20:51:25 +0000 (UTC), Aya the Vampire Slayer
ry... (AT) gatech (DOT) 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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

PL/SQL: cursors and loops, hang issue

comp.databases.oracle.misc comp.databases.oracle.misc

Re: PL/SQL: cursors and loops, hang issue - 10-23-2008 , 01:35 AM

Re: PL/SQL: cursors and loops, hang issue - 10-25-2008 , 09:57 AM

Re: PL/SQL: cursors and loops, hang issue - 10-25-2008 , 09:57 AM

Re: PL/SQL: cursors and loops, hang issue - 10-25-2008 , 09:57 AM

Re: PL/SQL: cursors and loops, hang issue - 10-25-2008 , 09:57 AM