Re: average nr of tablespaces ?

"robert" <newsreply@#removethis#cookiesoftware.nl> wrote in message news:aov0i8$ahb$1_at_reader12.wxs.nl...
> Hello,
>
> I'm writing a setup for a new database server and it's my first ORA DB.
> Is there a maximum of tablespace for a server ?

Yes. Assuming you are running a version 8 or later database, it's about 65,385. For version 7, it was 1022.

>Or can i open as much as
> wanted ?

64K is a lot of tablespaces! It should keep you happy!!

> What is the average nr of tablespaces ? I can imagine that many tablespace
> affects performance ?

Correct, they do. The more tablespaces, the more datafiles. The more datafiles, the longer it takes for a checkpoint to complete, because CKPT has to update the headers of all of them.

>
> What is advisable ? a few tablespaces with more schema's ?
>

Schema really doesn't (or should I say "shouldn't"?) have anything to do with it. It's the extent sizes that should determine which tablespaces to create first and foremost. You want tablespaces with uniform allocations of, say, 64K, 512K, 1M, 8M and 64M. That's 5. You want one for SYSTEM. That's 6. You want a separate UNDO tablespace (or one or two rollback tablespaces if you're not using version 9i). And a separate TEMP tablespace. That's 8. You want one for miscellaneous. That's 9 (or maybe 10 if you're not using version 9i -every time you post, ALWAYS include a version).

And then you start to get clever: just because two objects share the same extent size of (say) 1M, doesn't mean they should be housed in the one tablespace. If their I/O patterns conflict, they deserve to be separated to avoid a point of performance-compromising contention. So you'll need multiple 64K, 512K, 1M, 8M and 64M tablespaces, but how many of each you'll need depends entirely on the particular application you have in mind.

Make sure you use locally managed tablespace, not dictionary managed. And make sure the right segment goes in the right extent-sized tablespace (you'll waste huge acreages of space if you put the States of Australia lookup table in the 64M-extent tablespace, for example).

The only time you start worrying about schemas in this discussion is when you are asked to house two (or more) applications within the one database (which is not particularly a good idea, but sometimes you have no choice in the matter). Then each application gets its own schema (=user). And for ease of management, you may well end up repeating all of the above for the new application. So you end up with an APP1 1M tablespace and an APP2 1M tablespace; an APP1 TEMP tablespace and an APP2 TEMP tablespace; and so on.

You'll probably get debate here on the right subset of extent sizes to pick: I tend to stick in a 512K one, where a lot of people would go straight from 64K to 1M. That's fine too.

Hope that helps.
Regards
HJR Hope that helps.

> Thanks for all suggestions
>
> Robert
>
>
>
>
>
Received on Sun Oct 20 2002 - 14:55:11 CDT