OPTIMIZER_FEATURES_ENABLE을 사용하면 옵티마이저의 기능을 특정 버전으로 제한할 수 있다.
OPTIMIZER_FEATURES_ENABLE = {
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7
| 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7
| 9.0.0 | 9.0.1 | 9.2.0 | 9.2.0.8
| 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5
| 11.1.0.6 | 11.1.0.7 | 11.2.0.1 | 11.2.0.2 | 11.2.0.3 | 11.2.0.4
| 12.1.0.1 | 12.1.0.2 | 12.2.0.1
| 18.1.0 }
OPTIMIZER_FEATURES_ENABLE은 파라미터나 힌트로 사용할 수 있다.1
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '11.2.0.4';
/*+ OPT_PARAM('OPTIMIZER_FEATURES_ENABLE', '11.2.0.4') */
/*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */
버전에 해당하는 기능은 아래 문서에서 확인할 수 있다.
- 10.1 : https://docs.oracle.com/cd/B14117_01/server.101/b10755/initparams143.htm
- 10.2 : https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams142.htm
- 11.1 : https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams158.htm
- 11.2 : https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams164.htm
- 12.1 : https://docs.oracle.com/database/121/REFRN/GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C.htm
- 12.2 : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/OPTIMIZER_FEATURES_ENABLE.html
- 18.1 : https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/OPTIMIZER_FEATURES_ENABLE.html
- 19.1 : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html
매번 문서를 찾는 것은 번거로운 일이다. 아래와 같이 옵티마이저 기능을 저장할 테이블을 생성하자.
-- 1 DROP TABLE optimizer_features PURGE; CREATE TABLE optimizer_features (name VARCHAR2(200), version VARCHAR2(10));
아래와 같이 데이터를 입력하자. 문서별로 상이한 기능명을 일치시켰고, 최초 추가된 버전을 정리했다.
-- 2
INSERT INTO optimizer_features VALUES ('AWR source and auto capture filtering for SQL plan management', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Adaptive Query Optimization', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Adaptive cursor sharing', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Allow rewrites with multiple MVs and/or base tables', '10.2.0.1');
INSERT INTO optimizer_features VALUES ('Approximate count distinct', '12.1.0.2');
INSERT INTO optimizer_features VALUES ('Approximate percentile and median processing', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Automatically compute index statistics as part of creation', '10.1.0');
INSERT INTO optimizer_features VALUES ('Batch table access by rowid', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Bushy join optimization', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Cardinality Feedback', '11.2.0.1');
INSERT INTO optimizer_features VALUES ('Common subexpression elimination', '8.1.7');
INSERT INTO optimizer_features VALUES ('Complex view merging', '8.0.4');
INSERT INTO optimizer_features VALUES ('Consideration of bitmap access to paths for tables with only B-tree indexes', '8.0.4');
INSERT INTO optimizer_features VALUES ('Continuous adaptive query plans', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Conversion of joins that produce unnecessary duplicates to semi-joins', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Cost-based OR expansion', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Cost-based query transformations', '10.1.0');
INSERT INTO optimizer_features VALUES ('Dynamic sampling', '10.1.0');
INSERT INTO optimizer_features VALUES ('Dynamic statistics enhancements', '11.2.0.4');
INSERT INTO optimizer_features VALUES ('Enhance Auto DOP', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Enhanced Bind Peeking', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Expression tracking', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Filtering Join Elimination', '11.2.0.1');
INSERT INTO optimizer_features VALUES ('Group by placement optimization', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Group-by and aggregation elimination', '12.1.0.2');
INSERT INTO optimizer_features VALUES ('Improved average row length calculation', '8.1.6');
INSERT INTO optimizer_features VALUES ('Improved outer join cardinality calculation', '8.0.6');
INSERT INTO optimizer_features VALUES ('Improved partition statistics aggregation', '8.1.7');
INSERT INTO optimizer_features VALUES ('Improved verification of NULLs inclusion in B-tree indexes', '8.1.5');
INSERT INTO optimizer_features VALUES ('Index fast full scan', '8.0.4');
INSERT INTO optimizer_features VALUES ('Index joins', '9.0.0');
INSERT INTO optimizer_features VALUES ('Join Factorization', '11.2.0.1');
INSERT INTO optimizer_features VALUES ('Join predicate pushdown', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Lateral views', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Multi-column key join elimination', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Multi-table left outer joins', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Null accepting semi joins', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Null aware antijoins', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Online statistics gathering for bulk loads', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Oracle In-Memory Database statistics', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Ordered nested loop costing', '8.0.4');
INSERT INTO optimizer_features VALUES ('Parallel Union and Parallel Union All operations', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Partition pruning based on subquery predicates', '8.1.6');
INSERT INTO optimizer_features VALUES ('Partition pruning using join filtering', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Peeking into user-defined bind variables', '9.0.0');
INSERT INTO optimizer_features VALUES ('Push-join predicate', '8.0.4');
INSERT INTO optimizer_features VALUES ('Query rewrite enables', '10.1.0');
INSERT INTO optimizer_features VALUES ('Query rewrite for approximate query processing', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Random distribution method for left of nested loops', '8.1.6');
INSERT INTO optimizer_features VALUES ('Scalar subquery unnesting', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Session level statistics for Global Temporary Tables', '12.1.0.1');
INSERT INTO optimizer_features VALUES ('Setting of optimizer mode for user recursive SQL', '8.1.6');
INSERT INTO optimizer_features VALUES ('Skip unusable indexes', '10.1.0');
INSERT INTO optimizer_features VALUES ('Space-saving algorithm for partition synopses', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Statistics advisor', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Sub-query elimination', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Subquery Coalescing', '11.2.0.1');
INSERT INTO optimizer_features VALUES ('Subquery Unnesting', '9.0.0');
INSERT INTO optimizer_features VALUES ('Support for Oracle Database In-Memory', '12.1.0.2');
INSERT INTO optimizer_features VALUES ('Support for sharded databases', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Support for sharding', '12.2.0.1');
INSERT INTO optimizer_features VALUES ('Table Expansion', '11.2.0.1');
INSERT INTO optimizer_features VALUES ('Type-dependent selectivity estimates', '8.1.6');
INSERT INTO optimizer_features VALUES ('Use extended statistics to estimate selectivity', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Use native implementation for full outer joins', '11.1.0.6');
INSERT INTO optimizer_features VALUES ('Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity', '8.1.7');
COMMIT;
-- 3
SELECT *
FROM optimizer_features
ORDER BY TO_NUMBER (REGEXP_REPLACE (version, '\.', NULL, 4))
, name;
NAME VERSION
----------------------------------------------------------------------------------------------------- --------
Complex view merging 8.0.4
Consideration of bitmap access to paths for tables with only B-tree indexes 8.0.4
Index fast full scan 8.0.4
Ordered nested loop costing 8.0.4
Push-join predicate 8.0.4
Improved outer join cardinality calculation 8.0.6
Improved verification of NULLs inclusion in B-tree indexes 8.1.5
Improved average row length calculation 8.1.6
Partition pruning based on subquery predicates 8.1.6
Random distribution method for left of nested loops 8.1.6
Setting of optimizer mode for user recursive SQL 8.1.6
Type-dependent selectivity estimates 8.1.6
Common subexpression elimination 8.1.7
Improved partition statistics aggregation 8.1.7
Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity 8.1.7
Index joins 9.0.0
Peeking into user-defined bind variables 9.0.0
Subquery Unnesting 9.0.0
Automatically compute index statistics as part of creation 10.1.0
Cost-based query transformations 10.1.0
Dynamic sampling 10.1.0
Query rewrite enables 10.1.0
Skip unusable indexes 10.1.0
Allow rewrites with multiple MVs and/or base tables 10.2.0.1
Adaptive cursor sharing 11.1.0.6
Enhanced Bind Peeking 11.1.0.6
Group by placement optimization 11.1.0.6
Join predicate pushdown 11.1.0.6
Null aware antijoins 11.1.0.6
Partition pruning using join filtering 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
Cardinality Feedback 11.2.0.1
Filtering Join Elimination 11.2.0.1
Join Factorization 11.2.0.1
Subquery Coalescing 11.2.0.1
Table Expansion 11.2.0.1
Dynamic statistics enhancements 11.2.0.4
Adaptive Query Optimization 12.1.0.1
Batch table access by rowid 12.1.0.1
Conversion of joins that produce unnecessary duplicates to semi-joins 12.1.0.1
Enhance Auto DOP 12.1.0.1
Lateral views 12.1.0.1
Multi-table left outer joins 12.1.0.1
Null accepting semi joins 12.1.0.1
Online statistics gathering for bulk loads 12.1.0.1
Parallel Union and Parallel Union All operations 12.1.0.1
Scalar subquery unnesting 12.1.0.1
Session level statistics for Global Temporary Tables 12.1.0.1
Approximate count distinct 12.1.0.2
Group-by and aggregation elimination 12.1.0.2
Support for Oracle Database In-Memory 12.1.0.2
AWR source and auto capture filtering for SQL plan management 12.2.0.1
Approximate percentile and median processing 12.2.0.1
Bushy join optimization 12.2.0.1
Continuous adaptive query plans 12.2.0.1
Cost-based OR expansion 12.2.0.1
Expression tracking 12.2.0.1
Multi-column key join elimination 12.2.0.1
Oracle In-Memory Database statistics 12.2.0.1
Query rewrite for approximate query processing 12.2.0.1
Space-saving algorithm for partition synopses 12.2.0.1
Statistics advisor 12.2.0.1
Sub-query elimination 12.2.0.1
Support for sharded databases 12.2.0.1
Support for sharding 12.2.0.1
66 행이 선택되었습니다.
optimizer_features.sql- OPTIMIZER_FEATURES_ENABLE 힌트는 기존에 설정한 옵티마이저 관련 파라미터를 초기화시키므로 사용에 주의할 필요가 있다. [본문으로]
'Oracle > Tuning' 카테고리의 다른 글
| V$DIAG_TRACE_FILE_CONTENT 뷰 (0) | 2019.10.18 |
|---|---|
| Null-Aware 안티 조인 (1) | 2019.10.17 |
| PQ_FILTER 힌트 (0) | 2019.10.14 |
| 누적합 계산 (0) | 2019.10.14 |
| In-Memory CDT 기능과 스칼라 서브 쿼리 (0) | 2019.10.11 |
