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
>
>
>
>
>"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
>>
>>

I've seen the same thing which is why I am not ready to purchase what Howard is selling. In fact the entire
concept of "scaling up" I interpret as meaning a new name for pct_increase and it is what I have observed.

If I'm worng I'd be happy to be corrected. But my experience with UNIFORM extents is that they solve
every problem I have had. And I've never experienced what Sybrand describes as being the experience
related by the instructor.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)