Tablespace Lmt Oracle

Locally Managed Tablespace

Administration Tips

Locally Managed versus Dictionary Managed Tablespaces There's no real issue here at all. Locally Managed tablespaces knock the spots of their Dictionary 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 for performance. Dictionary Managed tablespace can simply NOT be made to guarantee consistent extent sizes. It doesn't help that their default PCTINCREASE is 50%, thus absolutely guaranteeing odd-sized extents, and hence eventual fragmentation. But even when you remember when to 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 when creating a table is going to over-ride whatever the default storage clause happens to allow for. And yes, there's a MINIMUM EXTENT clause in Oracle 8.0 and above which can try and alleviate that problem by guaranteeing that all extents must be a minimum size, or multiples thereof. But if I have a MINIMUM EXTENT of 500K, and request a new extent of 1232K, I'm going to get a 1500K extent -which is not consistent with all the other 500K extents I've already acquired. So the problem is still there. Locally Managed tablespace, created with the 'UNIFORM SIZE x' clause simply can NOT have odd-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 in consistent allocations. It is therefore absolutely impossible for a uniform size locally managed tablespace ever to experience fragmentation. So which is better for efficient use of scarce disk resources? It has to be locally managed tablespace.... 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 thought possible! Consider a true case: application installs itself by creating something like 300 tables, each of a single extent, each sized to be around 48K big. Installed into dictionary managed tablespace, that applicaiton uses around 14.5Mb of disk space. Installed by an eager but inexperienced Junior DBA into locally managed tablespace, with a uniform size of 1M, the install now takes 300Mb of disk space. That's because there are now 300 1Mb extents all sitting their practically empty. Now when senior DBA saw that (no-one you know!) and re-installed having created 16K, 64K and 128K tablespaces, the install came back down to a far more reasonable 15.2Mb. Used carefully, they can be good for disk space. Used unwisely, they'll be an utter disaster.

Copyright © Howard Rogers 2001

10/17/2001

Page 1 of 3

Locally Managed Tablespace

Administration Tips

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 of space in Dictionary Managed tablespace, it has to consult and update the Data Dictionary to 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, but lost some old free space. Inserts and deletes are both expensive in the amount of redo they 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 the updates to FET$ suffer the double-whammy of expensive redo AND rollback. That translates into relatively slow extent allocation... and, of course, the same happens whenever extents are de-allocated (by means of a truncate or drop command). In Locally Managed tablespace, however, extent allocations are controlled by setting a bit in 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 of updating a "0" to a "1". That's a trivially small operation, the redo and rollback for which would be correspondingly tiny (and hence quick to perform). Dynamic extent allocations and de-allocations are therefore easy meat for Locally Managed tablespaces -and that translates into better performance during normal working hours. (It's true that there are still some updates in the data dictionary to record which table has acquired the extent, but you'd be doing those anyway, whatever type of tablespace you have -they are therefore not a factor that needs 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 implement Locally Managed tablespaces throughout your entire database, you really ought to make those 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 unless there 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 tablespace to record the extent allocations. -and that's an update to a non-SYSTEM tablespace, which are not permitted if the system rollback segment is the only one you've got. You therefore have to create a rollback segment in some Dictionary Managed tablespace first -and that tablespace 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 and brought online, you can create the real segment in Locally Managed tablespace without a problem. And once that's been brought online, you don't need the Dictionary Managed one Copyright © Howard Rogers 2001

10/17/2001

Page 2 of 3

Locally Managed Tablespace

Administration Tips

at all any more, so you can simply offline it and then drop it. The creation of all other rollback segments in Locally Managed tablespace is then handled perfectly well by the first one 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's nothing 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 does so. It knows what it is doing, and has been designed and tuned to do it that way. Don't try and second-guess Oracle on this one!

Copyright © Howard Rogers 2001

10/17/2001

Page 3 of 3