| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Parallel index builds can crash
Larry Ellison kept saying during the iFS webcast that disk is cheap.
Good thing, too!
: )
Regards,
Patrice Boivin
Systems Analyst (Oracle DBA)
Bedford Institute of Oceanography
-----Original Message-----
From: dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com]
Sent: Monday, April 09, 2001 2:36 PM
To: Multiple recipients of list ORACLE-L
Subject: Re:RE: Parallel index builds can crash
Lisa,
Regrettably I'm not kidding, and neither was OTS. I'm on HP-UX
10.20 Oracle
8.0.4.4.0. According to OTS this is the "normal" behavior for all
versions of
Oracle, on all platforms, that support parallel query. On a small
index I could
see one not noticing, but on what should be a 4 GB index one notices
the extra 4
GB that goes down the drain!
Dick Goulet
____________________Reply Separator____________________
Author: "Koivu; Lisa" <lkoivu_at_qode.com>
Date: 4/9/2001 11:02 AM
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
lkoivu_at_qode.com
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-----
Sent: Monday, April 09, 2001 11:41 AM
To: Multiple recipients of list ORACLE-L
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: rdbms4_at_us.oracle.com (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: dgoulet_at_vicr.com
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: ListGuru_at_fatcity.com (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).
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;
charset=US-ASCII">
<META NAME="Generator" CONTENT="MS Exchange Server version
5.5.2653.12">
<TITLE>RE: Parallel index builds can crash</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Hello Dick, </FONT>
</P>
<P><FONT SIZE=2>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?</FONT></P>
<P><FONT SIZE=2>And, back by popular demand, is my highly overrated
signature</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=2>lkoivu_at_qode.com</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2> NeoMedia</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>2201 Second St., Suite 600</FONT>
<BR><FONT SIZE=2>Fort Myers, FL 33901, USA</FONT>
<BR><FONT SIZE=2>Phone: 941-337-3434</FONT>
<BR><FONT SIZE=2>Fax: 941-337-3668</FONT>
<BR><FONT SIZE=2>www.neom.com <<A HREF="http://www.neom.com"
TARGET="_blank">http://www.neom.com</A>> </FONT>
<BR><FONT SIZE=2>www.paperclick.com <<A
HREF="http://www.paperclick.com"
TARGET="_blank">http://www.paperclick.com</A>> </FONT>
<BR><FONT SIZE=2>www.qode.com <<A HREF="http://www.qode.com"
TARGET="_blank">http://www.qode.com</A>> </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>P a p e r C l i c k . c o m <<A
HREF="http://www.paperclick.com/home.htm"
TARGET="_blank">http://www.paperclick.com/home.htm</A>> </FONT>
<BR><FONT SIZE=2> </FONT>
</P>
<BR>
<P><FONT SIZE=2> Enter Your PaperClick Code Here!</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2> </FONT>
</P>
<BR>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: dgoulet_at_vicr.com [<A
HREF="mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Monday, April 09, 2001 11:41 AM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: Parallel index builds can crash</FONT>
</P>
<BR>
<P><FONT SIZE=2>To ALL,</FONT>
</P>
<P><FONT SIZE=2> Over the weekend I've been trying
to rebuild
a VERY large index on a data</FONT>
<BR><FONT SIZE=2>warehouse table. Well in an attempt to get
faster
processing I invoked the</FONT>
<BR><FONT SIZE=2>parallel option, but the index failed for the one
reason I
could not easily</FONT>
<BR><FONT SIZE=2>figure out. I expected the index to be 4 to 5
GB in size
when completed, but it</FONT>
<BR><FONT SIZE=2>tried to create itself at 8 to 10 GB instead.
Odd I
thought until this morning</FONT>
<BR><FONT SIZE=2>when I noticed that there was two temporary
segments in the
target tablespace. </FONT>
<BR><FONT SIZE=2>I submitted a TAR to OTS for an explanation
(Attached).
Basically if you do</FONT>
<BR><FONT SIZE=2>your index builds in parallel one should expect
them to be the
estimated size</FONT>
<BR><FONT SIZE=2>times the parallel setting. OOPS!!! :-(
Someone can
correct me if they know</FONT>
<BR><FONT SIZE=2>otherwise, but I don't remember seeing this in any
of the
documentation.</FONT>
</P>
<P><FONT SIZE=2>BTW: The final index size, now that it did
successfully
complete, is twice what</FONT>
<BR><FONT SIZE=2>I expected (parallel 2).</FONT>
</P>
<P><FONT SIZE=2>Dick Goulet</FONT>
</P>
<P><FONT SIZE=2>____________________Forward
Header_____________________</FONT>
<BR><FONT SIZE=2>Author: rdbms4_at_us.oracle.com (Oracle
Support)</FONT>
<BR><FONT SIZE=2>Date: 4/9/2001
10:33
AM</FONT>
</P>
<P><FONT SIZE=2> Hello Richard,</FONT>
</P>
<P><FONT SIZE=2>Creating Indexes in Parallel</FONT>
</P>
<P><FONT SIZE=2>Parallel index creation works in much the same way
as a table
scan with an ORDER</FONT>
<BR><FONT SIZE=2>BY clause. The table is randomly sampled and a set
of index
keys is found that</FONT>
<BR><FONT SIZE=2>equally divides the index into the same number of
pieces as the
degree of</FONT>
<BR><FONT SIZE=2>parallelism(DOP). A first set of query processes
scans the
table, extracts key,</FONT>
<BR><FONT SIZE=2>ROWID pairs, and sends each pair to a process in a
second set
of query processes</FONT>
<BR><FONT SIZE=2>based on key. Each process in the second set sorts
the keys and
builds an index</FONT>
<BR><FONT SIZE=2>in the usual fashion.</FONT>
<BR><FONT SIZE=2>After all index pieces are built, the parallel
coordinator
simply concatenates</FONT>
<BR><FONT SIZE=2>the pieces (which are ordered) to form the final
index. </FONT>
</P>
<P><FONT SIZE=2>Parallel local index creation uses a single server
set. Each
server process in</FONT>
<BR><FONT SIZE=2>the set is assigned a table partition to scan,
and</FONT>
<BR><FONT SIZE=2>for which to build an index partition. Because half
as many
server processes are</FONT>
<BR><FONT SIZE=2>used for a given DOP, parallel local index</FONT>
<BR><FONT SIZE=2>creation can be run with a higher DOP. </FONT>
</P>
<P><FONT
SIZE=2> Note:
</FONT>
</P>
<P><FONT
SIZE=2> When
creating an index in parallel, the STORAGE clause refers to
the</FONT>
<BR><FONT SIZE=2>storage of each of
the
subindexes created by the query server processes.</FONT>
<BR><FONT SIZE=2>Therefore, an index created with an INITIAL</FONT>
<BR><FONT
SIZE=2> of 5MB
and a DOP of 12 consumes at least 60MB of storage during
index</FONT>
<BR><FONT SIZE=2>creation because each
process starts
with an extent of 5MB. When the query</FONT>
<BR><FONT SIZE=2>coordinator process combines
the
sorted subindexes, some of the extents may</FONT>
<BR><FONT SIZE=2>be trimmed, and the resulting index may be
smaller than the requested 60MB. </FONT>
</P>
<BR>
<P><FONT SIZE=2>ORA-1652</FONT>
<BR><FONT SIZE=2>From version 7.x, we can create certain objects in
parallel, or
unrecoverable. </FONT>
<BR><FONT SIZE=2>In order for Oracle to accomplish this, temporary
segments are
created that </FONT>
<BR><FONT SIZE=2>eventually become a permanent part of the object,
yet Oracle
still refers to </FONT>
<BR><FONT SIZE=2>them as temp segments. Thus, most of the time you
receive this
error, it will </FONT>
<BR><FONT SIZE=2>be referring to the tablespace the object is going
to be
created in. </FONT>
</P>
<P><FONT SIZE=2>Do the following query to find out if you're out of
extents:
</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2> select max(blocks), max (bytes)
from
sys.dba_free_space </FONT>
<BR><FONT SIZE=2> where
tablespace_name =
'<tablespace in error message>'; </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>For example, The above query may return:</FONT>
<BR><FONT SIZE=2> SQL> blocks bytes </FONT>
<BR><FONT SIZE=2>
6143 12,580,864 </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>Notice that the biggest CONTIGUOUS block of free
space is only
6143 blocks and </FONT>
<BR><FONT SIZE=2>Oracle needs a contiguous block of free space of
6144 to create
an object. </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>You may have a lot of free space in separate blocks
in your
tablespace, but if </FONT>
<BR><FONT SIZE=2>it is not contiguous, Oracle cannot use it.
Allocating extents
requires that </FONT>
<BR><FONT SIZE=2>there be a contiguous block of free space. </FONT>
</P>
<BR>
<P><FONT SIZE=2>SOLUTION: </FONT>
<BR><FONT SIZE=2>1. Add a datafile to the tablespace </FONT>
<BR><FONT SIZE=2>2. Adjust the storage parameters of the object you
are trying
to create. </FONT>
<BR><FONT SIZE=2> Parameters to look at: initial extent,
next
extent, pct increase. </FONT>
<BR><FONT SIZE=2>3. If you have a lot of free space in that
tablespace, but the
it is </FONT>
<BR><FONT SIZE=2> very fragmented, you may want to
consider
rebuilding the tablespace. </FONT>
<BR><FONT SIZE=2>4. Enable AUTOEXTEND for the datafile </FONT>
</P>
<BR>
<P><FONT SIZE=2> </FONT>
</P>
<P><FONT SIZE=2> - Also, Please
review<Note:100492.1>Via
metalink (Metalink - - >technical</FONT>
<BR><FONT SIZE=2>library- - >reach with note#)</FONT>
<BR><FONT
SIZE=2>Title: &nb sp;&
nbsp; ORA-01652: estimate space needed to create index</FONT>
</P>
<BR>
<BR>
<BR>
<P><FONT SIZE=2> - Also further researched and
found:<Bug:377439.-P>
</FONT>
<BR><FONT SIZE=2>Abstract: INDEX
BUILD FAIL
WITH PARALLEL DEGREE > 1</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2> - is there any ora-7445 and core dump file in
udump
directory?</FONT>
<BR><FONT SIZE=2> - is there an internal error (ora-600) trace
file?</FONT>
</P>
<P><FONT SIZE=2>Please update the tar via metalink.</FONT>
<BR><FONT SIZE=2> Thank you,</FONT>
<BR><FONT SIZE=2>Oracle Support Services.</FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2> </FONT>
<BR><FONT SIZE=2>Have you tried MetaLink? </FONT>
<BR><FONT SIZE=2>Search our technical libraries,
create/review/update your TARs
at: </FONT>
<BR><FONT SIZE=2><A HREF="http://metalink.oracle.com"
TARGET="_blank">http://metalink.oracle.com</A> </FONT>
</P>
<BR>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: <A
HREF="http://www.orafaq.com"
TARGET="_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: </FONT>
<BR><FONT SIZE=2> INET: dgoulet_at_vicr.com</FONT>
</P>
<P><FONT SIZE=2>Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego,
California
-- Public Internet access / Mailing Lists</FONT>
<BR><FONT
SIZE=2>--------------------------------------------------------------------<
/FON
T>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an
E-Mail
message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru')
and in</FONT>
<BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB
ORACLE-L</FONT>
<BR><FONT SIZE=2>(or the name of mailing list you want to be removed
from). You may</FONT>
<BR><FONT SIZE=2>also send the HELP command for other information
(like
subscribing).</FONT>
</P>
</BODY>
</HTML>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: dgoulet_at_vicr.com
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: ListGuru_at_fatcity.com (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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: BoivinP_at_mar.dfo-mpo.gc.ca 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: ListGuru_at_fatcity.com (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).Received on Mon Apr 09 2001 - 12:43:17 CDT
 |
 |