leading hint influences the optimizer when choosing the order in which tables are joined. | Query Transformer | If advantageous, the query transformer rewrites the original statement into a semantically equivalent statement (for example from … where col in (17, 42) to … where col = 17 … union all … where col = 42). |
| Estimator | Uses statistics to estimate plans' cost. |
| Plan Generator | Compares estimated costs and chooses plan with least cost; passes this plan to the row source generator. |
dbms_stats
select name, value, isdefault from v$parameter where name like 'optimizer%' or name like 'parallel%' or name in ( 'cursor_sharing', 'db_file_multiblock_read_count', 'hash_area_size', 'hash_join_enabled', 'query_rewrite_enabled', 'query_rewrite_integrity', 'sort_area_size', 'star_transformation_enabled', 'bitmap_merge_area_size', 'partition_view_enabled' ) order by name;
no_query_transformation hint. _optimizer_cost_based_transformation. leading, ordered or qb_name) loose their meaning. In such a case, it looks as though the hint was ignored. | OR-expansion | where tab.col_1 = 'foo' or tab.col_2 = 'bar' -> where tab.col_1 = 'foo' … UNION ALL … where tab.col_2 = 'bar' |
| Simple view merging | Merging select-project-join views |
| Complex view merging | Merging views that contain group by and distinct clauses |
| Predicate pushing | Push a predicate that is formulated on the outer query into the inner query. |
| Subquery unnesting | Transform a subquery into a join operation. (IN or EXIST to a semi join, NOT IN or NOT EXISTS to an anti join, NULL aware and single null aware to an anti join). See MOS Note 1082123.1 |
| Query rewriting | Use a materialized view to improve performance of a query |
| Star tranformation | Avoid full table scans of fact tables in a star schema. |
| In-memory aggregation (vector group by) | |
| Cursor duration temporary tables | Temporarily store result of a query blocks that are used multiple times. See also the with clause. |
| Table expansion | See also the expand_table hint |
| Join factorization | Factorize common computations from branches of a union all query. |
| Aggregate Subquery Elimination | |
| Common subexpression elimination | |
| Group by placement | |
| Intersect conversion into join | |
| Join Elimination | |
| Join predicate push down (JPPD) | See MOS Note 1082106.1 |
| Materialized view rewrite | |
| Minus conversion into anti-join | |
| Native Full Outer Join | See MOS Note 1082125.1 |
| Order by Elimination | |
| Outer to inner join conversion | |
| Predicate move-around | |
| Transitive Predicates | See MOS Note 1082133.1 |
optimizer_features_enable (which corresponds to a release) determines which features are enabled | Release | ||
| Adaptive cursor sharing | 11.1.0.6 | |
| Join predicate pushdown | 11.1.0.6 | |
| Use extended statistics to estimate selectivity | 11.1.0.6 | |
| Use native implementation for full outer joins | 11.1.0.6 | |
| Partition pruning using join filtering | 11.1.0.6 | |
| Group by placement optimization | 11.1.0.6 | |
| Null aware antijoins | 11.1.0.6 | |
| Join Factorization | 11.2.0.1 | |
| Cardinality Feedback | 11.2.0.1 | |
| Subquery Unnesting | 11.2.0.1 | |
| Subquery Coalescing | 11.2.0.1 | |
| Table Expansion | 11.2.0.1 | |
| Filtering Join Elimination | 11.2.0.1 | |
| Dynamic statistics enhancements | 11.2.0.4 | Originally named dynamic sampling, renamed in 12c. See init parameter optimizer_dynamic_sampling. |
| Adaptive Query Optimization | 12.1.0.1 | |
| Online statistics gathering for bulk loads | 12.1.0.1 | |
| Session level statistics for Global Temporary Tables | 12.1.0.1 | |
| Multi-table left outer joins | 12.1.0.1 | |
| Lateral views | 12.1.0.1 | |
| Batch table access by rowid | 12.1.0.1 | |
| Null accepting semi joins | 12.1.0.1 | |
| Scalar subquery unnesting | 12.1.0.1 | |
| Conversion of joins that produce unnecessary duplicates to semi-joins | 12.1.0.1 | |
| Parallel Union and Parallel Union All operations | 12.1.0.1 | |
| Enhance Auto DOP | 12.1.0.1 | Enabled or disabled with init parameter parallel_degree_policy |
| Approximate count distinct | 12.1.0.2 | |
| Support for Oracle Database In-Memory | 12.1.0.2 | |
| Group-by and aggregation elimination | 12.1.0.2 | |
| Query rewrite for approximate query processing | 12.2.0.1 | |
| Statistics advisor | 12.2.0.1 | |
| Support for sharded databases | 12.2.0.1 | |
| Expression tracking | 12.2.0.1 | |
| Space-saving algorithm for partition synopses | 12.2.0.1 | |
| Oracle In-Memory Database statistics | 12.2.0.1 | |
| Support for sharding | 12.2.0.1 | |
| Cost-based OR expansion | 12.2.0.1 | |
| Sub-query elimination | 12.2.0.1 | |
| Multi-column key join elimination | 12.2.0.1 | |
| SQL Quarantine | 19c | |
| Gathering and use of real-time statistics | 19c | |
| Use of automatic indexes | 19c | |
| Exists-to-any rewrite of subqueries | 21c | |
WITH clause filter predicate pushdown | 21c | |
| Number of distinct value (NDV) modeling for real time statistics | 21c | |
| Pushing down group-by into union-all branches | 23c | |
| Subsumption of views or subqueries | 23c |
sys.dual is a single-row table. set autotrace on explain in SQL*Plus. alter session set events '10053 trace name context forever';
aux_stats$, compare with dbms_stats.gather_system_stats. dual has only one record. create table as select …) do not undergo SQL statement optimization. opt_param hint.