RE: RE: Parallel index builds can crash

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Mon, 09 Apr 2001 11:18:25 -0700
Message-ID: <F001.002E57C7.20010409104636@fatcity.com>

Wow, Tim, that's the longest "RTFM" I've ever read!

<==> -----Original Message-----
<==> From: Tim Sawmiller [mailto:sawmillert_at_state.mi.us]
<==> Sent: Monday, April 09, 2001 2:02 PM
<==> To: Multiple recipients of list ORACLE-L
<==> Subject: Re:RE: Parallel index builds can crash
<==>
<==>
<==> BTW, this is documented in the the Oracle 8 Concepts
<==> manual, the chapter on "Parallel Execution" (28 I
<==> think), Heading "Parallel DDL", sub heading "Space
<==> Management for Parallel DDL".
<==>
<==> >>> dgoulet_at_vicr.com 04/09/01 01:35PM >>>
<==> 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>
<==>
<==> 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 <<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>&gt
<==> ; 
<==>  
<==> 
<==> 
<==>
<==>  Enter Your PaperClick Code Here!
<==>  
<==>  
<==> 
<==> 
<==>
<==> -----Original Message-----
<==> From: dgoulet_at_vicr.com [<A
<==> HREF="mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A
<==> >]
<==> 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: rdbms4_at_us.oracle.com (Oracle
<==> Support)
<==> <FONT
<==> SIZE=2>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. 
<==> 
<==>
<==> <FONT
<==> SIZE=2>        &n
<==> bsp; Note:
<==> 
<==> 
<==>
<==> <FONT
<==> SIZE=2>        &n
<==> bsp; 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
<==> <FONT
<==> SIZE=2>        &n
<==> bsp; 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#)
<==> <FONT
<==> SIZE=2>Title:       &n
<==> bsp;   &
<==> nbsp;  ORA-01652: estimate space needed to create
<==> index
<==> 
<==> 
<==> 
<==> 
<==>
<==>  - Also further researched and
<==> found:<Bug:377439.-P>
<==> 
<==> <FONT
<==> SIZE=2>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:   
<==> <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>
<==> -- 
<==> 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
<==> <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).
<==> 
<==>
<==> </BODY>
<==> </HTML>
<==> --
<==> Please see the official ORACLE-L FAQ: http://www.orafaq.com
<==> --
<==> Author:

<==>     INET: dgoulet_at_vicr.com

<==>     INET: sawmillert_at_state.mi.us