real world sql tuning tips

1. only CBO. RBO is dead.
2. 90-9-1 stats methodology. Don't touch optimizer parameters, refine your stats!
3. check the cardinality, compare the optimizer estimated and the actual one.

SQL> alter session set statistics_level=all;
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));you can also find actual plan with stats from v$sql_plan_statistics_all

4. notice when stats are not enough and optimizer exceptions