요즘에 말 못할 여러가지 일들이 있어서 뛰엄 뛰엄 글을 올리고 있습니다.
이번에는 Oracle DataPump를 사용하다 발생하는 수많은 Error중 하나를 소개 하려 합니다.
[증상]
Oracle Datapump로 Export/Import를 실행 시 아래와 같은 Error 메세지 출력
$ expdp <user_name>/<user_pw>parfile=dp.par
Export: Release 10.2.0.1.0 - 64bit Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user <User_Name>
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1193
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.
[원인]
Oracle DB Init Parameter중 streams_pool_size 값이 '0'로 설정 되어 있어서 발생
SQL> show parameter stream
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
[처리방법]
1)pfile인 경우
$ORACLE_HOME/dbs/init<SID>.ora 파일에 streams_pool_size 값을 추가 하거나 변경 후 Oracle DB Restart
2)spfile인 경우
SQL>alter system set streams_pool_size = XXM scope=spfile;
위와 같이 실행 후 Oracle DB Restart Restart
[참고문헌]
DataPump Export (Expdp) Fails With Errors ORA-31626 ORA-31637 ORA-39080 ORA-4031 ("Streams Pool"..."Fixed Allocation") [ID 846537.1]
Master Note for Data Pump [ID 1264715.1]
이번에는 Oracle DB에서 Parallel Option을 이용하여 대용량 Data를 처리 하는 방법에 관하여 이야기 하려 합니다.
1.Oracle Parallel Operation이란?
Oracle Parallel Operation이란 하나의 큰 작압이 2개 이상의 작업으로 나누어서 동시에 작업하는 것을 의미 한다.
Oracle Parallel기능을 사용하려면 Oracle Paramter값 중 parallel_max_servers 값이 0 이상으로 설정 되어 있어야 한다.
만약 parallel_max_servers값이 0 이면 Oracle Parallel Operation을 사용 하지 않는 다는 의미 한다.
2.Oracle Parallel Operation 설정
Parallel query가 수행되도록 하려면 다음과 같이 3가지 방법으로 정의할 수 있다.
1)Instance level, init<SID>.ora file 내에 정의
parallel_max_servers
- instance 당 max slave (0는 parallel query option을 사용하지 않음을 의미)
parallel_min_servers
- instance가 startup 당시 최소한 뜨게 되는 slave의 수.
만약 Parallel_min_servers를 설정하지 않으면, default는 0이며, 이 파라미터는 parallel query server가 자주 start되고 shutdown되는 경우 일정한 갯수의 query server는 항상 떠있게 한다.
2) Table level, create/alter table 문장에서 정의
table은 n degree의 parallelism을 가지도록 create 혹은 alter될 수 있다. 이것은 init<SID>.ora files 내의 PARALLEL_DEFAULT_MAX_SCANS나 PARALLEL_DEFAULT_SCANSIZE가 정의되어 있더라도 이러한 값에 우선한다.
PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있다. 예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을 실행하도록 할 수 있다.
이 때 parallel이란 글자와 괄호( '(' )사이에 blank를 넣지 않도록 한다.
select /*+ PARALLEL(emp, 4) */ * from emp;
다음과 같이 NOPARALLEL hint를 사용하면, parallel query option을 사용하지 않도록 할 수 있다.
select /*+ NOPARALLEL(emp) */ * from emp;
3.Oracle Parallel Operation 우선순위 및 주의사항
Oracle Parallel Operation 우선순위는 Query level의 PARALLEL hint > Table level의 정의 > Instance level 순으로 정의 되며,
주의사항은 Instance level이나 Table level에 parallel query option을 거는 경우, Table의 정보가 바르게 analyze되지 않았거나,
그 외의 이유로 인하여 예상치 못한 문제를 접하는 경우가 많으므로 가능한 instance나 Table level로는 parallel query option을 setting하지 말고
필요한 Query마다 hint를 이용하여 parallel을 지정하는 것이 바람직하다.
4.Oracle Parallel Operation을 이용한 PCTAS와 PDML
대용량 Data를 처리 하다 보면 작업시간을 줄이기 위해서 Oracle Parallel을 사용 해야 할 경우 아래와 같이 작업을 진행 하면 된다.
Parallel CTAS(Create Table AS Select)
create table <New_Table_Name> tablespace <Tablespace Name> parallel <DEGREE> as select * from <Old Table Name>;
주의)PCTAS로 생성된 Table은 모든 작업이 끝난 후에 NOPARALLEL로 변경 해야 한다.
참고적으로 Oracle SQL*Loader도 아래와 같이 Parallel Operation를 사용 할 수 있다.
sqlload scott/tiger control=pipe direct=true parallel=true
5.Oracle Parallel Operation의 DEGREE란?
Parallel query에서 degree란 하나의 operation 수행에 대한 server process의 갯수이다.
이러한 degree 결정에 영향을 주는 요인들에는 다음과 같은 것들이 있다.
system의 CPU 갯수
system의 maximum process 갯수
table이 striping되어 있는 경우, 그 table이 걸쳐있는 disk의 갯수
data의 위치 (즉, memory에 cache되어 있는지, disk에 있는지)
query의 형태 (예를 들어 sorts 혹은 full table scan)
한 사용자만이 parallel query를 사용하는 경우, sorting이 많이 필요한 작업과 같은 CPU-bound 작업의 경우는 CPU 갯수의 1 ~ 2배의 degree가 적당하며, sorting보다는 table scan과 같은 I/O bound 작업의 경우는 disk drive 갯수의 1 ~ 2배가 적당하다.
동시에 수행되는 parallel query가 많은 경우에는 위의 각 사용자의 degree를 줄이거나 동시에 사용하는 사용자 수를 줄여야 한다.
위 이야기는 일반적인 이야기 이고 운영중인 DB에서는 DEGREE값은 통상적으로 parallel_max_servers값의 1/2 또는 1/3정도로 사용 합니다.
6.Oracle Parallel Operation 모니터링
아래 SQL문을 이용하면 Oracle Parallel Operation 모니터링 할 수 있다.
select decode(px.qcinst_id, NULL, username, ' - '||lower(substr(s.program, length(s.program)-4, 4) ) ) "Username",
decode(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL , to_char(s.sid) , px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from v$px_session px,
v$session s
where px.sid=s.sid (+)
and px.serial#=s.serial#
order by 5 , 1 desc;
7.참고문헌
Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML) [ID 50592.1]
Why didn't my parallel query use the expected number of slaves? [ID 199272.1]
Script to map parallel query coordinators to slaves [ID 202219.1]
