Re: 8.1.7 LMTs Autoallocate vs Uniform Extents

Sundeep,

Major differences between AUTOALLOCATE and UNIFORM is the fact that extent sizes in AUTOALLOCATE tablespace are not uniformly-sized.

I've been working in v9.0.1 (not 8.1.7 -- don't have one of those!) and noticed the following pattern in non-partitioned tables and range-partitioned tables:

    first 16 extents = 64K (8 blocks of 8K apiece)
    next 63 extents = 1M (128 blocks of 8K apiece)
    next ? extents = 8M (1024 blocks of 8K apiece)

In my tablespaces, I haven't seen more than 16 extents of 64K for any segment, and I haven't seen more than 63 extents of 1M for any segment. I don't have any objects big enough (yet) to probe the upper reaches of the 8M extent range...

These tables were loaded with conventional-path SQL*Loader, which appears to be an important factor.

Because, for my composite-partitioned tables, I noticed that the subpartitions do not follow this pattern at all. It could be due to the nature of a HASH-partition or a COMPOSITE-partition, but I suspect that it is due more to the fact that they were loaded with direct, parallel SQL*Loader. The extent sizes follow a pattern that I, as a non-mathematically-inclined person, cannot yet quantify. For those with the same pattern-seeking mania exhibited by the character of John Nash in the movie "A Beautiful Mind", here's a query of a single large subpartition:

 EXTENT_ID BLOCKS
---------- ----------

         0          8
         1          8
         2          8
         3          8
         4          8
         5          8
         6          8
         7          8
         8          8
         9          8
        10          8
        11          8
        12          8
        13          8
        14          8
        15          8
        16        128
        17        128
        18        128
        19         24
        20          8
        21          8
        22          8
        23          8
        24          8
        25          8
        26          8
        27          8
        28          8
        29          8
        30          8
        31          8
        32          8
        33          8
        34          8
        35          8
        36        128
        37        128
        38         96
        39          8
        40          8
        41          8
        42          8
        43          8
        44          8
        45          8
        46          8
        47          8
        48          8
        49          8
        50          8
        51          8
        52          8
        53          8
        54          8
        55        128
        56        128
        57        128
        58        128
        59         48
        60          8
        61          8
        62          8
        63          8
        64          8
        65          8
        66          8
        67          8
        68          8
        69          8
        70          8
        71          8
        72          8
        73          8
        74          8
        75          8
        76        128
        77        128
        78        128
        79         64
        80          8
        81          8
        82          8
        83          8
        84          8
        85          8
        86          8
        87          8
        88          8
        89          8
        90          8
        91          8
        92          8
        93          8
        94          8
        95          8
        96        128
        97        128
        98        128
        99        128
       100         64
       101          8

Quite a few patterns leap to mind from this, but then I started seeing trench-coated CIA operatives so I'm just going to leave it alone (a reference to the above-mentioned movie, in case you're wondering)...

Hope this helps...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, March 26, 2002 11:53 AM

> Can someone point me to good reading material on this
> subject. Is one better than the other for performance
> and manageability?
>
> Syntactically the autoallocate is shorter and seems to
> be more hands off (does that mean worry free also?).
>
>
> TIA
>
>
> =====
>
> Sundeep Maini
> Consultant
> Currently on Assignement at Marshfield Clinic WI
> mainis_at_mfldclin.edu
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Movies - coverage of the 74th Academy Awards®
> http://movies.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: sundeep maini
> INET: sundeep_maini_at_yahoo.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 Gorman
  INET: Tim_at_SageLogix.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).