Re: PX Deq Credit: send blkd

On 3 oct, 17:48, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Carlos" <miotromailcar..._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 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. Received on Wed Oct 03 2007 - 10:56:11 CDT