Re: LMT advice

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

"Howard J. Rogers" wrote:
>
> Daniel Morgan wrote:
>
> > foolishHurts wrote:
> >
> >>We are looking at LMT (finally) and I find two options: Autoallocate
> >>and uniform.
> >>
> >>I find with autoallocate, we are seeing truely huge amounts of extents
> >>(2000) of very small size (100 KB). While 100 KB seems reasonable for
> >>small tables, for large tables I would expect extent sizes in the MB
> >>range.
> >>
> >>Question:
> >>Which is better and why: Uniform (one large, one small) or
> >>Autoallocate (and ignore extents)?
> >>
> >>Also, I can not find the old note where I stored the archive location
> >>for this newsgroup, so please post that address as well.
> >>
> >>Thanks!
> >>
> >>Evan
> >>
> >>Currently on 8.1.7.4, heading for 9.x as fast as we can drag our
> >>vendors and customers. <GRIN>
> >>
> >>
> > Use UNIFORM ... it eliminates tablespace fragmentation.
> >
> > Archive can be viewed through google.com.
> >
>
> So does using autoallocate, since even if it gives you a 1 MB extent, it's
> handled by actually allocating you multiple 64K 'subextents'.
>
> There is no reason I can think of not to use autoallocate.
>
> Note to the original poster: since autoallocate only ever allocateds 64K,
> 1M, 8M and 64M (and 256M) extents, I'm not entirely sure how you managed to
> get 100K extents. Also, the algorithm really will start scaling up the
> extents' size to prevent the allocation of thousands of small extents. So
> whatever it is you've done, it isn't autoallocate!
>
> Regards
> HJR
 

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"