LOL!!
On Monday 09 April 2001 09:54, Tim Sawmiller wrote:
> 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: Jared Still
INET: jkstill_at_cybcon.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).
Received on Tue Apr 10 2001 - 02:05:00 CDT
