Re: LMT advice

Brian Peasland wrote:

> Never seen an "odd" sized LMT with the tablespace in AUTOALLOCATE? Check
> out the following from one of my production instances:
>
> GASP SQL> select owner,segment_name,tablespace_name
> 2 from dba_segments where extents=2806;
>
> OWNER SEGMENT_NAME TABLESPACE_NAME
> ---------- -----------------------------------
> ------------------------------
> WEBMAP SDE_BLK_1071 ORTHO_STLOUIS_DATA
>
>
> A table that has 2,806 extents. Just to verify that this tablespace is
> LMT with AUTOALLOCATE:
>
> GASP SQL> select tablespace_name,extent_management,allocation_type
> 2 from dba_tablespaces
> 3 where tablespace_name='ORTHO_STLOUIS_DATA';
>
> TABLESPACE_NAME EXTENT_MAN ALLOCATIO
> ------------------------------ ---------- ---------
> ORTHO_STLOUIS_DATA LOCAL SYSTEM
>
> Ok...now let's look at the extents that this table is comprised of:
>
>
> GASP SQL> select blocks,bytes,count(*) as num
> 2 from dba_extents
> 3 where owner='WEBMAP' and segment_name='SDE_BLK_1071'
> 4 group by blocks,bytes order by blocks;
>
> BLOCKS BYTES NUM
> ---------- ---------------- ----------
> 8 65,536 16
> 128 1,048,576 65
> 1024 8,388,608 120
> 4864 39,845,888 1
> 5888 48,234,496 3
> 6016 49,283,072 3
> 8064 66,060,288 2
> 8192 67,108,864 2596
>
> 8 rows selected.
>
>
> Now how did my 1 segment of 39,845,888 bytes get there? This is a 38MB
> extent. It's always been my suspicion, although never confirmed, that
> these "subextents" get broken up, and fragmented but are involved in the
> autoallocation of extents. By the way, I can come up with more examples
> than this 175GB table. I've got plenty of other large tables of this
> magnitude which exhibit similar behaviour.
>
> HTH,
> Brian

How it got there? As I mentioned, even though 1MB or 64MB extents are allocated, under the hood, everything in autoallocated tablespace is actually allocated as 64K subextents. Your odd-sized extents are whole multiples of 64K, so I'm not surprised to see them there.

(The comment about odd-sized extents I made was in reference to the original poster's claim of 100K extents... which aren't multiples of 64K and which I am reasonably confident can't get into an autoallocated tablespace in the first place... but I wait for the inevitable correction!!)

Two things could give rise to that behaviour. Either you bump into the physical disk boundary, so Oracle breaks the request for a 64M extent into pieces that fit the physical disk structure. Or a previous segment was dropped, and Oracle is best making use of the 'holes' left behind. Just goes to showow smart the autoallocate algorithm is.

On the other hand, your figures do show that it does indeed attempt to allocate 64K, 1M, 8M and 64M extents, and only breaks that pattern when required to.

It also goes to demonstrate that the 64K sub-extents model does a perfectly fine job of avoiding fragmentation (ie, pockets of free space that nothing can make use of. Drop an 8M extent, and you have actually created 128 pieces of free space, which a future 1M extent will slot into easily, and which a future 8M extent will then be able to take 7M from, and find 1M elsewhere.

Regards
HJR Received on Fri Sep 26 2003 - 07:41:25 CDT