RE: Optimizing Big Index Build on Standard Edition

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <mwf@xxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <charlottejanehammond@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 31 Jan 2014 09:59:05 -0500

Doh, sent too soon: And IF the single column is nullable and you have a
significant number of nulls (like half or more) then you can redo the math
for the number of non-null values to get the max single column width.

Notice that you have to do this same arithmetic for whatever you isolate
TEMP to. (Crucial, for one, has .96 TB ssd drives for under $600, though I
wouldn't slap them into a production server unplexed for any serious
duration.)

Isolated local storage already present, if available and currently not under
significant load is at an even better price point!

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Friday, January 31, 2014 9:39 AM
To: jonathan@xxxxxxxxxxxxxxxxxx; charlottejanehammond@xxxxxxxxx; 'ORACLE-L'
Subject: RE: Optimizing Big Index Build on Standard Edition

All valid points. Likely only something like a one or two alpha status or 1
or 2 digit class code would fit the bill, and I do think you have to use a
pessimistic budget in this sort of operation where everyone else is shut out
for the duration and you probably don't get to test.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Friday, January 31, 2014 9:13 AM
To: mwf@xxxxxxxx; charlottejanehammond@xxxxxxxxx; 'ORACLE-L'
Subject: RE: Optimizing Big Index Build on Standard Edition



Mark,

If you do the arithmetic, and take the pessimistic 2x storage then the index
would have to be a single column index with an average column length of 2.

I like the SSD idea -- but as an alternative is there a local disk on the
server which could be used to create a local TEMP for the duration ?

Some questions I'd ask myself:

While most of the waits were direct path read temp, what fraction of the
session time was CPU and what was I/O ? Creating a very large memory isn't
always the fastest way to sort.
What were the sizes of the reads and writes to temp ? Has the internal code
done something odd because of a large boundary condition ?
What is the average column length of each of the columns in the index How
sure are you that the session is taking up the maximum memory that you want
it to 


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Mark W. Farnham [mwf@xxxxxxxx]
Sent: 31 January 2014 13:57
To: charlottejanehammond@xxxxxxxxx; 'ORACLE-L'
Subject: RE: Optimizing Big Index Build on Standard Edition

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),
--
//www.freelists.org/webpage/oracle-l


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


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


Other related posts: