Locally Managed Tablespaces 2005-01-21 - By Don Granaman
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;
SEGMENT_NAME COUNT(*) 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. Som
e 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@(protected)>
To: <oracle-l@(protected)>
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
--
http://www.freelists.org/webpage/oracle-l
|
|
