OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트
오라클에서 테이블이나 인덱스 등의 용량을 차지하는 object이자 segment임
하지만 프로시저, 함수, 뷰 등은 용량을 차지하지 않는 object임(segment가 아님)
그래서 이 오브젝트들은 디스크가 아닌 메모리 상에, 정확히는 library cache에 상주하게 되는데
만약 적은 library cache에 아주 많은 오브젝트들을 생성하게되면 어떻게 되는지 궁금해서 테스트해봄
본문에서는 sga와 shared_pool_size를 작게 설정한 상태에서 많은 프로시저, 함수, 뷰 등의 오브젝트를 많이 생성한뒤 영향도를 확인하는 테스트를 진행해봄
테스트
사전 환경 구성
sga_target, shared_pool_size 크기를 최소한으로 조정
|
1
2
3
4
5
|
SQL>
alter system set sga_target = 300m scope=spfile;
alter system set shared_pool_size = 240m scope=spfile;
shutdown immediate;
startup;
|
system 데이터파일 5g로 resize
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 /oradata1/ORACLE19/system01.dbf 1 0 NO AVAILABLE SYSTEM
TEMP 1 /oradata1/ORACLE19/temp01.dbf .03 32 YES ONLINE
SYSAUX 3 /oradata1/ORACLE19/sysaux01.dbf 1 0 NO AVAILABLE ONLINE
UNDOTBS1 4 /oradata1/ORACLE19/undotbs01.dbf 1 0 NO AVAILABLE ONLINE
IMSITS 5 /oradata1/ORACLE19/imsits01.dbf 2 0 NO AVAILABLE ONLINE
USERS 7 /oradata1/ORACLE19/users01.dbf 1 0 NO AVAILABLE ONLINE
6 rows selected.
SQL> alter database datafile 1 resize 5g;
Database altered.
|
오브젝트들이 system ts에 쌓이기 때문에 5gb로 증가시켜줌
기존 오브젝트 갯수 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
set timing on
select count(*) from dba_objects;
COUNT(*)
----------
72367
Elapsed: 00:00:00.07
|
현재 용량이 가장큰 오브젝트 확인
|
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
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col segment_name for a40
col segment_type for a20
select * from
(select tablespace_name, segment_name, segment_type, bytes/1024/1024 mb
from dba_segments
where tablespace_name in ('SYSTEM', 'SYSAUX')
order by 4 desc)
where rownum <= 10;
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE MB
-------------------- ---------------------------------------- -------------------- ----------
SYSTEM IDL_UB1$ TABLE 392
SYSAUX SYS_LOB0000064076C00006$$ LOBSEGMENT 72.1875
SYSAUX SYS_LOB0000072914C00006$$ LOBSEGMENT 72.1875
SYSTEM C_TOID_VERSION# CLUSTER 48
SYSAUX SYS_LOB0000007422C00004$$ LOBSEGMENT 40.1875
SYSTEM SOURCE$ TABLE 38
SYSTEM IDL_UB2$ TABLE 30
SYSAUX SYS_LOB0000066421C00004$$ LOBSEGMENT 24.1875
SYSTEM JAVA$MC$ TABLE 21
SYSAUX SYS_LOB0000067342C00002$$ LOBSEGMENT 16.1875
10 rows selected.
|
현재 shared pool 메모리 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select * from (
select name, bytes/1024/1024 as size_mb
from v$sgastat
where pool = 'shared pool'
order by 2 desc)
where rownum <= 10;
NAME SIZE_MB
-------------------------- ----------
SQLA 19.6125336
free memory 13.4635162
ksunfy_meta 1 12.8199463
KGLH0 12.1591263
row cache mutex 8.60121918
ASH buffers 8
KGLS 7.40318298
SO private sga 7.29341888
private strands 7.14355469
row cache hash 5.73414612
10 rows selected.
|
사용량 sum 해서 확인
|
1
2
3
4
5
|
SQL> select sum(bytes/1024/1024) as size_mb from v$sgastat where pool = 'shared pool';
SIZE_MB
----------
240
|
shared_pool_sized와 동일한 240mb임
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
|
SQL>
select component, current_size/1024/1024 as size_mb
from v$sga_dynamic_components;
COMPONENT SIZE_MB
---------------------------------------------------------------- ----------
shared pool 240
large pool 4
java pool 0
streams pool 0
unified pga pool 0
memoptimize buffer cache 0
DEFAULT buffer cache 36
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 4
Data Transfer Cache 0
In-Memory Area 0
In Memory RW Extension Area 0
In Memory RO Extension Area 0
ASM Buffer Cache 0
20 rows selected.
|
현재 shared pool이 240mb, DEFAULT buffer cache가 36mb, Shared IO Pool이 4mb 할당되어 있음
파싱 카운트 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col name for a30
select name, value
from v$sysstat
where name in ('parse count (total)', 'parse count (hard)');
NAME VALUE
------------------------------ ----------
parse count (total) 19897
parse count (hard) 3465
|
v$sql에 sql이 몇개 저장되는지 확인을 위해 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set timing on
DECLARE
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..10000 LOOP
v_sql := 'SELECT /* test2025_01 */ COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_ID = ' || TRUNC(DBMS_RANDOM.VALUE(1, 100000));
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:20.45
|
위 sql 수행 직후 파싱 카운트 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col name for a30
select name, value
from v$sysstat
where name in ('parse count (total)', 'parse count (hard)');
NAME VALUE
------------------------------ ----------
parse count (total) 50643
parse count (hard) 13834
|
파싱 카운트 계산
|
1
2
3
4
5
|
SQL> select 50643-19897 "total", 13834-3465 "hard" from dual;
total hard
---------- ----------
30746 10369
|
1만회 pl/sql 수행시 각각 total : 30746, hard : 10369 증가함
내부적으로 시스템 sql도 실행되어 수치가 조금더 큰듯함
위 sql 수행 직후 v$sql의 총 sql 갯수 및 테스트 sql 갯수 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
select 'all sql', count(*) from v$sql
union all
select 'test sql', count(*) from v$sql where sql_text like '%SELECT /* test2025_01 */%';
'ALLSQL' COUNT(*)
-------- ----------
all sql 400
test sql 164
|
v$sql에 총 400개 sql이 존재하고 test sql은 164개 존재함
대량 오브젝트 생성용 스크립트 생성
|
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
|
$ cat create_objects.sh
#!/bin/bash
# 오브젝트 타입 설정 (procedure, function, view)
OBJECT_TYPE=$1
# 시작 ID 설정 (각 스크립트별 100000씩 증가)
START_ID=$2
END_ID=$((START_ID + 99999))
# 오라클 환경 변수 (필요시 수정)
ORACLE_HOME=/ORA19/app/oracle/product/19.0.0/db_1
ORACLE_SID=oracle19
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID PATH
# SQL*Plus 실행하여 오브젝트 생성
sqlplus -s / as sysdba <<EOF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
DECLARE
v_sql VARCHAR2(4000);
i NUMBER;
BEGIN
FOR i IN $START_ID..$END_ID LOOP
v_sql := CASE '$OBJECT_TYPE'
WHEN 'procedure' THEN 'CREATE OR REPLACE PROCEDURE test_proc_' || i || ' AS BEGIN NULL; END;'
WHEN 'function' THEN 'CREATE OR REPLACE FUNCTION test_func_' || i || ' RETURN NUMBER AS BEGIN RETURN ' || i || '; END;'
WHEN 'view' THEN 'CREATE OR REPLACE VIEW test_view_' || i || ' AS SELECT ' || i || ' AS dummy FROM dual'
END;
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
EXIT
EOF
|
스크립트 파일에 권한 부여
|
1
|
$ chmod u+c create_objects.sh
|
스크립트 nohub로 실행
(원래는 100만개씩 생성하려했지만 너무 느려 40,30,30만개 해서 총 100만개만 생성함)
|
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
|
$
# 프로시저 생성 (4개 세션)
nohup ./create_objects.sh procedure 1 > proc1.log 2>&1 &
nohup ./create_objects.sh procedure 100001 > proc2.log 2>&1 &
nohup ./create_objects.sh procedure 200001 > proc3.log 2>&1 &
nohup ./create_objects.sh procedure 300001 > proc4.log 2>&1 &
#nohup ./create_objects.sh procedure 400001 > proc5.log 2>&1 &
#nohup ./create_objects.sh procedure 500001 > proc6.log 2>&1 &
#nohup ./create_objects.sh procedure 600001 > proc7.log 2>&1 &
#nohup ./create_objects.sh procedure 700001 > proc8.log 2>&1 &
#nohup ./create_objects.sh procedure 800001 > proc9.log 2>&1 &
#nohup ./create_objects.sh procedure 900001 > proc10.log 2>&1 &
# 함수 생성 (3개 세션)
nohup ./create_objects.sh function 1 > func1.log 2>&1 &
nohup ./create_objects.sh function 100001 > func2.log 2>&1 &
nohup ./create_objects.sh function 200001 > func3.log 2>&1 &
#nohup ./create_objects.sh function 300001 > func4.log 2>&1 &
#nohup ./create_objects.sh function 400001 > func5.log 2>&1 &
#nohup ./create_objects.sh function 500001 > func6.log 2>&1 &
#nohup ./create_objects.sh function 600001 > func7.log 2>&1 &
#nohup ./create_objects.sh function 700001 > func8.log 2>&1 &
#nohup ./create_objects.sh function 800001 > func9.log 2>&1 &
#nohup ./create_objects.sh function 900001 > func10.log 2>&1 &
# 뷰 생성 (3개 세션)
nohup ./create_objects.sh view 1 > view1.log 2>&1 &
nohup ./create_objects.sh view 100001 > view2.log 2>&1 &
nohup ./create_objects.sh view 200001 > view3.log 2>&1 &
#nohup ./create_objects.sh view 300001 > view4.log 2>&1 &
#nohup ./create_objects.sh view 400001 > view5.log 2>&1 &
#nohup ./create_objects.sh view 500001 > view6.log 2>&1 &
#nohup ./create_objects.sh view 600001 > view7.log 2>&1 &
#nohup ./create_objects.sh view 700001 > view8.log 2>&1 &
#nohup ./create_objects.sh view 800001 > view9.log 2>&1 &
#nohup ./create_objects.sh view 900001 > view10.log 2>&1 &
|
실행중인 job들을 jobs 명령으로 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ jobs -l
[1] 3148796 Running nohup ./create_objects.sh procedure 1 > proc1.log 2>&1 &
[2] 3148797 Running nohup ./create_objects.sh procedure 100001 > proc2.log 2>&1 &
[3] 3148798 Running nohup ./create_objects.sh procedure 200001 > proc3.log 2>&1 &
[4] 3148799 Running nohup ./create_objects.sh procedure 300001 > proc4.log 2>&1 &
[5] 3148802 Running nohup ./create_objects.sh function 1 > func1.log 2>&1 &
[6] 3148803 Running nohup ./create_objects.sh function 100001 > func2.log 2>&1 &
[7] 3148805 Running nohup ./create_objects.sh function 200001 > func3.log 2>&1 &
[8] 3148806 Running nohup ./create_objects.sh view 1 > view1.log 2>&1 &
[9]- 3148808 Running nohup ./create_objects.sh view 100001 > view2.log 2>&1 &
[10]+ 3148809 Running nohup ./create_objects.sh view 200001 > view3.log 2>&1 &
|
실행중인 job들을 top 명령으로 확인
|
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
|
$ top
top - 15:38:12 up 4 days, 22:55, 4 users, load average: 3.76, 2.12, 3.35
Tasks: 387 total, 8 running, 379 sleeping, 0 stopped, 0 zombie
%Cpu(s): 59.7 us, 14.3 sy, 0.0 ni, 21.6 id, 2.0 wa, 1.7 hi, 0.8 si, 0.0 st
MiB Mem : 7937.9 total, 113.4 free, 1323.5 used, 6500.9 buff/cache
MiB Swap: 3908.0 total, 2935.3 free, 972.7 used. 5898.7 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3148828 oracle 20 0 769616 183112 177772 R 28.2 2.3 0:09.52 oracle_3148828_
3148827 oracle 20 0 769680 183868 178428 R 27.9 2.3 0:09.31 oracle_3148827_
3148820 oracle 20 0 771728 184600 178480 S 27.2 2.3 0:08.96 oracle_3148820_
3148818 oracle 20 0 769600 187388 181620 R 26.6 2.3 0:09.42 oracle_3148818_
3148812 oracle 20 0 769680 182748 177500 S 26.2 2.2 0:08.78 oracle_3148812_
3148823 oracle 20 0 769600 183108 177964 S 25.9 2.3 0:08.93 oracle_3148823_
3148813 oracle 20 0 768576 180240 175508 R 25.6 2.2 0:08.85 oracle_3148813_
3148824 oracle 20 0 771748 189200 183104 S 21.9 2.3 0:07.84 oracle_3148824_
3148819 oracle 20 0 769676 186904 181444 S 20.6 2.3 0:07.94 oracle_3148819_
3148832 oracle 20 0 768576 183980 179172 S 20.3 2.3 0:07.80 oracle_3148832_
3148766 oracle 20 0 772740 213968 206100 S 14.6 2.6 0:02.96 oracle_3148766_
3148395 oracle 20 0 764816 68476 65640 R 4.3 0.8 0:01.54 ora_lg00_oracle
263 root 0 -20 0 0 0 I 4.0 0.0 3:50.78 kworker/0:1H-kblockd
2192 root 20 0 2300624 303048 11780 S 3.7 3.7 91:13.26 java
63 root 0 -20 0 0 0 I 3.0 0.0 1:53.66 kworker/2:1H-kblockd
3148517 oracle 20 0 784512 106604 99628 S 3.0 1.3 0:00.34 ora_cjq0_oracle
261 root 0 -20 0 0 0 I 2.7 0.0 5:00.05 kworker/1:1H-kblockd
|
참고 : system 테이블스페이스 공간이 작을경우 아래 에러 발생함
|
1
2
3
4
|
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.SETTINGS$ by 128 in tablespace SYSTEM
ORA-06512: at line 8
|
수행시 로그스위치 수회 발생함
|
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
|
$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2025-03-09T13:13:23.467780+09:00
Thread 1 advanced to log sequence 2 (LGWR switch)
Current log# 2 seq# 2 mem# 0: /oradata1/ORACLE19/redo02.log
2025-03-09T13:13:25.840864+09:00
ARC1 (PID:2437441): Archived Log entry 1 added for T-1.S-1 ID 0xc8f1bd0c LAD:1
2025-03-09T13:15:14.472101+09:00
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /oradata1/ORACLE19/redo03.log
2025-03-09T13:15:15.852854+09:00
ARC2 (PID:2437443): Archived Log entry 2 added for T-1.S-2 ID 0xc8f1bd0c LAD:1
2025-03-09T13:16:59.471746+09:00
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log# 1 seq# 4 mem# 0: /oradata1/ORACLE19/redo01.log
2025-03-09T13:17:00.841991+09:00
ARC3 (PID:2437445): Archived Log entry 3 added for T-1.S-3 ID 0xc8f1bd0c LAD:1
2025-03-09T13:18:44.474996+09:00
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: /oradata1/ORACLE19/redo02.log
2025-03-09T13:18:45.923809+09:00
ARC0 (PID:2437415): Archived Log entry 4 added for T-1.S-4 ID 0xc8f1bd0c LAD:1
2025-03-09T13:20:29.470045+09:00
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /oradata1/ORACLE19/redo03.log
2025-03-09T13:20:30.852176+09:00
ARC1 (PID:2437441): Archived Log entry 5 added for T-1.S-5 ID 0xc8f1bd0c LAD:1
2025-03-09T13:22:17.470581+09:00
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /oradata1/ORACLE19/redo01.log
2025-03-09T13:22:18.799124+09:00
ARC2 (PID:2437443): Archived Log entry 6 added for T-1.S-6 ID 0xc8f1bd0c LAD:1
|
참고로 현재 redo 크기는 1gb임
생성한 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select 'TEST_PROC', count(*) from dba_objects where object_name like 'TEST_PROC%'
union all
select 'TEST_FUNC', count(*) from dba_objects where object_name like 'TEST_FUNC%'
union all
select 'TEST_VIEW', count(*) from dba_objects where object_name like 'TEST_VIEW%';
'TEST_PRO COUNT(*)
--------- ----------
TEST_PROC 400000
TEST_FUNC 300000
TEST_VIEW 300000
|
모두 정상적으로 생성됨
현재 오브젝트 갯수 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
set timing on
select count(*) from dba_objects;
COUNT(*)
----------
1072376
Elapsed: 00:00:00.37
|
100만개가 증가함
현재 용량이 가장큰 오브젝트 확인
|
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
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col segment_name for a40
col segment_type for a20
select * from
(select tablespace_name, segment_name, segment_type, bytes/1024/1024 mb
from dba_segments
where tablespace_name in ('SYSTEM', 'SYSAUX')
order by 4 desc)
where rownum <= 10;
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE MB
-------------------- ---------------------------------------- -------------------- ----------
SYSTEM IDL_UB1$ TABLE 720
SYSTEM IDL_UB2$ TABLE 320
SYSTEM C_OBJ# CLUSTER 256
SYSTEM SETTINGS$ TABLE 232
SYSTEM IDL_SB4$ TABLE 176
SYSTEM I_SETTINGS1 INDEX 176
SYSTEM OBJ$ TABLE 136
SYSTEM I_IDL_UB11 INDEX 104
SYSTEM SOURCE$ TABLE 96
SYSTEM I_IDL_SB41 INDEX 96
10 rows selected.
|
IDL_UB1$이 392mb에서 720mb로, IDL_UB2$이 30mb에서 320mb로, SOURCE$이 38mb에서 96mb로 증가함
그리고 C_OBJ#라는 오브젝트도 256mb로 3번째에 존재함, 기존에는 16mb이하였는지 top 10에 없었음
현재 shared pool 메모리 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select * from (
select name, bytes/1024/1024 as size_mb
from v$sgastat
where pool = 'shared pool'
order by 2 desc)
where rownum <= 10;
NAME SIZE_MB
-------------------------- ----------
free memory 40.0060425
ksunfy_meta 1 12.8199463
kglsim object batch 8.86241913
row cache mutex 8.60121918
ASH buffers 8
KGLH0 7.92325592
kglsim heap 7.85595703
SO private sga 7.28937531
private strands 7.14355469
row cache hash 5.73414612
10 rows selected.
|
SQLA(SQL Area)가 기존 19mb에서 5mb로 이하 줄어듬(top 10 목록에 안나옴)
사용량 sum 해서 확인
|
1
2
3
4
5
|
SQL> select sum(bytes/1024/1024) as size_mb from v$sgastat where pool = 'shared pool';
SIZE_MB
----------
256
|
256으로 증가됨
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
|
SQL>
select component, current_size/1024/1024 as size_mb
from v$sga_dynamic_components;
COMPONENT SIZE_MB
---------------------------------------------------------------- ----------
shared pool 256
large pool 4
java pool 0
streams pool 0
unified pga pool 0
memoptimize buffer cache 0
DEFAULT buffer cache 20
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 4
Data Transfer Cache 0
In-Memory Area 0
In Memory RW Extension Area 0
In Memory RO Extension Area 0
ASM Buffer Cache 0
20 rows selected.
|
shared pool 크기가 기존 240mb에서 256mb로 증가됨
DEFAULT buffer cache 공간이 36mb에서 20mb로 줄어듬
파싱 카운트 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col name for a30
select name, value
from v$sysstat
where name in ('parse count (total)', 'parse count (hard)');
NAME VALUE
------------------------------ ----------
parse count (total) 21632803
parse count (hard) 2033329
|
v$sql에 sql이 몇개 저장되는지 확인을 위해 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set timing on
DECLARE
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..10000 LOOP
v_sql := 'SELECT /* test2025_02 */ COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_ID = ' || TRUNC(DBMS_RANDOM.VALUE(1, 100000));
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:20.64
|
위 sql 수행 직후 파싱 카운트 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col name for a30
select name, value
from v$sysstat
where name in ('parse count (total)', 'parse count (hard)');
NAME VALUE
------------------------------ ----------
parse count (total) 21663580
parse count (hard) 2044249
|
파싱 카운트 계산
|
1
2
3
4
5
|
SQL> select 21663580-21632803 "total", 2044249-2033329 "hard" from dual;
total hard
---------- ----------
30777 10920
|
1만회 pl/sql 수행시 각각 total : 30777, hard : 10920 증가함
100만개 오브젝트 추가전에는 각각 total : 30746, hard : 10369 증가했었음
하드파싱이 600개정도 더 증가한듯함(dbms_random을 사용한거라 랜덤값이 어떻게 사용됬는지에 따라서도 다를수 있긴함)
v$sql의 총 sql 갯수 및 테스트 sql 갯수 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
select 'all sql', count(*) from v$sql
union all
select 'test sql', count(*) from v$sql where sql_text like '%SELECT /* test2025_02 */%';
'ALLSQL' COUNT(*)
-------- ----------
all sql 332
test sql 38
|
v$sql에 총 332개 sql이 존재하고 test sql은 38개 존재함
shared pool에 library cache 사용량이 늘어나 sqla(sql area) 공간이 부족해져서 v$sql에 기존보다 sql이 적게 저장됨(표시됨)
빠르게 aging out 된다는 말이고, 이 말은 하드파싱이 많이 발생할수 있다는 말임
테스트 후 오브젝트 정리
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
BEGIN
FOR i IN 1..400000 LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE test_proc_' || i;
END LOOP;
END;
/
BEGIN
FOR i IN 1..300000 LOOP
EXECUTE IMMEDIATE 'DROP FUNCTION test_func_' || i;
EXECUTE IMMEDIATE 'DROP VIEW test_view_' || i;
END LOOP;
END;
/
|
결론 :
shared pool을 작게 설정한 상태에서 대량의 오브젝트를 생성하면 library cache 부하 증가와 shared pool 메모리 부족으로 성능 저하가 발생할 가능성이 높음
본문에는 작성하지 않았지만 30만개 오브젝트 생성 후 특정 작업들을 할때 ORA-04031이 발생하였음
|
1
2
3
|
ERROR at line 1:
ORA-04031: unable to allocate 152 bytes of shared memory ("shared pool","SELECT /* test2025_01 */ COU...","KGLH0^683cc1e3","wds: qksmmGetWorkArea")
ORA-06512: at line 6
|
그리고 shared pool 이 부족한 경우 sql을 저장할 sql area의 공간이 없어져서 자주 aging out이 일어나고 이로인해 하드파싱이 증가할 수 있음
또한 가용가능한 sga 메모리중 일부를 shared pool 크기로 재할당하여 크기를 증가시킴
본문에서도 기존 shared pool 크기를 240mb로 할당하였지만 기존 DEFAULT buffer cache에서 사용하던 36mb중 일부를 shared pool에 추가함
이렇게 오브젝트가 많은 경우 일부 시스템 뷰(딕셔너리)를 읽을때 쿼리가 느릴 수 있음
본문에서도 dba_objects뷰 전체 count시 기존에는 0.07초 소요되었지만 100만개 오브젝트 추가 이후에는 00.37초 소요됨
이 경우 fixed object 통계와 dictionary 통계를 재수집해주면 빨라질 수 있음(오라클 sr등 검토를 받은후 진행하는걸 권장함)
|
1
2
|
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_fixed_objects_stats;
|
*전체 오브젝트보다 특정 느린 오브젝트만 통계를 수집해주는것도 방법임
참고 : https://blog.naver.com/seuis398/70178552040
참조 :
3013269.1
https://forums.oracle.com/ords/r/apexds/community/q?question=sum-shareable-mem-vs-sqla-in-v-sgastat-0785
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LIBRARY_CACHE_MEMORY.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LIBRARYCACHE.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-shared-pool-and-large-pool.html#GUID-B3D48D5B-D19D-4E0F-84F0-1276FA113581
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmsu/regathering-fixed-objects-statistics.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmsu/gathering-dictionary-statistics-after-upgrading.html
https://blog.naver.com/seuis398/70178552040
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석 (0) | 2025.05.05 |
|---|---|
| 오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인 (0) | 2025.04.27 |
| 오라클 19c Insert 시 Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.02.25 |
| 오라클 19c xplan(display_cursor) 실행계획 확인 방법 (0) | 2025.01.20 |
| 오라클 데이터베이스 무료 분석 툴 SQLdb360 사용법 (0) | 2024.12.17 |