운영 중 상황이 발생하였습니다.
테이블에 row를 삭제할 일이 있는데 DML작업 시 ORA-01502 에러가 발생하였습니다.
ORA-01502: index 'TEST.EMP_PK' or partition of such index is in unusable state
에러의 내용은 INDEX가 UNUSABLE 상태여서 테이블작업에 에러가 발생하는 것이었습니다.
관련된 내용을 해결하기 위해서 찾아보다 해당 파라미터의 존재에 대해서 알게되었습니다.
비록 이 파라미터가 해당 에러를 해결하기 위한 방법은 아니었지만, 관련이 있는 파라미터인것 같아 찾아본 후 정리하고자 합니다.
해당 파라미터는 바로 Unusable 상태의 Index를 무시(Skip)하는 것입니다.
테이블의 조회나 DML의 경우 인덱스와 연관이 있게 되는데 해당 인덱스가 Unusable 상태일 때,
위의 에러가 발생하고 그것을 무시하게 해주는 파라미터입니다.
기본적으로는 TRUE이므로 크게 신경쓰지 않아도 되는 파라미터입니다.
테스트를 통해 확인해보겠습니다.
1.value가 TRUE인 것을 확인할 수 있습니다.
SQL> select name,value,default_value from v$parameter
where name like '%skip%';
NAME VALUE
--------------------------------
skip_unusable_indexes TRUE
TRUE
1 rows selected.2.인덱스와 테이블의 상태를 확인해봅니다.
SQL> select index_name,status from user_indexes
where table_name='EMP';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------
EMP_PK VALID
1 rows selected.
SQL> select count(*) from emp;
COUNT(*)
----------------
14
1 rows selected.
3.인덱스를 UNUSABLE 상태로 변경합니다.
SQL> alter index emp_pk unusable;
Statement Processed.
4.조회 및 DML을 확인합니다.
조회는 되지만 DML은 되지 않는 것을 확인할 수 있습니다.
SQL> select index_name,status from user_indexes
where table_name='EMP';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------
EMP_PK UNUSABLE
1 rows selected.
#조회
SQL> select count(*) from emp;
COUNT(*)
----------------
14
1 rows selected.
#DML
SQL> insert into emp (empno)
values (00);
ORA-01502: 인덱스 'TEST.EMP_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
SQL Execution Time > 00:00:00.015
SQL> delete from emp where empno=7788;
ORA-01502: 인덱스 'TEST.EMP_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
SQL> update emp set empno=0 where empno=7788;
ORA-01502: 인덱스 'TEST.EMP_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
5.skip_unusable_indexes 파라미터를 false로 변경 후 재시도
조회도 되지 않습니다.
SQL> alter session set skip_unusable_indexes=false;
Session altered.
SQL>
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-01502: index 'TEST.EMP_PK' or partition of such index is in unusable state
6.INDEX rebuild 후 재시도
잘 되는 것을 확인할 수 있습니다.
TRUE인 경우 INDEX가 있으면 EMP_PK라는 인덱스를 활용한 스캔을 하게됩니다.
alter index emp_pk rebuild
SQL> select index_name,status from user_indexes
where table_name='EMP';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------
EMP_PK VALID
1 rows selected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| EMP_PK | 14 | 1 (0)|
--------------------------------------------------------
7.TRUE이고 INDEX가 UNUSABLE상태일 때 조회해서 실행계획을 확인해보면 FULL TABLE SCAN을 타면서 쿼리가 수행될것이라는 것을 확인할 수 있습니다.
#FALSE 일떄
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)|
--------------------------------------------------------
결론 :
default 값이 TRUE이므로 따로 건드리지 말고 사용하면 되는 파라미터입니다.
delete 시 index가 unusable 상태여서 안되는 현상에 대해서는 Index를 drop -> dml 작업-> 재생성
하는 순서로 작업하여 처리했습니다.
또한 파라미터가 FALSE이고 인덱스가 UNUSABLE이면 조회조차 되지 않는 것을 테스트를 통해 확인하였습니다.
운영을 위해서 중요한 파라미터라고 생각이 듭니다. 왠만하면 TRUE에서 안 건드리는게 좋습니다.
출처 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jinh2004&logNo=80109628965
"skip_unusable_indexes"라는 initialization parameter에 대해서…
이 parameter는 Oracle10g부터 새롭게 등장했습니다. 하지만 SQL*Loader를 자주 사용하시는 분들은 무...
blog.naver.com
'Oracle > 운영' 카테고리의 다른 글
| 대용량 테이블 컬럼 순서 변경 테스트(12c new feature column visiable 기능 사용) (0) | 2021.11.03 |
|---|---|
| 다른 스키마(유저) Database link 삭제하기 (0) | 2021.10.27 |
| 다른 유저 테이블 Truncate 시키는 권한 (0) | 2021.10.25 |
| Locale의 LANG 과 Putty의 캐릭터셋 설정에 따른 한글 깨짐 현상 (0) | 2021.10.15 |
| DBA_DEPENDENCIES (0) | 2021.09.07 |
댓글