RE: Optimizing Big Index Build on Standard Edition
Date: Fri, 31 Jan 2014 09:39:21 -0500
Message-ID: <1b5d01cf1e92$3bc6da60$b3548f20$_at_rsiz.com>
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
Received on Fri Jan 31 2014 - 15:39:21 CET