Optimizing Big Index Build on Standard Edition
- From: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 31 Jan 2014 04:25:33 -0800 (PST)
Hi All,
For reasons I won't bore you with I need to build an index on a table on a
legacy application on a Standard Edition database which has grown like topsy
and is now almost 3 billion rows (I know, this is a disaster and it being
addressed, but that's for another day.....)
I need to this index build to go as fast as possible.
Oracle 11.2.0.4 Standard Edition - so no parallelism
The backend storage is very slow and most of the waits are "direct path read
temp". I thought that increasing the PGA might help keep more of the sort in
memory and avoid the slow I/O. The server has 96Gb RAM and nobody else will
be using it during the index build.
I tried setting a large PGA_AGGREGATE_TARGET and _PGA_MAX_SIZE but this appears
to be limited to 2Gb (MOS 453540.1 How to Super-Size Work Area Memory Size Used
by Sessions? - bug 3946308). I also had a look at "old school" SORT_AREA_SIZE
but that appears to be a 32-bit integer and is also limited to 2Gb.
Is there any other way to get a bigger work area memory size?
Or, if I'm on the wrong track completely, any other way to get the index build
to go faster (upgrading the database version or the storage isn't going to
happen!)
Thanks for any tips!
Charlotte
--
//www.freelists.org/webpage/oracle-l
Other related posts:
