Hi all.

This is Oracle 10.2.0.3 on Oracle Enterprise Linux. SGA: 1,5 Gb. PGA
512 Mb. On a bi-processor PIV machine.

I'm running a process which has two MERGE /*+ parallel */ statements.
The where's and clauses are almost the same (minor changes though).
The first of them finishes in about 90 mins, and the second can last
for 12 hours. (This is so strange to me: I would understand the two of
them lasting more or less, but the almost the same). The tables merged
have about 13 million rows each.

Anyway: v$session_wait is telling me that the session is waiting for
'Deq Credit: send blkd' very frequently.

I'm wondering about turning the query into Nested Loops. It should run
slowlier, but...

Suggestions?

Thanks in advance.

Cheers.

Carlos.

On Oct 3, 2:53 am, Carlos <miotromailcar...@netscape.net> wrote:
> Hi all.
>
> This is Oracle 10.2.0.3 on Oracle Enterprise Linux. SGA: 1,5 Gb. PGA
> 512 Mb. On a bi-processor PIV machine.
>
> I'm running a process which has two MERGE /*+ parallel */ statements.
> The where's and clauses are almost the same (minor changes though).
> The first of them finishes in about 90 mins, and the second can last
> for 12 hours. (This is so strange to me: I would understand the two of
> them lasting more or less, but the almost the same). The tables merged
> have about 13 million rows each.
>
> Anyway: v$session_wait is telling me that the session is waiting for
> 'Deq Credit: send blkd' very frequently.
>
> I'm wondering about turning the query into Nested Loops. It should run
> slowlier, but...
>
> Suggestions?
>
> Thanks in advance.
>
> Cheers.
>
> Carlos.

You have explain plans for both of these statements? Please post
them.


David Fitzjarrell

On 3 oct, 15:28, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Oct 3, 2:53 am, Carlos <miotromailcar...@netscape.net> wrote:
>
>
>
> > Hi all.
>
> > This is Oracle 10.2.0.3 on Oracle Enterprise Linux. SGA: 1,5 Gb. PGA
> > 512 Mb. On a bi-processor PIV machine.
>
> > I'm running a process which has two MERGE /*+ parallel */ statements.
> > The where's and clauses are almost the same (minor changes though).
> > The first of them finishes in about 90 mins, and the second can last
> > for 12 hours. (This is so strange to me: I would understand the two of
> > them lasting more or less, but the almost the same). The tables merged
> > have about 13 million rows each.
>
> > Anyway: v$session_wait is telling me that the session is waiting for
> > 'Deq Credit: send blkd' very frequently.
>
> > I'm wondering about turning the query into Nested Loops. It should run
> > slowlier, but...
>
> > Suggestions?
>
> > Thanks in advance.
>
> > Cheers.
>
> > Carlos.
>
> You have explain plans for both of these statements? Please post
> them.
>
> David Fitzjarrell

Sure.

The long one:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3000988899

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 12M|
935M| | 145K (2)| 00:29:09 | | | |
| 1 | MERGE | TRAS_NORM |
| | | | | | | |
| 2 | PX COORDINATOR | |
| | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 12M|
4363M| | 145K (2)| 00:29:09 | Q1,03 | P->S | QC (RAND) |
| 4 | VIEW | |
| | | | | Q1,03 | PCWP | |
|* 5 | HASH JOIN BUFFERED | | 12M|
4363M| 398M| 145K (2)| 00:29:09 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 12M|
1446M| | 64917 (2)| 00:13:00 | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 12M|
1446M| | 64917 (2)| 00:13:00 | Q1,01 | P->P | HASH |
|* 8 | VIEW | | 12M|
1446M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
|* 9 | WINDOW SORT PUSHED RANK | | 12M|
595M| 1659M| 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 12M|
595M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 12M|
595M| | 64917 (2)| 00:13:00 | Q1,00 | P->P | HASH |
|* 12 | WINDOW CHILD PUSHED RANK| | 12M|
595M| | 64917 (2)| 00:13:00 | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 12M|
595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL | SELBASE | 12M|
595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
| 15 | PX RECEIVE | | 13M|
2983M| | 22011 (4)| 00:04:25 | Q1,03 | PCWP | |
| 16 | PX SEND HASH | :TQ10002 | 13M|
2983M| | 22011 (4)| 00:04:25 | Q1,02 | P->P | HASH |
| 17 | PX BLOCK ITERATOR | | 13M|
2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWC | |
|* 18 | TABLE ACCESS FULL | TRAS_NORM | 13M|
2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------

the short one:

select * from table(dbms_xplan.display()) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 382060359

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 3060K|
224M| | 126K (2)| 00:25:20 | | | |
| 1 | MERGE | TRAS_NORM |
| | | | | | | |
| 2 | PX COORDINATOR | |
| | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 3060K|
1114M| | 126K (2)| 00:25:20 | Q1,03 | P->S | QC (RAND) |
| 4 | VIEW | |
| | | | | Q1,03 | PCWP | |
|* 5 | HASH JOIN BUFFERED | | 3060K|
1114M| 188M| 126K (2)| 00:25:20 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 3129K|
716M| | 21707 (2)| 00:04:21 | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 3129K|
716M| | 21707 (2)| 00:04:21 | Q1,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 3129K|
716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | TRAS_NORM | 3129K|
716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 12M|
1726M| | 72284 (2)| 00:14:28 | Q1,03 | PCWP | |
| 11 | PX SEND HASH | :TQ10002 | 12M|
1726M| | 72284 (2)| 00:14:28 | Q1,02 | P->P | HASH |
|* 12 | VIEW | | 12M|
1726M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
|* 13 | WINDOW SORT PUSHED RANK | | 12M|
717M| 2164M| 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
| 14 | PX RECEIVE | | 12M|
717M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10000 | 12M|
717M| | 72284 (2)| 00:14:28 | Q1,00 | P->P | HASH |
|* 16 | WINDOW CHILD PUSHED RANK| | 12M|
717M| | 72284 (2)| 00:14:28 | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 12M|
717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
| 18 | TABLE ACCESS FULL | SELBASE | 12M|
717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------

The long merge finished a few moments ago after 20 hours.

Cheers.

Carlos.

"Carlos" <miotromailcarlos@netscape.net> wrote in message
news:1191424330.534720.242750@k79g2000hse.googlegr oups.com...
>
> --------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
> --------------------------------------------------------------------------------------------------------------------------------------
> | 0 | MERGE STATEMENT | | 12M|
> 935M| | 145K (2)| 00:29:09 | | | |
> | 1 | MERGE | TRAS_NORM |
> | | | | | | | |
> | 2 | PX COORDINATOR | |
> | | | | | | | |
> | 3 | PX SEND QC (RANDOM) | :TQ10003 | 12M|
> 4363M| | 145K (2)| 00:29:09 | Q1,03 | P->S | QC (RAND) |
> | 4 | VIEW | |
> | | | | | Q1,03 | PCWP | |
> |* 5 | HASH JOIN BUFFERED | | 12M|
> 4363M| 398M| 145K (2)| 00:29:09 | Q1,03 | PCWP | |
> | 6 | PX RECEIVE | | 12M|
> 1446M| | 64917 (2)| 00:13:00 | Q1,03 | PCWP | |
> | 7 | PX SEND HASH | :TQ10001 | 12M|
> 1446M| | 64917 (2)| 00:13:00 | Q1,01 | P->P | HASH |
> |* 8 | VIEW | | 12M|
> 1446M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> |* 9 | WINDOW SORT PUSHED RANK | | 12M|
> 595M| 1659M| 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> | 10 | PX RECEIVE | | 12M|
> 595M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> | 11 | PX SEND HASH | :TQ10000 | 12M|
> 595M| | 64917 (2)| 00:13:00 | Q1,00 | P->P | HASH |
> |* 12 | WINDOW CHILD PUSHED RANK| | 12M|
> 595M| | 64917 (2)| 00:13:00 | Q1,00 | PCWP | |
> | 13 | PX BLOCK ITERATOR | | 12M|
> 595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
> | 14 | TABLE ACCESS FULL | SELBASE | 12M|
> 595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
> | 15 | PX RECEIVE | | 13M|
> 2983M| | 22011 (4)| 00:04:25 | Q1,03 | PCWP | |
> | 16 | PX SEND HASH | :TQ10002 | 13M|
> 2983M| | 22011 (4)| 00:04:25 | Q1,02 | P->P | HASH |
> | 17 | PX BLOCK ITERATOR | | 13M|
> 2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWC | |
> |* 18 | TABLE ACCESS FULL | TRAS_NORM | 13M|
> 2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWP | |
> --------------------------------------------------------------------------------------------------------------------------------------
>
> the short one:
>
> select * from table(dbms_xplan.display()) ;
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 382060359
>
> --------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
> --------------------------------------------------------------------------------------------------------------------------------------
> | 0 | MERGE STATEMENT | | 3060K|
> 224M| | 126K (2)| 00:25:20 | | | |
> | 1 | MERGE | TRAS_NORM |
> | | | | | | | |
> | 2 | PX COORDINATOR | |
> | | | | | | | |
> | 3 | PX SEND QC (RANDOM) | :TQ10003 | 3060K|
> 1114M| | 126K (2)| 00:25:20 | Q1,03 | P->S | QC (RAND) |
> | 4 | VIEW | |
> | | | | | Q1,03 | PCWP | |
> |* 5 | HASH JOIN BUFFERED | | 3060K|
> 1114M| 188M| 126K (2)| 00:25:20 | Q1,03 | PCWP | |
> | 6 | PX RECEIVE | | 3129K|
> 716M| | 21707 (2)| 00:04:21 | Q1,03 | PCWP | |
> | 7 | PX SEND HASH | :TQ10001 | 3129K|
> 716M| | 21707 (2)| 00:04:21 | Q1,01 | P->P | HASH |
> | 8 | PX BLOCK ITERATOR | | 3129K|
> 716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWC | |
> |* 9 | TABLE ACCESS FULL | TRAS_NORM | 3129K|
> 716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWP | |
> | 10 | PX RECEIVE | | 12M|
> 1726M| | 72284 (2)| 00:14:28 | Q1,03 | PCWP | |
> | 11 | PX SEND HASH | :TQ10002 | 12M|
> 1726M| | 72284 (2)| 00:14:28 | Q1,02 | P->P | HASH |
> |* 12 | VIEW | | 12M|
> 1726M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> |* 13 | WINDOW SORT PUSHED RANK | | 12M|
> 717M| 2164M| 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> | 14 | PX RECEIVE | | 12M|
> 717M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> | 15 | PX SEND HASH | :TQ10000 | 12M|
> 717M| | 72284 (2)| 00:14:28 | Q1,00 | P->P | HASH |
> |* 16 | WINDOW CHILD PUSHED RANK| | 12M|
> 717M| | 72284 (2)| 00:14:28 | Q1,00 | PCWP | |
> | 17 | PX BLOCK ITERATOR | | 12M|
> 717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
> | 18 | TABLE ACCESS FULL | SELBASE | 12M|
> 717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
> --------------------------------------------------------------------------------------------------------------------------------------
>
> The long merge finished a few moments ago after 20 hours.
>


Merge commands are operated (usually) by an outer join
between the two sets of data (I don't understand why your's
is not - have you omitted the INSERT clause) and the join
can be a hash, merge or nested loop.

In both your cases, the optimiser has used a hash join.

The major difference between the two plans is that the two
sub-plans (one with a couple of analytic functions - which
I assume is the new data, and one without - which I assume
is the "old" data) have been reversed.

In a hash join, the first "child" operation has its data scattered into
a hash table and the data set from the second child is used to probe
the hash table that has been created. Oracle basic strategy is to choose
the data set that will return the smaller volume of data (which is not
the same thing as the number of rows) as the first child.

If the second plan is unsuitable, it is because Oracle's estimate of the
volume of the two data sets was wrong. Possibly, however, a fairly
small change in the predicates could have resulted in a sufficiently large
change in the data volumes that the plan is the best possible plan, and
the time is a side effect of the limit on the available memory for building
the hash table.



--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

On 3 oct, 17:48, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk> wrote:
> "Carlos" <miotromailcar...@netscape.net> wrote in message
>
> news:1191424330.534720.242750@k79g2000hse.googlegr oups.com...
>
>
>
>
>
> > --------------------------------------------------------------------------------------------------------------------------------------
> > | Id | Operation | Name | Rows |
> > Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
> > --------------------------------------------------------------------------------------------------------------------------------------
> > | 0 | MERGE STATEMENT | | 12M|
> > 935M| | 145K (2)| 00:29:09 | | | |
> > | 1 | MERGE | TRAS_NORM |
> > | | | | | | | |
> > | 2 | PX COORDINATOR | |
> > | | | | | | | |
> > | 3 | PX SEND QC (RANDOM) | :TQ10003 | 12M|
> > 4363M| | 145K (2)| 00:29:09 | Q1,03 | P->S | QC (RAND) |
> > | 4 | VIEW | |
> > | | | | | Q1,03 | PCWP | |
> > |* 5 | HASH JOIN BUFFERED | | 12M|
> > 4363M| 398M| 145K (2)| 00:29:09 | Q1,03 | PCWP | |
> > | 6 | PX RECEIVE | | 12M|
> > 1446M| | 64917 (2)| 00:13:00 | Q1,03 | PCWP | |
> > | 7 | PX SEND HASH | :TQ10001 | 12M|
> > 1446M| | 64917 (2)| 00:13:00 | Q1,01 | P->P | HASH |
> > |* 8 | VIEW | | 12M|
> > 1446M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> > |* 9 | WINDOW SORT PUSHED RANK | | 12M|
> > 595M| 1659M| 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> > | 10 | PX RECEIVE | | 12M|
> > 595M| | 64917 (2)| 00:13:00 | Q1,01 | PCWP | |
> > | 11 | PX SEND HASH | :TQ10000 | 12M|
> > 595M| | 64917 (2)| 00:13:00 | Q1,00 | P->P | HASH |
> > |* 12 | WINDOW CHILD PUSHED RANK| | 12M|
> > 595M| | 64917 (2)| 00:13:00 | Q1,00 | PCWP | |
> > | 13 | PX BLOCK ITERATOR | | 12M|
> > 595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
> > | 14 | TABLE ACCESS FULL | SELBASE | 12M|
> > 595M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
> > | 15 | PX RECEIVE | | 13M|
> > 2983M| | 22011 (4)| 00:04:25 | Q1,03 | PCWP | |
> > | 16 | PX SEND HASH | :TQ10002 | 13M|
> > 2983M| | 22011 (4)| 00:04:25 | Q1,02 | P->P | HASH |
> > | 17 | PX BLOCK ITERATOR | | 13M|
> > 2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWC | |
> > |* 18 | TABLE ACCESS FULL | TRAS_NORM | 13M|
> > 2983M| | 22011 (4)| 00:04:25 | Q1,02 | PCWP | |
> > --------------------------------------------------------------------------------------------------------------------------------------
>
> > the short one:
>
> > select * from table(dbms_xplan.display()) ;
> > PLAN_TABLE_OUTPUT
> > ------------------------------------------------------------------------------------------------------------------------------------------------------
> > Plan hash value: 382060359
>
> > --------------------------------------------------------------------------------------------------------------------------------------
> > | Id | Operation | Name | Rows |
> > Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
> > --------------------------------------------------------------------------------------------------------------------------------------
> > | 0 | MERGE STATEMENT | | 3060K|
> > 224M| | 126K (2)| 00:25:20 | | | |
> > | 1 | MERGE | TRAS_NORM |
> > | | | | | | | |
> > | 2 | PX COORDINATOR | |
> > | | | | | | | |
> > | 3 | PX SEND QC (RANDOM) | :TQ10003 | 3060K|
> > 1114M| | 126K (2)| 00:25:20 | Q1,03 | P->S | QC (RAND) |
> > | 4 | VIEW | |
> > | | | | | Q1,03 | PCWP | |
> > |* 5 | HASH JOIN BUFFERED | | 3060K|
> > 1114M| 188M| 126K (2)| 00:25:20 | Q1,03 | PCWP | |
> > | 6 | PX RECEIVE | | 3129K|
> > 716M| | 21707 (2)| 00:04:21 | Q1,03 | PCWP | |
> > | 7 | PX SEND HASH | :TQ10001 | 3129K|
> > 716M| | 21707 (2)| 00:04:21 | Q1,01 | P->P | HASH |
> > | 8 | PX BLOCK ITERATOR | | 3129K|
> > 716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWC | |
> > |* 9 | TABLE ACCESS FULL | TRAS_NORM | 3129K|
> > 716M| | 21707 (2)| 00:04:21 | Q1,01 | PCWP | |
> > | 10 | PX RECEIVE | | 12M|
> > 1726M| | 72284 (2)| 00:14:28 | Q1,03 | PCWP | |
> > | 11 | PX SEND HASH | :TQ10002 | 12M|
> > 1726M| | 72284 (2)| 00:14:28 | Q1,02 | P->P | HASH |
> > |* 12 | VIEW | | 12M|
> > 1726M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> > |* 13 | WINDOW SORT PUSHED RANK | | 12M|
> > 717M| 2164M| 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> > | 14 | PX RECEIVE | | 12M|
> > 717M| | 72284 (2)| 00:14:28 | Q1,02 | PCWP | |
> > | 15 | PX SEND HASH | :TQ10000 | 12M|
> > 717M| | 72284 (2)| 00:14:28 | Q1,00 | P->P | HASH |
> > |* 16 | WINDOW CHILD PUSHED RANK| | 12M|
> > 717M| | 72284 (2)| 00:14:28 | Q1,00 | PCWP | |
> > | 17 | PX BLOCK ITERATOR | | 12M|
> > 717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWC | |
> > | 18 | TABLE ACCESS FULL | SELBASE | 12M|
> > 717M| | 21559 (3)| 00:04:19 | Q1,00 | PCWP | |
> > --------------------------------------------------------------------------------------------------------------------------------------
>
> > The long merge finished a few moments ago after 20 hours.
>
> Merge commands are operated (usually) by an outer join
> between the two sets of data (I don't understand why your's
> is not - have you omitted the INSERT clause) and the join
> can be a hash, merge or nested loop.
>
> In both your cases, the optimiser has used a hash join.
>
> The major difference between the two plans is that the two
> sub-plans (one with a couple of analytic functions - which
> I assume is the new data, and one without - which I assume
> is the "old" data) have been reversed.
>
> In a hash join, the first "child" operation has its data scattered into
> a hash table and the data set from the second child is used to probe
> the hash table that has been created. Oracle basic strategy is to choose
> the data set that will return the smaller volume of data (which is not
> the same thing as the number of rows) as the first child.
>
> If the second plan is unsuitable, it is because Oracle's estimate of the
> volume of the two data sets was wrong. Possibly, however, a fairly
> small change in the predicates could have resulted in a sufficiently large
> change in the data volumes that the plan is the best possible plan, and
> the time is a side effect of the limit on the available memory for building
> the hash table.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thanks Jonathan.

So you think the parallel hint is not the one to blame?

What do you think about sort_merge join here?

TIA.

Cheers.

Carlos.

"Carlos" <miotromailcarlos@netscape.net> wrote in message
news:1191426971.871390.111260@22g2000hsm.googlegro ups.com...
> On 3 oct, 17:48, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk> wrote:
>> "Carlos" <miotromailcar...@netscape.net> wrote in message
>>
>> news:1191424330.534720.242750@k79g2000hse.googlegr oups.com...
>>
>> If the second plan is unsuitable, it is because Oracle's estimate of the
>> volume of the two data sets was wrong. Possibly, however, a fairly
>> small change in the predicates could have resulted in a sufficiently
>> large
>> change in the data volumes that the plan is the best possible plan, and
>> the time is a side effect of the limit on the available memory for
>> building
>> the hash table.
>>
>
> Thanks Jonathan.
>
> So you think the parallel hint is not the one to blame?
>
> What do you think about sort_merge join here?
>
>

Before blaming anything, or trying to fiddle with the query until it
works, I would examine the data patterns and work out the volume
of data in the old and new data sets to see if the optimizer's estimates
were close or far out. Then I'd try to work out why the estimates were
bad (if they were) and see if I could improve the estimates - possibly
by correcting the statistics on the tables. Then I'd try to work out if
a merge or NL would work more efficiently; and I'd consider (a) allowing
more memory to the session by switching to manual workarea execution
and (b) forcing the hash join order.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html