Re: Excessive disk reads, hash join, parallel, Insert as select

Diagnostic strategy:

1. enable event 10104 (the hash join trace) before running the code. That should give you some idea of what's gone wrong.
2. if it's really important, you could try embedding the gather_plan_statistics hint in the code (or set statistics_level = all) then after the query has been running for a little while pull the plan with stats from memory with dbms_xplan() with the 'allstats' option. (You can do this after interrupting the query, rather than letting it run to completion.
3. if you can let a long one run to completion you can also follow it up with a query against v$pq_tqstat to see if the problem was a data distribution problem.

Oracle hash join isn't quite "grace"ful - it doesn't do a recursive re-distribution if it finds that its first partitioning is wrong. That's why a multi-pass hash join can go badly wrong if the session is short of memory when the hash join starts. If (for example) Oracle decides to partition the data 16 ways, to turn the join into 16 "mini hash joins" and then finds that only one 10th of the first (small) partition fits in memory it won't repartition, it just loads the partition in 10 consecutive pieces and scans the corresponding second (large) partition once for each piece.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: <Laimutis.Nedzinskas_at_seb.lt> To: <oracle-l_at_freelists.org> Sent: Friday, May 24, 2013 12:24 PM Subject: Excessive disk reads, hash join, parallel, Insert as select

| Hi
| One more try (i.e. cry) for help:
|
| 11.2.0.3 db does insert as select from 10.2.0.4 db.
| The plan is hash join (normally it reads 200GB and completes) and
parallel
| degreee 4.
| However, time and again the statement goes into infinite processing and
| gets killed with 6TB of data read from disks (data source is V$SQL and
| DBA_HIST_SQLSTATS)
| Any ideas, bug references ?
| My best guess - hash join grace algorithm
| (http://en.wikipedia.org/wiki/Hash_join) overpartitions...
|
| Thank you in advance, Laimis N
|
| --
| http://www.freelists.org/webpage/oracle-l
|

--
http://www.freelists.org/webpage/oracle-l