Re: Advice Wanted: Optimizing Oracle for a Production Environment

In article <bradley.1199000832A_at_aux>,

        bradley_at_marcvm.marc.usda.gov (Randy Bradley) writes:
>
>
> Now if I may add a question for the real guru's. Is it better to stripe a
>tablespace accross drives and create both index's and data tables there, or
>just put a data tablespace on one drive and an index on the other drive? I
>have two scsi controllers so I can split drives between controllers.
>

Altho it does depend on a lot of factors (i.e. can queries be handled by indices without referncing the data files at all, or whether or not you cache a large share of the database), I use a few guidelines:

• On tables or indices with lots of cache misses, I almost always place indexes and tables on both different spindles and different controllers. This is generally good, with even better gains in parallel queries. Once the actual dml has been altered to take advantage of the multiple threads, performance can be good.
• No tablespace goes on the same spindle as the redo logs, temp segs, or archived redo logs, unless such tablespaces are only referenced rarely. In addition, tables that are only rarely updated (i.e. annually, etc) ... I make their tablespaces read only, and schedule a database bounce at both the start and end of this update to alter the read only to read write, and then back. This definitely reduces redo activity. I particularly like to do this on intensively used tables, in our case map grids and geophysical culture tables, which are quite static, yet are needed for most queries in petrophysical applications.
• Tables that are to be cached can readily all be placed on a very limited set of spindles ... generally, no gains will be realized on striping these areas, except at startup, or during cache reload. The same principle applies to the index tablespace datafiles for cached tables.
• On the Barracuda drives, I try to also stripe so that no datafile for a tablespace exceeds 400M. In terms of physical head movements and I/O waits, this may be too conservative, but I do get measurable performance gains on this during periods of extremely high load ... approaching 400TPS on a Sparc 2000 with 6 cpus.
• Again, on the topic of the redo log striping ... try to get all users doing only 'select' operations to do the commit, set transaction readonly, then the query selects, and a closing commit. The savings are especially noticable in short duration queries. Use of the 'redo' is a very heavy resource use.

Sorry for adding some opinions beyond your base question, but I find that striping is just a piece of the overall tuning activity, and if not implemented with a full understanding of the environment, people resources are simply wasted in over-tuning pieces that give little benefit. I try to follow the 80/20 rule, where the most of my gains come with only a little work.

Personally, I've found that in my environment ... very many complex joins (i.e. 10 - 15 outer joins in queries are extremely common) that caching tables provides benefits many orders of magnitude better than almost anything else I can do. Of course this does have a large dollar cost ... I can't wait till more hardware/OS vendors provide true 64 bit support ... the 2GB ram limit per process on 32-bit systems is a real killer when you're going for good performance.

Bill

-- 
----------------------------------
E-Mail: Beatonb_at_cadvision.com or Bill_Beaton_at_calgary.qc-data.com
----------------------------------