Stefan,
2GB memory to do 70GB index should be an easy one-pass sort the algorithm is
"n-squared" viz: we might have to produce 140 streams of 1GB (allowing for
various overheads out of 2GB) but then a merge of 140 streams at (say) 1MB per
reload per stream needs only 140MB of memory. With 2GB you might be able to
sort something in the order of 1TB in a one-pass.
Greg,
When the storage says 40ms and Oracle says 750ms I think of three things:
a) There's a queue between the storage and Oracle - e.g. a very small queue
depth on the HBA so talk to the admins about configuration
b) The Oracle accounting is misleading somewhere - the 10033 trace will tell
you how many asynch reads and writes took place, compare this with session
stats and the 10046 trace to see if your 750ms is actually the sum of multiple
read and write events.
c) Frits Hoogland has got lots of blog posts about direct path I/O and the odd
accounting and ways to dive very deeply into what is going on.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 04 July 2015 09:09
To: oracle-l@xxxxxxxxxxxxx; grzegorzof@xxxxxxxxxx
Subject: Re: Large index creation speed up / issues with direct path read temp
/ direct path write temp
Hi GG,
multipass sorts should be avoided in general, but with your settings (70 GB vs.
2 GB) it is pretty unavoidable i guess.
waits, as AWR reported direct path read temp as top wait (second was direct
path write temp) with 753ms per I/O. When trying to confirm that on the
storage side we concluded that storage reported only 30-40 ms waits during
that period.
I've checked sysmetric during the slow index creation and it reported like
40MB/s reads and 30MB/s writes whereas the storage can easy sustain
400MB/s and the whole server was like 96% idle.
What I'm looking for is the way to deep dive / troubleshoot the issue. Any
ideas ? Short stack profiling during the issue , some events to turn
on?
GG <grzegorzof@xxxxxxxxxx> hat am 4. Juli 2015 um 09:16 geschrieben:--
Hi,
when dealing with relatively large (70GB ) index creation (composite
with 3 cols first low cardinality only 4 values for 2bilions of rows and
compress was 2)
with parallel 4 which took about 4hours to complete on moderate hardware
(Solaris M4000 48 cpu + 3PAR storage ) I've observed strange issue with
direct path read temp
direct path write temp
waits, as AWR reported direct path read temp as top wait (second was
direct path write temp) with 753ms per I/O .
When trying to confirm that on the storage side we concluded that
storage reported only 30-40 ms waits during that period.
I've checked sysmetric during the slow index creation and it reported
like 40MB/s reads and 30MB/s writes
whereas the storage can easy sustain 400MB/s and the whole server was
like 96% idle .
So for me it looks like above waits may include some additional work
into instrumentation no i/o related.
All on ORacle Solaris EE 11.2.0.3 latest PSU.
We used manual workareas, with below tweaks supposed to speed the process up
alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set sort_area_size=2G ;
What I'm looking for is the way to deep dive / troubleshoot the issue .
Any ideas ? Short stack profiling during the issue , some events to turn
on ?
From v$sql_workarea_active I was able to see that temp_segsize was
growing really slow and v2 sort was used (maybe v1 is better choice ?) .
Regards
GG
--
//www.freelists.org/webpage/oracle-l
