Re: Surprising parameters for direct path read in 100046 trace

  • From: Chris Dunscombe <chris@xxxxxxxxxxxxxxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 09 Mar 2006 09:44:45 +0000

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@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



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
..................



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




Chris Dunscombe

www.christallize.com

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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2006