Re: initial/next computation with DOP 4

You really should be looking at locally
managed tablespaces with uniform extent
size (see www.dbazine.com for one article on this, www.oracledba.co.uk for another).

Even if you want to avoid LMTs, then you should be looking at aiming for uniform
extent sizing by mechanical methods.
(initial = next = minimum extent).

You problem comes from the fact that when you create an index using parallel slaves, each slave creates its own section of the index using the base initial/next, and when all slaves have completed, the co-ordinator creates a root block linking them together.

To minimise space wastage, just work on the fact that each slave will, on average, leave half an extent of space unused.

In your case, I would probably consider 16M or 32M as the unit size - 16M is the index was only going to grow slowly after the rebuild, 32M if it was likely to grow at a rate that would result in extra extents appearing more than once per month. (16 and 32 because they are powers to 2, and in your case lead to 40 to 80 extents)

Smaller extents give you less wastage, larger extents give you slower subsequent growth rates and a lower granularity of monitoring.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 24 February 2003 00:23

> OpenVMS 7.1-2
> Oracle 8.1.7.4
> db_block_size 4096
>
> I need to re-create a large table and its associated
> indexes as fast as possible, and with a limited amt of
> disk space. I have a new tblspace at 7000m for index
> creation.
>
> I'm creating indexes with DOP4. (I really need the
> speed I get with parallel to create the indexes.) In
> my testing, I keep running out of contiguous space in
> my index tablespace -- it gets fragmented
> all-to-blazes. I end up with each index at 4 extents.
>
> I have a pretty good estimate of how large the indexes
> will be. Is there some sane way to compute a
> reasonable initial and next extent when using
> parallel?
>
> As an example, I created an index (wodh_pk) with
> initial 600m next 20m pctincrease 0. The index is now
> 1334m with 4 extents. If I know the index should be
> about 1300 megs, what's a good initial and next size?
>
> Thanks for any assistance!
> Barb
>
>
> SELECT SEGMENT_NAME, BYTES/1024/1024 M, EXTENTS,
> NEXT_EXTENT,
> PCT_INCREASE FROM DBA_SEGMENTS
> WHERE TABLESPACE_NAME='ARCHIDX';
>
> Segment Next Pct
> Name M Extents Extent Increase
>
> ------------ ---------- ------- ------------ ----
> WODH_PK 1333.55859 4 20,971,520 0
> WODH_FK1 821.289063 4 20,971,520 0
>
> here's the code . . .
>
> create unique INDEX
> REPORT_REP.WODH_PK
> ON
> REPORT_REP.WORK_ORDER_DETAILS_NOHIST(WORK_ORDER_KEY)
> TABLESPACE ARCHIDX
> STORAGE(INITIAL 600M
> NEXT 20M
> MINEXTENTS 1
> MAXEXTENTS 249)
> PARALLEL (DEGREE 4)
> ;
>
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - forms, calculators, tips, more
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Barbara Baker
> INET: barbarabbaker_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
services
> --------------------------------------------------------------------
-
> 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.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).