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.