Basics of SQL tuning

http://www.orafaq.com/forum/mv/msg/170674/505512/136107/#msg_505512

Says Kevin, "An optimizer works only as well as the information you provide it is good. If you give the optimizer the information it needs, it will do a really good job for you. Just taking care of the six items above will ensure that 99 out of 100 plans will be very good plans." 

6 basic things to ensure first. They cure 99% of the times.

These are before even IOT, clustering are considered.

Next is to examine the execution plan:

The author offers some fantastic things to attack first.


New terms today: SKEWONLY, TOAD has a "Optimize Current SQL" (see image below) option basically the Query optimizer which does analysis and provides alternatives... what i saw is that it tries with various hints like 
/*+ NO_CPU_COSTING */  and  /*+ INDEX(DIN DIN_PDA_IDX) */ (forcing index use) etc to come up with alternatives and if one is better than original it says how like on CPU or Elapsed time or such params.

All hints explained here:
http://mohibalvi.wordpress.com/2009/05/19/sql-optimization-using-oracle-hints-2/