Re: PX Deq Credit: send blkd

"Carlos" <miotromailcarlos_at_netscape.net> wrote in message news:1191424330.534720.242750_at_k79g2000hse.googlegroups.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