Create index only where needed, drop unneeded indexes.
Use outlines if code is not modifiable.
Use RAID-5 for OLTP systems, 85-95% of io’s are reads anyway.
Create tablespace with extent management local and segment space management auto
If you cannot use the datapump:
TIP #1: Export with buffer=several hundred MB, recordlength=65535, direct=y
TIP #2: Import with commit=n, buffer=several hundred MB, recordlength=65535; and a large UNDO.
Use SGA_TARGET, don’t bother setting up the sga yourself.
In the Grid Control, set tablespace alert threshold to 92%, the default at 97% is too risky. Better, create a monitoring template.
Rebuild indexes online regularly; forget about validating the need to since some application indexes might be locked all the time. And as it is time consuming to find which indexes really need a rebuild.
TIP: If you can afford, recreate the index instead, it’s better than a rebuild
On a normal OLTP database, let Oracle gather the stats. Don’t bother.
Reorganize the tables by using alter table move tablespace ….
Cache all small static table into a buffer keep pool.
Scan the db regularly for costly full table scans.
Forget about the myth of restarting the database weekly to supposedly clean the instance, it flushes your cache and some views might suffer from this. In one instance, the view took 1h30 to re-cache itself.
If you see queries with lots of outer joins, validate each and every outer joins, some developers don’t take any chances!!! At one client, I saved them 2 cpu by removing 7 outer joins in a table trigger.