오라클 19c hash 조인 과다 실행시 부하 테스트

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c hash 조인 과다 실행시 부하 테스트

오라클 19c 환경에서 과도한 PGA(Program Global Area) 사용이 시스템 성능에 미치는 영향을 확인해봄
여러개의 세션에서 hash join 쿼리를 동시에 실행 하는 테스트를 통해 cpu, 메모리, Disk I/O 사용량이 어떻게 변하는지 확인해봄

 

 

테스트
샘플 테이블 생성

1 2 3 4 5 6 7 8 9 10 11 12

SQL> create table large_table_a (     id number primary key,     data varchar2(100) );   create table large_table_b (     id number primary key,     large_table_a_id number,     data varchar2(100),     foreign key (large_table_a_id) references large_table_a(id) );

 

 

샘플 데이터 삽입

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> begin     for i in 1..1000000 loop         insert into large_table_a (id, data) values (i, 'Data ' || i);     end loop;     commit; end; /   begin     for i in 1..1000000 loop         insert into large_table_b (id, large_table_a_id, data) values (i, mod(i, 1000000) + 1, 'Data ' || i);     end loop;     commit; end; /

 

 

세션 재접속

 

 

pga 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> set linesize 200 pagesize 1000 col username for a20 col program for a30 select     s.sid,     s.serial#,     s.username,     s.program,     round(p.pga_used_mem / 1024 / 1024, 2) as pga_used_mb,     round(p.pga_alloc_mem / 1024 / 1024, 2) as pga_alloc_mb,     round(p.pga_freeable_mem / 1024 / 1024, 2) as pga_freeable_mb,     round(p.pga_max_mem / 1024 / 1024, 2) as pga_max_mb from v$session s, v$process p where s.paddr = p.addr and s.sid = sys_context('userenv', 'sid') order by p.pga_used_mem desc;          SID    SERIAL# USERNAME             PROGRAM                        PGA_USED_MB PGA_ALLOC_MB PGA_FREEABLE_MB PGA_MAX_MB ---------- ---------- -------------------- ------------------------------ ----------- ------------ --------------- ----------        786      16643 IMSI                 sqlplus@ora19 (TNS V1-V3)             1.49         2.16               0       2.16

아무것도 하지 않았을때의 pga 사용량이 2mb 정도임

 

 

hash 조인 사용하는 느린 쿼리 실행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> select x.id, x.a_data  from (     select /*+ leading(a) use_hash(b) use_hash(c) use_hash(d) use_hash(e) */         a.id, a.data as a_data, b.data as b_data     from          large_table_a a,         large_table_b b,         (select level id from dual connect by level <= 1000000) c,         (select id, data from large_table_a where rownum <= 1000000) d,         (select id, data from large_table_a where rownum <= 1000000) e     where          a.id = b.large_table_a_id         and a.id = c.id         and a.id = d.id and a.id = e.id         and a.data like 'Data%'     order by          a.id ) x where rownum <= 10000; (결과 생략)

 

 

pga 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> set linesize 200 pagesize 1000 col username for a20 col program for a30 select     s.sid,     s.serial#,     s.username,     s.program,     round(p.pga_used_mem / 1024 / 1024, 2) as pga_used_mb,     round(p.pga_alloc_mem / 1024 / 1024, 2) as pga_alloc_mb,     round(p.pga_freeable_mem / 1024 / 1024, 2) as pga_freeable_mb,     round(p.pga_max_mem / 1024 / 1024, 2) as pga_max_mb from v$session s, v$process p where s.paddr = p.addr and s.sid = sys_context('userenv', 'sid') order by p.pga_used_mem desc;          SID    SERIAL# USERNAME             PROGRAM                        PGA_USED_MB PGA_ALLOC_MB PGA_FREEABLE_MB PGA_MAX_MB ---------- ---------- -------------------- ------------------------------ ----------- ------------ --------------- ----------       1159      25282 IMSI                 sqlplus@ora19 (TNS V1-V3)            77.47        78.66               1     150.98

1개 세션의 1개의 쿼리를 수행한 후 확인 결과
현재 77mb의 pga가 할당되어 있고, 최대 pga 할당량은 150mb임

 

 

여러개 세션에서 동시에 실행하는 스크립트 작성

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

$ vi run_sess.sh #!/bin/bash   # 동시 실행할 세션의 수 session_count=200   # PL/SQL 스크립트 파일 경로 sql_script="hash.sql" kill_script="kill_test_sess.sql"   # time limit timeout_duration=60   for i in $(seq 1 $session_count) do    timeout $timeout_duration sqlplus -S imsi/imsi < $sql_script & done   wait   sqlplus imsi/imsi < $kill_script rm kill_test_sess.sql   echo "SQL excuted."

 

 

해시 조인 sql 파일 작성(세션 kill 구문도 같이 작성)

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

$ cat hash.sql exec DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'HASH_TEST', action_name => 'HASH_TEST'); select x.id, x.a_data from (     select /*+ leading(a) use_hash(b) use_hash(c) use_hash(d) use_hash(e) */         a.id, a.data as a_data, b.data as b_data     from         large_table_a a,         large_table_b b,         (select level id from dual connect by level <= 1000000) c,         (select id, data from large_table_a where rownum <= 1000000) d,         (select id, data from large_table_a where rownum <= 1000000) e     where         a.id = b.large_table_a_id         and a.id = c.id         and a.id = d.id                 and a.id = e.id         and a.data like 'Data%'     order by         a.id ) x where rownum <= 10000;   SET HEADING OFF FEEDBACK OFF PAGESIZE 0 ECHO OFF; SPOOL kill_test_sess.sql APPEND;   SELECT 'alter system kill session ''' || t1.sid || ',' || t1.serial# || ''' immediate;' "session kill" FROM GV$SESSION t1 WHERE username = 'IMSI' AND module = 'HASH_TEST' AND action = 'HASH_TEST'; PROMPT   SPOOL OFF;

 

 

기존 cpu 메모리 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12

$ top top - 20:57:25 up 6 days, 16:14,  2 users,  load average: 1.08, 0.64, 0.46 Tasks: 357 total,   1 running, 356 sleeping,   0 stopped,   0 zombie %Cpu(s):  0.1 us,  1.6 sy,  0.0 ni, 98.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st MiB Mem :   7961.3 total,    431.7 free,   1241.5 used,   6288.2 buff/cache MiB Swap:   3908.0 total,   2779.4 free,   1128.6 used.   4701.4 avail Mem     PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND     1 root      20   0  242484   8128   6244 S   0.0   0.1   0:41.39 systemd     2 root      20   0       0      0      0 S   0.0   0.0   0:00.24 kthreadd     3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp     4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par_gp

cpu 사용량이 1% 미만이고 메모리 사용량도 1241.5 used 임

 

 

sh 파일 권한 부여 후 실행

1 2	$ chmod u+x run_sess.sh $ sh run_sess.sh

 

 

cpu 메모리 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ top top - 21:07:49 up 6 days, 16:24,  2 users,  load average: 17.27, 7.31, 5.62 Tasks: 966 total,  64 running, 901 sleeping,   0 stopped,   1 zombie %Cpu(s): 55.4 us, 39.6 sy,  0.0 ni,  2.7 id,  1.0 wa,  0.9 hi,  0.5 si,  0.0 st MiB Mem :   7961.3 total,    100.9 free,   4103.8 used,   3756.7 buff/cache MiB Swap:   3908.0 total,   2484.0 free,   1424.0 used.   1936.3 avail Mem     PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND  1235 root      20   0  381404   3580   3580 R  11.6   0.0   0:18.62 rngd 41032 root      20   0  125212  62808   6660 R   7.0   0.8   0:00.27 perl 41387 oracle    20   0 2595944 144572 115640 R   5.0   1.8   0:00.15 oracle_41387_or    59 root      20   0       0      0      0 R   4.7   0.0   0:59.56 kswapd0 41383 oracle    20   0 2595948 136456 110948 R   4.3   1.7   0:00.13 oracle_41383_or 41388 oracle    20   0 2595952 136352 111028 R   4.3   1.7   0:00.13 oracle_41388_or 41213 oracle    20   0 2595948 132724 109136 R   4.0   1.6   0:00.12 oracle_41213_or 41325 oracle    20   0 2595948 130048 107128 R   4.0   1.6   0:00.12 oracle_41325_or

cpu 사용량이 55% 로 올라가고 메모리 사용량도 1241.5 used 에서 4103.8 used 로 확연히 늘어남

 

 

sh 파일 실행시 ash 확인

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 wait_class for a12 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 ------------ -------------------------------------------------- ---------------- Scheduler    acknowledge over PGA limit                                     2200 User I/O     direct path write temp                                          456 User I/O     direct path read temp                                           326                                                                              185 System I/O   control file parallel write                                       1

몇가지 이벤트가 발생함
"direct path write/read temp" 이벤트는 정렬작업시 pga 가 부족할때 temp disk로 내려서 정렬한 후 다시 올릴때 발생하는 이벤트임
"Acknowledge Over PGA Limit" 이벤트는 12cR1에서 도입된 새로운 대기 이벤트로 pga_aggregate_limit 파라미터와 함께 도입됨
이 이벤트는 인스턴스가 pga_aggregate_limit에 도달하거나 근접할 때 발생하며, 추가 PGA 메모리를 필요로 하는 프로세스를 일시적으로 대기 상태로 전환함
이렇게 함으로써 다른 프로세스가 메모리를 해제하고, ORA-04036 오류를 피할 수 있도록 함

 

 

결론 :
oltp환경에서 빈번히 수행되는 쿼리에 hash join을 사용하면
cpu, 메모리, temp로 인한 disk i/o 사용량이 증가해 장애로 이어질수있으니 튜닝시 주의해야함

 

 

참조 : 2138882.1