| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Locally Managed Tablespaces
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)
> 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-lReceived on Fri Jan 21 2005 - 03:59:16 CST
 |
 |