Re: Large index creation speed up / issues with direct path read temp / direct path write temp

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
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 




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