May 5, 2016

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;

No comments: