Large index creation speed up / issues with direct path read temp / direct path write temp
Date: Sat, 4 Jul 2015 09:16:48 +0200
Message-ID: <55978860.3010009_at_interia.pl>
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-lReceived on Sat Jul 04 2015 - 09:16:48 CEST