Some notes about tuning a sql

From my limited experience, the following methods are used or can be used to tune a sql

1. Obtain optimizer execution plan by autotrace and check consistent gets from the autotrace.

2. Obtain real run-time execution plan:
- Fire the sql
- Obtain the hash value, address or sql_id (in 10g) of the sql from v$sql
- Generate the execution plan from v$sql_plan
- Kill the session

3. Judge if the exection plan is reasonable,
- check all the indexes of tables involved
- check table column stats if needed
- check table stats if needed
- check v$session_longops to see the time estimation for available operations.
- join order, join type

4. 10046 event trace/TKPROF

5. 10053 event trace

6. Oracle SQLT tool

7. OEM SQL tunning advisor

8. SQL Profile

Burleson has a page that provides overview of SQL tuning: Oracle tuning - Tune individual SQL statements