Oracle tuning

djberg96 on 2004-05-25T13:26:47

I'm just now getting around to reading Guy Harrison's Oracle SQL High Performance Tuning. It has some minor flaws, such as the schoolbook style "in this chapter we covered...", but otherwise it's been pretty good so far.

This book is aimed more at developers than DBA's, though I think both might benefit. Here's what I've learned in the first 5 chapters:

• Analyze your tables
• Don't use '!=' or functions on an index
• Set a default value for NULL's if that column has an index
• Partition large tables
• Put indexes on FK's
• Avoid overindexing on columns that are frequently updated
• If you've got some old code where you forgot to use placeholders and are too lazy, or don't have time, to go back and do things the right way, you can use "alter session set cursor_sharing=FORCE".

  I need to go back and double check some of these things (I wrote that from some notes I've taken), so correct me if I messed anything up.

  ---

  Optimizing Oracle Performance

  jdavidb on 2004-05-25T14:53:32

  O'Reilly's Optimizing Oracle Performance also looked interesting for this. Seems to be very numbers based. But I've bought enough books for now, so I have to wait awhile. :)

  one point of contention ...

  tinman on 2004-05-30T12:40:36

  I don't know about 10g, but I haven't always had success with partitions. Not that I would hesitate to use it, but there is at least one gotcha that stops the show, so to speak.
  Export and import are horribly, terribly broken with respect to partition tables.
  

  There you go. Sometimes, you can work around this, but it can be irritating (not to mention painful) if you take out a large large database using exp and test it only to have it barf over the partition.

  Other than that, the rest make a lot of sense. I didn't know about the "alter session" trick, I must try it out sometime.