Re: Surprising parameters for direct path read in 100046 trace

  • From: Chris Dunscombe <chris@xxxxxxxxxxxxxxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx
  • Date: Wed, 15 Mar 2006 07:52:51 +0000

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


Other related posts:

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