Re: Would like to know more about tablespaces

In article <33282F4B.3839_at_chsra.wisc.edu> dora_at_chsra.wisc.edu writes:
>I have some questions about Oracle tablespaces. We would like to
>separate our tables into tablespaces in some sensible way, but we may be
>limited to a single disk controller.
>
>Can performance be improved at all by spreading tables, indexes, etc.
>across multiple tablespaces on a single drive, or do you have to have
>files spread across multiple drives? We can, of course, plan for any
>possible future hardware upgrades.

Performance can be changed by using multiple tablespaces, whether it is improved or not can be quite complicated. However, it is a good idea because in the future you might get more disks. On a single disk, you want to have the most used files towards the middle, so if you can control the placement you would want the redo logs there, moving outward and inward alternately placing less and less used ones to minimize disk arm movement.

With modern cacheing, I not certain this isn't an exercise in futility, since Oracles dbwriter tends to write the data in bursts, and the data is read from in-memory caches when possible. More performance can be gained from careful application design and SQL programming, followed by SGA tuning and the occasional defragmenting to help those inevitable full-table scans.

If you are running into cpu or I/O bound conditions, you might have a more serious issue of capacity planning.

>
>Are there any downsides or pitfalls in creating too many tablespaces?
>How do you know if you have too many tablespaces or too many datafiles?

There is a maxdatafiles init.ora parameter. Haven't ever tried creating lots of tablespaces because I haven't had a reason to. You increase the size of tablespaces by adding datafiles, so you want to size things appropriately so you don't have to add datafiles very often - in production, the db will run out of room at the worst possible time. If you have some real volatile tables, you may have no choice. Depending on your OS, you may have to watch out for OS dependent maxfiles, too.

>
>Also, we are curious about whether it is possible to use a RAM drive or
>virtual drive for datafiles. I suspect that it is not possible to use
>it for any kind of permanent table or permanent tablespace.

I suspect so too. You might want to read the concepts manual about how oracle caches data with its LRU algorithm. Data that is used often will be kept in memory in the natural course of things, and in more recent versions of Oracle there is a standard package for pinning code in memory (DBMS_SHARED_POOL.KEEP), and you can pin small tables in memory (the CACHE parameter of the ALTER TABLE command reverses the LRU algorithm to put those tables at the head of the list).

>
>I am new to Oracle and these points were not covered to my satisfaction
>in either the Oracle7 Server manual or Oracle Performance Tuning (Mark
>Gurry & Peter Corrigan). I would appreciate any wisdom that you
>experienced and knowledgeable people may have.

Get the basics working before trying anything too hairy. Oracle is fairly labor intensive for the DBA, and you don't want to make too much work for yourself in the future, or for those who come after you. Disk drives are cheaper than people. There are usually pretty good presentations about these things in the International Oracle Users Group meetings, see if you can find someone that has the "Proceedings" on CD. Document everything you do, and be sure and keep .sql scripts of the tablespace creations updated.

>
>Thanks,
>
>Dora
>
>--
>News posting tip of the day: Have your subject line be the actual
>SUBJECT of your message, not "HELP, HELP, HELP ME PLEASE I NEED
>HELP!!!"

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O