문서유형ㅣ장애해결
분야ㅣ튜닝
적용제품버전ㅣ6FS01, 6FS02, 6FS03, 6FS04, 6FS05, 6FS06, 6FS07, 7FS01, 7FS02
문서번호ㅣTTUTS005
현상
Index명을 별도로 명시하지 않은 경우, index range scan 힌트를 지정하고 index range scan이 가능한 Index가 있음에도 다른 Index를 통한 index fast full scan으로 처리되는 현상 입니다.
SQL> SELECT /*+INDEX_RS(A)*/ *
FROM TEST_RS A
WHERE 1=1
AND IX_COL_1 IN ('N','F')
AND ROWNUM <=5000
AND PK_COL_2 > TO_CHAR(SYSDATE-31,'YYYYMMDD');
0 row selected.
SQL ID: 6mu243bn13wb1
Child number: 1412
Plan hash value: 2008546442
Execution Plan
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Cost:7344, %%CPU:6, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:7344, %%CPU:6, Rows:1) (PS:KEY, PE:12)
3 TABLE ACCESS (ROWID): TEST_RS (Cost:7344, %%CPU:6, Rows:1)
4 FILTER (Cost:7343, %%CPU:6, Rows:1)
5 INDEX (FAST FULL SCAN): TEST_RS_PK (Cost:6858, %%CPU:0, Rows:902412)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: (("A"."IX_COL_1") IN (('F'),('N'))) (1.000)
4 - filter: ("A"."PK_COL_2" > TO_CHAR((SYSDATE - 31),'YYYYMMDD')) (0.000)
NAME VALUE
------------------------------ ----------
db block gets 10
consistent gets 36
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 5
rows processed 0
Execution Stat
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Time:0. ms, Rows:0, Starts:1)
2 PARTITION RANGE (SUBSET PART) (Time:.02 ms, Rows:0, Starts:1)
3 TABLE ACCESS (ROWID): TEST_RS (Time:0. ms, Rows:0, Starts:5)
4 FILTER (Time:.01 ms, Rows:0, Starts:5)
5 INDEX (FAST FULL SCAN): TEST_RS_PK (Time:.1 ms, Rows:0, Starts:5)
SQL> SELECT /+INDEX_RS(A(IX_COL_1))/ *
FROM TEST_RS A
WHERE 1=1
AND IX_COL_1 IN ('N','F')
AND ROWNUM <=5000
AND PK_COL_2 > TO_CHAR(SYSDATE-31,'YYYYMMDD');
0 row selected.
SQL ID: fgv1msjfdbxb8
Child number: 1413
Plan hash value: 1845789918
Execution Plan
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Cost:731223, %%CPU:0, Rows:1)
2 TABLE ACCESS (ROWID): TEST_RS (Cost:731223, %%CPU:0, Rows:1)
3 INLIST ITERATOR (Cost:0, %%CPU:0, Rows:902412)
4 PARTITION RANGE (SUBSET PART) (Cost:8799, %%CPU:0, Rows:902412) (PS:KEY, PE:12)
5 INDEX (RANGE SCAN): TEST_RS_IX (Cost:8799, %%CPU:0, Rows:902412)
Predicate Information
--------------------------------------------------------------------------------
2 - filter: ("A"."PK_COL_2" > TO_CHAR((SYSDATE - 31),'YYYYMMDD')) (0.000)
5 - access: ("A"."IX_COL_1" = :0) (1.000)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 36
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 5
rows processed 0
Execution Stat
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Time:0. ms, Rows:0, Starts:1)
2 TABLE ACCESS (ROWID): TEST_RS (Time:0. ms, Rows:0, Starts:1)
3 INLIST ITERATOR (Time:0. ms, Rows:0, Starts:1)
4 PARTITION RANGE (SUBSET PART) (Time:.01 ms, Rows:0, Starts:2)
5 INDEX (RANGE SCAN): TEST_RS_IX (Time:.02 ms, Rows:0, Starts:10)
원인
- optimizer 내부에서 iscan 생성 시 dd 상에서 index rs를 수행할 수 없는 Index가 먼저 나오고 그 뒤에 index rs를 수행할 수 있는 Index가 나온 경우, rs를 수행할 수 없는 Index로 index fast fullscan 등 다른 것도 생성하여 발생하는 현상 입니다.
- create index IX를 우선적으로 수행하고 create index PK 수행 시 해당 현상은 발생하지 않습니다. 그러나 먼저 create index PK 수행 후 create index IX 수행 시, PK로 fast full scan이 한번 수행되고 IX로 rs 힌트 적용 후 PK를 지워야 하나 cost 경쟁을 시키는 상황이 존재하게 됩니다.
해결
패치를 적용해 해결합니다. (적용패치: 309150)
주의티맥스티베로에서 제공하는 기술지원을 통해 패치를 적용합니다.
참고회피책으로서, INDEX_RS(A(IX_COL_1))와 같이 인덱스 명까지 같이 지정하면 의도한 Index 수행이 가능하며, Index 생성 순서로 조절할 수 있습니다.