Re: Surprising parameters for direct path read in 100046 trace

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_at_jlcomp.demon.co.uk:

> 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_at_thedunscombes.f2s.com>
>> 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
>> ..................
>>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Chris Dunscombe

www.christallize.com

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