RE: Optimizing Big Index Build on Standard Edition
Date: Mon, 3 Feb 2014 18:09:21 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDA242_at_exmbx05.thus.corp>
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
_at_jloracle
From: Charlotte Hammond [charlottejanehammond_at_yahoo.com] 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_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> [mailto: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<mailto:jonathan_at_jlcomp.demon.co.uk>; charlottejanehammond_at_yahoo.com<mailto: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> [mailto: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<mailto:mwf_at_rsiz.com>; charlottejanehammond_at_yahoo.com<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Mark W. Farnham [mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>] Sent: 31 January 2014 13:57
To: charlottejanehammond_at_yahoo.com<mailto: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
Received on Mon Feb 03 2014 - 19:09:21 CET