Below is a list of possibilities as to why Oracle might pick a subpar execution plan:

  • Your tables/indexes have missing or stale statistics (or stats are missing in table partitions/subpartitions or index partitions/subpartitions)
  • Your data violates “uniformity” assumptions that Oracle makes about your data (e.g. missing histograms, missing extended statistics, correlated or anti-correlated joins) .
  • Your query is written in such a way that it becomes very difficult for Oracle to figure out how much data to expect (e.g. complex filter predicates that Oracle has to “guess” at)
  • Oracle doesn’t having an accurate representation of how long it takes to complete one or more operations (e.g. if the system statistics are off)
  • The phrasing of your query might prevent Oracle from selecting a good plan (e.g. subqueries won’t unnest when paired with disjunction, star transformation won’t work with bind variables, “with” clauses won’t allow order-by elimination, etc.)
  • Your data set isn’t represented in the statistics that Oracle has (e.g. unlucky “dynamic sampling” or a HUGE widely-varied table where the data skew cannot be accurately represented by a histogram with a limited number of buckets)

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here:

7 SQL Tuning Secrets You can Use Immediately, Even If You've Never Tuned A Query In Your Life

Leave a Reply

Your email address will not be published. Required fields are marked *