Re: Locally managed tablespaces and migrated rows

On Tue, 8 May 2001 11:03:45 +1000, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>
>
>I trust that's not a dig at certain, er, known individuals, Nuno!! LMTS
>*is* nearly as good as sliced bread (with Vegemite, natch), and I'd
>recommend them heavily -but not stupidly. If you've different extent needs,
>use different tablespaces.

No way Howard! I don't (usually) "dig". :-)

No, that's not the point. I've done a bit more (true) digging and found out the X$KTBF* tables that mirror the UET$ behaviour. No major difference, apparently. In fact, I was hoping the thing would be more "clear" in its content for uniform tablespaces. What's the point of keeping pairs of start-block/length when length is always the same? Why not just calculate the length as a constant and do without it as a column in the BUE table? The uniform length for each tablespace is kept in another X$ anyway. Given the whole thing is kept in a metra-layer of SYS views, it would be easy to make that work. It may have to do with AUTO LMTS and hey!: that's another can of worms. But I digress.

Let me reply not only to you here but also to the others that kindly participated.

My problem is not with LMTS per se. My problem is that ORACLE education (at least here in Oz) is passing on the message (intentional or accidentally) that DBAs should use UNIFORM and LMTS for ALL tablespaces other than SYSTEM. Across the board. No qualification. As I said, may not be intentional but it's coming through that way loud and clear on the newbies I've been dealing with in the last year or so.

I don't have a problem with this in normal systems. But when I'm faced with a third party package such as Peoplesoft where everything is "managed" in their own "dictionary" (and I could add SAP here, but I won't), things are not so linear. That's the problem I have. It's not a universal panacea. It can create problems.

Case in point: with dictionary managed if I decide to have say, 6000 tables with initial 16K and next 16K, I can. They're mostly empty, why make them larger? And the remaining 1000 tables that have any real data with corresponding sensible allocations (say 100K/100K, 1M/1M, 10M/10M). And I can move these around between the fixed tablespaces given to me by the PS install without any problem.

I know that my 16K tables will be created upfront (I do that on purpose) and they'll never affect the rest of the tablespace because they're never used. Then I can mix and match the 500K, 1M and 10M fixed sizes as much as the available tablespaces let me.

It doesn't always work and I end up putting some of the "problem" ones in dedicated tablespaces and bugger PS support(!). No other way of making the darn thing perform. I know I'll cop flak from the PS "support expert" when he comes around on the next upgrade and finds stuff different from the DMO "core" database, but I usually can get away with it.

Now, I rarely get fragmentation with this setup. But if I had to use LMTS for the standard PS-default tablespaces, I'd be in deep poodoo: my 6000 16K tables would suddenly (assuming 64K UNIFORM) take up 4 times more disk than now, all of it preciously empty and never to be used. I dunno about others here, but I have a finite budget for those round stacked coasters. Particularly when they're bunched up 4 at a time in an EMC mirror set. Expen$ive!

I get upset when I have to explain this to the "hotheads" with a freshly taken "course" from ORACLE, who come around and point-blank whine: "why aren't you using LMTS? It's what ORACLE recommends everybody use everywhere!"

*THAT's* when I start "digging"! ;-)

>up all his tablespaces with 1m extents... net result: one app that used to
>take 55Mb or so suddenyl decided to chew up 650Mb. And 90% of those 1Mb
>extents were fresh air! I'd prefer fragmentation myself!! (Those Apps boys
>wouldn't know one end of a data dictionary from the other, bless them!)

Bingo! Exactly the case here. Now try to convince one of these inexperienced DBAs that what they just heard (or think they heard) from ORACLE education isn't exactly what reality is all about?

>But that's just daft *implementation* of LMTS, not an indictment of LMTS
>themselves.
>

Sure. My problem is not the LMTS itself, it's the "blanket approach" to their use.

Lesse: what are the advantages of LMTS versus DMTS?

1- Can use them in standby database for read-only access. This one is a winner! I *LOVE* it! Solves so many problems it almost is worth the whole thing. Almost. Again, mostly to do with TEMP and RLB. Create an object in a standby database and there it goes down the drain as standby, LMTS or not.

2- Faster allocation/deallocation of space. How many times do we have to do that in a normal day? Bugger all, other than TEMP or RLB. So let's *definitely* use them there, but not across the board just because of this.

3- Less free space fragmentation. Well, duh? Is there anything preventing me from doing exactly the same in DMTS other than lazyness? Or even like I do, multiple allocation schemes stacked inside the same TS, zero growth tables first? Try that with uniform LMTS and you'll quadruple your disk space.

4- Ooops, there's no 4. Unless you wanna go through the yadda yadda about recursive SQL, etc. Hey, that happens with space management. You got heavy recursive SQL due to space management in a production system ALL the time, you got a BIG problem in your hands. LMTS or not!

Anyways, that's the core of the problem I had. Education being perceived as promoting the thing as a "blanket". It's not always that black and white. Most of the time it's bloody dark grey...

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue May 08 2001 - 05:16:43 CDT