오라클 일반 테이블과 blob 테이블 생성, 데이터 삭제 후 datapump 테스트

2020. 8. 1. 00:34 내맘대로긍정

프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 5.8 (64bit)

DB 환경 : Oracle Database 10.2.0.5, 19.6.0.0

방법 : 오라클 일반 테이블과 blob 테이블 생성, 데이터 삭제 후 datapump 테스트

테스트 내용

1. 일반 테이블 테스트

일반 테이블의 segment를 약 2GB로 만든 뒤 아래 항목을 비교함

1_1. datapump로 expdp estimate_only=yes 의 예상용량

1_2. datapump로 expdp 한 뒤 만들어진 실제용량

일반 테이블을 delete 구문으로 50%를 삭제한 뒤 아래 항목을 비교함

1_3. datapump로 expdp estimate_only=yes 의 예상용량

1_4. datapump로 expdp 한 뒤 만들어진 실제용량

2. blob 테이블 테스트

blob 테이블의 segment를 약 2GB로 만든 뒤 아래 항목을 비교함

2_1. datapump로 expdp estimate_only=yes 의 예상용량

2_2. datapump로 expdp 한 뒤 만들어진 실제용량

blob 테이블을 delete 구문으로 50%를 삭제한 뒤 아래 항목을 비교함

2_3. datapump로 expdp estimate_only=yes 의 예상용량

2_4. datapump로 expdp 한 뒤 만들어진 실제용량

환경구성

datapump용 디렉토리 생성

1
2
3
4
5
6
7
SQL> create directory hwmdir as '/home/oracle/';
 
Directory created.
 
SQL> grant read, write on directory hwmdir to public;
 
Grant succeeded.

테이블 스페이스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> create tablespace hwmtest_ts datafile '/oracle/app/oracle/oradata/ORCL10/hwmtest_ts01.dbf' size 3g autoextend off;
 
Tablespace created.
 
SQL> select name from v$tablespace;
 
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
HWMTEST_TS
 
6 rows selected.

유저 생성

1
2
3
4
5
6
7
SQL> create user hwmuser identified by hwmuser account unlock default tablespace hwmtest_ts quota unlimited on hwmtest_ts;
 
User created.
 
SQL> grant resource, connect to hwmuser;
 
Grant succeeded.

1. 일반 테이블 테스트

테이블(nologging) 생성 및 데이터 삽입

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
SQL> conn hwmuser/hwmuser
Connected.
 
SQL> create table hwm_table (c1 varchar2(10), c2 varchar2(10), c3 varchar2(10));
 
Table created.
 
SQL> insert into hwm_table values ('AAAAAAAAAA','BBBBBBBBBB','CCCCCCCCCC');
 
1 row created.
 
SQL> insert into hwm_table select * from hwm_table;
 
1 row created.
 
SQL> /
SQL> /
. 
16777216 rows created 까지 반복
.
SQL> /
 
16777216 rows created.
 
rownum 기준으로 16000000 row insert 
SQL> insert into hwm_table select * from hwm_table where rownum <=16000000;
 
16000000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from hwm_table;
 
  COUNT(*)
----------
  49554432

세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE');
 
PL/SQL procedure successfully completed.

용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE         262144      2048

테이블이 2GB로 만들어짐

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table;
 
      used
----------
    259448

1_1. datapump로 expdp estimate_only=yes 의 예상용량 확인

datapump로 expdp 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table1_1.log tables=hwm_table estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 10:55:05
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table1_1.log tables=hwm_table estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE"                           2 GB
Total estimation using BLOCKS method: 2 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 10:55:06

예상용량(estimated)을 2GB로 측정함

1_2. datapump로 expdp 한 뒤 만들어진 실제용량 확인

datapump로 expdp 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table1_2.dat logfile=hwm_table1_2.log tables=hwm_table
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 10:57:13
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table1_2.dat logfile=hwm_table1_2.log tables=hwm_table 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE"                       1.707 GB 49554432 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table1_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 10:57:27

예상용량(estimated)은 2GB 이지만

실제 export 된 용량은 1.7GB임

다음 테스트를 위해 hwm_table의 데이터 50% delete

1
2
3
SQL> delete hwm_table where rownum <= 24777216;
 
24777216 rows deleted.

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' 가 발생하는경우

undo tbs가 작아서 한번에 삭제가 안된다면 아래 방법 사용(4번에 나눠서 작업)

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
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.

테이블 count 확인

1
2
3
4
5
SQL> select count(*) from hwm_table;
 
  COUNT(*)
----------
  24777216

49554432 에서 24777216(50%)만 남음

세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE');
 
PL/SQL procedure successfully completed.

용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE         262144      2048

dba_segments의 blocks와 용량은 변함없음

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table;
 
      used
----------
    129725

delete 전 후 실제사용 BLOCK 비교

delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만(262144)

실제 사용 BLOCK은 259448에서 129725로 줄어든것을 확인할 수 있음

이는 HWM 까지 사용하고 있다고 표시하고 있기 때문

이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋음

(참조 https://positivemh.tistory.com/350)

1_3. datapump로 expdp estimate_only=yes 의 예상용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table1_3.log tables=hwm_table estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 11:49:24
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table1_3.log tables=hwm_table estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE"                           2 GB
Total estimation using BLOCKS method: 2 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 11:49:28

50%을 지웠지만 예상용량(estimated)을 2GB로 측정함

1_4. datapump로 expdp 한 뒤 만들어진 실제용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table1_4.dat logfile=hwm_table1_4.log tables=hwm_table
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 11:53:30
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table1_4.dat logfile=hwm_table1_4.log tables=hwm_table 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE"                       874.2 MB 24777216 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table1_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 11:53:42

예상용량(estimated)은 2GB 이지만

실제 export 된 용량은 874MB임

결론

datapump 시 estimate_only로 나온 예상용량은 dba_segments 의 bytes 용량과 동일하게 나오지만

실제로 datapump로 받아진 용량은 bytes 용량보다 같거나 더 작을수 있음

blob 테이블 테스트

일반 테이블을 담은 테이블 스페이스를 재사용할 예정이기 때문에

먼저 일반 테이블 삭제

1
2
3
SQL> drop table hwm_table purge;
 
Table dropped.

blob 테이블 생성 및 데이터 삽입

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
SQL> conn hwmuser/hwmuser
Connected.
 
SQL> create table hwm_table_blob (c1 blob, c2 blob, c3 blob);
 
Table created.
 
SQL> insert into hwm_table_blob
values (utl_raw.cast_to_raw('hello1'),utl_raw.cast_to_raw('hello2'),utl_raw.cast_to_raw('hello3'));
 
1 row created.
 
SQL> insert into hwm_table_blob select * from hwm_table_blob;
 
1 row created.
 
SQL> /
SQL> /
. 
4194304 rows created 나올때 까지 반복
.
SQL> /
 
4194304 rows created.
 
rownum 기준으로 6700000 row insert 
SQL> insert into hwm_table_blob select * from hwm_table_blob where rownum <= 6700000;
 
6700000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from hwm_table_blob;
 
  COUNT(*)
----------
  15088608

세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE_BLOB');
 
PL/SQL procedure successfully completed.

용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE_BLOB         286720      2240

테이블이 2.1GB로 만들어짐

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table_blob;
 
      used
----------
    279419

2_1. datapump로 expdp estimate_only=yes 의 예상용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table2_1.log tables=hwm_table_blob estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 15:42:23
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table2_1.log tables=hwm_table_blob estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE_BLOB"                  2.187 GB
Total estimation using BLOCKS method: 2.187 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 15:42:24

예상용량(estimated)을 2.1GB로 측정함

2_2. datapump로 expdp 한 뒤 만들어진 실제용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 15:44:01
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/'********' directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.187 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  474.8 MB 15088608 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 15:44:29

예상용량(estimated)은 2.1GB 이지만

실제 export 된 용량은 470MB임

아직 delete를 하지도 않았는데 blob 타입이어서 그런지 datapump 실행시 2.1GB에서 470MB로 줄었음

다음 테스트를 위해 hwm_table_blob의 데이터 50% delete

1
2
3
4
5
6
7
SQL> delete hwm_table_blob where rownum <= 7544304;
 
7544304 rows deleted.
 
SQL> commit;
 
Commit complete.

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' 가 발생하는경우

undo tbs가 작아서 한번에 삭제가 안된다면 아래 방법 사용(4번에 나눠서 작업)

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
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.

테이블 count 확인

1
2
3
4
5
SQL> select count(*) from hwm_table_blob;
 
  COUNT(*)
----------
   7544304

15088608에서 7544304(50%)만 남음

세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE_BLOB');
 
PL/SQL procedure successfully completed.

용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE_BLOB         286720      2240

dba_segments의 blocks와 용량은 변함없음

실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table_blob;
 
      used
----------
    139710

delete 전 후 실제사용 BLOCK 비교

delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만(286720)

실제 사용 BLOCK은 279419에서 139710로 줄어든것을 확인할 수 있음

이는 HWM 까지 사용하고 있다고 표시하고 있기 때문

이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋음

(참조 https://positivemh.tistory.com/350)

2_3. datapump로 expdp estimate_only=yes 의 예상용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table2_3.log tables=hwm_table_blob estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 06 August, 2020 17:28:54
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table2_3.log tables=hwm_table_blob estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE_BLOB"                  2.187 GB
Total estimation using BLOCKS method: 2.187 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 17:28:57

50%을 지웠지만 예상용량(estimated)을 2.1GB로 측정함

2_4. datapump로 expdp 한 뒤 만들어진 실제용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob
 
Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 06 August, 2020 17:29:13
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.187 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  237.4 MB 7544304 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 17:29:29

예상용량(estimated)은 2.1GB 이지만

실제 export 된 용량은 237MB임

delete 전 일반 테이블과 blob 테이블 dba_segments blobks, 용량 비교(1-1, 1-2, 2-1, 2-2 부분)

1
2
3
4
5
6
7
8
9
10
SQL>
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE','HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE              262144      2048
HWM_TABLE_BLOB         286720      2240

delete 전 일반 테이블과 blob 테이블 데이터 건수 비교(1-1, 1-2, 2-1, 2-2 부분)

1
2
. . exported "HWMUSER"."HWM_TABLE"                       874.2 MB 24777216 rows
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  474.8 MB 15088608 rows

dba_segments 기준 같은 2GB를 export 했을 때

rows를 보면 일반테이블은 2천4백만건, blob 테이블은 1천5백만건 정도임

rows 가 차이 나지만 실제 사용 blocks 수는 별반차이가 나지않음 그에따라 용량도 비슷함

blob 데이터 건수가 일반테이블 건수보다 작아서 실제 export 시 받아지는 용량도 작음

데이터 건수를 동일하게 맞추면 비슷한 용량으로 받아짐

일반 테이블과 blob 테이블의 용량 차이가 나는 이유는

blob 테이블에 데이터 삽입시 사용한 utl_raw.cast_to_raw() 함수 때문임

12자리값으로 변환해서 insert 한것

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
col before for a20
col after for a20
select 'hello1' as "before", utl_raw.cast_to_raw('hello1') as "after" from dual;
 
before             after
-------------------- --------------------
hello1             68656C6C6F31
 
select utl_raw.cast_to_varchar2('68656C6C6F31') as "before" from dual;
 
before
--------------------
hello1

문자열 수 때문에 차이가 나는것 같아서(일반테이블 varchar2(10), blob 데이터 12자리)

이후 일반테이블 insert 시 varchar2(12)로 만든 뒤 테스트를 해보았지만

똑같이 blob 테이블(utl_raw.cast_to_raw로 insert)이 용량을 더 많이 차지하는 것을 확인하였음

결론

datapump 시 estimate_only로 나온 예상용량은 dba_segments 의 bytes 용량과 동일하게 나오지만

실제로 datapump로 받아진 용량은 bytes 용량보다 같거나 더 작을수 있음

blob 데이터(정확히는 utl_raw.cast_to_raw로 삽입된 데이터)가 일반 데이터보다 용량을 더 많이 차지함

(blob 데이터에 블록을 더 할당하는듯함)

19c 에서도 똑같이 blob 형식의 데이터를 delete 했을 때 실제 사용 BLOCK과 expdp 시 용량이 줄어듬

참고 - 19c에서 똑같은 방식으로 테스트 한 내용

실제 사용 BLOCK 수 비교

19c 에서도 delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만

실제 사용 BLOCK은 270601에서 148919로 줄어든것을 확인할 수 있음

datapump 비교

19c delete 전 expdp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob
 
Export: Release 19.0.0.0.0 - Production on Wed Aug 5 17:29:42 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/'********' directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  528.0 MB 16777216 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 17:32:57 2020 elapsed 0 00:03:07

528MB export 됨

19c delete 후 expdp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob
 
Export: Release 19.0.0.0.0 - Production on Wed Aug 5 17:50:04 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  290.5 MB 9232912 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 17:51:56 2020 elapsed 0 00:01:50

290MB export 됨

delete 전에는 528MB 였지만 delete 후 약 50%정도 줄어듬

참고용

데이터 삽입시간이 너무 오래걸려서 취소한 내용(blob 테이블에 이미지 넣는 구문)

참조 : https://seul96.tistory.com/166

https://hrjeong.tistory.com/230

https://positivemh.tistory.com/183

https://docs.oracle.com/database/121/SUTIL/GUID-3DF4A0F1-385B-4749-99D3-A9C3F9706C0B.htm#SUTIL846

https://positivemh.tistory.com/377

https://positivemh.tistory.com/350

http://dbcafe.co.kr/wiki/index.php/%EC%98%A4%EB%9D%BC%ED%81%B4_LOB_%EC%9D%B4%EA%B4%80

https://positivemh.tistory.com/356

https://docs.oracle.com/database/121/WSMQG/app_urpkg.htm#WSMQG293

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 11g R2 tfa(ahf) SQL 성능 진단 자동 수집 가이드(SRDC) (0)	2020.08.12
오라클 19c RDA(Remote Diagnostic Agent) 사용 가이드 (0)	2020.08.09
오라클 11g 새로운 Job (0)	2020.07.27
오라클 11g R2 diag collection 로그수집 가이드 (0)	2020.07.24
Windows Server 2016 서버 정상종료 시 oracle shutdown 프로세스 (0)	2020.07.15

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link