Re: LMT advice

Brian,

I've never seen odd sizes appear - but I have heard comments about parallel execution and 'create table as select' not being aware of LMTs and therefore doing extent trimming.

Your system doesn't look as if this was the case (as I think you would have 'N x 16' of the smallest extents rather than just 16, but it might be a related issue.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
news:3F7365E5.5960973C_at_remove_spam.peasland.com...


> 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

>

>