Optimizer can make cardinality misestimates leading to sub-optimal plans.
ADAPTIVE PLANS Adaptive plans are aimed at statements that can benefit from delaying the optimizer‚Äôs decisions on the best final execution plan until the statement is executed. -- Optimzations possible by adaptive plans - changing the join method from NL to HJ or vice versa with same join order - changing distribution method during parallel execution Cardinality feedback was introduced in 11g. Limitation of cardinality feedback: - cardinality feedback was used for identical statements only- Once the cursor aged out, optimizer needed to re learn correct cardinality estimates In 12c above shortcomings have been overcome by use of Adaptive Reoptimization- Cardinality estimates are made for the predicate rather than the statement- The estimates are stored persistently in the form of SQL plan directives and hence are available even when the cursor ages out.--OPtimizations possible using reoptimizaton- dynamic sampling for - skewed data distribution in column - extended column stats - changing join order (with / without change in join method)
Anju Garg has over 12 years of experience in IT Industry in various roles. Her initial tenure was with TCS where she was involved in design, development and testing of various Applications using C , SQL and Oracle .
For the past 3.5 years, she has been a part of Koenig Solutions Private Limited as Senior Corporate Trainer (Oracle DBA). She has trained more than 100 DBA's from across the world in various technologies like RAC, Dataguard, Performance Tuning, SQL statement tuning etc. She is certified for
- Oracle 9i OCP (DBA)
- Oracle 11g OCP (DBA)
- Oracle 11g Performance Tuning OCE
- Oracle 11g R2 RAC OCE
- Oracle 11g SQL statement Tuning OCE
She is passionate about learning and has her technical blog at http://oracleinaction.com/