OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 자동 인덱스 개선
오라클 19c부터 자동 인덱스 기능이 추가됨(기존에 자율운영 데이터베이스(Autonomous Database)에는 존재하던 기능임)
하지만 이 기능은 일반 non-exadata 환경에서는 사용할수 없고, on-premise exadata 환경이나 oci exadata 에서만 사용가능하다고함
이 자동 인덱싱 기능이 23ai에서 개선되었음
인덱스를 생성하게 되면 조회 성능에는 도움이 되지만 DML 성능은 인덱스 갯수가 많아지면 많아질수록 나빠짐
이번 개선된 자동 인덱싱은 인덱스 유지 비용을 고려해 전체 워크로드에 도움이 되는 인덱스만 선택함. 또한 범위 조건으로 필터링되는 컬럼과 함수 기반 인덱스도 지원하여, 자동 인덱싱의 효과 범위를 넓힘.
예를들어 DML 중 인덱스 유지 관리로 인한 성능 오버헤드가 쿼리 성능 향상의 이점보다 클 경우, DML 활동이 많은 테이블은 자동 인덱스 대상에서 제외됨
이로 인해 자동 인덱싱이 DML 작업의 영향을 더 잘 평가하여 워크로드에 실질적으로 도움이 되는 인덱스를 선택함으로써 데이터베이스 성능이 최적화됨.
본문에서는 실제 exadata 환경은 아니지만 23ai exadata용 설치 파일을 다운받아 설치한 db로 테스트를 진행해봄
참고 : Oracle Linux 8.4에 Oracle 23ai Exa 버전(정식23ai 버전X) 설치 가이드 ( https://positivemh.tistory.com/1141 )
본문의 테스트 쿼리는 아래 링크에서 가져옴
참고 : OCI Autonomous Database - Automatic Indexing ( https://oracle-cloud.tistory.com/entry/Oracle-Database-Automatic-Indexing )
테스트
샘플 테이블 생성
|
1
2
3
|
SQL> create table t1 as select * from dba_objects ;
Table created.
|
샘플 데이터 추가 삽입
|
1
2
3
4
5
6
7
8
|
SQL> insert into t1 select * from t1;
..
117044 rows created.
SQL> commit;
Commit complete.
(반복)
|
샘플쿼리 반복 수행 시 조건으로 들어갈 컬럼을 일괄 업데이트
|
1
2
3
4
5
6
7
|
SQL> update t1 set object_id = rownum;
234088 rows updated.
SQL> commit;
Commit complete.
|
auto_index_mode 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col parameter_name for a40
col parameter_value for a20
select parameter_name, parameter_value
from dba_auto_index_config
--where parameter_name = 'AUTO_INDEX_MODE'
order by 1;
PARAMETER_NAME PARAMETER_VALUE
-------------------- --------------------
AUTO_INDEX_MODE OFF
|
현재 off임
auto index 활성화
|
1
2
3
|
SQL> exec dbms_auto_index.configure('auto_index_mode','implement');
PL/SQL procedure successfully completed.
|
재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col parameter_name for a20
col parameter_value for a20
select parameter_name, parameter_value
from dba_auto_index_config
where parameter_name = 'AUTO_INDEX_MODE'
order by 1;
PARAMETER_NAME PARAMETER_VALUE
-------------------- --------------------
AUTO_INDEX_MODE IMPLEMENT
|
IMPLEMENT로 변경됨
샘플 쿼리 수행
인덱스 없는 컬럼을 조건으로 한 SQL을 반복수행할 PL/SQL 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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;
/
(실행중)
|
oratop 모니터링
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ cd $ORACLE_HOME/suptools/oratop/
$ ./oratop as sysdba
s입력(sql 모드)
x입력(sql 플랜 확인)
해당 sql id 입력
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 155 (100)| |
| 1 | SORT UNIQUE NOSORT| | 1 | 5 | 155 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 154 (0)| 00:00:01 |
---------------------------------------------------------------------------
|
현재 full 스캔 수행중
15분 간격으로 스캐줄러가 실행됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col execution_name for a30
select * from
(select execution_name
, to_char(execution_start, 'yyyy/mm/dd hh24:mi:ss') execution_start
, to_char(execution_end, 'yyyy/mm/dd hh24:mi:ss') execution_end
, status
from dba_auto_index_executions
order by execution_start desc
) where rownum <= 5;
EXECUTION_NAME EXECUTION_START EXECUTION_END STATUS
------------------------------ ------------------- ------------------- -----------
SYS_AI_2024-10-27/15:55:27 2024/10/27 15:55:27 2024/10/27 15:55:27 COMPLETED
SYS_AI_2024-10-27/15:40:24 2024/10/27 15:40:25 2024/10/27 15:40:25 COMPLETED
SYS_AI_2024-10-27/15:25:22 2024/10/27 15:25:25 2024/10/27 15:25:31 COMPLETED
|
실행된 job들의 통계정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col execution_name for a30
select * from dba_auto_index_statistics
where execution_name in ('SYS_AI_2024-10-27/15:55:27')
--and value > 0
order by 1;
EXECUTION_NAME STAT_NAME VALUE
------------------------------ ----------------------------- ----------
SYS_AI_2024-10-27/15:25:22 Index candidates 0
SYS_AI_2024-10-27/15:25:22 Indexes created (visible) 0
SYS_AI_2024-10-27/15:25:22 Indexes created (invisible) 0
SYS_AI_2024-10-27/15:25:22 Indexes dropped 0
SYS_AI_2024-10-27/15:25:22 Space used in bytes 0
SYS_AI_2024-10-27/15:25:22 Space reclaimed in bytes 0
SYS_AI_2024-10-27/15:25:22 SQL statements verified 0
SYS_AI_2024-10-27/15:25:22 SQL statements improved 0
SYS_AI_2024-10-27/15:25:22 SQL statements managed by SPM 0
SYS_AI_2024-10-27/15:25:22 SQL plan baselines created 0
SYS_AI_2024-10-27/15:25:22 Improvement percentage 0
11 rows selected.
|
하지만 oci나 실제 exadata 환경이 아니라그런지
SYS_AI_2024-10-27/15:55:27, SYS_AI_2024-10-27/15:40:24, SYS_AI_2024-10-27/15:25:22 모두 value가 0으로 표시됨
관련 보고서 출력
최근 24시간 보고서 출력
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL>
set long 99999999
select dbms_auto_index.report_activity() from dual;
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 27-OCT-2024 15:57:45
Activity end : 27-OCT-2024 15:57:45
Executions completed : 3
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 1x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------
|
가장 마지막 수행 Job에 대한 보고서 출력
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
SQL> select dbms_auto_index.report_last_activity() from dual;
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 27-OCT-2024 15:55:27
Activity end : 27-OCT-2024 15:55:27
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 1x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------
|
특정 시간대에 수행된 JOB의 보고서 출력
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL> select dbms_auto_index.report_activity(activity_start => to_timestamp('2024-10-27','yyyy-mm-dd'),
activity_end => to_timestamp('2022-10-29','yyyy-mm-dd'))
from dual;
DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2024-10-27','YYYY-
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 27-OCT-2024 00:00:00
Activity end : 29-OCT-2022 00:00:00
Executions completed : 0
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 1x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------
|
15분마다 auto 인덱스 스케줄러가 실행되는데 이 파라미터는 dba_auto_index_config 에서 찾아볼수 없었음
구글링해보니 sys.smb$config 에서 볼수 있다고함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
SQL>
set lines 200 pages 1000
col parameter_name for a50
col parameter_value for 9999999999
select parameter_name, parameter_value
from sys.smb$config
order by 1;
PARAMETER_NAME PARAMETER_VALUE
-------------------------------------------------- ---------------
AUTO_CAPTURE_ACTION 0
AUTO_CAPTURE_MODULE 0
AUTO_CAPTURE_PARSING_SCHEMA_NAME 0
AUTO_CAPTURE_SQL_TEXT 0
AUTO_INDEX_COMPRESSION 0
AUTO_INDEX_DEFAULT_TABLESPACE 0
AUTO_INDEX_INCLUDE_DML_COST 0
AUTO_INDEX_MODE 0
AUTO_INDEX_REPORT_RETENTION 373
AUTO_INDEX_RETENTION_FOR_AUTO 0
AUTO_INDEX_RETENTION_FOR_MANUAL 0
AUTO_INDEX_SCHEMA 0
AUTO_INDEX_SPACE_BUDGET 50
AUTO_INDEX_TABLE 0
AUTO_SPM_EVOLVE_TASK 0
AUTO_SPM_EVOLVE_TASK_INTERVAL 3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS 53
SPACE_BUDGET_PERCENT 10
SPM_TRACING 0
_AUTO_INDEX_ABSDIFF_THRESHOLD 100
_AUTO_INDEX_CLEANUP_FREQUENCY_RATIO 1
_AUTO_INDEX_CLEANUP_TIMESTAMP 0
_AUTO_INDEX_CONCURRENCY 1
_AUTO_INDEX_CONTROL 0
_AUTO_INDEX_DEFERRED_INVALIDATION 0
_AUTO_INDEX_DERIVE_STATISTICS 0
_AUTO_INDEX_FBI_COL_LIMIT_PCT 50
_AUTO_INDEX_FBI_VC_COL_LIMIT_PCT 10
_AUTO_INDEX_FOR_JSON 0
_AUTO_INDEX_FOR_RANGE 0
_AUTO_INDEX_IMPROVEMENT_THRESHOLD 20
_AUTO_INDEX_INVISIBLE_TIME 1
_AUTO_INDEX_MIN_EXECS_TO_VERIFY 2
_AUTO_INDEX_MIN_TABLE_AGE 604800
_AUTO_INDEX_REBUILD_COUNT_LIMIT 5
_AUTO_INDEX_REBUILD_TIME_LIMIT 30
_AUTO_INDEX_REGRESSION_THRESHOLD 10
_AUTO_INDEX_REVERIFY_TIME 30
_AUTO_INDEX_SPA_CONCURRENCY 1
_AUTO_INDEX_STS_CAPTURE_TASK 0
_AUTO_INDEX_TASK_INTERVAL 900 <<
_AUTO_INDEX_TASK_MAX_RUNTIME 3600
_AUTO_INDEX_TRACE 0
_AUTO_SPM_NON_BLOCKING_HARD_PARSE 0
_AUTO_STS_CAPTURE_ORACLE_SQL 0
_AUTO_STS_CAPTURE_SQLANL_SQL 0
_AUTO_STS_CAPTURE_START_TIME 0
_AUTO_STS_CLEANUP_FREQUENCY 1
_AUTO_STS_LAST_CLEANUP_TIME 0
_AUTO_STS_RETENTION 373
_AUTO_STS_RETENTION_PCT_FMS 10
_AUTO_ZONEMAP_MODE 1
53 rows selected.
|
이중 _AUTO_INDEX_TASK_INTERVAL 이 스캐줄러 실행 주기에 관련된 파라미터로 보임(900초=15분)
결론 :
automatic indexing 기능이 있긴하지만 온프레미스환경에서는 제대로 동작하지 않음, oci 환경에서 다시 테스트가 필요할듯함
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1735
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_AUTO_INDEX.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/introduction-to-sql-tuning.html#GUID-B8EDB012-58BA-4020-AC3B-BF25850F1D6B
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/introduction-to-sql-tuning.html#GUID-2E23C8E6-7FC6-406F-AE5F-BF2677BB8800
https://oracle-base.com/articles/19c/automatic-indexing-19c
https://oracle-cloud.tistory.com/entry/Oracle-Database-Automatic-Indexing
https://velog.io/@rhjnow/Oracle-Databsae-19c-NF-Automatic-Indexing
https://db.geeksinsight.com/2019/04/26/oracle-19c-auto-indexing-feature-testing-onprem/
https://richardfoote.wordpress.com/category/auto_index_retention_for_auto/
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 Lock Free Reservation (0) | 2024.12.24 |
|---|---|
| 오라클 23ai 신기능 트랜잭션 우선순위 설정 및 자동 롤백 (0) | 2024.12.03 |
| 오라클 23ai 신기능 Parallel DML 에 대한 트랜잭션 제한 해제 (0) | 2024.11.18 |
| 오라클 19c sql 취소 기능(alter system cancel sql) (0) | 2024.11.14 |
| 오라클 23ai 신기능 스테이징 테이블(Staging) (0) | 2024.11.08 |