RE: Optimizing Big Index Build on Standard Edition
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 Feb 2014 18:09:21 +0000
Create a special user that is allowed to create the index for the owner - and
get the create index syntax right:
"create index owner.index ..."
Create a temporary tablespace on the local disc and make it the default temp
tablespace only for that user and no other.
Should be okay - run it past MoS if you're uncomfortable.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Charlotte Hammond [charlottejanehammond@xxxxxxxxx]
Sent: 03 February 2014 16:43
To: Mark W. Farnham; Jonathan Lewis; 'ORACLE-L'
Subject: Re: Optimizing Big Index Build on Standard Edition
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@xxxxxxxx> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto: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<mailto:jonathan@xxxxxxxxxxxxxxxxxx>;
charlottejanehammond@xxxxxxxxx<mailto: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>
[mailto: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<mailto:mwf@xxxxxxxx>;
charlottejanehammond@xxxxxxxxx<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on
behalf of Mark W. Farnham [mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>]
Sent: 31 January 2014 13:57
To: charlottejanehammond@xxxxxxxxx<mailto: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
Other related posts:
