Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: effect of Initial and Next exents...

RE: effect of Initial and Next exents...

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Thu, 26 Jul 2001 09:39:13 -0700
Message-ID: <F001.003562A5.20010726095640@fatcity.com>

Lisa,
 You are right about the concern about fragmentation over the number of extents. When you use LMT's that have manually sized extents rather that the auto-allocate option, you have a good handle on the fragmentation problem. If you have a number of different tablespaces you can size the extents to match the tables put in them or visa-versa. As an example: if the table is large make the extents large (20 M), if the tables are small make the extents small (10 K). when you built the tables make your initial and next extent size the same extent size as the LTM and or a multiple of the LTM extent size. The waste will be eliminated if the table is active and the WHM is moved downward.
 If you use the autoallocate option the LMT extents will grow in size as the need for space increases.
 For an explaination see:
http://www.oracle.com/oramag/oracle/00-nov/o60o8i.html I have migrated a 15 tablespace , 150 table , 72 GIG database with the data critical by date in each table and there was a lot of wasted space to LMT with 148 tablespaces partitioned by date range for the tables and the waste (free space) is minimul and the speed of the applications has increased. A BIGGGG plus will be the database management options I will have when the damagements decides on the data retention policy.
ROR mª¿ªm

>>> lisa.koivu_at_efairfield.com 07/26/01 11:47AM >>> Raghu,

There are differing opinions regarding multiple extents. I subscribe to the belief that multiple extents are not a bad thing. The latency between the extent reads is not a factor until the number of extents reaches ~4000. I remember reading a white paper on this topic, however I can't tell you exactly where it is off the top of my head.

If I was the dba for this database, I would be more concerned with fragmentation and size of extents than the number of extents. If you understand your data thoroughly, have fairly accurate estimates for growth and understand the application it is supporting, you can choose your extent sizes properly and won't have the problem of 4000+ extents. Uniform extent sizes are optimal to avoid fragmentation.

My choice to avoid fragmentation is the method in the white paper entitled 'How to Stop Defragmenting and Start Living'. I don't know about LMT's, haven't read up on them, but just from the discussions I've seen on the list about them, it looks like may be an implementation of this idea.

Here's another way to look at it: If you think multiple extents are bad, are you going to create a tablespace with a 6gb datafile for your 6gb 1-extent index and hope it doesn't grow? With larger databases this idea breaks down very quickly.

HTH, and list, correct me if I am wrong about LMT's. I can always blame it on the pain killers.

Lisa Koivu
Ft. Lauderdale, FL, USA

> -----Original Message-----
> From: Raghu Kota [SMTP:raghukota_at_hotmail.com]
> Sent: Wednesday, July 25, 2001 1:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: effect of Initial and Next exents...
>
>
>
>
> Hi Friends
>
> I obsered in my big database Tables and Indexes set different Initial and
> next extents..So How it will effect on performance?? Suppose one my big
> table has initial 72Mb and next 245Mb..All big indexes(6Gb) was set low
> Initial and next around 245Mb..Any ideas about the performance and
> rectification??...
>
> TIA
> Raghu.
>
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Raghu Kota
> INET: raghukota_at_hotmail.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: Ron Rogers
  INET: RROGERS_at_galottery.org

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 Thu Jul 26 2001 - 11:39:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US