Re: Locally Managed Tablespaces ... again!!!

"Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message news:217ac5a8.0301100657.43251985_at_posting.google.com...
> Oracle 8.1.7.2
>
> Well, on my last project I finally convinced my DBA to try LMTs. So
> he knows about them.
>
> I get transferred to another project and, lo-and-behold, we're back to
> Dictionary Managed Tablespaces (sigh), with tables and indexes in
> separate tablespaces (double sigh).
>
> He says theres no advantage to LMTs as long as you ensure that your
> PCTINCREASE is 0 and your extents are always <4 blocks * (a power of
> 2)> - i.e. 4, blocks, 8 blocks, 16 blocks, 32 blocks etc. His logic
> is that in this case any space freed up will always be available for
> use and so you won't have any fragmentation.

He's an idiot then.

Drop a table that had acquired an 8 block extent, followed by an adjacent 8 block extent, followed by an 8 block extent.

Now try and allocate a new extent to a table that's using 16 block extents. Reckon it can use the 8+8+8 space previously made available? Sure, there's *room* for the 16-block extent in the sense that 8+8+8=24. But the 16-block table can't use it because it sees it as three 8's, not a 16 or a 24-block space. So it either fails to extend at all, or it acquires its 16 blocks from somewhere else in the tablespace, leaving a 24-block hole behind.

And *that's* fragmentation.

He's also ignoring the fact that in DMT, extent allocations and de-allocations are handled by the data dictionary tables UET$ and FET$. How many UET$ tables do you have? Er, one. So if a dozen tables all decide to extend at the same time, they each have to fight for access to the one data dictionary table. That's a potential source of contention. By contrast, each LMT tablespace handles its own extent allocations and deallocations. So you now have multiple 'things' processing the space requests, not just one data dictionary table. Provided your dozen tables are in different tablespace, there won't be any contention at all when they all simultaneously decide to acquire another extent.

What's more, UET$ and FET$ are tables (albeit stored in a cluster) so what's needed to record an extent allocation is an insert or a delete. That's DML, which causes redo and undo to be generated. And lots of it. Plus it's not just a simple insert or delete: I believe the source-code to handle the acquisition of a single extent is about 6 pages long: we've got to check what the table wants to extend by, search to see if an appropriate space is available, carve it out of free space if it's not, and so on. It's a complex operation, and it's a big one, and it's relatively slow as a result (meanwhile your user doing the insert that has provoked the extent acquisition is just stuck there, waiting for all this magic to happen).

In LMT, extent acquisition is handled by flipping a bit from 0 to 1. You can't get a much smaller transaction than that. So it's quick. We stll have to consult the data dictionary to see what the table wants to extend by, so there is still some data dictionary activity. But it's relatively minimal. This is what your other respondents meant by saying that LMTs generate far less recursive SQL (ie, SQL on the data dictionary) than DMTs. FAR less.

Slap your DBA around the head and tell him to get some updated Oracle education. He doesn't sound like he knows what he's talking about, frankly.

Oh, and then point out to him that DMTs will be disappearing in a future release of Oracle (maybe 10i, maybe not). Indeed, in 9iR2 they've already been disallowed under certain circumstances. Oracle's not just pushing LMTs, they're gradually making them compulsory. And they're not doing that simply because they feel LMTs are somehow more fashionable or modern, but because they work better than DMTs.

Regards
HJR
>
> I'd be grateful for any comments as I'm starting to get more than a
> bit frustrated.
>
> TIA,
>
>
> CE
Received on Fri Jan 10 2003 - 16:19:02 CST