오라클 19c 하나의 쿼리블록에 힌트 여러개 작성시 나타나는 현상

2024. 1. 21. 14:39 내맘대로긍정

프린트 하기

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : Oracle Database 19.10.0.0

방법 : 오라클 19c 하나의 쿼리블록에 힌트 여러개 작성시 나타나는 현상

오라클에서 하나의 쿼리블록에 힌트를 여러개 작성 했을때 어떻게 플랜이 만들어지는지 확인해봄

/*+ a힌트 b힌트 c힌트 */ 이렇게 있을때 왼쪽에서 부터(먼저나온 순으로) 힌트가 적용되거나,

오른쪽에서 부터(나중에 나온 순으로) 유효할수도 있겠다고 생각했지만

실제 결과는 모두 무시되고 cost가 낮은 쪽으로 플랜이 만들어짐

아래 테스트로 확인 가능함

샘플 테이블, 인덱스 생성

1
2
3
4
5

SQL>
drop table tbl purge;
create table tbl as select * from emp connect by level <= 4;
create index tbl_ix01 on tbl(empno, ename);
create index tbl_ix02 on tbl(empno, sal, ename);

통계정보 수집

1
2
3
4
5
6
7

SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'TBL');
 
PL/SQL procedure successfully completed.
 
SQL> alter session set statistics_level=all;
 
Session altered.

샘플 쿼리 수행 및 플랜확인(힌트 미사용)

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

SQL> select empno
from tbl
where empno = 7788
and deptno = 20;
 
     EMPNO
----------
      7900
 
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, null, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 2144214008
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    72 (100)|          |   2955 |00:00:00.01 |     443 |
|*  1 |  TABLE ACCESS FULL| TBL  |      1 |    985 |  6895 |    72   (2)| 00:00:01 |   2955 |00:00:00.01 |     443 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TBL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TBL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("EMPNO"=7788 AND "DEPTNO"=20))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22]
 
Query Block Registry:
---------------------
 
  <q o="2" f="y">
 
 
 
48 rows selected.

힌트 미사용시 full scan을 이용함

샘플 쿼리 수행 및 플랜확인(full)

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

SQL> select /*+ full(tbl) */ empno
from tbl
where empno = 7788
and deptno = 20;
 
     EMPNO
----------
      7900
 
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, null, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 2144214008
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    72 (100)|          |   2955 |00:00:00.01 |     443 |
|*  1 |  TABLE ACCESS FULL| TBL  |      1 |    985 |  6895 |    72   (2)| 00:00:01 |   2955 |00:00:00.01 |     443 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TBL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TBL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("EMPNO"=7788 AND "DEPTNO"=20))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$1 / TBL@SEL$1
           -  full(tbl)
 
Query Block Registry:
---------------------
 
  <q o="2" f="y">
 
 
 
56 rows selected.

full 힌트를 사용하면 table full scan을 함

샘플 쿼리 수행 및 플랜확인(index(tbl tbl_ix01))

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

SQL> select /*+ index(tbl tbl_ix01) */ empno
from tbl
where empno = 7788
and deptno = 20;
 
     EMPNO
----------
      7900
 
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, null, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 3359412653
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |        |       |   254 (100)|          |   2955 |00:00:00.01 |     632 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL      |      1 |    985 |  6895 |   254   (0)| 00:00:01 |   2955 |00:00:00.01 |     632 |
|*  2 |   INDEX RANGE SCAN                  | TBL_IX01 |      1 |   2955 |       |    10   (0)| 00:00:01 |   2955 |00:00:00.01 |     208 |
------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TBL@SEL$1
   2 - SEL$1 / TBL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TBL"@"SEL$1" ("TBL"."EMPNO" "TBL"."ENAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TBL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=20)
   2 - access("EMPNO"=7788)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22]
   2 - "TBL".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$1 / TBL@SEL$1
           -  index(tbl tbl_ix01)
 
Query Block Registry:
---------------------
 
  <q o="2" f="y">
 
 
 
61 rows selected.

index(tbl tbl_ix02) 힌트를 사용하면 tbl_ix01 인덱스를 이용해 index range scan을 함

샘플 쿼리 수행 및 플랜확인(index(tbl tbl_ix02))

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

SQL> select /*+ index(tbl tbl_ix02) */ empno
from tbl
where empno = 7788
and deptno = 20;
 
     EMPNO
----------
      7900
 
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, null, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 2947791168
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |        |       |   256 (100)|          |   2955 |00:00:00.01 |     633 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL      |      1 |    985 |  6895 |   256   (0)| 00:00:01 |   2955 |00:00:00.01 |     633 |
|*  2 |   INDEX RANGE SCAN                  | TBL_IX02 |      1 |   2955 |       |    12   (0)| 00:00:01 |   2955 |00:00:00.01 |     209 |
------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TBL@SEL$1
   2 - SEL$1 / TBL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TBL"@"SEL$1" ("TBL"."EMPNO" "TBL"."SAL" "TBL"."ENAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TBL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=20)
   2 - access("EMPNO"=7788)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22]
   2 - "TBL".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$1 / TBL@SEL$1
           -  index(tbl tbl_ix02)
 
Query Block Registry:
---------------------
 
  <q o="2" f="y">
 
 
 
61 rows selected.

index(tbl tbl_ix02) 힌트를 사용하면 tbl_ix02 인덱스를 이용해 index range scan을 함

이렇게 full 힌트와 index 힌트들이 사용가능한 유효한 힌트임을 알수 있음
이상태에서 유효한 힌트를 여러개 사용한다면 어떤 인덱스를 선택할까?

샘플 쿼리 수행 및 플랜확인(full(tbl) + index(tbl tbl_ix02) + index(tbl tbl_ix02))

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

SQL> select /*+ full(tbl) index(tbl tbl_ix01) index(tbl tbl_ix02) */ empno
from tbl
where empno = 7788
and deptno = 20;
 
     EMPNO
----------
      7900
 
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, null, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 2144214008
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    72 (100)|          |   2955 |00:00:00.01 |     443 |
|*  1 |  TABLE ACCESS FULL| TBL  |      1 |    985 |  6895 |    72   (2)| 00:00:01 |   2955 |00:00:00.01 |     443 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TBL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TBL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("EMPNO"=7788 AND "DEPTNO"=20))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (3))
---------------------------------------------------------------------------
 
   1 -  SEL$1 / TBL@SEL$1
         U -  full(tbl) / hint conflicts with another in sibling query block
         U -  index(tbl tbl_ix01) / hint conflicts with another in sibling query block
         U -  index(tbl tbl_ix02) / hint conflicts with another in sibling query block
 
Query Block Registry:
---------------------
 
  <q o="2" f="y">
 
 
 
58 rows selected.

유효한 힌트를 모두 사용했을때 table full scan을 했음

다른 힌트 대신 full 힌트가 적용된것처럼 보이지만
Hint Report 부분을 보면 3가지 힌트 모두 사용되지 않았음을 확인할 수 있음(U Unused)
설명에 hint conflicts with another in sibling query block(힌트가 형제 쿼리 블록의 다른 힌트와 충돌합니다) 라고 나와있음
여기서 full scan 선택된 이유는 3가지 힌트 중 가장 cost가 낮기때문임
full scan cost : 72
tbl_ix01 index cost : 254
tbl_ix02 index cost : 256

결론 : 한 쿼리블록에 유효한 힌트 여러개를 작성하면 /*+ */ 안에 나열한 힌트 순서와 관계없이 모두 무시되고 cost가 낮다고 판단되는 스캔 방식으로 플랜이 만들어짐

참조 : 친절한 SQL 튜닝

저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

오라클 19c sqlplus의 statementcache(애플리케이션 커서 캐시) (0)	2024.02.24
오라클 19c 인덱스 생성시 nosort 옵션 대기 이벤트 확인 (0)	2024.02.23
오라클 19c 10053 trace 자동수행 스크립트 (0)	2023.12.14
오라클 19c 재기동시 발생하는 wait event (0)	2023.12.08
오라클 19c ash 뷰 데이터 1분마다 백업하는 스크립트 (0)	2023.12.07

내맘대로긍정

OracleDBA@kakao.com

Category

Menu

Comment

Link