OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c invisible index 테스트
invisible 인덱스는 11g에 새로나온 기능으로 제대로 알고 사용하면 유용하게 쓸수있음
1. 인덱스를 삭제하려 할떄 삭제 전 인덱스 삭제 테스트가 가능함
2. 전체 애플리케이션에 영향을 주지 않고 튜닝을 할떄 테스트 용도로 사용할수 있음
3. 이미 존재하는 인덱스에도 컬럼을 invisible 상태로 추가해서 테스트 할수도 있음
invisible 인덱스 관련 파라미터 확인
이 값이 false 여야만 invisible 인덱스를 옵티마이저가 실행계획 생성시 자동으로 이용하지 못함
true : invisible 인덱스도 visible(일반) 인덱스로 취급됨
false : invisible 인덱스가 옵티마이저에 의해 고려되지 않음
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col name for a50
col value for a20
select name, value
from v$parameter
where name = 'optimizer_use_invisible_indexes';
NAME VALUE
-------------------------------------------------- --------------------
optimizer_use_invisible_indexes FALSE
|
두가지 테스트를 진행함
테스트1. invisible 인덱스를 이용해 실행계획 테스트
테스트2. 기존 인덱스에 컬럼 추가시 invisible 인덱스를 활용하는 테스트
테스트1. invisible 인덱스를 이용해 실행계획 테스트
샘플 테이블 생성
|
1
2
3
|
SQL> create table invidx_test as select * from dba_objects;
Table created.
|
데이터 조회 쿼리 실행
|
1
2
3
4
5
6
7
8
9
|
SQL>
select /*+ gather_plan_statistics invtest1 */ count(*) from invidx_test a
where owner = 'SYS'
and object_type = 'TABLE'
and object_id <= 300;
COUNT(*)
----------
119
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select sql_id, sql_text
from v$sql
where sql_text like '%gather_plan_statistics invtest1%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
04c2x2h7d68u3
select sql_id, sql_text from v$sql where sql_text like '%gather_plan_statistics invtest1%'
8vum8kd98ftk5
select /*+ gather_plan_statistics invtest1 */ count(*) from invidx_test a where owner = 'SYS' and object_type = 'TABLE' and object_id <= 300
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8vum8kd98ftk5', NULL, 'ADVANCED ALLSTATS LAST'));
Plan hash value: 3681357145
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 128 (100)| | 1 |00:00:00.01 | 449 |447 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.01 | 449 |447 |
|* 2 | TABLE ACCESS FULL| INVIDX_TEST | 1 | 1 | 18 | 128 (1)| 00:00:01 | 119 |00:00:00.01 | 449 |447 |
-------------------------------------------------------------------------------------------------------------------------------------
|
invisible 인덱스 생성
|
1
2
3
|
SQL> create index invidx_test_ix01 on invidx_test(owner, object_type, object_id) invisible;
Index created.
|
invisible 인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
col table_name for a15
col index_name for a20
col column_name for a20
select b.table_name, b.index_name, b.column_position, b.column_name, a.visibility
from dba_indexes a, dba_ind_columns b
where a.table_name = b.table_name
and a.index_name = b.index_name
and a.index_name = 'INVIDX_TEST_IX01'
order by b.column_position;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME VISIBILIT
--------------- -------------------- --------------- -------------------- ---------
INVIDX_TEST INVIDX_TEST_IX01 1 OWNER INVISIBLE
INVIDX_TEST INVIDX_TEST_IX01 2 OBJECT_TYPE INVISIBLE
INVIDX_TEST INVIDX_TEST_IX01 3 OBJECT_ID INVISIBLE
|
invisible 인덱스를 사용할수 있게끔 use_invisible_indexes 힌트를 사용해서 데이터 조회 쿼리 실행
|
1
2
3
4
5
6
7
8
9
|
SQL>
select /*+ gather_plan_statistics invtest2 use_invisible_indexes(a) */ count(*) from invidx_test a
where owner = 'SYS'
and object_type = 'TABLE'
and object_id <= 300;
COUNT(*)
----------
119
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
select sql_id, sql_text
from v$sql
where sql_text like '%gather_plan_statistics invtest2%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2hqp01h8gh4fc
select /*+ gather_plan_statistics invtest2 use_invisible_indexes(a) */ count(*) from invidx_test a where owner = 'SYS' and object_type = 'TABLE' and object_id <= 300
g5bucna4tkt1h
select sql_id, sql_text from v$sql where sql_text like '%gather_plan_statistics invtest2%'
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2hqp01h8gh4fc', NULL, 'ADVANCED ALLSTATS LAST'));
Plan hash value: 970173573
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.01 | 3 | 2 |
|* 2 | INDEX RANGE SCAN| INVIDX_TEST_IX01 | 1 | 1 | 18 | 2 (0)| 00:00:01 | 119 |00:00:00.01 | 3 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------
|
테스트2. 기존 인덱스에 컬럼 추가시 invisible 인덱스를 활용하는 테스트
기존에 인덱스가 있다고 가정하기 위해 테스트1에서 사용한 인덱스를 visible로 변환
|
1
2
3
4
5
6
7
|
SQL> alter index invidx_test_ix01 visible;
Index altered.
(참고용)
invisible 인덱스 => invisible 인덱스로 변환
SQL> alter index invidx_test_ix01 invisible;
|
인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
col table_name for a15
col index_name for a20
col column_name for a20
select b.table_name, b.index_name, b.column_position, b.column_name, a.visibility
from dba_indexes a, dba_ind_columns b
where a.table_name = b.table_name
and a.index_name = b.index_name
and a.index_name = 'INVIDX_TEST_IX01'
order by b.column_position;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME VISIBILIT
--------------- -------------------- --------------- -------------------- ---------
INVIDX_TEST INVIDX_TEST_IX01 1 OWNER VISIBLE
INVIDX_TEST INVIDX_TEST_IX01 2 OBJECT_TYPE VISIBLE
INVIDX_TEST INVIDX_TEST_IX01 3 OBJECT_ID VISIBLE
|
정상적으로 visible로 변경됨
invisible 인덱스로 신규컬럼 넣어서 생성
|
1
2
3
|
SQL> create index invidx_test_ix02 on invidx_test(owner, object_type, object_id, created) invisible;
Index created.
|
테스트1의 경우처럼 테스트 후 invidx_test_ix02 인덱스 visible로 변경
|
1
2
3
|
SQL> alter index invidx_test_ix02 visible;
Index altered.
|
기존 인덱스 invisible 처리
|
1
2
3
|
SQL> alter index invidx_test_ix01 invisible;
Index altered.
|
기존 인덱스 완전히 제거
|
1
2
3
|
SQL> drop index invidx_test_ix01;
Index dropped.
|
인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
col table_name for a15
col index_name for a20
col column_name for a20
select b.table_name, b.index_name, b.column_position, b.column_name, a.visibility
from dba_indexes a, dba_ind_columns b
where a.table_name = b.table_name
and a.index_name = b.index_name
and a.index_name = 'INVIDX_TEST_IX02'
order by b.column_position;
TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME VISIBILIT
--------------- -------------------- --------------- -------------------- ---------
INVIDX_TEST INVIDX_TEST_IX02 1 OWNER VISIBLE
INVIDX_TEST INVIDX_TEST_IX02 2 OBJECT_TYPE VISIBLE
INVIDX_TEST INVIDX_TEST_IX02 3 OBJECT_ID VISIBLE
INVIDX_TEST INVIDX_TEST_IX02 4 CREATED VISIBLE
|
위 경우 처럼 기존 인덱스를 삭제하기 전 영향도 확인 용으로 invisible 기능을 사용할수도 있음
먼저 invisible 로 변경 후 영향이 있을 경우 다시 visible로 변경하고
그렇지 않은 경우 삭제하는 방식을 이용하면됨
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-3A66938F-73C6-4173-844E-3938A0DBBB54
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_USE_INVISIBLE_INDEXES.html
https://cafe.naver.com/oraclebank/964
http://haisins.epac.to/wordpress/?p=3757
https://positivemh.tistory.com/213
오라클 12c 테이블 컬럼 순서 변경(invisible 컬럼 속성)
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 12.2.0.1 방법 : 오라클 12c 테이블 컬럼 순서 변경(invisible 컬럼 속성) 오라클 12c 부터 테이블 컬럼의 순서를 변경하는 방법이 생김 정확히는 컬럼을
positivemh.tistory.com
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c Memoptimize pool 테스트(memoptimize for read) (0) | 2023.02.14 |
|---|---|
| 오라클 19c db_files 파라미터 과다 설정시 pga 사용량 비교 (0) | 2023.02.10 |
| 오라클 11gR2 dbca silent mode 설치시 dbca에 나오는지 확인 (0) | 2023.02.02 |
| 오라클 19c ORA-04031 에러를 발생시키는 방법 (0) | 2023.01.25 |
| 오라클 19c 컨트롤파일 maxdatafiles와 db_files 파라미터 (0) | 2023.01.15 |