RE: Optimizing Big Index Build on Standard Edition

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 31 Jan 2014 08:57:09 -0500
Message-ID: <1af701cf1e8c$56156090$024021b0$_at_rsiz.com>

IF indeed the problem is slow throughput of temp disk, and IF indeed no other work is being done, and IF about 2x the index size is not bigger than 96GB minus enough room to run your database (sga size plus enough room for the background sessions, the rebuilt session, and probably a few monitoring session), IF you can do a shutdown, restart before and after the index rebuild, and IF your operating system supports configuration of "ramdisk", THEN it seems a likely way to de-heat your disk farm (and therefore the table source and index final destination) from competing i/o seeks and data from temp would be to:
1) shutdown
2) configure a clearly sufficient amount of your memory to fit the index temporary storage as ramdisk

3) point the user creating the index as the ramdisk for temp
4) create the index
5) point the user creating the index back at your normal temp
6) shutdown
7) get rid of the ramdisk
8) startup

You may also be able to do this much less drastically if you can plug some decently fast flash SSD into your server to define as TEMP for the operation, remembering that in the unlikely event of a failure for this single operation all you lose is temp. (Even if your bare metal throughput with no completion i/o rates for your disk farm is marginally better than the SSD, a big bit of the throughput increase likely is the elimination of competition. Since you've reported that it is an old slow disk farm it seems likely the SSD may also be faster at the hardware layer as well.) IF you decide to permanently plug some SSD into your box, then of course it has to be "enterprise class" usually meaning no more likely to fail than your existing diskfarm.

If online and archived redo logs are tangled up on the same throughput pacing pieces of the stack from programs writing to the persistence layer, then peeling them away from each other and from the datafiles where your table and indexes are permanent will probably tend to reduce competition for throughput for your table during the read phase and from your index for the write phase. (Online logs get lots of frequent little writes and intermittent big reads when arch is running; archived logs get big chunks of write from arch. Moving online logs can be important for removing the competition for i/o from your table and index. You'll probably get push back if folks misinterpret your desire to separate logs as an effort to speed up online log operations themselves.) But your observed wait reported is for temp, so isolating temp seems more important than this.

(noticing that IF you're only doing the one table and one index there is no mileage from separating them; that can only apply if two separate jobs would have the indexes of one job's work interrupting streaming reads from the other job's table, or if you separate the index destination writes by index for a single job doing a lot of inserts on a particular table, so you don't have to worry about the statistical plus/minus possibilities of that for this index rebuilt. Most people don't have enough independent threads of i/o from program to the persistence layer for this to be useful any more, and the dropping cost of SSD diminishes the value of the calculations in most situations.)

Now, we didn't talk about how many indexes you have on this table. If there is more than one, it seems unlikely you want to rebuild any others, so physical re-ordering of the table sounds like a non-starter. IF, however, this index is the only index, and IF indeed no one else can molest the table while you're building the index, and IF the NOSORT clause of index creation operates correctly on your release/version/patch, then copying the table into the index order before the index creation should work quite nicely. IF this narrow case is true, you will also have minimized the actual cluster factor of the index which has substantial benefits (perishable as new rows come in out of order, but still significant for the existing 3 billion rows.)

Good luck,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond
Sent: Friday, January 31, 2014 7:26 AM
To: ORACLE-L
Subject: Optimizing Big Index Build on Standard Edition

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-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 31 2014 - 14:57:09 CET