| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PX Deq Credit: send blkd
On 3 oct, 15:28, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Oct 3, 2:53 am, Carlos <miotromailcar..._at_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
| 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
| 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. Received on Wed Oct 03 2007 - 10:12:10 CDT
 |
 |