Strategy for Oracle Unix Installation _ I need help

[raygg]

I am new to installing oracle on unix - but lots of NT DBA experience. I discovered in talking with our UNIX guy that if I follow the OFA, then I am locked into the allocated limit for each mount point. It is my understanding that one of the major benfits, in addition to consistency, for following OFA, is to improve performance by reducing or eliminating I/O contention.

We have 20GB allocated for a new 817 db on an AIX box. After using 2gb for u01 for the oracle EE software, I put the system, rbs, temp, redo and usr on u02. This will be a development and test db only, so I suggested to our unix guy to allocate 5gb for u02, and 3gb each for u03 and u04. This leaves 7gb to grow and allocate in some method to be determined based on our needs as we move forward Our app uses a lot of temp space, so I am thinking just put the data datafiles on u03, the index datafiles on u04, and wait and see how much temp space we are using in u02 in our stress testing before deciding how much we should grow u02, u03, or u04 and by how much. But we are on a RAID setup. Having said all this, would it be simpler and more manageable and just as effective for throughput and I/O contention to have set up just u01 and u02 and let the RAID manage the I/O issues? With such a configuration, I would set u01 to 2gb and u02 to 18gb. Our ultimate goal is to stress test an OLTP app at several hundred thousand transactions / day. But we have a batch overnight side to it that is the part that chews up the temp space with grow by statements in the sql.

 

[raygg]

Typo in the last line - it should read "group by statements in the sql."

 

[lfoata]

If you let the RAID take care of where it puts what, you may run into IO contention.
I would have at least two RAID sets, so that you can split indexes and data.
You may also want to split RBS and TEMP on different disks.

 

[raygg]

Do you think RBS belong on the same drive with data or indexes, if not on the drive with temp?

Further talk with my aix guy finds that I misspoke - that box does not have RAID - just multiple drives handled by the aix 4.3.3 volume management. He says to populate the drives, - then he can move mount points to make sure they are on different drives. Is this a comon practise?

 

[lfoata]

If you look at OFA, you will see that the best architecture for a DB is on 7 disks.
In reality, if you spread your data, indexes, rollbak and software on 4 drives, it is efficient.
You can put rollback with temp, since they serve completely different purposes. Every time you update something, the changes will be written to data and RBS, so there could be a problem if you have RBS with data or indexes. You couls also put your RBS with the system,user,tools tablespaces since they are almost static.
What your sysadmin says is correct, just pay attention that the logical volumes are created with the physical disk layout in mind.

If your database is OLTP, make sure you optimize the parameters in the init.ora for an OLTP database.
Also make your rollback segments big enough so that the database does not spend its time on wait for rollbacks segment to switch. (major bottleneck on OLTP).

 

[raygg]

I set the rollbacks at storage (initial 512k next 512k minextents 8 max 4096). What are good settings for oltp in init.ora? There are no triggers in use, and the OLTP is new adds only, no updating.

 

[lfoata]

If your database is going to be as you said, I would make the rollback segs bigger (10M ...). There are no secret recipe, you will have to monitor the use.

I made a small error when I said rollback segments in the prev post, I meant redologs that are switched. Make them something like 20M.