Re: ASM and SAN Layout

comments in-line

On 9/25/07, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
> 1. One bigfile tablespace for my data and one for my indexes. Is this
> appropriate?

One disadvantage to bigfile tablespaces is the creation is done by a single process. For example, if you needed 1TB, one process would make this datafile. If you were to use smallfile tablespaces (assuming 8k db_block) you could create a tablespace with a single 32GB file and then kick off 31 sqlplus sessions all adding a 32GB datafile, thus doing the operation in parallel.

Separating index/data is an organization thing, probably not a performance thing.

> 2. Is it preferable to create one diskgroup for each LUN? Or is one
> diskgroup per the following more appropriate

I would recommend 1 diskgroup composed of several luns. There is no reason for a 1:1 relationship for tablespace to diskgroup. In fact, it would probably perform worse.

> 3. Should I keep all of the above on dedicated spindles? I was told that
> since redo and archive logs do serial writes and data files do random reads
> and writes, performance is better if these are on seperate dedicated
> spindles? What about seperating out my indexes from data on different
> spindles? Along with the rest of them?

There is probably very little to gain by separating out data/index onto dedicated spindles, especially if using ASM (which I would strongly recommend.) Same goes for redo.

> It appears that the major advantage with ASM for me would be the reduction
> in datafiles to manage.

IMHO, the biggest advantage of ASM is the striping of data evenly over all the ASM disks in the ASM diskgroup. It is very difficult to do this by hand, as well as time consuming. And not to mention the very nice feature of rebalancing when new ASM disks are added to the diskgroup. This generally done on non-ASM filesystems by adding new mount points and then moving datafiles by hand.

If you haven't, go to
http://www.oracle.com/technology/products/database/asm and check out some of the whitepapers.

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l