Re: Locally Managed Tablespaces

  • From: "Don Granaman" <granaman@xxxxxxx>
  • To: <rlsmith@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 21 Jan 2005 02:55:21 -0800

Yours must be a 3rd party app.  Multiple application tablespaces for any 
significant database shouldn't be considered a "luxury" any more than the 
fourth wheel on an automobile is a "luxury".  It *might* run (for a while) on 
three, but it won't work very well, won't go very far, and might require some 
abnormal maneuvers.
As others have said, go with LMT - no doubt about it.  If you have widely 
varying sizes of objects and are stuck with a single tablespace, go with 
autoallocate.  You'll still be far better off than with a dictionary managed 
tablespace, UET$, FET$, ST locks, the occasional pctincrease=50, pseudo-random 
extent sizes, ad nauseum.  Actually, you can vaccinate against the last two, 
but if you can actually segregate monster objects and tiny ones (at least) into 
different tablespaces, go with uniform extent sizes.  (If not, my condolences.)

I've been a devout believer in and evangelist for uniform extent sizes in 
almost all tablespaces since long before LMTs - since about 1990 in fact, when 
it was pure heresy.  When we went to 9i RAC over a year ago I conceded to ASSM 
- trading off uniform extents for no more tinkering with freelists, freelist 
groups, etc.  I don't know if the algorithm for extent allocation in ASSM is a 
descendent (or clone or genetic mutation) of the algorithm for LMTs with 
autoallocate or not, but suspect it is.  [I never had to deal with autoallocate 
LMTs (until ASSM?)].  ASSM was a bit aggravating at first (and still is 
compared to uniform extents), but some early experiments with initial extent 
sizes showed some patterns.  Its likely a function of block size, but I've been 
too lazy to research it enough to come up with a formula.  

With a 8k block size though, the thresholds I've found are:
Initial          Smallest extent size    (Typical) Extent size escalation
<     2M        64K                           (16) 64K, then (n?) 1M
       2M        1M                            (64) 1M, then (120) 8M, then ???
>1024M        8M                            Sometimes (113 or 128) 8M, then 
>(n?) 64M... Mostly, unpredictable.
-- I couldn't get the smallest extent size above 8M, but quit the test at 
somewhere in the vicinity of initial<=8G.

I thought maybe I had found a Rosetta stone for ASSM, but it turned out to be 
petrified dung.

Here are some of the results from this query on DBA_EXTENTS - showing a few 
objects in ASSM tablespaces - with a count by extent size ( MB).  I manually 
changed the result order here to make a few things stand out.

SQL> break on SEGMENT_NAME skip 1
SQL> select SEGMENT_NAME, count(*), BLOCKS/128
  2    from DBA_EXTENTS
  3   where OWNER = 'ORASAURUS'
  4   group by SEGMENT_NAME, BLOCKS/128
  5   order by SEGMENT_NAME, BLOCKS/128;

------------------ ---------- ----------
CLASS_TREE                 16      .0625
                            3          1

DOMAIN                     64          1
                            1          8

EVENT                      64          1
                           10          8

EVENT_QUEUE_IDX03          64          1
                          120          8
                            6         64

SUMMARY_RPT                64          1
                          120          8
                           39         64

SCAN_RUN                  113          8
                           27         64

SCAN_OUTPUT               113          8
                           27         64

WEB_ACCT                  113          8
                           37         64

As expected, there is a pattern!  (But who picked 120 or 113 as a magic 
number?)  However, all the above were created in largely empty tablespaces - 
where datafile "head room" has never really been an issue.  Now, it gets 
somewhat strange...

VUL_HISTORY                66          1  -- Why 66 instead of 64?
                            2          3  -- kOoL!
                           45          8

POST_2_ORA                 64          1
                            1          2  -- Eh?
                            1          3  -- Eh?
                           64          8

SYS_LOB0000036839C00003$$ 114          8  -- What happened to 113?
                            1         60  -- Eh?
                          126         64

PAYLOAD_PULL               68          1  -- Why 68 instead of 64?
                            1          2  -- Loose change?
                            2          3  -- Eh?
                          119          8
                            3         64

SUMMARY_IDX01             113          8
                            1         37  -- Nice prime!
                            1         63  -- Dang header
                          269         64

EVENT_QUEUE_LOG_DTL       128          8
                            7         16
                            2         24
                            1         40
                            1         48
                          595         64

EVENT_QUEUE               117          8
                            3         16
                            1         22
                            1         23 -- Entertaining
                            1         32
                          198         64

Extent sizes and the number of extents at a given size are not entirely 
predictable.  A few, but not all, of these odd cases were indexes that had been 
rebuilt or tables that have been moved and it *seems* that odd extent sizes are 
more common afterward (probably from filling in some of the freespace potholes 
that were caused by some previous bout of bipolar extent allocation).  [At the 
end of a datafile I hope - at least that would make some sense.  I have broken 
it down as above by datafile, but haven't really checked where the oddballs are 
yet.]  Still, its a "vicious cycle".  Even with raw devices, I would prefer to 
waste a little space at the end of some datafiles than have stuff like 3M, 23M, 
and 37M extent sizes anywhere in a tablespace dominated by 8M and 64M extents.  
(It just makes my head hurt.)  If there is 23M free at the end of some file and 
the next extent size is *supposed* to be 64M, then go elsewhere - or fail.  
Don't get greedy and lower the standards.  Some newborn object will come in 
some day and grow into it.  Its a cornerstone of the "autoallocate / Stop 
defragmenting and start living / Space, the Final Frontier / 64K-1M-8M-64M-..." 
concept.  What kind of a party were the developers having - or recovering from 
- when they came up with this?

Perhaps Tanel Poder is giving away decoder rings at his "Automatic Segment 
Space Management Internals" session at RMOUG Training Days 2005.

-Don Granaman
OraSaurus (& reluctant OCP)

----- Original Message ----- 
From: "Smith, Ron L." <rlsmith@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, January 20, 2005 11:47 AM
Subject: Locally Managed Tablespaces

> We are upgrading an application that uses an external database to set=20
> initial table create parameters. =20
> I would like to use Locally Manages tablespaces and Uniform extent=20
> sizes, instead of the table create parameters.=20
> My question is, will Locally Manages tablespaces cause Oracle to ignore=20
> the table create parameters? (I hope)=20
> And, if I have some tables that are empty, and some that have a million=20
> rows, what do I use for an initial extent size?=20
> Do I allocate them all small and let the large tables go into extents?=20
> I don't have the luxury of putting large tables in a separate=20
> tablespace.  Everything is in one schema and one tablespace.=20
> Hope this is clear.=20
> Thanks!=20
> Ron=20


Other related posts: