OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 인덱스 선두컬럼 값이 null인 경우 성능 테스트
이전 테스트에서 인덱스 선두 컬럼의 값이 null인 경우와 그렇지 않은 경우 플랜이 다르게 풀려 통계정보 import시 지연현상이 있었음
참고 : 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
본문에서는 통계정보 import시 수행되는 sql과 비슷한 샘플 테이블과 sql을 만들어 테스트를 진행해봄
t_stat_test 테이블의 인덱스 선두 컬럼에는 null이 저장되어 있고 t_stat_test2 테이블의 인덱스 선두 컬럼에는 상수값이 저장되어 있음
각각 어떻게 동작하는지 테스트해봄
테스트
테스트 환경 구성
1. 인덱스 선두컬럼이 null인 경우
2. 인덱스 선두컬럼이 상수값인 경우
테스트
테스트 환경 구성
샘플 테이블 생성
|
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
|
SQL>
drop table t_stat_test purge;
create table t_stat_test (
c1 varchar2(30),
c2 char(1),
c3 varchar2(30),
c4 varchar2(30),
c5 varchar2(30),
c6 varchar2(30),
c7 varchar2(30),
c8 varchar2(30),
c9 number,
c10 date
);
SQL>
drop table t_stat_test2 purge;
create table t_stat_test2 (
c1 varchar2(30),
c2 char(1),
c3 varchar2(30),
c4 varchar2(30),
c5 varchar2(30),
c6 varchar2(30),
c7 varchar2(30),
c8 varchar2(30),
c9 number,
c10 date
);
|
복합 인덱스 생성
|
1
2
3
4
5
6
7
|
SQL> create index ix_t_stat_test_c1_7 on t_stat_test(c1, c2, c3, c4, c5, c6, c7);
Index created.
SQL> create index ix_t_stat_test2_c1_7 on t_stat_test2(c1, c2, c3, c4, c5, c6, c7);
Index created.
|
데이터 100,000건 생성(t_stat_test 테이블)
c1 은 전부 null
c2 는 12종 코드가 균등 분포가 되도록 mod(level,12)로 할당
|
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
|
SQL>
insert /*+ append */ into t_stat_test (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10)
select
null as c1,
case mod(level,12)
when 0 then 'T'
when 1 then 'C'
when 2 then 'E'
when 3 then 'P'
when 4 then 'H'
when 5 then 'B'
when 6 then 'T'
when 7 then 'C'
when 8 then 'M'
when 9 then 'U'
when 10 then 'G'
when 11 then 'L'
end as c2,
'c3_'||to_char(level) as c3,
'c4_'||to_char(level) as c4,
'c5_'||to_char(mod(level,1000)) as c5,
'c6_'||to_char(mod(level,500)) as c6,
'sys_'||to_char(mod(level,200)) as c7,
'c8_'||to_char(mod(level,100)) as c8,
level as c9,
date '2025-01-01' + mod(level,365) as c10
from dual
connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
|
데이터 100,000건 생성(t_stat_test2 테이블)
c1 은 전부 'TESTDATA'
c2 는 12종 코드가 균등 분포가 되도록 mod(level,12)로 할당
|
1
2
3
4
5
6
7
|
SQL> insert /*+ append */ into t_stat_test2 select 'TESTDATA' as c1,c2,c3,c4,c5,c6,c7,c8,c9,c10 from t_stat_test;
100000 rows created.
SQL> commit;
Commit complete.
|
통계정보 수집
|
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
|
SQL>
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T_STAT_TEST',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade => true
);
end;
/
PL/SQL procedure successfully completed.
SQL>
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T_STAT_TEST2',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade => true
);
end;
/
PL/SQL procedure successfully completed.
|
1. 인덱스 선두컬럼이 null인 경우
샘플 조회 쿼리 수행
c1 is null, c2 in (12종), 나머지 c3~c7는 '=' 조건
참고. b-tree 인덱스는 "모든 인덱스 컬럼이 null"인 경우만 인덱스 엔트리를 저장하지 않음
여기선 c2~c7가 not null이라 엔트리는 존재하지만,
선두컬럼(c1)에 대한 is null 조건은 access predicate로 잘 안쓰일 수 있어
실제 플랜은 버전/옵티마이저상태에 따라 스킵스캔이나 필터로 나타날 수 있음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
var b1 varchar2(30);
var b3 varchar2(30);
var b4 varchar2(30);
var b5 varchar2(30);
var b6 varchar2(30);
var b7 varchar2(30);
--exec :b1 := null;
exec :b3 := 'c3_1';
exec :b4 := 'c4_1';
exec :b5 := 'c5_1';
exec :b6 := 'c6_1';
exec :b7 := 'sys_1';
|
실제 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> select /*+ gather_plan_statistics index(t ix_t_stat_test_c1_7) */
t.*
from t_stat_test t
where 1=1
--and t.c1 = :b1
and t.c1 is null
and t.c2 in ('T','C','E','P','H','B','T','C','M','U','G','L')
and t.c3 = :b3
and t.c4 = :b4
and t.c5 = :b5
and t.c6 = :b6
and t.c7 = :b7;
(결과생략)
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS -PROJECTION');
Plan hash value: 971769313
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STAT_TEST | 1 | 1 |00:00:00.01 | 18 |
|* 2 | INDEX SKIP SCAN | IX_T_STAT_TEST_C1_7 | 1 | 1 |00:00:00.01 | 17 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."C1" IS NULL AND "T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6
AND "T"."C7"=:B7)
filter(("T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6 AND "T"."C7"=:B7 AND
INTERNAL_FUNCTION("T"."C2")))
|
테스트 환경에서는 인덱스 첫번째 컬럼이 null이어서 그런지 skip scan을 함
샘플 조회 쿼리 수행2(range scan으로 유도하기위해 no_index_ss 힌트 사용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
var b1 varchar2(30);
var b3 varchar2(30);
var b4 varchar2(30);
var b5 varchar2(30);
var b6 varchar2(30);
var b7 varchar2(30);
--exec :b1 := null;
exec :b3 := 'c3_1';
exec :b4 := 'c4_1';
exec :b5 := 'c5_1';
exec :b6 := 'c6_1';
exec :b7 := 'sys_1';
|
실제 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> select /*+ gather_plan_statistics index(t ix_t_stat_test_c1_7) no_index_ss(t ix_t_stat_test_c1_7) */
t.*
from t_stat_test t
where 1=1
--and t.c1 = :b1
and t.c1 is null
and t.c2 in ('T','C','E','P','H','B','T','C','M','U','G','L')
and t.c3 = :b3
and t.c4 = :b4
and t.c5 = :b5
and t.c6 = :b6
and t.c7 = :b7;
(결과생략)
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS -PROJECTION');
Plan hash value: 1635882346
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 668 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STAT_TEST | 1 | 1 |00:00:00.01 | 668 |
|* 2 | INDEX RANGE SCAN | IX_T_STAT_TEST_C1_7 | 1 | 1 |00:00:00.02 | 667 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."C1" IS NULL AND "T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6
AND "T"."C7"=:B7)
filter(("T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6 AND "T"."C7"=:B7 AND
INTERNAL_FUNCTION("T"."C2")))
|
skip scan이 아닌 range scan으로 풀리자 비효율적인 scan으로 인해 buffer가 많이 증가함
샘플 조회 쿼리 수행3(inlist 방식으로 유도하기위해 USE_CONCAT 힌트 사용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
var b1 varchar2(30);
var b3 varchar2(30);
var b4 varchar2(30);
var b5 varchar2(30);
var b6 varchar2(30);
var b7 varchar2(30);
--exec :b1 := null;
exec :b3 := 'c3_1';
exec :b4 := 'c4_1';
exec :b5 := 'c5_1';
exec :b6 := 'c6_1';
exec :b7 := 'sys_1';
|
실제 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> select /*+ gather_plan_statistics index(t ix_t_stat_test_c1_7) no_index_ss(t ix_t_stat_test_c1_7) USE_CONCAT */
t.*
from t_stat_test t
where 1=1
--and t.c1 = :b1
and t.c1 is null
and t.c2 in ('T','C','E','P','H','B','T','C','M','U','G','L')
and t.c3 = :b3
and t.c4 = :b4
and t.c5 = :b5
and t.c6 = :b6
and t.c7 = :b7;
(결과생략)
|
플랜 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS -PROJECTION +HINT_REPORT');
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 668 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STAT_TEST | 1 | 1 |00:00:00.01 | 668 |
|* 2 | INDEX RANGE SCAN | IX_T_STAT_TEST_C1_7 | 1 | 1 |00:00:00.01 | 667 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."C1" IS NULL AND "T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6
AND "T"."C7"=:B7)
filter(("T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6 AND "T"."C7"=:B7 AND
INTERNAL_FUNCTION("T"."C2")))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1
U - USE_CONCAT
1 - SEL$1 / T@SEL$1
- index(t ix_t_stat_test_c1_7)
- no_index_ss(t ix_t_stat_test_c1_7)
|
use_concat 힌트는 적용되지 않고 일반 range scan으로 풀림
2. 인덱스 선두컬럼이 상수값인 경우
샘플 조회 쿼리 수행
c1 ='TESTDATA', c2 in (12종), 나머지 c3~c7는 '=' 조건
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
var b1 varchar2(30);
var b3 varchar2(30);
var b4 varchar2(30);
var b5 varchar2(30);
var b6 varchar2(30);
var b7 varchar2(30);
exec :b1 := 'TESTDATA';
exec :b3 := 'c3_1';
exec :b4 := 'c4_1';
exec :b5 := 'c5_1';
exec :b6 := 'c6_1';
exec :b7 := 'sys_1';
|
실제 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> select /*+ gather_plan_statistics index(t ix_t_stat_test2_c1_7) */
t.*
from t_stat_test2 t
where 1=1
and t.c1 = :b1
and t.c2 in ('T','C','E','P','H','B','T','C','M','U','G','L')
and t.c3 = :b3
and t.c4 = :b4
and t.c5 = :b5
and t.c6 = :b6
and t.c7 = :b7;
(결과생략)
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS -PROJECTION');
Plan hash value: 579616738
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 25 |
| 1 | INLIST ITERATOR | | 1 | 1 |00:00:00.01 | 25 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STAT_TEST2 | 10 | 1 |00:00:00.01 | 25 |
|* 3 | INDEX RANGE SCAN | IX_T_STAT_TEST2_C1_7 | 10 | 1 |00:00:00.01 | 24 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."C1"=:B1 AND (("T"."C2"='B' OR "T"."C2"='C' OR "T"."C2"='E' OR "T"."C2"='G' OR
"T"."C2"='H' OR "T"."C2"='L' OR "T"."C2"='M' OR "T"."C2"='P' OR "T"."C2"='T' OR "T"."C2"='U')) AND
"T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6 AND "T"."C7"=:B7)
|
인덱스 첫번째 컬럼이 null이 아닌 상수값이어서 inlist로 range scan을 함, skip scan시만큼 buffer를 적게 읽음
샘플 조회 쿼리 수행2(inlist로 안되게 유도하기위해 no_expand 힌트 사용)
c1 ='TESTDATA', c2 in (12종), 나머지 c3~c7는 '=' 조건
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
var b1 varchar2(30);
var b3 varchar2(30);
var b4 varchar2(30);
var b5 varchar2(30);
var b6 varchar2(30);
var b7 varchar2(30);
exec :b1 := 'TESTDATA';
exec :b3 := 'c3_1';
exec :b4 := 'c4_1';
exec :b5 := 'c5_1';
exec :b6 := 'c6_1';
exec :b7 := 'sys_1';
|
실제 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> select /*+ gather_plan_statistics index(t ix_t_stat_test2_c1_7) no_expand */
t.*
from t_stat_test2 t
where 1=1
and t.c1 = :b1
and t.c2 in ('T','C','E','P','H','B','T','C','M','U','G','L')
and t.c3 = :b3
and t.c4 = :b4
and t.c5 = :b5
and t.c6 = :b6
and t.c7 = :b7;
(결과생략)
|
플랜 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS -PROJECTION');
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 25 |
| 1 | INLIST ITERATOR | | 1 | 1 |00:00:00.01 | 25 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STAT_TEST2 | 10 | 1 |00:00:00.01 | 25 |
|* 3 | INDEX RANGE SCAN | IX_T_STAT_TEST2_C1_7 | 10 | 1 |00:00:00.01 | 24 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."C1"=:B1 AND (("T"."C2"='B' OR "T"."C2"='C' OR "T"."C2"='E' OR "T"."C2"='G' OR
"T"."C2"='H' OR "T"."C2"='L' OR "T"."C2"='M' OR "T"."C2"='P' OR "T"."C2"='T' OR "T"."C2"='U')) AND
"T"."C3"=:B3 AND "T"."C4"=:B4 AND "T"."C5"=:B5 AND "T"."C6"=:B6 AND "T"."C7"=:B7)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1
- no_expand
2 - SEL$1 / T@SEL$1
- index(t ix_t_stat_test2_c1_7)
|
inlist를 방지하려 no_expand 힌트를 사용했지만 이전과 동일하게 inlist 방식으로 풀림
(hint report에는 힌트가 사용되었다고 표시됨)
결론 :
통계정보 import 내부 쿼리와는 다르게 테스트 환경에서 비슷한 쿼리를 수행했을때 인덱스 선두 컬럼이 null인 경우 자동으로 skip scan이 발생하여 성능이 좋게 풀림
(이전 글처럼 19.27에서 통계정보 import 내부 쿼리는 비효율적으로 range scan으로 수행되어 buffer 소모가 커졌었음)
같은 조건에서 선두 컬럼에 상수값이 존재하면 inlist 기반 range scan이 안정적으로 수행되어 효율적인 접근 방식이 적용됨
선두 컬럼 null 여부에 따라 옵티마이저의 실행 계획이 달라지고 성능에도 차이가 발생함
대량 통계정보 이관 작업 등에서 statid 등의 선두 컬럼의 null 여부가 성능에 영향을 줄 수 있으므로 statid를 기입해주는것을 권장함
참조 :
오라클 19c 통계정보 대량 이관시 병렬 처리 ( https://positivemh.tistory.com/1267 )
오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
오라클 19c 통계정보 export 관련 궁금증 테스트 ( https://positivemh.tistory.com/1269 )
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선2 (1) | 2025.08.21 |
|---|---|
| 오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트 (0) | 2025.05.26 |
| 오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.05.05 |
| 오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인 (0) | 2025.04.27 |
| 오라클 19c Insert 시 Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.02.25 |