Re: Optimizing Big Index Build on Standard Edition

If the RAM disk isn't a resource 'visible' to both nodes then you can't use it as a temporary workspace across nodes although you can use it as a node-specific temp area, as Jonathan suggested.  

Are you using multi-pathing or ASM with this RAC installation?


 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On Monday, February 3, 2014 9:47 AM, Charlotte Hammond <charlottejanehammond_at_yahoo.com> wrote:
 
Hi All,

Many thanks to you all for the replies!

I think I've got a bit lost at the requirement for a Ram Disk to be 2x the index size.   Please could you point me to a description on how you calculate this? 

Currently I believe that I'm limited to 2Gb sort in memory due to _PGA_MAX_SIZE from below (admittedly I've not had a chance to confirm this but that's certainly the value that's activity for this parameter so it's either that or less).   But are you saying there's no benefit in having a RAM disk of 4Gb or 8Gb etc.?   The final index will be about 60Gb so there's no chance of squeezing 2x this into memory in one go.   The index is a composite (NUMBER(10),VARCHAR2(30)) both columns not null.

This is probably not going to help here though as it's Standard Edition 2-node RAC:  I will have sole use of one node to build the index, but there will be on-going activity on the other.   Therefore I can't use Ram disk for the temporary tablespace as it's not shared (or can I?  Does it matter that one node of the RAC can't see my temporary-temporary tablespace?   Sounds a bit iffy...!)

SSDs sound interesting but not something we've got the option of using here either unfortunately.     

Thanks for your comments!
Charlotte



On Friday, January 31, 2014 2:59 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
 
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Friday, January 31, 2014 9:39 AM
To: jonathan_at_jlcomp.demon.co.uk; charlottejanehammond_at_yahoo.com; '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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Friday, January 31, 2014 9:13 AM
To: mwf_at_rsiz.com; charlottejanehammond_at_yahoo.com; '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/
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
behalf of Mark W. Farnham [mwf_at_rsiz.com]
Sent: 31 January 2014 13:57
To: charlottejanehammond_at_yahoo.com; '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),
--
http://www.freelists.org/webpage/oracle-l


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

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