Hello Dick,
YOU ARE KIDDING. I've done this many times in the past and never encountered that type of behavior... I don't see what version/OS you are running? Was the table partitioned?
And, back by popular demand, is my highly overrated signature
Lisa Rutland Koivu
Oracle Database Administrator
[EMAIL PROTECTED]
NeoMedia
2201 Second St., Suite 600
Fort Myers, FL 33901, USA
Phone: 941-337-3434
Fax: 941-337-3668
www.neom.com <http://www.neom.com>
www.paperclick.com <http://www.paperclick.com>
www.qode.com <http://www.qode.com>
P a p e r C l i c k . c o m <http://www.paperclick.com/home.htm>
Enter Your PaperClick Code Here!
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 09, 2001 11:41 AM
To: Multiple recipients of list ORACLE-L
Subject: Parallel index builds can crash
To ALL,
Over the weekend I've been trying to rebuild a VERY large index on a data
warehouse table. Well in an attempt to get faster processing I invoked the
parallel option, but the index failed for the one reason I could not easily
figure out. I expected the index to be 4 to 5 GB in size when completed, but it
tried to create itself at 8 to 10 GB instead. Odd I thought until this morning
when I noticed that there was two temporary segments in the target tablespace.
I submitted a TAR to OTS for an explanation (Attached). Basically if you do
your index builds in parallel one should expect them to be the estimated size
times the parallel setting. OOPS!!! :-( Someone can correct me if they know
otherwise, but I don't remember seeing this in any of the documentation.
BTW: The final index size, now that it did successfully complete, is twice what
I expected (parallel 2).
Dick Goulet
____________________Forward Header_____________________
Author: [EMAIL PROTECTED] (Oracle Support)
Date: 4/9/2001 10:33 AM
Hello Richard,
Creating Indexes in Parallel
Parallel index creation works in much the same way as a table scan with an ORDER
BY clause. The table is randomly sampled and a set of index keys is found that
equally divides the index into the same number of pieces as the degree of
parallelism(DOP). A first set of query processes scans the table, extracts key,
ROWID pairs, and sends each pair to a process in a second set of query processes
based on key. Each process in the second set sorts the keys and builds an index
in the usual fashion.
After all index pieces are built, the parallel coordinator simply concatenates
the pieces (which are ordered) to form the final index.
Parallel local index creation uses a single server set. Each server process in
the set is assigned a table partition to scan, and
for which to build an index partition. Because half as many server processes are
used for a given DOP, parallel local index
creation can be run with a higher DOP.
Note:
When creating an index in parallel, the STORAGE clause refers to the
storage of each of the subindexes created by the query server processes.
Therefore, an index created with an INITIAL
of 5MB and a DOP of 12 consumes at least 60MB of storage during index
creation because each process starts with an extent of 5MB. When the query
coordinator process combines the sorted subindexes, some of the extents may
be trimmed, and the resulting index may be smaller than the requested 60MB.
ORA-1652
From version 7.x, we can create certain objects in parallel, or unrecoverable.
In order for Oracle to accomplish this, temporary segments are created that
eventually become a permanent part of the object, yet Oracle still refers to
them as temp segments. Thus, most of the time you receive this error, it will
be referring to the tablespace the object is going to be created in.
Do the following query to find out if you're out of extents:
select max(blocks), max (bytes) from sys.dba_free_space
where tablespace_name = '<tablespace in error message>';
For example, The above query may return:
SQL> blocks bytes
6143 12,580,864
Notice that the biggest CONTIGUOUS block of free space is only 6143 blocks and
Oracle needs a contiguous block of free space of 6144 to create an object.
You may have a lot of free space in separate blocks in your tablespace, but if
it is not contiguous, Oracle cannot use it. Allocating extents requires that
there be a contiguous block of free space.
SOLUTION:
1. Add a datafile to the tablespace
2. Adjust the storage parameters of the object you are trying to create.
Parameters to look at: initial extent, next extent, pct increase.
3. If you have a lot of free space in that tablespace, but the it is
very fragmented, you may want to consider rebuilding the tablespace.
4. Enable AUTOEXTEND for the datafile
- Also, Please review<Note:100492.1>Via metalink (Metalink - - >technical
library- - >reach with note#)
Title: ORA-01652: estimate space needed to create index
- Also further researched and found:<Bug:377439.-P>
Abstract: INDEX BUILD FAIL WITH PARALLEL DEGREE > 1
- is there any ora-7445 and core dump file in udump directory?
- is there an internal error (ora-600) trace file?
Please update the tar via metalink.
Thank you,
Oracle Support Services.
Have you tried MetaLink?
Search our technical libraries, create/review/update your TARs at:
http://metalink.oracle.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
