Hi,
I would definitely also have a look on sql_monitor if you have DT pack.
I will give you a better overall picture of what is going on.
How about going higher in parallel? Parallel 4 seems petty low for this
task.
Consider that you are bottlenecked on a switch.
Regards
Lothar
On 04.07.2015 09:16, GG wrote:
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
