Oracle performance tips

dangerstat · 17 Mar 2010 at 17:52

Hi,

Could anyone point me to some top draw articles / papers / resources on how to optimize large Oracle databases. Even better if there is a give for people transitioning from MySQL to Oracle what would be great.

Thanks

D a n g e r s t a t

bigredshark · 17 Mar 2010 at 19:23

I know there's a decent o'reilly book (but what isn't that true for) and wiki.oracle.com likely has some decent advice

http://wiki.oracle.com/page/Performance+Tuning

http://oreilly.com/catalog/9780596005276/

Manlove is my forte · 18 Mar 2010 at 12:21

Oracle 10g + does its own diagnostics

There are two packages that come in the default enterprise installation
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/addmrpt.sql (this is part of the Tunning pack and "Requires" an additional license"

Oracle 10g (and 11G) runs a diagnostics snapshot every hour by default. You run the database as per usual for a while, then run the addmrpt.sql follow the instructions and it lobs out a nice HTML webpage that details all the improvements you can make. (if you don't pay for the Tuning license then awrrpt will produce a text file with the raw data, which you can either analyze yourself or plug into one of many free Oracle Tuning websites)

Rwac · 18 Mar 2010 at 13:00

Which platform?

dangerstat · 25 Mar 2010 at 22:48

Platform: Linux x64

thanks for the tips guys

adsyf · 26 Mar 2010 at 23:41

Make sure indexes are correct, you should only see hash joins if queries use > 5% of tables.

Check pl/sql. Most big problems come from there.

Redo, redo ,redo. Your database will only be fast if you can write/archive redo very quick.

Rwac · 28 Mar 2010 at 08:55

dangerstat said:
Platform: Linux x64

thanks for the tips guys

In terms of optimising the platform, it will do it's own check of kernel parameters and offer to change them.

If you're specifically on Red Hat/CentOS there is a good document on redhat.com about tuning for Oracle.