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

1. I would really increase pga_aggregate_target, to allow sort data in memory, moving the minimum to a higher level increasing the memory_target too And would test with several combinations of pga and db_cache_size, and check the performance views to tune them.
2. I would use nologging (if you have standby then create in both database with nologging)
3. in 12c I would try the new compression of indexes and see how is working, because the old compression of indexes 11g, reduced performance to the half.
4. I could recreate the table in the order of the index columns, set the initial extent to the size of the index and test parallelism.

2015-07-04 3:16 GMT-04:00 GG <grzegorzof_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-l
>
>
>

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