Oracle/Tuning2019. 10. 17. 15:36

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_FEATURES_ENABLE을 사용하면 옵티마이저의 기능을 특정 버전으로 제한할 수 있다.

1
2
3
4
5
6
7
8
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]

1
2
3
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') */

버전에 해당하는 기능은 아래 문서에서 확인할 수 있다.


매번 문서를 찾는 것은 번거로운 일이다. 아래와 같이 옵티마이저 기능을 저장할 테이블을 생성하자.

1
2
3
-- 1
DROP TABLE optimizer_features PURGE;
CREATE TABLE optimizer_features (name VARCHAR2(200), version VARCHAR2(10));

아래와 같이 데이터를 입력하자. 문서별로 상이한 기능명을 일치시켰고, 최초 추가된 버전을 정리했다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- 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;

아래는 테이블을 조회한 결과다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
-- 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


  1. 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
Posted by 정희락_

티스토리툴바