All SQL tuning experts must be
- proficient in reading Oracle execution plans and
- understand the steps within explain plans and
- the sequence in which the steps are executed.
To successfully understand an explain plan you must know the order that the
plan steps are executed.
Reading an explain plan is important Oracle SQL tuning DBAs reveal the
explain plans to check many things:
- Ensure that the tables will be joined in optimal order.
- Determine the most restrictive indexes to fetch the rows.
- Determine the best internal join method to use (e.g. nested loops, hash join).
- Determine that the SQL is executing the steps in the optimal order.
Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.