Download - Oracle optimizer in 12c
![Page 1: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/1.jpg)
Oracle Optimizer in 12cSession 1 - Adaptive part
![Page 2: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/2.jpg)
Statement processing
1. Parser
Soft
Hard
![Page 3: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/3.jpg)
Oracle Optimizer
Optimizer
DictionaryStatistics
Adaptive
![Page 4: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/4.jpg)
Adaptive Query
OPTIMIZER_ADAPTIVE_FEATURESDefault=TRUE
![Page 5: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/5.jpg)
Adaptive Query- definitions• “In Oracle Database, adaptive query optimization is a
set of capabilities that enables the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. Adaptive optimization is helpful when existing statistics are not sufficient to generate an optimal plan.”
• “An adaptive plan enables the optimizer to defer the final plan decision for a statement until execution time. The ability of the optimizer to adapt a plan, based on information learned during execution, can greatly improve query performance“
• What is not the new optimizer:
![Page 6: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/6.jpg)
Adaptive Query - parametersParameters that can disable this optimization:Disable all:• optimizer_adaptive_features = false• optimizer_features_enable <= 12.1.0.1 Disable adaptive plans: • optimizer_adaptive_reporting_only = true • _optimizer_adaptive_plans=false • _optimizer_use_feedback=falseDisable adaptive statistcs:• optimizer_dynamic_sampling=0
![Page 7: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/7.jpg)
New kernel component12.1.0.1 11.2.0.4
![Page 8: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/8.jpg)
Adaptive joins
The optimizer is changing the plans on the fly based on the analysis of the rows buffered at each step.
“During statement execution, the statistics collector gathers information about the execution, and buffers some rows received by the subplan. Based on the information observed by the collector, the optimizer chooses a subplan. At this point, the collector stops collecting statistics and buffering rows, and permits rows to pass through instead. On subsequent executions of the child cursor, the optimizer continues to use the same plan unless the plan ages out of the cache, or a different optimizer feature (for example, adaptive cursor sharing or statistics feedback) invalidates the plan.”
![Page 9: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/9.jpg)
Example
![Page 10: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/10.jpg)
Optimal plan
![Page 11: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/11.jpg)
Screwed statistics – screwed plan
3rd run
![Page 12: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/12.jpg)
Trace
DP: Found point of inflection for NLJ vs. HJ: card = 6.20
![Page 13: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/13.jpg)
Automatic Reoptimization
Automatic reoptimization (ARO) means that the optimizer will adapt a plan during subsequent executions of the statement when it’s the victim of incorrect optimizer estimates (e.g., a sub-optimal distribution method or incorrect choice for the degree of parallelism). Immediately after the statement’s first execution, the optimizer willthen have a more complete set of statistics, and can accumulate that information for improved future plan selection.
Difference between ARO and AJ is that ARO can change globally the plan for example the join order while AJ can change only at query block level.
![Page 14: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/14.jpg)
SQL Plan Directives• Additional GLOBAL persistent information that helps the optimizer in generating a
more optimal plan. SQL plan directives are automatically created based on information learnt via Automatic Reoptimization.
• There is only one type of SQL directive which instructs the optimizer to use dynamic statistics to obtain a more accurate estimate.
• A new package was introduced to manage them: DBMS_SPD • Flush on disk to SYSAUX every 15 minutes and purged automatically after 53
weeks from the last usage
How they work:“During SQL execution, if a cardinality misestimate occurs, then the database creates SQL plan directives. During SQL compilation, the optimizer examines the query corresponding to the directive to determine whether missing extensions or histograms exist (see "Managing Extended Statistics"). The optimizer records any missing extensions. Subsequent DBMS_STATS calls collect statistics for the extensions.”
![Page 15: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/15.jpg)
SQL Plan Directives
Example of SQL Plan Directives
Type of SQL Plan Directives
Notes column in DBA_SQL_PLAN_DIR_OBJECTS shows the capabilities of the directive
1st pass of a SPD needs extended stats(gathered automatically)
Extended stats gathered(still might need stats)
![Page 16: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/16.jpg)
DEMO
![Page 17: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/17.jpg)
Dynamic statistics• Previously known as Dynamic Sampling• Enhanced to get more accurate cardinality estimates
for not only single table accesses but also joins and group-by predicates (additional features on top of level 4 from 11.2)
• New level 11:” When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.”
• A trade-off was made since the parsing time increased so the dynamic statistics were persisted in the cache for being used by other SQLs
![Page 18: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/18.jpg)
Dynamic statistics
![Page 19: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/19.jpg)
Dynamic statistics
![Page 20: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/20.jpg)
Statistics feedback
• Formerly known as cardinality feedback• Improve the plans for repeated queries which have estimation on
cardinality• The statistics feedback is enabled for:
• Tables with no statistics• Multiple conjunctive or disjunctive filter predicates on a
table• Predicates containing complex operators for which the
optimizer cannot accurately compute selectivity estimates
• The mechanism it is enabled only at the first execution• Only individual table cardinalities and group by estimates examined
– not joins• Information is stored in the cursor only and is lost if cursor ages out
![Page 21: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/21.jpg)
Statistics feedback
The one with wrong estimationThe one with statistics feedback
![Page 22: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/22.jpg)
Auto column stats
• Oracle can automatically detect column group candidates based on an STS or by monitoring a workload
• Extended statistics are created also automatically based on SPD
![Page 23: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/23.jpg)
Auto column stats
• Part of the functionality was added from 11.2.0.2• Oracle can automatically detect column group
candidates based on an STS or by monitoring a workload
• Extended statistics are created also automatically based on SQL Plan directives
• New functionality added in DBMS_STATS:– seed_col_usage– report_col_usage
![Page 24: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/24.jpg)
Auto column stats
SSLIDE 21
![Page 25: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/25.jpg)
Auto column stats
![Page 26: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/26.jpg)
Q & A
![Page 27: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/27.jpg)
Thank you!
![Page 28: Oracle optimizer in 12c](https://reader035.vdocuments.us/reader035/viewer/2022081715/546f9e65b4af9f210b8b45f9/html5/thumbnails/28.jpg)
References• http://www.oracle.com/technetwork/database/bi-
datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
• http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optcncpt.htm#TGSQL221
• http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
• http://kerryosborne.oracle-guy.com/papers/12c_Adaptive_Optimization.pdf
• http://www.voug.org/Kyte12c_optimizer_overview.pdf• http://docs.oracle.com/