복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 리터럴 쿼리와 바인드 변수 사용 쿼리 shared pool 사용량 비교
본문에서는 리터럴 쿼리와 바인드 변수 사용 쿼리간 shared pool을 얼마나 사용하는지 비교해보는 테스트를 수행함
참고로 리터럴 쿼리는 아래 sql로 찾을 수 있음
참고 : 리터럴 sql 조회 쿼리 ( https://positivemh.tistory.com/421 )
테스트
현재 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
sga_min_size big integer 0
sga_target big integer 1504M
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 32M
shared_pool_size big integer 0
|
현재 sga가 1.5gb 할당되어 있고 shared pool은 별도 할당 안해놓았기 때문에 자동으로 할당된 상태임
샘플 테이블 생성
|
1
2
3
|
SQL> create table t_shared_test (id number, val varchar2(100));
Table created.
|
온전한 테스트 결과를 위해 Shared Pool 비우기
|
1
2
3
|
SQL> alter system flush shared_pool;
System altered.
|
현재 shared pool 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
SELECT
ROUND(used_bytes / total_bytes * 100, 2) || '%' "Usage (%)",
ROUND(free_bytes / 1024 / 1024, 2) "Free (MB)",
ROUND(total_bytes / 1024 / 1024, 2) "Total (MB)"
FROM (
SELECT
SUM(CASE WHEN name != 'free memory' THEN bytes ELSE 0 END) used_bytes,
SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) free_bytes,
SUM(bytes) total_bytes
FROM v$sgastat
WHERE pool = 'shared pool'
);
Usage (%) Free (MB) Total (MB)
----------------------------------------- ---------- ----------
33.71% 424.26 640
|
33% 사용중임
shared pool 사용량 상세 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
SELECT
pool,
name,
ROUND(bytes / 1024 / 1024, 2) "MB"
FROM v$sgastat
WHERE pool = 'shared pool'
AND (name = 'free memory' OR bytes > 10 * 1024 * 1024)
ORDER BY bytes DESC;
POOL NAME MB
-------------- -------------------------- ----------
shared pool free memory 423.42
shared pool ksunfy_meta 1 12.01
shared pool SO private sga 11.09
|
별다르게 많이 사용중인 영역이 없음
리터럴 쿼리 10만건 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR i IN 1..100000 LOOP
v_sql := 'INSERT INTO t_shared_test VALUES (' || i || ', ''Value ' || i || ''')';
EXECUTE IMMEDIATE v_sql;
END LOOP;
COMMIT;
END;
/
Elapsed: 00:00:39.79
|
39초가 소요됨
shared pool 사용량 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
SELECT
ROUND(used_bytes / total_bytes * 100, 2) || '%' "Usage (%)",
ROUND(free_bytes / 1024 / 1024, 2) "Free (MB)",
ROUND(total_bytes / 1024 / 1024, 2) "Total (MB)"
FROM (
SELECT
SUM(CASE WHEN name != 'free memory' THEN bytes ELSE 0 END) used_bytes,
SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) free_bytes,
SUM(bytes) total_bytes
FROM v$sgastat
WHERE pool = 'shared pool'
);
Usage (%) Free (MB) Total (MB)
----------------------------------------- ---------- ----------
79.01% 134.36 640
|
사용율이 79%로 증가함
shared pool 사용량 상세 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
SELECT
pool,
name,
ROUND(bytes / 1024 / 1024, 2) "MB"
FROM v$sgastat
WHERE pool = 'shared pool'
AND (name = 'free memory' OR bytes > 10 * 1024 * 1024)
ORDER BY bytes DESC;
POOL NAME MB
-------------- -------------------------- ----------
shared pool KGLH0 148.72
shared pool free memory 134.2
shared pool SQLA 89.33
shared pool KGLHD 40.82
shared pool KGLDA 17.5
shared pool ksunfy_meta 1 12.01
shared pool SO private sga 11.09
7 rows selected.
|
KGLH0, SQLA, KGLHD, KGLDA 등 객체가 많이 용량을 차지하고 있음
리터럴 쿼리 실행 당시 top 명령으로 cpu 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ top
top - 19:12:01 up 4 days, 7:06, 3 users, load average: 1.09, 0.42, 0.24
Tasks: 320 total, 6 running, 314 sleeping, 0 stopped, 0 zombie
%Cpu0 : 1.7 us, 2.0 sy, 0.0 ni, 94.6 id, 0.0 wa, 1.4 hi, 0.3 si, 0.0 st
%Cpu1 : 99.0 us, 0.3 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.7 hi, 0.0 si, 0.0 st
MiB Mem : 7970.8 total, 331.4 free, 4789.2 used, 2850.2 buff/cache
MiB Swap: 10240.0 total, 10223.1 free, 16.9 used. 2978.7 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2641350 oracle 20 0 2042728 103152 97280 R 98.7 1.3 0:45.55 oracle_2641350_
2392113 oracle -2 0 2036352 62112 59040 S 1.0 0.8 6:07.66 ora_vktm_ora19d
2897 root -30 0 253180 91800 78628 S 0.7 1.1 25:32.93 ahf-sysmon
969 root 20 0 695424 32164 14576 S 0.3 0.4 19:11.14 tuned
1108 root 20 0 225048 3088 2652 S 0.3 0.0 0:49.41 init.tfa
1670 gdm 20 0 3409352 263128 56156 S 0.3 3.2 4:08.73 gnome-shell
2392149 oracle 20 0 2070656 70084 63776 S 0.3 0.9 0:20.99 ora_dia0_ora19d
2392155 oracle 20 0 2067340 68816 65032 S 0.3 0.8 0:01.03 ora_lgwr_ora19d
2392315 oracle -2 0 2038592 66396 61520 S 0.3 0.8 0:00.84 ora_ctwr_ora19d
2587718 oracle 20 0 2042792 105056 98496 S 0.3 1.3 0:04.92 ora_m000_ora19d
2598150 oracle 20 0 2116328 135176 113148 R 0.3 1.7 0:05.24 ora_m004_ora19d
2626787 oracle 20 0 2040748 101796 95652 S 0.3 1.2 0:01.64 ora_m003_ora19d
1 root 20 0 238432 10740 7844 S 0.0 0.1 0:18.69 systemd
|
insert 중인 40초 동안 계속 cpu가 높게 치고 insert 완료 후 바로 내려감
온전한 테스트 결과를 위해 Shared Pool 비우기
|
1
2
3
|
SQL> alter system flush shared_pool;
System altered.
|
바인드 변수 사용 쿼리 10만번 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
BEGIN
FOR i IN 1..100000 LOOP
EXECUTE IMMEDIATE 'INSERT INTO t_shared_test VALUES (:1, :2)' USING i, 'Value ' || i;
END LOOP;
COMMIT;
END;
/
Elapsed: 00:00:01.66
|
1초가 소요됨
shared pool 사용량 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
SELECT
ROUND(used_bytes / total_bytes * 100, 2) || '%' "Usage (%)",
ROUND(free_bytes / 1024 / 1024, 2) "Free (MB)",
ROUND(total_bytes / 1024 / 1024, 2) "Total (MB)"
FROM (
SELECT
SUM(CASE WHEN name != 'free memory' THEN bytes ELSE 0 END) used_bytes,
SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) free_bytes,
SUM(bytes) total_bytes
FROM v$sgastat
WHERE pool = 'shared pool'
);
Usage (%) Free (MB) Total (MB)
----------------------------------------- ---------- ----------
33.95% 422.71 640
|
사용량이 33%임
shared pool 사용량 상세 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
SELECT
pool,
name,
ROUND(bytes / 1024 / 1024, 2) "MB"
FROM v$sgastat
WHERE pool = 'shared pool'
AND (name = 'free memory' OR bytes > 10 * 1024 * 1024)
ORDER BY bytes DESC;
POOL NAME MB
-------------- -------------------------- ----------
shared pool free memory 419.35
shared pool ksunfy_meta 1 12.01
shared pool SO private sga 11.09
|
flush 직후와 큰 차이 없음
바인드 변수 쿼리 실행 당시 top 명령으로 cpu 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ top
top - 19:15:38 up 4 days, 7:10, 3 users, load average: 0.23, 0.34, 0.25
Tasks: 318 total, 3 running, 315 sleeping, 0 stopped, 0 zombie
%Cpu0 : 86.5 us, 5.4 sy, 0.0 ni, 8.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 5.3 us, 5.3 sy, 0.0 ni, 84.2 id, 5.3 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 7970.8 total, 78.5 free, 4866.6 used, 3025.7 buff/cache
MiB Swap: 10240.0 total, 10222.9 free, 17.1 used. 2901.3 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2641350 oracle 20 0 2040680 103268 97280 S 84.2 1.3 1:40.66 oracle_2641350_
2392155 oracle 20 0 2067340 68816 65032 S 2.6 0.8 0:01.21 ora_lgwr_ora19d
2392315 oracle -2 0 2038592 66396 61520 S 2.6 0.8 0:01.07 ora_ctwr_ora19d
2644555 oracle 20 0 264352 4612 3688 R 2.6 0.1 0:00.82 top
1 root 20 0 238432 10740 7844 S 0.0 0.1 0:18.69 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.11 kthreadd
3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp
|
insert 중인 1초간만 cpu가 높게 치고 insert 완료 후 바로 내려감
결론 :
바인드 변수를 사용하지 않고 리터럴 쿼리를 많이 사용하는 환경에서는
shared pool의 사용량이 많을수 밖에 없고, 이로 인해 cpu 사용율 증가 및 부하가 생길 수 있음
꼭 필요한 경우가 아니라면 바인드변수를 사용하는것을 권장함
리터럴 쿼리는 아래 sql로 찾을 수 있음
참고 : 리터럴 sql 조회 쿼리 ( https://positivemh.tistory.com/421 )
참조 :
리터럴 sql 조회 쿼리 ( https://positivemh.tistory.com/421 )
오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법 ( https://positivemh.tistory.com/849 )
오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트 ( https://positivemh.tistory.com/1213 )
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c SQL Profile 플랜 고정 방법 (0) | 2026.05.16 |
|---|---|
| 오라클 19c 튜닝시 qb_name 힌트 사용 방법 (0) | 2026.04.17 |
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선2 (1) | 2025.08.21 |
| 오라클 19c 인덱스 선두컬럼 값이 null인 경우 성능 테스트 (0) | 2025.08.14 |
| 오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트 (0) | 2025.05.26 |