OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 통계정보 대량 이관시 병렬 처리
지난 게시글에서 통계정보를 이관하는 방법을 설명했음
참고 : 오라클 19c 통계정보 이관 방법 ( https://positivemh.tistory.com/1265 )
규모가 작은 db의 경우 이 방식으로만 이관해도 큰 무리가 없음
하지만 db 크기가 큰 경우 이 방식으로만 이관하게되면 속도가 너무 느려 프로시저가 끝나질 않음
(20250820 업데이트 : 버그로 인한 속도 저하였음, 일반적으로라면 db 크기가 커도 빠르게 끝나야 정상임)
그리고 dbms_stats.import_database_stats에는 병렬 옵션이 존재하지 않음
본문에서는 이런 경우 수동으로 세션을 여러개로 나눠서 통게 import 시 테이블 레벨로 이관하는 방법을 설명함
그리고 import_database_stats과 import_table_stats 두개의 시간도 같이 측정해봄
테스트
샘플 유저 및 테이블, 인덱스 등 생성
참고 : 오라클 19c 유저 100개, 테이블 100개, 인덱스 100개, 데이터 1000건 생성 ( https://positivemh.tistory.com/1266 )
현재 db에는 유저 100개와 해당 유저에 테이블 100개, 인덱스 100개 생성 후 데이터도 1000건씩 생성되어있음
이 상태에서 통계정보를 export 한뒤 다시 import 하는 테스트를 진행함
통계정보 수집(db 레벨 수집)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set timing on
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
degree => DBMS_STATS.AUTO_DEGREE,
cascade => TRUE
);
END;
/
PL/SQL procedure successfully completed.
|
각 파라미터 의미
DBMS_STATS.GATHER_DATABASE_STATS : db 레벨에서 통계정보 수집
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE : 자동 샘플링, Oracle이 데이터 양과 통계 품질에 따라 적절한 샘플링 비율을 자동 결정
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY' : Oracle이 컬럼의 데이터 분포를 기반으로 히스토그램을 수집할 컬럼을 결정
degree => DBMS_STATS.AUTO_DEGREE : 병렬 자동 시스템 상황(코어 수, 부하 등)에 따라 Oracle이 병렬도를 자동 설정
cascade => TRUE 인덱스 포함 테이블뿐 아니라 해당 테이블에 속한 인덱스 통계도 함께 수집
참고 : 오라클 19c 통계정보 이관 방법 ( https://positivemh.tistory.com/1265 )
통계 저장용 테이블 생성
|
1
2
3
|
SQL> exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
통계정보 export(ALL_TABLE_STATS 테이블로 통계정보를 넣는작업)
|
1
2
3
|
SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
all_table_stats 테이블 count 확인
|
1
2
3
4
5
|
SQL> select count(*) from system.all_table_stats;
COUNT(*)
----------
1529703
|
약 150만건이 저장됨
참고1. 통계정보 export 시 statown과 stattab만 넣었기 때문에 statid는 모두 NULL로 되어있음
|
1
2
3
4
5
6
7
8
|
SQL>
select nvl(statid, 'NULL') statid, count(*)
from system.all_table_stats
group by statid;
STATID COUNT(*)
------ ----------
NULL 1529703
|
참고2. stat 테이블에는 index도 존재함
|
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>
set lines 200 pages 1000
col owner for a15
col index_type for a10
col index_name for a20
col table_name for a30
col columns for a50
SELECT a.*, (SELECT status from dba_indexes where index_name = a.index_name and rownum<=1) status from (
SELECT
ind.owner AS "OWNER",
ind.table_name AS "TABLE_NAME",
ind.index_name AS "INDEX_NAME",
ind.index_type AS "INDEX_TYPE",
LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS"
FROM
dba_indexes ind
JOIN
dba_ind_columns ind_col
ON ind.owner = ind_col.index_owner
AND ind.index_name = ind_col.index_name
WHERE
ind.table_name = 'ALL_TABLE_STATS'
AND ind.owner = 'SYSTEM'
GROUP BY
ind.owner,
ind.index_name,
ind.index_type,
ind.table_name
ORDER BY
ind.owner,
ind.index_name) a;
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE COLUMNS STATUS
--------------- ------------------------------ -------------------- ---------- -------------------------------------------------- --------
SYSTEM ALL_TABLE_STATS ALL_TABLE_STATS NORMAL STATID, TYPE, C5, C1, C2, C3, C4, VERSION VALID
|
ALL_TABLE_STATS에 인덱스 이름도 ALL_TABLE_STATS임 STATID, TYPE, C5, C1, C2, C3, C4, VERSION 컬럼으로 이루어져 있음
다시 이 통계정보를 db에 import(ALL_TABLE_STATS 테이블에서 db 테이블로 통계정보를 넣는작업)
|
1
2
3
4
|
SQL>
set timing on
exec dbms_stats.import_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
(실행중)
|
top 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ top
top - 16:07:05 up 30 min, 2 users, load average: 1.01, 0.72, 0.68
Tasks: 378 total, 2 running, 376 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 0.0 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st
%Cpu3 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 99.3 us, 0.0 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.3 hi, 0.3 si, 0.0 st <<<<<<<<
%Cpu5 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 0.7 us, 0.7 sy, 0.0 ni, 97.0 id, 0.0 wa, 1.7 hi, 0.0 si, 0.0 st
%Cpu7 : 0.0 us, 0.0 sy, 0.0 ni, 96.0 id, 4.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu8 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu9 : 0.0 us, 0.0 sy, 0.0 ni, 99.3 id, 0.3 wa, 0.3 hi, 0.0 si, 0.0 st
MiB Mem : 7932.5 total, 52.2 free, 1343.4 used, 6536.9 buff/cache
MiB Swap: 10240.0 total, 10227.9 free, 12.1 used. 4166.1 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2331 oracle 20 0 2984868 2.1g 2.0g R 100.0 26.6 17:39.92 oracle_2331_ora
1692 oracle -2 0 2935420 64480 61408 S 1.0 0.8 0:16.42 ora_vktm_ora19d
2885 oracle 20 0 264360 4636 3716 R 0.7 0.1 0:06.01 top
251 root 0 -20 0 0 0 I 0.3 0.0 0:00.13 kworker/7:1H-kblockd
955 root 20 0 499412 11548 9736 S 0.3 0.1 0:01.35 vmtoolsd
1729 oracle 20 0 2936712 98472 94492 S 0.3 1.2 0:00.55 ora_dbrm_ora19d
|
cpu 10개중에 하나만 사용중임
oratop 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ /app/oracle/product/19c/suptools/oratop/oratop / as sysdba -rf
Oracle 19c - 16:07:13 Primary r/w ORA1 up: 31t, 1 sn, 1 ins, 0 er, 23G sz, 2.3G sga, 0%fra, archivelog 9.9%db
ID CPU %CPU LOAD AAS ASC ASI ASW ISW REDO TEMP IORT MBPS IOPS R/S W/S LIO GCPS %FRE PGA NETV UTPS UCPS SQRT %DBC %DBW
1 10 10.4 1 1.0 1 0 0 0 162k 1M 3.7m 0.4 21.5 4.2 17.3 100k 0 14.4 598M 0 0 0.6 443u 9.8 0.2
EVENT (RT) SESSIONS WAIT TIME AVG %DBT WAIT_CLASS
log file parallel write 956 1.5s 1.5m 41.8 System I/O
DB CPU 970m 27.9
db file sequential read 82 940m 12m 27.2 User I/O
latch free 68 30m 518u 1 Other
oracle thread bootstrap 1 30m 31m 0.9 Other
ID SID SPID USERNAME PROGRAM SRV SERVICE OPN SQLID/BLOCKER E/T %CPU %LIO PGA STS STE WAIT_CLASS EVENT/OBJECT NAME W/T
1 197 2331 SYS sqlplus@o DED SYS$USE INS c13dgxma46u7s 3.6t 103 100 22M ACT RUN CPU Runqueue 7.2m
|
빠르게 나타났다 사라지는 insert 쿼리들에 %CPU가 103으로 높은 값이 찍힘
oratop에 걸리는 쿼리는 대부분 insert가 많음(insert들이 느리게 수행되서 그런것으로 판단됨)
oratop 플랜확인
oratop에서 x 입력 후 sql_id 입력
|
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
66
67
68
69
70
71
72
|
Enter sql_id: c13dgxma46u7s
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c13dgxma46u7s, child number 0
--------------------------------------
insert /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false')
*/ into sys.dbms_stats_id_map_tab (c5, c1, c2, c3, cn)
select /*+ leading(s) index(s) */ distinct s.c5, s.c1,
s.c2, s.c3, d.subpartition_name cn from
"SYSTEM"."ALL_TABLE_STATS" s, (select u.name
table_owner, op.name table_name, op.subname
partition_name, os.subname subpartition_name,
tsp.subpart# subpartition_position from user$
u, obj$ op, tabsubpartv$ tsp, obj$ os where
u.user# = op.owner# and op.type# = 19 and
op.obj# = tsp.pobj# and tsp.obj# = os.obj#
and os.type# = 34) d where s.c5 = :1 and s.c1 = :2
and s.type in ('T','C','E','P','H','B','t','c','M','U','G','L')
and s.n13 is not null and s.c3 like 'SYS_%' and
s.c5 = d.table_owner and s.c1 = d.table_name and s.c2 =
d.partition_name and s.n13 = d.su
Plan hash value: 3696387946
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 948 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | DBMS_STATS_ID_MAP_TAB | | | | |
| 2 | HASH UNIQUE | | 1 | 436 | 948 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 436 | 947 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 436 | 947 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 424 | 945 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 396 | 943 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 361 | 942 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| ALL_TABLE_STATS | 1 | 346 | 941 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ALL_TABLE_STATS | 1 | | 941 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 1 | 15 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| |
|* 13 | INDEX RANGE SCAN | I_OBJ2 | 1 | 35 | 1 (0)| 00:00:01 |
|* 14 | VIEW PUSHED PREDICATE | TABSUBPARTV$ | 1 | 28 | 2 (0)| 00:00:01 |
| 15 | WINDOW BUFFER | | 32 | 480 | 2 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | TABSUBPART$ | 32 | 480 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_TABSUBPART_POBJSUBPART$ | 32 | | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 12 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("S"."N13" IS NOT NULL)
9 - access("S"."STATID" IS NULL AND "S"."C5"=:1 AND "S"."C1"=:2 AND "S"."C3" LIKE 'SYS_%')
filter(("S"."C2" IS NOT NULL AND "S"."C1"=:2 AND "S"."C5"=:1 AND "S"."C3" LIKE 'SYS_%' AND
INTERNAL_FUNCTION("S"."TYPE")))
12 - access("U"."NAME"=:1)
13 - access("U"."USER#"="OP"."OWNER#" AND "OP"."NAME"=:2 AND "S"."C2"="OP"."SUBNAME" AND "OP"."TYPE#"=19)
filter(("OP"."SUBNAME" IS NOT NULL AND "S"."C2"="OP"."SUBNAME" AND "OP"."TYPE#"=19))
14 - filter("S"."N13"="TSP"."SUBPART#")
16 - filter(BITAND("FLAGS",8388608)=0)
17 - access("POBJ#"="OP"."OBJ#")
18 - access("TSP"."OBJ#"="OS"."OBJ#" AND "OS"."TYPE#"=34)
filter("OS"."TYPE#"=34)
19 - filter("S"."C3"<>"OS"."SUBNAME")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
press Enter to return
|
DBMS_STATS_ID_MAP_TAB 테이블에 insert를 하는 쿼리임, ALL_TABLE_STATS 테이블은 인덱스 range 스캔함
하지만 id 9를 보면 statid is null로 먼저 인덱스를 엑세스하고 있음
그부분에서 예상 cost도 높음, 이부분에 대해선 다음 게시글에서 테스트함, 본문에선 세션을 여러개로 나눠서 병렬 처리효과를 보는 테스트만 작성함
다음글 참고 : 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
v$session_longops 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col opname for a30
select sid, serial#, opname, sofar, totalwork
from v$session_longops
where sofar != totalwork;
SID SERIAL# OPNAME SOFAR TOTALWORK
---------- ---------- ------------------------------ ---------- ----------
197 55845 Import Database Statistics 4189 11851
|
1시간 30분이 지났음에도 아직 진행중임
dbms_stats.import_database_stats에는 병렬(parallel) 옵션이 없음
그렇기 때문에 통계정보 import 시 위와 같은 한 세션에서 테이블별 개별 insert문을 실행하여 import 함
테이블이 10000개인데도 이렇게 느리다면 실제 운영 환경에서는 훨씬 더 느리게 동작할것임
이렇게 통계정보를 넣게되면 너무 느리기 때문에 아래와 같이 유저별로 나눠서 import를 하는게 좋음
테이블 단위 import 구문 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 50000
spool statimport_all.sql
select 'exec dbms_stats.import_table_stats(ownname=>'''||owner||''', tabname=>'''||table_name||''', cascade=>true, statown=>''SYSTEM'', stattab=>''ALL_TABLE_STATS'');' cmd
from dba_tables
where owner in (select username from dba_users where oracle_maintained = 'N');
spool off
CMD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exec dbms_stats.import_table_stats(ownname=>'IMSI001', tabname=>'IMSI001_TBL001', cascade=>true, statown=>'SYSTEM', stattab=>'ALL_TABLE_STATS');
exec dbms_stats.import_table_stats(ownname=>'IMSI001', tabname=>'IMSI001_TBL002', cascade=>true, statown=>'SYSTEM', stattab=>'ALL_TABLE_STATS');
..
exec dbms_stats.import_table_stats(ownname=>'IMSI100', tabname=>'IMSI100_TBL099', cascade=>true, statown=>'SYSTEM', stattab=>'ALL_TABLE_STATS');
exec dbms_stats.import_table_stats(ownname=>'IMSI100', tabname=>'IMSI100_TBL100', cascade=>true, statown=>'SYSTEM', stattab=>'ALL_TABLE_STATS');
10000 rows selected.
|
statimport_all.sql 파일에서 명령어를 제외한 구문 삭제
|
1
2
|
$ vi statimport_all.sql
sql 구문 및 cmd, ----, 10000 rows selected. 등 텍스트 제거
|
statimport_all.sql 파일을 원하는 세션 갯수만큼 split 명령으로 쪼개기
나의 경우 1000줄씩 나눠서 10개 파일을 만듦
|
1
2
3
4
5
|
$ mkdir -p statimport_job
$ mv statimport_all.sql statimport_job
$ cd statimport_job
$ split -l 1000 statimport_all.sql statimport_part_
$ for f in statimport_part_*; do mv "$f" "$f.sql" ; done
|
파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ ls -al
total 3972
drwxr-xr-x 2 oracle oinstall 4096 Aug 10 17:36 .
drwx------. 10 oracle oinstall 4096 Aug 10 17:34 ..
-rw-r--r-- 1 oracle oinstall 2010000 Aug 10 17:30 statimport_all.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_aa.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ab.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ac.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ad.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ae.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_af.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ag.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ah.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_ai.sql
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:35 statimport_part_aj.sql
|
정상적으로 파일이 10개로 나누어짐
해당 파일들을 sqlplus를 각각 열어서 실행할수도 있고
|
1
2
3
4
5
6
7
8
9
10
|
#세션1
$ sqlplus / as sysdba
SQL> @statimport_part_aa.sql
#세션2
$ sqlplus / as sysdba
SQL> @statimport_part_ab.sql
#세션n
...
|
nohup를 이용해 한번에 10개 세션을 열어서 수행할수도 있음
run_all.sh 쉘파일 생성
|
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
|
$ vi run_all.sh
#!/bin/bash
main_log="statimport_all.log"
# 로그 초기화
> "$main_log"
start_ts=$(date +%s)
echo "===== START: $(date -Is) =====" | tee -a "$main_log"
# 각 파일 실행 (백그라운드)
for f in statimport_part_*.sql; do
log_file="${f%.sql}.log"
echo "[START] $f at $(date -Is)" >> "$main_log"
sqlplus -s / as sysdba @"$f" > "$log_file" 2>&1 &
done
# 모든 백그라운드 종료 대기
wait
end_ts=$(date +%s)
elapsed=$((end_ts - start_ts))
echo "===== END : $(date -Is) =====" | tee -a "$main_log"
printf "TOTAL ELAPSED: %02d:%02d:%02d\n" \
$((elapsed/3600)) $(((elapsed%3600)/60)) $((elapsed%60)) | tee -a "$main_log"
|
권한 부여
|
1
|
$ chmod +x run_all.sh
|
실행
|
1
2
3
|
$ nohup ./run_all.sh &
$ jobs
[1]+ Running nohup ./run_all.sh &
|
nohup으로 수행됨
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
26
27
28
29
30
|
$ top
top - 18:11:56 up 1 min, 3 users, load average: 2.68, 0.85, 0.30
Tasks: 424 total, 12 running, 412 sleeping, 0 stopped, 0 zombie
%Cpu0 : 94.1 us, 0.7 sy, 0.0 ni, 1.0 id, 3.6 wa, 0.3 hi, 0.3 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu1 : 98.7 us, 0.0 sy, 0.0 ni, 1.0 id, 0.0 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu2 : 98.0 us, 0.3 sy, 0.0 ni, 1.0 id, 0.3 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu3 : 96.3 us, 0.3 sy, 0.0 ni, 0.3 id, 2.7 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu4 : 93.7 us, 0.3 sy, 0.0 ni, 1.0 id, 4.7 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu5 : 96.0 us, 0.0 sy, 0.0 ni, 1.7 id, 2.0 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu6 : 97.3 us, 0.0 sy, 0.0 ni, 1.7 id, 0.7 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu7 : 98.3 us, 0.7 sy, 0.0 ni, 0.7 id, 0.0 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu8 : 93.0 us, 0.7 sy, 0.0 ni, 4.0 id, 2.0 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
%Cpu9 : 94.3 us, 0.3 sy, 0.0 ni, 0.3 id, 4.7 wa, 0.3 hi, 0.0 si, 0.0 st <<<<<<<<<<<<<<<<<
MiB Mem : 7932.5 total, 1929.0 free, 1244.4 used, 4759.1 buff/cache
MiB Swap: 10240.0 total, 10240.0 free, 0.0 used. 4191.8 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2908 oracle 20 0 2943736 296356 289024 R 99.0 3.6 0:11.02 oracle_2908_ora
2907 oracle 20 0 2941684 288564 281196 R 98.3 3.6 0:11.02 oracle_2907_ora
2906 oracle 20 0 2947852 298464 291020 R 97.7 3.7 0:10.93 oracle_2906_ora
2899 oracle 20 0 2943796 320032 311416 R 96.3 3.9 0:10.81 oracle_2899_ora
2903 oracle 20 0 2956048 318568 311140 R 96.3 3.9 0:10.86 oracle_2903_ora
2900 oracle 20 0 2943756 313064 305264 R 96.0 3.9 0:10.64 oracle_2900_ora
2893 oracle 20 0 2943736 306880 299372 R 94.4 3.8 0:10.44 oracle_2893_ora
2896 oracle 20 0 2951944 317084 309688 R 94.4 3.9 0:10.98 oracle_2896_ora
2905 oracle 20 0 2943736 300496 293260 R 94.0 3.7 0:10.89 oracle_2905_ora
2909 oracle 20 0 2941692 300496 293040 R 93.7 3.7 0:10.75 oracle_2909_ora
1760 oracle 20 0 2935172 71876 69016 S 0.7 0.9 0:00.13 ora_lg00_ora19d
1711 oracle -2 0 2935420 64288 61220 S 0.3 0.8 0:00.63 ora_vktm_ora19d
1740 oracle 20 0 2965376 67748 64520 S 0.3 0.8 0:00.11 ora_vkrm_ora19d
|
cpu를 모두 사용함
oratop 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ /app/oracle/product/19c/suptools/oratop/oratop / as sysdba -rf
Oracle 19c - 18:11:54 Primary r/w ORA1 up: 1.4t, 13 sn, 1 ins, 0 er, 18G sz, 2.3G sga, 0%fra, archivelog 25.2%db
ID CPU %CPU LOAD AAS ASC ASI ASW ISW REDO TEMP IORT MBPS IOPS R/S W/S LIO GCPS %FRE PGA NETV UTPS UCPS SQRT %DBC %DBW
1 10 21.7 2 2.5 9 0 3 0 1M 10M 553u 10.6 439 411 27.5 707k 0 41.6 391M 1.9k 8.9 17.7 1.4m 15.6 9.6
EVENT (RT) SESSIONS WAIT TIME AVG %DBT WAIT_CLASS
read by other session 98k 22s 224u 25 User I/O
cursor: pin S wait on X 232 20s 87m 23.0 Concurrency
db file sequential read 23k 13s 580u 15 User I/O
external table read 1 9.8s 9.8s 11.1 User I/O
log file parallel write 1 1.3k 7.4s 5.9m 8.5 System I/O
ID SID SPID USERNAME PROGRAM SRV SERVICE OPN SQLID/BLOCKER E/T %CPU %LIO PGA STS STE WAIT_CLASS EVENT/OBJECT NAME W/T
1 105 2908 SYS sqlplus@o DED SYS$USE INS 7habw4pk381bv 175u 67 10.4 4M ACT RUN On CPU 175u
1 249 2896 SYS sqlplus@o DED SYS$USE INS 7habw4pk381bv 16u 65.1 9.7 4M ACT RUN On CPU 16u
1 441 2906 SYS sqlplus@o DED SYS$USE INS 7habw4pk381bv 181u 64.7 10 3.9M ACT RUN On CPU 181u
1 55 2907 SYS sqlplus@o DED SYS$USE UPS 3dbzmtf9ahvzt 1.0s 64.2 10.1 4M ACT WAI Concurrenc buffer busy waits 11m
1 392 2905 SYS sqlplus@o DED SYS$USE SEL 8rhyb2w8nrzjr 1.0s 62.9 10.2 4.2M ACT RUN On CPU 14u
1 201 2899 SYS sqlplus@o DED SYS$USE INS 3v214syvr26ac 1.0s 62.8 9.8 4.6M ACT RUN On CPU 168u
1 296 2903 SYS sqlplus@o DED SYS$USE INS 3v214syvr26ac 1.0s 62.2 10.0 4.4M ACT RUN On CPU 51u
1 151 2909 SYS sqlplus@o DED SYS$USE UPS 3dbzmtf9ahvzt 1.0s 62.1 10.2 4.1M ACT WAI Concurrenc buffer busy waits 12m
1 7 2893 SYS sqlplus@o DED SYS$USE INS 7habw4pk381bv 40u 61.4 9.7 4M ACT RUN On CPU 40u
1 343 2900 SYS sqlplus@o DED SYS$USE INS du1vfy7c7zvf2 1.0s 60.7 10.0 6M ACT I/O User I/O db file sequential 11m
|
oratop에도 동시에 많은 세션들이 실행중인게 보임
로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ tail -300f statimport_all.log
===== START: 2025-08-10T18:11:39+09:00 =====
[START] statimport_part_aa.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ab.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ac.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ad.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ae.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_af.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ag.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ah.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_ai.sql at 2025-08-10T18:11:39+09:00
[START] statimport_part_aj.sql at 2025-08-10T18:11:39+09:00
===== END : 2025-08-10T18:23:59+09:00 =====
TOTAL ELAPSED: 00:12:20
|
통계정보 import가 12분만에 끝남
실제로 다 수행되었는지 확인
|
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
|
$ ls -al
total 4424
drwxr-xr-x 2 oracle oinstall 4096 Aug 10 19:05 .
drwx------. 10 oracle oinstall 4096 Aug 10 19:05 ..
-rw------- 1 oracle oinstall 114 Aug 10 18:23 nohup.out
-rwxr-xr-x 1 oracle oinstall 657 Aug 10 18:03 run_all.sh
-rw-r--r-- 1 oracle oinstall 714 Aug 10 18:23 statimport_all.log
-rw-r--r-- 1 oracle oinstall 2010000 Aug 10 17:51 statimport_all.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_aa.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_aa.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ab.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ab.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ac.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ac.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ad.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ad.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ae.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ae.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_af.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_af.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ag.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ag.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ah.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ah.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_ai.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_ai.sql
-rw-r--r-- 1 oracle oinstall 43000 Aug 10 18:23 statimport_part_aj.log
-rw-r--r-- 1 oracle oinstall 201000 Aug 10 17:52 statimport_part_aj.sql
$ grep "PL/SQL procedure successfully completed." *.log | wc -l
10000
|
통계정보 import 시 "PL/SQL procedure successfully completed." 구문이 출력되는데 이 구문이 10000개로 정확함
결론 :
대용량 db에서 통계정보 전체를 이관할(import) 경우 일반적인 방법으로 하면 느릴수 있기때문에
테이블 단위나 스키마 단위로 쪼개서 동시에 여러세션에서 import 하면 훨씬 빠르게 작업을 마칠 수 있음
다음 게시글에선 본문처럼 세션을 쪼개지않고 statid만 추가해줌으로써 대량 통계이관을 빠르게 수행한 내용을 설명함
오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
참조 :
오라클 19c 유저 100개, 테이블 100개, 인덱스 100개, 데이터 1000건 생성 ( https://positivemh.tistory.com/1266 )
오라클 19c 통계정보 이관 방법 ( https://positivemh.tistory.com/1265 )
오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-0244C71F-40E4-4CC5-A1D3-08C96B8B9E16
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-D3FF95A6-ECAE-41B9-90CB-95A68A86AE2D
https://dohdatabase.com/2023/12/18/how-to-export-and-import-statistics-faster-using-dbms_stats-in-parallel/
https://dohdatabase.com/2022/05/21/if-importing-statistics-using-dbms_stats-is-slow/
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 19c 통계정보 export 관련 궁금증 테스트 (0) | 2025.08.11 |
|---|---|
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 (0) | 2025.08.10 |
| 오라클 19c 통계정보 이관 방법 (0) | 2025.08.10 |
| 오라클 11gR2 Raw Device RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |
| 오라클 11gR2 ASM RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |