| How to convince my bosses the time invest to move from Dictionary MT to Locally MT? [message #60351] |
Tue, 03 February 2004 01:53  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I would like to convince my bosses (in a meeting where I can present to them the problematic and the benefits of LMT) the time investment (and money..) to move all our schemas from Dictionary Managed Tablespace to Locally Managed Tablespace. To do this I would need maybe one week up to 2-3 weeks (lot of schemas with numerous tables, triggers, indexes, etc...)? As they are very result and efficiency oriented/focused, I have to convince them to use LMT and that it's worth the time and money invested to do this big move! I'm working in a telecom company with OLTP environments.
I've collected these arguments:
------------------------------
When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself.
Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces (DMT), and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces (LMT).
Dictionary Managed Tablespaces (DMT):
Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes against these tables. As only one process can acquire the ST enqueue at a given time, this often lead to contention.
Locally Managed Tablespaces (LMT):
Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a
Advantages of Locally Managed Tablespaces:
• Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
• Reduce contention on data dictionary tables (single ST enqueue)
• Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
• Changes to the extent bitmaps do not generate rollback information
LMT are not only faster at allocating extents but because they make fragmentation a thing of the past -- it just doesn't happen.
------------------------------
BUT my question is:
Do you think it's just enough arguments? Or do you think I should emphisize some aspects?
Can I say that each DML statments/processes on whichever tables in user-tablespaces are queuing to retreive info (ST - space transaction) about free or used allocated space from the system tablespace (SYS.FET$ table, SYS.UET$ table) to be able to execute? And this is quite bad in an OLTP environment! Lot of CPU and memory ressources can be spared with LMT?? Much more locks can be avoided?
How about fragmentation? How LMT can reduce fragmentation? Can someone explain me the concepts about how it can reduce fragmentation so dramatically? The benefit of defragmentation is only at data is more compact and can be then retreived in less I/O?
Thank you very much in advance!
Regards,
Patrick Tahiri.
|
|
|
|
|
|
| Re: How to convince my bosses the time invest to move from Dictionary MT to Locally MT? [message #60371 is a reply to message #60351] |
Tue, 03 February 2004 12:04  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
you have got the facts straight . ST enqueue is serialised and causes heavy contention when there is a heavy dml and multiple users such as in OLTP environemnt.The effects are very much pronounced when dropping or even truncating tables with drop storage.
LMT with UNIFORM extents eliminate tablespace level fragmentation altogether becos all the extents of all the objects in that tablespace are of the same size and any object can use the free space in whole . However LMT with AUTOALLOCATE can still cause tablespace level fragmentation as extents of multiple sizes can proliferate.
Also object level fragmentation can still occur .
Also when using quotas in locally managed tablespaces, the information is still stored in the data dictionary and hence needs to be accessed ,but there is no ST enqueue as is the case with UET$ and FET$ tables.
Also since the used/free information is not stored in the data dictionary anymore, they are not cached in the data dictionary anymore and hence a little more I/O is required to get the information from the tablespace/segment header !
there are few other things ,but I dont want to paint a bad picture about LMT, since the advantages outweigh any disadvantages/issues .
-Thiru
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
