Now back in the office.
Setting sort_multiblock_read_count = 8 at the session level (deprecated at the system level) has overcome the problem, no more re-reading of pairs of sort segment blocks. The SQL now runs in 50 mins - 60 mins compared to approx 3 hrs previously.
The join condition in the merge join is an in-equality, bounded range based condition which fits your explanation:
op.wef_date(+) <= mph.day_date AND op.wet_date(+) >= mph.day_date
Also I noticed that the block count for the direct path reads from the sort segments was still sometimes just 1 even with sort_multiblock_read_count = 8, (at other times it was 7). Any ideas?
Thanks again for your help, much appreciated.
Chris
Quoting jonathan@xxxxxxxxxxxxxxxxxx:
In the merge join, is your join condition an equality, or an range-based join such as: t2.col2 between t1.col1 - 1 and t1.col1 + 1
If so, then you may be seeing the physical effects of Oracle having to reposition in the t1 sorted data for repeated appearances of the same t2 value.
For example, the t2 data when sorted reads 1,1,1,2,2,3,3,3,3, .. So (using the above "between" join), Oracle would have to read t2.col2 = 1 scan the sort dump from 0 to 2 t2.col2 = 1 scan the sort dump from 0 to 2 t2.col2 = 1 scan the sort dump from 0 to 2 t2.col2 = 2 scan the sort dump from 1 to 3 t2.col2 = 2 scan the sort dump from 1 to 3 t2.col2 = 3 scan the sort dump from 2 to 4 t2.col2 = 3 scan the sort dump from 2 to 4 t2.col2 = 3 scan the sort dump from 2 to 4 t2.col2 = 3 scan the sort dump from 2 to 4
For some reason, your sort_multiblock_read_count (which is probably the value that controls the multiblock read of sorted data segments during merge passes***) is one - so every time you re-set the range scan on the second data set, you have to re-read the block.
This is just guesswork, by the way. I know that the reset is needed for non-equality joins but suspect the correctness of my comment about the multiblock read of "merge passes" (marked ***) above as my previous experience of this parameter says that it applies to the reads required for merging within a single single sort that goes onepass to multiplass. I have not checked if it is also relevant in any way to merge joins.
Regards Jonathan Lewis
------------------------------
Date: Tue, 07 Mar 2006 10:14:35 +0000 From: Chris Dunscombe <chris@xxxxxxxxxxxxxxxxxxxxx> Subject: Surprising parameters for direct path read in 100046 trace
Hi,
I've traced a long running piece of SQL and observed the following in the trace file (only a small sample):
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1 WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1 WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1 WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1 WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641041 p3=1 WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1 ..................
-- //www.freelists.org/webpage/oracle-l
Chris Dunscombe
www.christallize.com
-- //www.freelists.org/webpage/oracle-l