How to best optimize an Oracle database for writes?

Solution 1:

A good start is to follow Oracle's SAME methodology - Stripe and Mirror Everything. This gives you a good base from which you can add further more specific improvements.

The SAME methodology is in the following PDF:

http://www.oracle.com/technology/deploy/availability/pdf/oow2000_sane.pdf

There's a good discussion on Ask Tom:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433784236146

One of the main drivers for SAME is the ease of administration. It passes on a lot of the performance considerations to the OS and the underlying storage layer. The idea is that your tablespace files are already spread across a zillion disks in the storage array so any fiddling around you do on top of this doesn't help much. However, as ever, the devil's in the detail. It's tempting to treat the storage layer as a black box but you really do need to understand what's going on and know what sits under each of your tablespace files.

Solution 2:

From a don't-want-to-touch-the-config POV - check your indexes. if you are writing to tables that are heavily indexed, remember that each time you write a row, the indexes are being changed. Fewer indexes, less physical IO.

This will - obviously - have an impact on reads.

If you have the time & capacity, I have found separating commonly written tables into specific tablespaces and making sure that/those tablespaces are stored on a separate RAID channel also helps, but this depends on the hardware you are using and adds a bunch of other considerations.

If you really do have time to ponder this, then buy & read Cary Millsaps 'Optimizing Oracle Performance' - it's dated (all depending on what version of oracle you are using) but a classic.

Solution 3:

Set it up in a cluster and have your applications point to a specific node for writes; then read from the cluster itself.