오늘은 Oracle Databsae 19c부터 사용가능한 Automatic Indexing 기능에 대해 소개 드리고자 합니다. 기능 이름에서 이미 파악하셨겠지만, 인덱스를 자동으로 만들어주고 , 리빌드/삭제해주는 기능이고 19c 기준으로 먼저 테스트를 하고 21c 에서 보강된 기능들도 기술하겠습니다. 아쉽게도 현재는 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에 대해 간단히 테스트해보았습니다.
xTech