Oracle Databsae 19c부터 사용 가능한 Automatic Indexing 기능에 대해 소개 드리고자 합니다. 기능 이름에서 이미 파악하셨겠지만, 인덱스를 자동으로 권고, 만들어주는 기능이고 19c 기준으로 먼저 테스트를 하고 최신버전에서 보강된 기능들도 기술하겠습니다. 아쉽게도 현재는 Exadata 기반의 Oracle Database에서만 사용이 가능합니다.
테스트는 아래 순서대로 진행했습니다~
- 테스트 환경 준비
- 샘플 데이터/쿼리 준비
- Auto Indexing 설정 확인 및 Enable
- 샘플 쿼리 반복 수행
- 인덱스정보 확인 및 보고서 출력
1. 테스트 환경 준비
앞서 말씀드린대로 Automatic Indexing 기능은 현재 Exadata 기반의 Oracle Database에서만 제공되고 있습니다. Exadata 환경을 준비할 수 없어서 오라클 클라우드 상에서 Autonumous Transcation Processing(이하 ATP)* 생성해서 진행했습니다.
- ATP는 오라클 클라우드에서 제공되는 Autonomous Database 서비스 중에서 OLTP 업무에 최적화된 서비스입니다.
2. 샘플 데이터/쿼리 준비**
PC에 설치된 SQL Developer나 ATP 서비스에 포함되어 있는 Web SQL Developer을 이용하여 아래와 같이 테스트환경을 준비합니다. Automatic Indexing의 대상이 될려면 통계정보가 수집되어 있어야합니다. 아래 테스트에서는 19c에서 진행했기 때문에 Bulk Insert 시 통계정보가 자동수집되어 별도의 수집절차는 생략했습니다.
간단한 테이블을 준비하고 기능 적용 후 인덱스 스캔이 유리하지만 적절한 인덱스가 없어서 전체 테이블을 스캔하는 쿼리를 반복 수행 후 어떻게 조치되는지 확인해보자 합니다.
/* 간단한 샘플 테이블 생성 */
create table t1 as select * from dba_objects ;
/* 반복수행하여 테이블 사이즈 증설 */
insert into t1 select * from t1 ; commit;
/* 샘플쿼리 반복 수행 시 조건으로 들어갈 컬럼을 일괄 업데이트 */
update t1 set object_id = rownum ; commit;
/* 인덱스 없는 컬럼을 조건으로 한 SQL을 반복수행할 PL/SQL을 준비합니다 */
declare
sql_num number := 1;
max_num number := 99999999;
id_output number;
begin
loop
exit when max_num = sql_num;
begin
select distinct OBJECT_ID into id_output from t1 where object_id = sql_num;
exception
when no_data_found then
id_output := 0;
end;
-- dbms_output.put_line(output);
sql_num := sql_num + 1;
end loop;
end;
/
/* 쿼리의 플랜을 확인해보면 Full Table Scan을 유도했지만 ATP가 ExaData기반이므로 Smart Scan을 수행하고 있습니다 */
-------------------------------------
SELECT DISTINCT OBJECT_ID FROM T1 WHERE OBJECT_ID = :B1
Plan hash value: 1953586847
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE NOSORT | | 1 | | | |
|* 2 | TABLE ACCESS STORAGE FULL| T1 | 4160 | 1025K| 1025K| 12M (0)|
-------------------------------------------------------------------------------
3. Automatic Indexing 설정 확인 및 Enable
DB의 해당 기능 활성화 여부와 설정내용/적용여부 등은 아래 뷰들을 통해 확인할 수 있습니다.
select * from dict where table_name like 'DBA_AUTO_INDEX%';
DBA_AUTO_INDEX_VERIFICATIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_IND_ACTIONS
Automatic Indexing을 enable합니다. 이 때 동작모드와 적용대상을 선택합니다. 테스트이므로 다른 옵션은 default 유지합니다.
/* A.I의 동작방식을 설정
IMPLEMENT : Enable 후 인덱스를 선생하고 성능개선까지 확인되면, index를 visible처리하여 SQL의 실행계획에 반영.
REPORT ONLY : 후보 인덱스를 생성하지만 invisible상태를 유지한다. 운영자의 최종 결정이 필요.
OFF : automatic indexing. disable한다.
*/
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','<IMPLEMENT/REPORT ONLY/OFF>');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
/* 스키마 단위로 대상을 선정합니다 */
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','<Schema Name>',<TRUE/FALSE/NULL>);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'ADMIN');
select * from DBA_AUTO_INDEX_CONFIG ;
4. 샘플 쿼리 반속 수행
사전에 준비한 쿼리를 DB에서 수행해줍니다~
5. 인덱스정보 확인 및 보고서 출력
15분 간격으로 스케쥴러 Job이 수행되는 것을 확인 할 수 있습니다.
select execution_name,execution_start,execution_end, status
from dba_auto_index_executions
order by execution_start desc fetch first 4 rows only;
select * from DBA_AUTO_INDEX_STATISTICS
where execution_name in ('SYS_AI_2022-01-11/02:28:43','SYS_AI_2022-01-11/07:30:42')
and value > 0
order by 1 ;
인덱스 생성이 어떤식으로 진행되었는지도 확인합니다.
뷰에서 수행된 SQL statement를 확인하면 아래와 같습니다.
CREATE INDEX "ADMIN"."SYS_AI_cpyc6j835g6ng" ON "ADMIN"."T1"("OBJECT_ID") TABLESPACE "DATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE
ALTER INDEX "ADMIN"."SYS_AI_cpyc6j835g6ng" REBUILD ONLINE
ALTER INDEX "ADMIN"."SYS_AI_cpyc6j835g6ng" VISIBLE
인덱스를 먼저 unusable, invisible로 생성해서 기존 SQL 수행에 최대한 영향이 없도록 생성한 후에 온라인 리빌드를 수행해줍니다. 이 작업이 완료되어도 인덱스 상태는 여전히 invisible 상태이기 때문에 옵티마이저는 해당 인덱스를 참고하지 않습니다. 최종적으로 인덱스 생성으로 인한 성능개선이 검증되면 인덱스를 visible 처리하여 줍니다. 앞서 말씀 드린대로 AUTO_INDEX_MODE를 "IMPLEMENT"로 설정하면 이 과정이 자동으로 진행됩니다.
인덱스 생성 검증 결과는 아래 뷰에서 확인가능합니다.
영향받은 SQL의 sql_id 와 인덱스 생성 전후의 plan_hash_value, buffer_get, cpu_time등의 정보를 확인 할 수 있습니다.
select * from DBA_AUTO_INDEX_VERIFICATIONS;
후보인덱스를 선정하고 검증하고 생성하는 과정과 결과를 뷰를 통해 확인가능하지만, 아래와 같이 리포트 형태로 출력해보실 수도 있습니다.
/* 최근 24시간 보고서 출력 */
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
/* 가장 마지막 수행 Job에 대한 보고서 출력 */
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual ;
/* 특정 시간대에 수행된 JOB의 보고서 출력 */
SELECT DBMS_AUTO_INDEX.report_activity(activity_start => TO_TIMESTAMP('2022-01-10','YYYY-MM-DD'),
activity_end => TO_TIMESTAMP('2022-01-12','YYYY-MM-DD'))
from dual;
/* 그 외 리포트 내용에 대한 출력옵션 적용가능 */
보고서를 확인하면 인덱스를 자동으로 생성함으로써 개선된 SQL에 대한 정보까지 포함된 것을 확인할 수 있습니다.
<Automatic Indexing Report>
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 10-1월 -2022 00:00:00
Activity end : 12-1월 -2022 00:00:00
Executions completed : 158
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 10.13 GB (10.13 GB / 0 B)
Indexes dropped : 0
SQL statements verified : 3
SQL statements improved (improvement factor) : 3 (9147722.5x)
SQL plan baselines created : 0
Overall improvement factor : 9147722.5x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------
| ADMIN | T1 | SYS_AI_cpyc6j835g6ng | OBJECT_ID | B-TREE | NONE |
--------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : ADMIN
SQL ID : 4s74qfdgqj91a
SQL Text : select OBJECT_ID from t1 where object_id = 2
Improvement Factor : 9147722.5x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 472491 1186
CPU Time (s): 448038 817
Buffer Gets: 18295445 4
Optimizer Cost: 111824 4
Disk Reads: 18295362 3
Direct Writes: 0 0
Rows Processed: 4 2
Executions: 2 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 3617692013
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 111824 | |
| 1 | TABLE ACCESS STORAGE FULL | T1 | 1 | 7 | 111824 | 00:00:05 |
------------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 740849843
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 | 00:00:01 |
| * 1 | INDEX RANGE SCAN | SYS_AI_cpyc6j835g6ng | 2 | 14 | 4 | 00:00:01 |
-----------
이상 오라클 데이터베이스 19c New Feature인 Automatic Indexing에 대해 간단히 테스트해보았습니다.
개인 시간을 투자하여 작성된 글로서, 글의 내용에 오류가 있을 수 있으며, 글 속의 의견은 개인적인 의견입니다.
'3. 데이터관리' 카테고리의 다른 글
| PostgreSQL High Availability (0) | 2024.05.22 |
|---|---|
| OCI Data Integration 활용 - Data Pipeline Task 생성 및 Schedule 지정 (0) | 2024.05.22 |
| Oracle 데이터베이스 보안 솔루션 #3 - Data Safe (2) - Activity Auditing (활동 감사) (0) | 2024.05.07 |
| Oracle Database API for Mongo DB 살펴보기 (0) | 2024.05.07 |
| OCI Data Integration 활용 - Data Integration Task 생성 (0) | 2024.04.29 |
댓글