tablespace lmt oracle

8/14/2019 tablespace lmt Oracle

1/3

Locally Managed Tablespace Administration Tips

Copyright Howard Rogers 2001 10/17/2001 Page 1 of 3

Locally Managed versus Dictionary Managed Tablespaces

There's no real issue here at all. Locally Managed tablespaces knock the spots of theirDictionary Managed cousins, and I'd go so far as to suggest that every single tablespace you

create should be locally managed.

That's for two principle reasons, one a matter of efficient resources, the other forperformance.

Dictionary Managed tablespace can simply NOT be made to guarantee consistent extentsizes. It doesn't help that their default PCTINCREASE is 50%, thus absolutely guaranteeingodd-sized extents, and hence eventual fragmentation. But even when you remember whento set that to 0%, you are still going to get odd-sized extents. The tablespace DEFAULT

STORAGE clause is just that -a default. Anyone who sticks in a real STORAGE clause whencreating a table is going to over-ride whatever the default storage clause happens to allowfor. And yes, there's a MINIMUM EXTENT clause in Oracle 8.0 and above which can try andalleviate that problem by guaranteeing that all extents must be a minimum size, ormultiples thereof. But if I have a MINIMUM EXTENT of 500K, and request a new extent of1232K, I'm going to get a 1500K extent -which is not consistent with all the other 500Kextents I've already acquired. So the problem is still there.

Locally Managed tablespace, created with the 'UNIFORM SIZE x' clause simply can NOT haveodd-sized extents, ever. Ask for 1232K on a 500K locally managed uniform size tablespace,and you'll get 3 500K extents. You still get all the space you wanted, but always inconsistent allocations. It is therefore absolutely impossible for a uniform size locallymanaged tablespace ever to experience fragmentation.

So which is better for efficient use of scarce disk resources? It has to be locally managedtablespace.... with one critical proviso. You have to make intelligent use of them,otherwise you end up chewing your way through far more disk space than you ever thoughtpossible! Consider a true case: application installs itself by creating something like 300tables, each of a single extent, each sized to be around 48K big. Installed into dictionarymanaged tablespace, that applicaiton uses around 14.5Mb of disk space. Installed by aneager but inexperienced Junior DBA into locally managed tablespace, with a uniform sizeof 1M, the install now takes 300Mb of disk space. That's because there are now 300 1Mbextents all sitting their practically empty. Now when senior DBA saw that (no-one youknow!) and re-installed having created 16K, 64K and 128K tablespaces, the install cameback down to a far more reasonable 15.2Mb. Used carefully, they can be good for diskspace. Used unwisely, they'll be an utter disaster.


2/3



As for performance: there's no question: Locally Managed tablespaces every time.

The issue is one of dynamic extent allocation and de-allocation. When a table runs out ofspace in Dictionary Managed tablespace, it has to consult and update the Data Dictionaryto record the acquisition of a new extent. That involves doing inserts on the UET$ table

(which records used extents) and deletes from the FET$ table (which records free extents)-because an allocation of a new extent means you've gained some more used space, butlost some old free space. Inserts and deletes are both expensive in the amount of redothey generate -one half of the redo is always the entire row being inserted or deleted.That's multi-byte-sized redo. The rollback for a delete is also the entire row. So theupdates to FET$ suffer the double-whammy of expensive redo AND rollback. Thattranslates into relatively slow extent allocation... and, of course, the same happenswhenever extents are de-allocated (by means of a truncate or drop command).

In Locally Managed tablespace, however, extent allocations are controlled by setting a bitin a bitmap at the head of the tablespace. A bit of "1" indicates an extent is allocated,and a bit of "0" means it's free. An extent allocation by a table now therefore consists ofupdating a "0" to a "1".

That's a trivially small operation, the redo and rollback for which would be correspondinglytiny (and hence quick to perform). Dynamic extent allocations and de-allocations aretherefore easy meat for Locally Managed tablespaces -and that translates into betterperformance during normal working hours. (It's true that there are still some updates inthe data dictionary to record which table has acquired the extent, but you'd be doingthose anyway, whatever type of tablespace you have -they are therefore not a factor thatneeds to be taken into account when deciding on the one type or the other).

In particular, where do dynamic extent allocations and de-allocations happen all the time?In TEMP and ROLLBACk tablespace, that's where. So, even if you don't want to implementLocally Managed tablespaces throughout your entire database, you really ought to makethose two specific tablespace types locally managed.

Which brings me to the one nasty with Locally Managed tablespace for rollback segments:it

is impossible to create your first rollback segment in a Locally Managed tablespace unlessthere is already in existence a non-system rollback segment in Dictionary Managed tablespace.

That's because creating the first segment requires updating the bitmap of the tablespaceto record the extent allocations. -and that's an update to a non-SYSTEM tablespace, whichare not permitted if the system rollback segment is the only one you've got. You thereforehave to create a rollback segment in some Dictionary Managed tablespace first -and thattablespace can be the SYSTEM tablespace if you like (being created in the SYSTEM

tablespace doesn't make it a system rollback segment). Once that's been created andbrought online, you can create the real segment in Locally Managed tablespace without aproblem. And once that's been brought online, you don't need the Dictionary Managed one


3/3



at all any more, so you can simply offline it and then drop it. The creation of all otherrollback segments in Locally Managed tablespace is then handled perfectly well by the firstone you managed to create.

One final word: it is 100% utterly impossible to create the SYSTEM tablespace itself as

Locally Managed, despite the Oracle documentation itself telling you that it is possible.The documentation is wrong. SYSTEM is Dictionary Managed, even in Oracle 9i, and there'snothing you can do about it. Neither is there any need to want to do anything about it.SYSTEM breaks most of the "good DBA" rules, including this one, and it is fine that it doesso. It knows what it is doing, and has been designed and tuned to do it that way. Don't tryand second-guess Oracle on this one!

tablespace lmt oracle

Documents