Optimizing Big Index Build on Standard Edition
From: Charlotte Hammond <charlottejanehammond_at_yahoo.com>
Date: Fri, 31 Jan 2014 04:25:33 -0800 (PST)
Message-ID: <1391171133.52870.YahooMailNeo_at_web140002.mail.bf1.yahoo.com>
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
Date: Fri, 31 Jan 2014 04:25:33 -0800 (PST)
Message-ID: <1391171133.52870.YahooMailNeo_at_web140002.mail.bf1.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 31 2014 - 13:25:33 CET