Re: Locally Managed vs Dictionary managed tablespaces

Leigh-

This is one of those questions that turn into hair-splitting discussions rather than focus on the larger picture. The contention issue with dictionary managed tablespaces is with the ST enqueue, of which there is exactly one for the database. Steve mentions increased IO to access a couple of views, but that IO is read-only and cannot cause ST enqueue contention. A bit of IO to peek at segment and extent status is trivial in any case. I don't think his argument can be generalized intelligently.

I have a hard time understanding the last Q & A. The answer indicates having inherited a database with dictionary managed TS for temp and rollback and LMT for data and index. This would seem to be a nearly worst-case scenario. Temp and undo are most often busy with extent management and are the best candidates for LMT. In any case, it does not answer the question asked. Maybe this is presented out of context or something.

I think the correct answer to the question asked is "you do want to use LMT whenever possible. It presents a nice, generalized solution to extent management and enhances performance in nearly every case. Any case you can find for which LMT is not a good solution is probably fairly exotic and beyond the grasp of most DBAs anyway."

I also noticed a comment suggesting system tablespace should always be dictionary managed. Silly rationale and no logic is offered to support it. LMT makes sense, so use it when you can. Soon enough, it will be the only option anyway, so you might as well get used to it.

just my two pesos...

• ricky

Leigh wrote:
>
> Does anyone agree/disagree with Steve's synopsis of locally managed
> tablespaces particularly that they can cause much more physical IO
> than dict managed for tablespaces with many small segments? Has
> anyone had any significant improvment after using them on moderately
> sized databases - eg. 10 - 20GB or any phobia of using them?
>
Received on Thu Nov 01 2001 - 16:38:48 CST