[Tuning] Index Invisible

 

운영 환경에서 새로운 index를 추가하게되면 자칫 optimizer가 예상하지 못한 결과를 도래할 수 있다.

 

그래서 11g부터 invisible index 기능이 나왔는데

 

해당 기능의 경우 index는 존재하지 않지만 optimizer가 이를 기준으로 plan 을 생성하지 않고

운영 환경에 반영 전 검증을 하기 위한 방법 중 하나이다.

 

장점

- 인덱스를 그대로 유지하되 실행계획에는 반영하지 않으며 인덱스 생성 또는 drop 전 성능 변화에 대해 테스트가 가능
- app 단의 영향을 주지 않고 특정 app 단에서만 temporary 하게 index 사용
- 해당 index table에 대한 dml 변경 내역은 모두 적용 

 

성능 테스트

 

(1) 테스트 테이블 'IX_EMP_01' index 생성

create index IX_EMP_01 on emp (job);

 

(2) 조회 후 실행 계획 확인

select * 
from emp
where job='SALESMAN';
-------------------------------------------------------------------------------
SQL_ID  7u8bgjmg1zgwp, child number 1
-------------------------------------
select *  from emp where job='SALESMAN'
 
Plan hash value: 1399653988
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |       |     2 (100)|          |      4 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |      1 |   348 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP_01 |      1 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 | 
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB"='SALESMAN')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], 
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP".ROWID[ROWID,10], "JOB"[VARCHAR2,9]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
-------------------------------------------------------------------------------

IX_EMP_01 인덱스를 사용하는 것을 확인 할 수 있음

 

(3) IX_EMP_01 인덱스 invisible로 전환 후 실행 계획 확인

#전환 전 확인

select owner,table_name,index_name,VISIBILITY
from dba_indexes 
where index_name='IX_EMP_01';

KJY	EMP	IX_EMP_01	VISIBLE

#invisible로 전환

alter index IX_EMP_01 invisible;

#전환 후 확인

select owner,table_name,index_name,VISIBILITY
from dba_indexes 
where index_name='IX_EMP_01';

KJY	EMP	IX_EMP_01	INVISIBLE

select * 
from emp
where job='SALESMAN';

SQL_ID  7u8bgjmg1zgwp, child number 1
-------------------------------------
select *  from emp where job='SALESMAN'
 
Plan hash value: 2872589290
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |       |     3 (100)|          |      4 |00:00:00.01 |       8 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |   348 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("JOB"='SALESMAN')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], 
       "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], 
       "EMP"."DEPTNO"[NUMBER,22]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

더이상 IX_EMP_01를 사용하지 않고 Table Access Full타는 것을 확인 할 수 있음

 

❓그럼 Invisible index를 사용하게하려면 어떻게 해야할까

 

[특정 파라미터 필요]

파라미터명 : optimizer_use_invisible_indexes 

# 해당 파라미터 조회 방법

select name,value,display_value
from v$parameter
where name like '%invisible%';

시스템 상 False라면 Optimizer는 true로 변경되지 전까지 무시되어 사용할 수가 없다

따라서 접속 세션에만 테스트 할 수 있게 적용 필요

alter session set optimizer_use_invisible_indexes=true;

 

[파라미터 적용 후 실행 계획 확인]

 

index 힌트 지정하여 확인

select /*+ index( emp IX_EMP_01)  */ *
 from emp
where job='SALESMAN';

SQL_ID  cabuvubtgf0ms, child number 0
-------------------------------------
select /*+ index( emp IX_EMP_01)  */ *  from emp where job='SALESMAN'
 
Plan hash value: 1399653988
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |       |     2 (100)|          |      4 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |      1 |   348 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP_01 |      1 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB"='SALESMAN')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], 
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP".ROWID[ROWID,10], "JOB"[VARCHAR2,9]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$1 / EMP@SEL$1
           -  index( emp IX_EMP_01)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

 

 

[특정 파라미터 적용 안하고 use_invisible_indexes 힌트 적용하여 확인]

select /*+ use_invisible_indexes  */ *
 from emp
where job='SALESMAN';

SQL_ID  8d0g116v01va8, child number 0
-------------------------------------
select /*+ use_invisible_indexes  */ *  from emp where job='SALESMAN'
 
Plan hash value: 1399653988
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |       |     2 (100)|          |      4 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |      1 |   348 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP_01 |      1 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB"='SALESMAN')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], 
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP".ROWID[ROWID,10], "JOB"[VARCHAR2,9]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)