Points Explain Plans

• The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute.
• Read Right-Left and Top-Down on the same indentation level.
• Try to reduce the cardinality of the rowset as soon as possible.
• Using an index is not always the most beneficial approach. Where greater than 10% of rows in any table are joined, a HASH JOIN and Full Table Scan may be the best approach.
• Trust the Cost Based Optimiser but experiment by running the query. Cost and duration are not always the same.
• We use a SORT_AREA_SIZE set for Online Transaction Processing. Consider setting SORT_AREA_SIZE larger for long running batch jobs and other larger tables, but do this outside the normal working hours.
• This can be done using ALTER SESSION SET SORT_AREA_SIZE = 2M;