| 예제 | |
|---|---|
| {code:sql | borderStyle=solid} BIG_TABLE@THREE >create table user_info as select * from all_users; |
Table created.
BIG_TABLE@THREE >alter table user_info parallel;
Table altered.
BIG_TABLE@THREE >exec dbms_stats.gather_table_stats ( user, 'USER_INFO');
PL/SQL procedure successfully completed.
BIG_TABLE@THREE >explain plan for
2 create table new_table parallel
3 as
4 select a.*, b.user_id, b.created user_created
5 from big_table a, user_info b
6 where a.owner = b.username
7 /
Explained.
BIG_TABLE@THREE >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | TQ | IN-OUT | PQ Distrib |
| 0 | CREATE TABLE STATEMENT | 17M | 1847M | 30667 (1) | 00:06:09 | ||||
| 1 | PX COORDINATOR | ||||||||
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 17M | 1847M | 18162 (1) | 00:03:38 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | NEW_TABLE | Q1,01 | PCWP | |||||
| HASH JOIN | 17M | 1847M | 18162 (1) | 00:03:38 | Q1,01 | PCWP | ||
| 5 | PX RECEIVE | 20 | 380 | 2 (0) | 00:00:01 | Q1,01 | PCWP | ||
| 6 | PX SEND BROADCAST | :TQ10000 | 20 | 380 | 2 (0) | 00:00:01 | Q1,00 | P->P | BROADCAST |
| 7 | PX BLOCK ITERATOR | 20 | 380 | 2 (0) | 00:00:01 | Q1,00 | PCWC | ||
| 8 | TABLE ACCESS FULL | USER_INFO | 20 | 380 | 2 (0) | 00:00:01 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | 17M | 1536M | 18136 (1) | 00:03:38 | Q1,01 | PCWC | ||
| 10 | TABLE ACCESS FULL | BIG_TABLE | 17M | 1536M | 18136 (1) | 00:03:38 | Q1,01 | PCWP |
Predicate Information (identified by operation id):
4 - access("A"."OWNER"="B"."USERNAME")
22 rows selected.
|
h1. 병렬 DDL과 익스텐트 트리밍
* 병렬DDL의 각 병렬 실행 서버는 개별의 익스텐트을 할당받고, 자신의 익스텐트에 데이터를 입력한다. 병렬 실행 서버는 다른 병렬 실행 서버가 할당한 익스텐트를 공동으로 사용하지 않는다.
|| 병렬 DDL의 익스텐트 할당 ||
| !3.JPG! |
* 단점 : 데이터 웨어하우스 환경에서는 대량 로드 후 공간 낭비를 일으킬 수 있다.
* 이러한 단점을 보완하기 위해 오라클은 각 병렬 실행 서버의 마지막 익스텐트를 대상으로 가능한 한 가장 작은 크기로 다시 트림을 시도하는 익스텐트 트리밍을 발생한다.
* 테이블스페이스 종류 별 익스텐트 트리밍
** dictionary-managed 테이블스페이스 : 빈공간이 있는 익스텐트가 할지라도 불연속적인 상태이기 때문에 사용되지 않는 않는다.
** locally-managed 테이블스페이스
*** UNIFORM SIZE : 항상 같은 크기만큼 익스텐트를 늘려주는 것으로, 익스텐트 트리밍을 수행하지 않는다.
*** AUTOALLOCATE : 내부 알고리즘을 사용해서 얼마큼 큰 익스텐트를 사용할 것인지 결정한다. 인공 지능 방식은 아니지만 익스텐트 트리밍을 수행한다.
|| 테스트용 external 테이블 생성 ||
| {code:sql|borderStyle=solid}
-- big_table.ctl
LOAD DATA
INFILE '/tmp/big_table.dat'
INTO TABLE big_table
REPLACE
FIELDS TERMINATED BY '|'
(
id, owner, object_name, subobject_name, object_id, data_object_id, object_type,
created, last_ddl_time, timestamp, status, temporary, generated, secondary
)
-- 변환
sqlldr big_table/test big_table.ctl external_table=generate_only
-- big_table.log 의 external 테이블 생성문
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_BIG_TABLE"
(
"ID" NUMBER,
"OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16KSC5601
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00001':'big_table.bad'
LOGFILE 'big_table.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY "|",
"OWNER" CHAR(255)
TERMINATED BY "|",
"OBJECT_NAME" CHAR(255)
TERMINATED BY "|",
"SUBOBJECT_NAME" CHAR(255)
TERMINATED BY "|",
"OBJECT_ID" CHAR(255)
TERMINATED BY "|",
"DATA_OBJECT_ID" CHAR(255)
TERMINATED BY "|",
"OBJECT_TYPE" CHAR(255)
TERMINATED BY "|",
"CREATED" CHAR(255)
TERMINATED BY "|",
"LAST_DDL_TIME" CHAR(255)
TERMINATED BY "|",
"TIMESTAMP" CHAR(255)
TERMINATED BY "|",
"STATUS" CHAR(255)
TERMINATED BY "|",
"TEMPORARY" CHAR(255)
TERMINATED BY "|",
"GENERATED" CHAR(255)
TERMINATED BY "|",
"SECONDARY" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'big_table.dat'
)
)REJECT LIMIT UNLIMITED
-- 디렉토리명, 테이블명 변경한 테이블 생성문
BIG_TABLE@THREE >create or replace directory my_dir as '/tmp/'
2 /
Directory created.
CREATE TABLE "BIG_TABLE_ET"
(
"ID" NUMBER,
"OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" VARCHAR2(30), <- 데이터 형 변경 ( date -> varchar2)
"LAST_DDL_TIME" VARCHAR2(30), <- 데이터 형 변경 ( date -> varchar2)
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16KSC5601
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
)
location
(
'big_table.dat'
)
)REJECT LIMIT UNLIMITED
/
Table created.
-- 테이블에 병렬 설정
BIG_TABLE@THREE >alter table BIG_TABLE_ET parallel;
Table altered.
|
| locally-managed 테이블스페이스의 uniform 익스텐트 트리밍 vs. autoallocate 익스텐트 트리밍 비교 테스트 | |
|---|---|
| {code:sql | borderStyle=solid} -- 각 테이블스페이스 생성 SYS@THREE >create tablespace lmt_uniform 2 datafile '/data01/oradata/THREE/lmt_uniform.dbf' size 1048640K reuse 3 autoextend on next 100m 4 extent management local 5 uniform size 100m; |
Tablespace created.
SYS@THREE >create tablespace lmt_auto
2 datafile '/data01/oradata/THREE/lmt_auto.dbf' size 1048640K reuse
3 autoextend on next 100m
4 extent management local
5 autoallocate;
Tablespace created.
– load할 flat파일
oracle@primarydb big_table$ ls -al /tmp/big_table.datrw-rr- 1 oracle dba 2109876644 Dec 7 15:08 /tmp/big_table.dat
– uniform, autoallocate 두 테이블스페이스에 병렬 direct path 로드로 테이블 생성
BIG_TABLE@THREE >create table uniform_test
2 parallel
3 tablespace lmt_uniform
4 as
5 select * from big_table_et;
Table created.
– external 테이블 로그 파일을 통하 병렬 모니터링
oracle@primarydb tmp$ ls -ltrrw-rr- 1 oracle dba 1801 Dec 7 17:31 BIG_TABLE_ET_28935.logrw-rr- 1 oracle dba 1801 Dec 7 17:31 BIG_TABLE_ET_28884.log
– v$px_session를 통한 병렬 처리 모니터링
SYS@THREE >select sid, serial#, qcsid, qcserial#, degree
2 from v$px_session;
SID SERIAL# QCSID QCSERIAL# DEGREE
SYS@THREE >show parameter parallel_threads_per_cpu
NAME TYPE VALUE
--사용 공간 비교
BIG_TABLE@THREE >select segment_name, blocks, extents
2 from user_segments
3 where segment_name in ( 'UNIFORM_TEST', 'AUTOALLOCATE_TEST');
SEGMENT_NAME BLOCKS EXTENTS
--UNIFORM_TEST : 각 익스텐트의 크기는 100MB
BIG_TABLE@THREE >select segment_name, extent_id, blocks
2 from user_extents where segment_name = 'UNIFORM_TEST';
SEGMENT_NAME EXTENT_ID BLOCKS
23 rows selected.
--AUTOALLOCATE_TEST : 할당한 extents의 수가 219, 집계결과
BIG_TABLE@THREE >select segment_name, blocks, count(*)
2 from user_extents
3 where segment_name = 'AUTOALLOCATE_TEST'
4 group by segment_name, blocks
5 order by blocks;
SEGMENT_NAME BLOCKS COUNT(*)
|
{note:title=*요약*}
1. 병렬 환경에서는 AUTOALLOCATE 방식으로 익스텐트 관리하는 것이 좋다.
2. UNIFORM 방식은 익스텐트를 더 작게 할당할 수 있지만, 시간이 지남에 따라 공간의 낭비가 발생하게 되고 이 공간이 테이블의 전체 스캔에 포함된다.
{note}