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: tuningsql.com/secrets
