OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 대량 파티션 테이블에 로컬 인덱스 생성시 ORA-04031 발생 및 조치 시나리오
본문에서는 shared_pool_reserved_size를 작게 설정해놓은 상태에서
총 5만개의 파티션이 미리 생성된 테이블에 로컬 인덱스를 생성하는 테스트를 진행해봄
로컬 인덱스를 생성하는 구문 자체는 짧지만 내부적으로 모든 파티션에 대해 동일한 구조의 인덱스를 만들어야 하기 때문에 큰 청크가 필요할것임
이때 reserved 영역이 부족하면 ORA-04031 에러가 발생하게됨
shared pool reserved 영역이란?
큰 메모리 청크 할당을 위한 전용 공간임
일반 shared pool에서 처리하지 못하는 대형 패키지 로딩이나 커서 작업 시 사용됨
shared_pool_reserved_size 파라미터로 설정하고, 기본값은 shared_pool_size의 5%임
대부분의 경우 기본값으로 충분하지만 v$shared_pool_reserved 뷰에서
request_failures가 0보다 크면 reserved 영역이 부족하다는 뜻이므로 이 경우 reserved와 shared pool 크기를 함께 늘려줘야 함
반대로 reserved 영역이 너무 크면 일반 shared pool이 부족해져 성능 저하 가능성 있음
적절한 비율은 보통 전체 shared pool의 5~10% 사이가 권장됨
테스트
sga 확인
|
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
|
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 1184M
sga_min_size big integer 0
sga_target big integer 1184M
unified_audit_sga_queue_size integer 1048576
SQL>
set lines 200 pages 1000
col name for a40
select name, round(bytes/1024/1024, 2) mb from v$sgainfo order by 1;
NAME MB
---------------------------------------- ----------
Buffer Cache Size 96
Data Transfer Cache Size 0
Fixed SGA Size 8.52
Free SGA Memory Available 0
Granule Size 16
In-Memory Area Size 0
Java Pool Size 0
Large Pool Size 16
Maximum SGA Size 1184
Redo Buffers 7.47
Shared IO Pool Size 64
Shared Pool Size 1056
Startup overhead in Shared Pool 171.92
Streams Pool Size 0
14 rows selected.
|
shared_pool_reserved_size 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a30
col value for a30
select name, round(to_number(value)/1024/1024, 2) mb from v$parameter where name like '%shared_pool_%';
NAME MB
------------------------------ ----------
shared_pool_size 0
shared_pool_reserved_size 17.8
|
현재 1184mb의 sga를 사용중이고 이중 shared pool은 따로 파라미터 설정이 되어있지 않음, 하지만 자동할당되어 sga 영역중 1056mb를 사용하고 있음
그리고 shared_pool_reserved_size는 17.8mb 사용중임
imsi 유저가 없다면 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1017 )
테스트를 위해 imsi 유저에 dba 권한 부여
|
1
2
3
|
SQL> grant dba to imsi;
Grant succeeded.
|
대량 파티션 테이블 생성구문을 만드는 plsql 실행
|
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
|
SQL>
conn imsi/imsi
create or replace directory sql_output_dir as '/tmp';
alter session set nls_date_format = 'yyyymmdd';
SQL>
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_line VARCHAR2(32767);
v_date DATE := TO_DATE('20000101', 'YYYYMMDD');
BEGIN
v_file := UTL_FILE.FOPEN('SQL_OUTPUT_DIR', 'create_subpart_table.sql', 'W');
-- 테이블 헤더
UTL_FILE.PUT_LINE(v_file, 'CREATE TABLE SUB_PART_TEST1 (');
UTL_FILE.PUT_LINE(v_file, ' COL1 VARCHAR2(10),');
UTL_FILE.PUT_LINE(v_file, ' COL2 DATE');
UTL_FILE.PUT_LINE(v_file, ')');
UTL_FILE.PUT_LINE(v_file, 'PARTITION BY RANGE (COL1)');
UTL_FILE.PUT_LINE(v_file, 'SUBPARTITION BY RANGE (COL2)');
UTL_FILE.PUT_LINE(v_file, 'SUBPARTITION TEMPLATE (');
-- 서브파티션 반복 생성
FOR i IN 1..50000 LOOP
v_line := ' SUBPARTITION PT_' || TO_CHAR(v_date, 'YYYYMMDD') ||
' VALUES LESS THAN (''' || TO_CHAR(v_date + 1, 'YYYYMMDD') || ''')';
IF i < 50000 THEN
v_line := v_line || ',';
END IF;
UTL_FILE.PUT_LINE(v_file, v_line);
v_date := v_date + 1;
END LOOP;
-- 파티션 구문
UTL_FILE.PUT_LINE(v_file, ')');
UTL_FILE.PUT_LINE(v_file, '(');
UTL_FILE.PUT_LINE(v_file, ' PARTITION P1 VALUES LESS THAN (''K''),');
UTL_FILE.PUT_LINE(v_file, ' PARTITION P2 VALUES LESS THAN (''Z'')');
UTL_FILE.PUT_LINE(v_file, ');');
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('SQL file has been created in the SQL_OUTPUT_DIR directory.');
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF;
RAISE;
END;
/
PL/SQL procedure successfully completed.
|
생성된 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
$ cat /tmp/create_subpart_table.sql
CREATE TABLE SUB_PART_TEST1 (
COL1 VARCHAR2(10),
COL2 DATE
)
PARTITION BY RANGE (COL1)
SUBPARTITION BY RANGE (COL2)
SUBPARTITION TEMPLATE (
SUBPARTITION PT_20000101 VALUES LESS THAN ('20000102'),
SUBPARTITION PT_20000102 VALUES LESS THAN ('20000103'),
.
.
SUBPARTITION PT_21361121 VALUES LESS THAN ('21361122'),
SUBPARTITION PT_21361122 VALUES LESS THAN ('21361123')
)
(
PARTITION P1 VALUES LESS THAN ('K'),
PARTITION P2 VALUES LESS THAN ('Z')
);
|
5만라인의 테이블 생성 구문이 완성됨
테이블 생성
|
1
2
3
|
SQL> @/tmp/create_subpart_table.sql
Table created.
|
참고로 테이블 생성시에도 shared pool 부하가 있었음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col wait_class for a15
col event for a50
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
--where sample_time between sysdate - interval '1' hour and sysdate
--where sample_time between sysdate - interval '1' minute and sysdate
where sample_time between sysdate - interval '30' second and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum <6;
WAIT_CLASS EVENT EST_SECS_IN_WAIT
--------------- -------------------------------------------------- ----------------
Concurrency latch: shared pool 46
|
테이블 생성후 v$shared_pool_reserved 영역 확인
|
1
2
3
4
5
|
SQL> select request_failures from v$shared_pool_reserved;
REQUEST_FAILURES
----------------
20
|
request_failures가 20이나 발생했음, 하지만 이때는 ORA-04031은 발생하지 않음
인덱스 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
set timing on
create index sub_part_test1_ix1
on sub_part_test1 (col1)
unusable
local;
*
ERROR at line 2:
ORA-04031: unable to allocate 4016 bytes of shared memory ("sharedpool","SUB_PART_TEST1","pacdHds_kkpaco","kksgaAlloc: firstN")
|
ora-04031이 발생함
인덱스 생성중 v$shared_pool_reserved 확인
|
1
2
3
4
5
|
SQL> select request_failures from v$shared_pool_reserved;
REQUEST_FAILURES
----------------
46
|
인덱스 생성 실패 후 v$shared_pool_reserved 확인
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 200 pages 1000
select free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size
from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
15818544 58587.2 20279496 75109.2444 154 4096
|
request_failures가 무려 154번 발생함
조치를 위해 sga 크기 증가
|
1
2
3
4
|
SQL>
alter system set sga_target = 4096m scope=spfile;
alter system set shared_pool_size = 2048m scope=spfile;
alter system set shared_pool_reserved_size = 1024m scope=spfile;
|
sga_target은 4gb로, shared_pool_size는 2gb로, shared_pool_reserved_size는 1gb로 설정함
*참고로 shared_pool_reserved_size를 sharedpool 크기보다 크게 설정하면 ORA-00828 에러가 발생함
참고 : ORA-00828: specified value of shared_pool_reserved_size inconsistent with internal settings ( https://positivemh.tistory.com/499 )
재기동
|
1
2
3
|
SQL>
shutdown immediate
startup
|
sga 및 shared_pool_reserved_size 재확인
|
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
|
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 4G
sga_min_size big integer 0
sga_target big integer 4G
unified_audit_sga_queue_size integer 1048576
SQL>
set lines 200 pages 1000
col name for a40
select name, round(bytes/1024/1024, 2) mb from v$sgainfo order by 1;
NAME MB
---------------------------------------- ----------
Buffer Cache Size 2016
Data Transfer Cache Size 0
Fixed SGA Size 8.53
Free SGA Memory Available 0
Granule Size 16
In-Memory Area Size 0
Java Pool Size 0
Large Pool Size 16
Maximum SGA Size 4096
Redo Buffers 7.46
Shared IO Pool Size 128
Shared Pool Size 2048
Startup overhead in Shared Pool 201.6
Streams Pool Size 0
14 rows selected.
SQL>
set lines 200 pages 1000
col name for a30
col value for a30
select name, round(to_number(value)/1024/1024, 2) mb from v$parameter where name like '%shared_pool_%';
NAME MB
------------------------------ ----------
shared_pool_size 2048
shared_pool_reserved_size 1024
|
정상적으로 변경됨
인덱스 생성전 v$shared_pool_reserved 확인
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 200 pages 1000
select free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size
from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
283731312 8345038.59 0 0 0 0
|
재기동을 했기때문에 request_failures가 0으로 초기화됨
인덱스 생성 재시도
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set timing on
create index sub_part_test1_ix1
on sub_part_test1 (col1)
unusable
local;
Index created.
Elapsed: 00:04:14.11
|
인덱스가 에러 없이 정상적으로 생성됨
인덱스 생성중 oratop에서 plan 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
./oratop / as sysdba
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------
SQL_ID 2hfpu013y089s, child number 0
--------------------------------------
create index sub_part_test1_ix1 on sub_part_test1 (col1) unusable local
Plan hash value: 80209481
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | | | 19513 (100)| | | |
| 1 | PARTITION COMBINED ITERATOR | | 1 | 7 | | | KEY | KEY |
| 2 | INDEX BUILD NON UNIQUE (LOCAL)| SUB_PART_TEST1_IX1 | | | | |INVALID|INVALID|
| 3 | SORT CREATE INDEX | | 1 | 7 | | | | |
| 4 | TABLE ACCESS FULL | SUB_PART_TEST1 | 1 | 7 | 2 (0)| 00:00:01 |INVALID|INVALID|
----------------------------------------------------------------------------------------------------------------------
Note
-----
- estimated index size: 7374M bytes
|
예상 인덱스 크기도 알려줌
인덱스 생성중 v$shared_pool_reserved 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
set lines 200 pages 1000
select free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size
from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
970237280 8364114.48 0 0 0 0
..
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
1053621000 8231414.06 1057416 8261.0625 0 0
..
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
1052547992 8034717.5 2130424 16262.7786 0 0
|
초반에는 오히려 free space가 증가됨, 이후 free_space가 1gb된 뒤 used_space가 증가되기 시작함, request_failures는 증가되지 않음
인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> select segment_name, bytes/1024/1024 mb from dba_segments where segment_name = 'SUB_PART_TEST1_IX1';
no rows selected
SQL>
set lines 200 pages 1000
col object_name for a20
select object_name, object_type, count(*)
from dba_objects
where object_name = 'SUB_PART_TEST1_IX1'
group by object_name, object_type;
OBJECT_NAME OBJECT_TYPE COUNT(*)
-------------------- ----------------------- ----------
SUB_PART_TEST1_IX1 INDEX PARTITION 2
SUB_PART_TEST1_IX1 INDEX SUBPARTITION 100000
SUB_PART_TEST1_IX1 INDEX 1
|
unable로 생성했기때문에 segment는 없지만 object가 100003개 생성됨
결론 :
shared_pool_reserved 영역이 부족하면 큰 청크의 sql을 수행할떄 본문 테스트와 같이 ORA-04031이 발생할 수 있음
이를 방지하기 위해 적절한 shared_pool_reserved_size 파라미터 설정이 필요함
참고로 이 파라미터를 변경하기 위해선 재기동이 필요함
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SHARED_POOL_RESERVED_SIZE.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-shared-pool-and-large-pool.html#TGDBA606
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SHARED_POOL_RESERVED.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c autotask, 자동통계수집 잡 확인 및 비활성화 (0) | 2025.09.19 |
|---|---|
| 오라클 19c 통계정보 lock 방법 (0) | 2025.08.03 |
| 오라클 23ai 신기능 IVF Index Online Rebuild (0) | 2025.07.27 |
| 오라클 23ai 신기능 group by all 구문 (0) | 2025.07.27 |
| 오라클 23ai 신기능 uuid 생성 함수 (0) | 2025.07.27 |