Re: Surprising parameters for direct path read in 100046 trace

Jonathan,

Thanks for that most helpful. I'm not on site again until next Tuesday, and I don't have all the details with me. I'll investigate then and let you know.

Your point on block re-reading stacks up with the fact that there are many pairs
of blocks being re-read as per the trace snippet below. Oracle re-reads one pair
many times and then moves onto re-reading another pair.

Thanks again,

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
>

>>
>> 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= 3 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= 4 p1=202 p2=641040 p3=1
>> WAIT #3: nam='direct path read' ela= 5 p1=202 p2=641041 p3=1
>> WAIT #3: nam='direct path read' ela= 3 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= 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= 3 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= 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