프린트 하기

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems

 

방법 : 오라클 23ai 신기능 스테이징 테이블 기능(Staging)

오라클 23ai 버전부터 스테이징 테이블 기능을 이용할 수 있음(staging)
스테이징 테이블은 빠른 데이터 수집과 변동이 잦은 데이터를 처리하기 위해 최적화된 힙 구조 테이블임
이러한 용도에 맞춰서 테이블 속성들이 기본값으로 설정되고 추가적인 사용자 개입 없이도 동작함
스테이징 테이블을 생성하면 데이터 수집 속도를 높이고 변동성이 큰 데이터를 처리하기 위해
테이블 속성을 따로 조정할 필요가 없기 때문에 일반 테이블을 만들 때보다 시간과 노력을 절약할 수 있다고함
스테이징 테이블은 최상의 성능을 보장하고 불필요한 성능 디버깅 및 튜닝을 방지할 수 있도록 기본적으로 최적의 설정으로 구성된다고함
(https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html#SQLRF-GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6의 FOR STAGING 부분)

 

 

스테이징이란?
IT 및 데이터 관리 분야에서 일시적으로 데이터를 저장하고 처리하는 단계를 말함
특히 데이터 웨어하우스나 ETL(Extract, Transform, Load) 프로세스에서 자주 사용됨
스테이징 테이블은 이를 지원하기 위해 만들어진 일종의 임시 테이블임

 

 

주요 목적
1. 데이터 수집과 변환: 외부 소스에서 데이터를 수집한 후, 이를 최종 저장소에 로드하기 전에 변환하거나 가공하는 단계에서 사용됨
2. 빠른 데이터 처리: 스테이징 테이블은 데이터를 일시적으로 대량으로 빠르게 입력하기 위해 설계되었기 때문에, 성능 최적화가 필요 없음. 데이터는 일정 시간 후에 삭제되거나 다른 테이블로 옮겨짐
3. 데이터의 임시 저장: 복잡한 쿼리나 분석을 실행하기 전에 데이터를 일시적으로 저장해 필요한 작업을 수행하는 데 사용됨
스테이징 테이블은 일종의 작업 공간(임시 저장소) 역할을 하여, 데이터를 안전하고 효율적으로 처리할 수 있게 돕는 중요한 도구임

 

 

스테이징 테이블 생성 구문
문장 마지막에 for staging 만 붙여주면됨

1
2
3
4
5
6
7
8
9
10
#normal table
SQL> create table staging_table (col1 number, col2 varchar2(100)) for staging;
 
#partition table
SQL> create table part_staging_table (col1 number, col2 varchar2(100))
partition by range (col1) 
(partition p1 values less than (100), 
partition pmax values less than (maxvalue)
for staging;

 

 

스테이징 테이블 특징
- 압축이 명시적으로 꺼져 있고, 테이블 및 파티션, 서브파티션에 대한 향후 데이터 로드에 대해 압축이 허용되지 않음
- 기존 테이블을 스테이징 테이블로 변경해도 기존 데이터 저장에는 영향을 주지 않으며, 향후 데이터 로드에만 영향을 미침
- alter table 을 사용하여 스테이징 테이블, 그 파티션 또는 서브파티션, 그리고 향후 데이터 로드의 기본 속성을 변경할 수 없음
- alter table 을 사용해 데이터를 move 시키고 compress 하는 등의 파티션 유지보수 작업을 수행할 수 없음
- 스테이징 테이블을 파티션화할 수 없으며, 파티션에 압축을 지정할 수 없음
- 스테이징 테이블에 대한 쿼리에는 동적 샘플링이 사용됨 (스테이징 테이블이나 그 파티션에 대한 통계를 수집할 수 없음 force 옵션 지정시 가능하나 권장x)
- 스테이징 테이블을 삭제하면 recyclebin 설정과 상관없이 즉시 삭제됨
- user,all,dba_tables 에 staging 이라는 새로운 컬럼이 추가되어 스테이징 테이블 구분이 가능함(스테이징 테이블일 경우 YES, 아닐 경우 NO 로 표시됨)

 

 

본문에서는 스테이징 테이블을 생성한 뒤 여러가지 테스트를 진행해보고 일반 테이블과 어떤 차이점이 있는지 확인해봄

 

 

테스트
0. 사전작업
1. 스테이징 테이블과 일반 테이블 insert 시 redo, undo 생성량, 크기 비교
2. 테이블 압축 테스트
3. 테이블 통계정보 수집 테스트
4. ctas, append insert 시 자동 통계정보 수집 테스트
5. 테이블 삭제 테스트

 

 

테스트
0. 사전작업
샘플 테이블 생성 후 대량 데이터 삽입

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
SQL>
drop table sample_t purge;
create table sample_t 
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
);
 
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..100 LOOP
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := (d - 1) * 100000 + i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
   COMMIT;
END LOOP;
END;
/
 
PL/SQL procedure successfully completed.

 

 

샘플 테이블 크기 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a10
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'SAMPLE_T';
 
OWNER           SEGMENT_NA SEGMENT_TYPE               MB
--------------- ---------- ------------------ ----------
IMSI            SAMPLE_T   TABLE                     928

약 1gb 쯤 쌓임

 

 

스테이징 테이블과 일반 테이블 생성

1
2
3
4
5
SQL>
drop table staging_tbl purge;
drop table nostaging_tbl purge;
create table staging_tbl for staging as select * from sample_t where 1=2;
create table nostaging_tbl as select * from sample_t where 1=2;

sample_t 테이블을 이용해 컬럼구성을 동일하게 하고 데이터는 넣지 않음

 

 

dba_tables 에서 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> 
set lines 200 pages 1000
col table_name for a20
col staging for a10
select table_name, staging 
from dba_tables 
where table_name like '%STAGING%';
 
TABLE_NAME           STAGING
-------------------- ----------
STAGING_TBL          YES
NOSTAGING_TBL        NO

dba_tables 에서 staging 컬럼으로 스테이징 테이블을 구분할 수 있음

 

 

1. 스테이징 테이블과 일반 테이블 insert 시 redo, undo 생성량, 크기 비교
UNDO 및 버퍼 캐시를 지우기 위해 DB 재시작

1
2
3
4
5
SQL> shutdown immediate
Pluggable Database closed.
 
SQL> startup
Pluggable Database opened.

 

 

이후 세션 재접속 후 진행

 

 

일반 테이블에 insert 진행

1
2
3
4
5
6
7
SQL> 
set timing on autot on
insert into nostaging_tbl select * from sample_t;
commit;
set autot off
 
Elapsed: 00:00:06.37

일반 테이블의 경우 6.37초 소요됨

 

 

stat 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
Statistics
----------------------------------------------------------
       1414  recursive calls
     946145  db block gets
     228653  consistent gets
     117880  physical reads
  985322576  redo size
        204  bytes sent via SQL*Net to client
         83  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         99  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

 

 

리두 생성량 확인(redo_stat.sql)

1
2
3
4
5
6
7
8
9
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
 
   REDO_KB    REDO_MB
---------- ----------
 962237.91     939.69

일반 테이블의 경우 리두는 939mb 발생함

 

 

언두 생성량 확인(undo_stat.sql)

1
2
3
4
5
6
7
8
9
SQL>
select round(vm.value/1024, 2) "UNDO_KB", round(vm.value/1024/1024, 2) "UNDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'undo change vector size';
 
   UNDO_KB    UNDO_MB
---------- ----------
  33814.86      33.02

일반 테이블의 경우 언두는 33mb 발생함

 

 

UNDO 및 버퍼 캐시를 지우기 위해 DB 재시작

1
2
3
4
5
SQL> shutdown immediate
Pluggable Database closed.
 
SQL> startup
Pluggable Database opened.

 

 

이후 세션 재접속 후 진행

 

 

스테이징 테이블에 insert 진행

1
2
3
4
5
6
7
SQL> 
set timing on autot on
insert into staging_tbl select * from sample_t;
commit;
set autot off
 
Elapsed: 00:00:06.52

스테이징 테이블의 경우 6.52초 소요됨

 

 

stat 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
Statistics
----------------------------------------------------------
       1444  recursive calls
     946462  db block gets
     228509  consistent gets
     117882  physical reads
  985332516  redo size
        204  bytes sent via SQL*Net to client
         83  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         99  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

 

 

리두 생성량 확인(redo_stat.sql)

1
2
3
4
5
6
7
8
9
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
 
   REDO_KB    REDO_MB
---------- ----------
 962247.03     939.69

스테이징 테이블의 경우에도 리두가 939mb 발생함

 

 

언두 생성량 확인(undo_stat.sql)

1
2
3
4
5
6
7
8
9
SQL>
select round(vm.value/1024, 2) "UNDO_KB", round(vm.value/1024/1024, 2) "UNDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'undo change vector size';
 
   UNDO_KB    UNDO_MB
---------- ----------
  33815.86      33.02

스테이징 테이블의 경우에도 언두가 33mb 발생함

 

 

두 테이블 용량 비교

1
2
3
4
5
6
7
8
9
10
11
SQL> 
set lines 200 pages 1000
col segment_name for a20
select segment_name, bytes/1024/1024 mb, blocks, extents
from dba_segments
where segment_name like '%STAGING%';
 
SEGMENT_NAME                 MB     BLOCKS    EXTENTS
-------------------- ---------- ---------- ----------
NOSTAGING_TBL               928     118784        187
STAGING_TBL                 928     118784        187

용량 및 extent 할당량도 동일함

 

 

테스트1 결론 :
스테이징 테이블을 사용해도 일반 insert 와 비교했을때 redo 나 undo 사용량이 줄어들지 않음
insert 시간도 거의 동일함
용량 및 extent 할당량도 일반 테이블과 동일하게 할당됨

 

 

2. 테이블 압축 테스트
일반 테이블 압축

1
2
3
SQL> alter table nostaging_tbl compress;
 
Table altered.

정상적으로 압축됨

 

 

스테이징 테이블 압축

1
2
3
4
5
6
SQL> alter table staging_tbl compress;
alter table staging_tbl compress
*
ERROR at line 1:
ORA-38500: Invalid operation on Staging Table
Help: https://docs.oracle.com/error-help/db/ora-38500/

스테이징 테이블 압축 시도 시 에러가 발생함

 

 

테스트2 결론 :
스테이징 테이블은 압축이 되지 않음

 

 

3. 테이블 통계정보 수집 테스트
일반 테이블 통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'NOSTAGING_TBL');
 
PL/SQL procedure successfully completed.

정상적으로 통계정보 수집됨

 

 

스테이징 테이블 통계정보 수집

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL');
BEGIN dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL'); END;
 
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 42592
ORA-06512: at "SYS.DBMS_STATS", line 41877
ORA-06512: at "SYS.DBMS_STATS", line 9201
ORA-06512: at "SYS.DBMS_STATS", line 10265
ORA-06512: at "SYS.DBMS_STATS", line 41025
ORA-06512: at "SYS.DBMS_STATS", line 42025
ORA-06512: at "SYS.DBMS_STATS", line 42573
ORA-06512: at line 1

에러가 발생하며 통계정보 수집되지 않음

 

 

참고로 force 옵션 사용시 스테이징 테이블도 통계정보 수집이 가능하긴 함

1
2
3
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL', force => TRUE);
 
PL/SQL procedure successfully completed.

 

 

테스트3 결론 :
스테이징 테이블은 기본적으로 통계정보 수집이 되지 않음(force 옵션 사용시 가능)

 

 

4. ctas, append insert 시 자동 통계정보 수집 테스트
12c 이후 버전부터 _optimizer_gather_stats_on_load 파라미터에 의해 ctas나 itas append insert 시 자동으로 통계정보가 수집됨
이 파라미터가 true 로 설정된 상태에서 staging 테이블을 사용하는경우 어떻게 동작하는지 확인해봄
참고 : 오라클 19c 테이블 ctas 생성 및 append 삽입시, 인덱스 생성시 자동 통계정보 수집 ( https://positivemh.tistory.com/1167 )

 

 

_optimizer_gather_stats_on_load 파라미터 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL>
conn sys/oracle@localhost:1521/oracle23 as sysdba
set lines 200 pages 1000
col name for a50
col instance for a10
col desc for a80
select
a.ksppinm "name",
c.ksppstvl "instance",
a.ksppdesc "desc"
from x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
where 1=1
and a.indx=b.indx
and a.indx=c.indx
AND p.name(+) = a.ksppinm
AND SUBSTR(a.KSPPINM, 1, 1) = '_'
and a.ksppinm like '%_optimizer_gather_stats_on_load'
order by 1;
 
name                                               instance   desc
-------------------------------------------------- ---------- --------------------------------------------------------------------------------
_optimizer_gather_stats_on_load                    TRUE       enable/disable online statistics gathering

TRUE 임

 

 

staging_tbl2 테이블을 ctas 를 이용해 생성

1
2
3
SQL> create table staging_tbl2 for staging as select * from sample_t;
 
Table created.

 

 

staging_tbl2 테이블 통계정보 확인

1
2
3
4
5
6
7
8
9
10
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed, staging
from dba_tables 
where table_name = 'STAGING_TBL2';
 
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL STA
--------------- ---------- ---------- ------------ ----------- --------- ---
STAGING_TBL2      10000000     118063            0          80 22-OCT-24 YES

자동으로 통계가 수집됨

 

 

staging_tbl3 테이블을 생성 후 append insert 를 이용해 데이터 삽입

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> create table staging_tbl3
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
) for staging;
 
Table created.
 
SQL> insert /*+ append */ into staging_tbl3 select * from sample_t;
 
10000000 rows created.
 
SQL> commit;
 
Commit complete.

 

 

staging_tbl3 테이블 통계정보 확인

1
2
3
4
5
6
7
8
9
10
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed, staging
from dba_tables 
where table_name = 'STAGING_TBL3';
 
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL STA
--------------- ---------- ---------- ------------ ----------- --------- ---
STAGING_TBL3                                                             YES

itas append insert 시에는 자동으로 통계가 수집되지 않음

 

 

테스트4 결론 :
테스트3 결과와 같이 스테이징 테이블은 기본적으로 통계정보 수집이 되지 않아야함
하지만 테스트시 ctas 시에 _optimizer_gather_stats_on_load 파라미터에 의해 자동으로 통계정보가 수집됨
하지만 itas append insert 시에는 정상적으로 통계정보가 수집되지 않음, 이결과로 봤을때 ctas 시 통계가 수집되는것은 버그인것으로 판단됨

 

 

5. 테이블 삭제 테스트
테스트를 마친 일반 테이블, 스테이징 테이블 purge 옵션 없이 삭제

1
2
3
4
5
6
7
SQL> drop table NOSTAGING_TBL;
 
Table dropped.
 
SQL> drop table STAGING_TBL;
 
Table dropped.

 

 

recyclebin 확인

1
2
3
4
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
NOSTAGING_TBL    BIN$IuBKSpl6EfbgYxeJqMDiXQ==$0 TABLE        2024-09-25:00:37:59

일반 테이블만 recyclebin 에 들어가 있음
purge 옵션을 주지 않아도 스테이징 테이블은 recyclebin 에 들어가지 않음

 

 

recyclebin 비우기

1
2
3
SQL> purge recyclebin;
 
Recyclebin purged.

 

 

테스트5 결론 :
스테이징 테이블은 기본적으로 recyclebin 에 들어가지 않음
drop table 명령으로 삭제시 즉시 삭제됨

 

 

결론 :
메뉴얼 상에는 스테이징 테이블을 사용했을때 아주 빨라지는것처럼? 최젹화된 테이블인것 처럼 작성되어 있지만
실제 테스트해본 결과는 일반 테이블에 비해 성능상 큰 차이점을 찾지 못함
스테이징 테이블을 사용해도 일반 insert 와 비교했을때 redo 나 undo 사용량이 줄어들지 않음
insert 시간도 거의 동일함
용량 및 extent 할당량도 일반 테이블과 동일하게 할당됨
스테이징 테이블은 압축이 되지 않음
스테이징 테이블은 기본적으로 통계정보 수집이 되지 않음(force 옵션 사용시 가능), 스테이징 테이블 쿼리 수행시 동적 샘플링으로 통계수집됨
테스트시 ctas 시에 _optimizer_gather_stats_on_load 파라미터에 의해 자동으로 통계정보가 수집됨
하지만 itas append insert 시에는 정상적으로 통계정보가 수집되지 않음, 이 결과로 봤을때 ctas 시 통계가 수집되는것은 버그인것으로 판단됨
스테이징 테이블은 기본적으로 recyclebin 에 들어가지 않음, drop table 명령으로 삭제시 즉시 삭제됨
스테이징 테이블이라는 기능이 생겼지만 스크립트에서 for staging 이라는 구문 하나만 추가하면 된다는 장점 외에는 아직까지 눈에띄게 좋은 장점은 보이지 않는것 같음

 

 

참조 : 

https://positivemh.tistory.com/1163

https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1755
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html#SQLRF-GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/data_warehousing_big_data.html#GUID-75364-1
https://forums.oracle.com/ords/apexds/post/oracle-23ai-staging-table-statistics-bug-5542
https://oracle-base.com/articles/23/statging-tables-23#google_vignette
https://danischnider.wordpress.com/2024/07/19/staging-tables-in-oracle-23ai/
https://tuna.tistory.com/192
https://www.baremon.eu/the-role-of-staging-tables-in-database-administration-part-3/
https://positivemh.tistory.com/808