Re:RE: Parallel index builds can crash

From: <dgoulet_at_vicr.com>
Date: Mon, 09 Apr 2001 10:06:46 -0700
Message-ID: <F001.002E55FD.20010409095320@fatcity.com>

Lisa,

What do you mean "lie & claim not to"? I have one EVERY day.

BTW: Tim, it's initial xxx by minextents x. I had minextents 4, ended up with 8. Made a real mess of sorts, except now the index is 50% empty. The behavior did not make sense since I've user parallel in table creations in the past to speed matters up & this behavior was not displayed. It appears to be a peculiarity with indexes.

And: Lisa, the parallel slaves divy up the work by rowid's if you look at the cursor their executing it will say "where rowid < :1" or "where rowid > :1". In this case the parallel turds build two indexes, one on the top half & the other on the bottom half & then join them at the root point. Pretty slick, except in my case it took 18+ hours on each run.

Dick Goulet

____________________Reply Separator____________________
Author: "Koivu; Lisa" <lkoivu_at_qode.com>
Date:       4/9/2001 9:10 AM

On a partitioned table? If you have a slave for each partition, how would this be wrong? Or, less slaves than partitions?

It was pounded into my head not to use parallel with non-partitioned tables because without partitions the slaves would not know how to divy up the work. Seemed to me that it should be smart enough to split it up by extents, but what do I know. Any comments?

I never did claim to be that incredibly bright and I did burn quite a few brain cells over the weekend. Forgive my ignorance, people ! We all have our days and then there are those of us who lie and claim not to.

-----Original Message-----
Sent: Monday, April 09, 2001 12:55 PM
To: Multiple recipients of list ORACLE-L

Well, duh, of course you're going to get 12 pieces, each of initial xxx size. How else can it do a parallel creation? Seems pretty intuitive to me...but then, I'm a pretty smart guy!

8-)

>>> lkoivu_at_qode.com 04/09/01 11:55AM >>> 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tim Sawmiller
INET: sawmillert_at_state.mi.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

<!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>

On a partitioned table?  If you have a slave for each partition, how would this be wrong?  Or, less slaves than partitions?

It was pounded into my head not to use parallel with non-partitioned tables because without partitions the slaves would not know how to divy up the work.  Seemed to me that it should be smart enough to split it up by extents, but what do I know.  Any comments?

I never did claim to be that incredibly bright and I did burn quite a few brain cells over the weekend.  Forgive my ignorance, people !  We all have our days and then there are those of us who lie and claim not to.

-----Original Message----- From: Tim Sawmiller [<A HREF="mailto:sawmillert_at_state.mi.us">mailto:sawmillert_at_state.mi.us</A>]

<BR><FONT SIZE=2>Sent: Monday, April 09, 2001 12:55 PM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: RE: Parallel index builds can crash</FONT>
</P>
<BR>

Well, duh, of course you're going to get 12 pieces, each of initial xxx size.  How else can it do a parallel creation?  Seems pretty intuitive to me...but then, I'm a pretty smart guy!

8-)

>>> lkoivu_at_qode.com 04/09/01 11:55AM >>>
 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

<BR><FONT SIZE=2>&nbsp;</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>&nbsp;</FONT>
<BR><FONT SIZE=2>&nbsp;NeoMedia</FONT>
<BR><FONT SIZE=2>&nbsp;</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 &lt;<A HREF="http://www.neom.com"

TARGET="_blank">http://www.neom.com</A>> www.paperclick.com <<A HREF="http://www.paperclick.com" TARGET="_blank">http://www.paperclick.com</A>> www.qode.com <<A HREF="http://www.qode.com" TARGET="_blank">http://www.qode.com</A>>  
 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>>

<BR><FONT SIZE=2>&nbsp;</FONT>
</P>
<BR>

<P><FONT SIZE=2>&nbsp;Enter Your PaperClick Code Here!</FONT>
<BR><FONT SIZE=2>&nbsp;</FONT>
<BR><FONT SIZE=2>&nbsp;</FONT>

<P><FONT SIZE=2>-----Original Message-----</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>
</P>
<BR>

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

<BR><FONT SIZE=2>what</FONT>
<BR><FONT SIZE=2>I expected (parallel 2).</FONT>
</P>

Dick Goulet

<P><FONT SIZE=2>____________________Forward Header_____________________</FONT>

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

<BR><FONT SIZE=2>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

<BR><FONT SIZE=2>concatenates</FONT>
<BR><FONT SIZE=2>the pieces (which are ordered) to form the final index. </FONT>
</P>

Parallel local index creation uses a single server set. Each server process

<BR><FONT SIZE=2>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

<BR><FONT SIZE=2>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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Note:

When creating an index in parallel, the STORAGE clause refers to the
 storage of each of the       subindexes created by the query server

<BR><FONT SIZE=2>processes.</FONT>
<BR><FONT SIZE=2>Therefore, an index created with an INITIAL</FONT>
<BR><FONT SIZE=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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

<BR><FONT SIZE=2>60MB.&nbsp; </FONT>
</P>
<BR>

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.

<P><FONT SIZE=2>Do the following query to find out if you're out of extents:
</FONT>
<BR><FONT SIZE=2>&nbsp; </FONT>
<BR><FONT SIZE=2>&nbsp;&nbsp;&nbsp; select max(blocks), max (bytes) from

sys.dba_free_space  
       where  tablespace_name = '<tablespace in error message>';

<BR><FONT SIZE=2>&nbsp;</FONT>
<BR><FONT SIZE=2>For example, The above query may return:</FONT>
<BR><FONT SIZE=2>&nbsp;SQL&gt; blocks&nbsp; bytes </FONT>
<BR><FONT SIZE=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

<P><FONT SIZE=2>there be a contiguous block of free space. </FONT>
</P>
<BR>

<P><FONT SIZE=2>SOLUTION:&nbsp; </FONT>

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.

<BR><FONT SIZE=2>4. Enable AUTOEXTEND for the datafile </FONT>
</P>
<BR>

- Also,  Please review<Note:100492.1>Via metalink (Metalink - - >technical library- - >reach with note#) Title:           & nbsp;  ORA-01652: estimate space needed to create index

</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=2>&nbsp;- Also further researched and found:&lt;Bug:377439.-P&gt;

Abstract:       INDEX BUILD FAIL WITH PARALLEL DEGREE > 1

</P>
<BR>
<BR>

- is there any ora-7445 and core dump file in udump directory?
  - is there an internal error (ora-600) trace file?

<P><FONT SIZE=2>Please update the tar via metalink.</FONT>
<BR><FONT SIZE=2>&nbsp;Thank you,</FONT>
<BR><FONT SIZE=2>Oracle Support Services.</FONT>

<BR><FONT SIZE=2>&nbsp;</FONT>
<BR><FONT SIZE=2>&nbsp; </FONT>
<BR><FONT SIZE=2>&nbsp; </FONT>

Have you tried MetaLink?   Search our technical libraries, create/review/update your TARs at:   
 <A HREF="http://metalink.oracle.com" TARGET="_blank">http://metalink.oracle.com</A>

-- 
 Please see the official ORACLE-L FAQ: <A HREF="http://www.orafaq.com" TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: </FONT>
<BR><FONT SIZE=2>&nbsp; INET: dgoulet_at_vicr.com </FONT>
</P>

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California       
-- Public Internet access / Mailing Lists
 <FONT

SIZE=2>--------------------------------------------------------------------</FON

T>
 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: <A HREF="http://www.orafaq.com" TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: Tim Sawmiller</FONT>
<BR><FONT SIZE=2>&nbsp; INET: sawmillert_at_state.mi.us</FONT>
</P>

SIZE=2>--------------------------------------------------------------------</FON

</BODY>
</HTML> Received on Mon Apr 09 2001 - 12:06:46 CDT