Oracle Tuning

This topic is staying to long in my drafts, so I decide to publish it, as is.

It has nothing to do with my new application "DG Tournament"... the application is "my free-time" ;-) during the day I'm a consultant doing mainly Oracle database things.

Today I had to do some database tuning... There won't be anything new in this thread, but if you don't know it, you can't use it.

You can tune a lot of different things. The most important thing are:
(0. think before, think "how" the application will work, do a proper design, test, test, test, ...) - but in a lot of cases I'm only involved when this is already finished ;-) - actually the smiley should be :-(
1. the Application (logic, how to do things, select statements, programming code, ...)
2. the Application Server
3. the Database
4. the OS and the Hardware (disks, cpu, network)

A lot of people have the reflection to shout add the dba and tell him "the database is working slow". We can discuss about this ;-)

Following I found useful in my tuning (tuning of application and database):
- SQL> set autotrace on
- Oracle Trace Analyzer, as Eddie describes in this great article: "TKPROF on steroids"
- YAPP, as Anjo describes it here, 10 years later
- Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues: Metalink Note 258167.1 (yeah, some customers still go from 8 to 9!)
- Oracle Database 10g Migration/Upgrade: Known Issues and Best Practices with Self-Managing Database: Metalink Note 332889.1
- If I develop myself I "instrument" my application code and investigate what would be the best way to do things. Asktom and Q&A from Steven Feuerstein are great sources how you should develop PL/SQL code. This thread for ex. can be useful for developers.