AWR을 보다 보니 낯선 enqueue 2개가 자주 보여 요거 좀 정리합니다.

RO-Multiple Object Reuse (fast object reuse)

RO enqueue는 "multi object reuse" enqueue로 알려져 있습니다. 이 Enqueue는 foreground process와 background process 간의 sync 하는데 사용되는 enqueue 입니다. Background는 주로 DBWR나 CKPT를 말합니다.

특별히 object drop이나 table truncate 할 때 많이 사용됩니다.

oracle database내에서 truncate나 drop이 발생하면 다음과 같은 내부 작업들이 수행됩니다.


사실상 이 enqueue 작업은 truncate/drop operation을 순차적으로 수행되기 때문에 자주 drop/truncate가 발생하면 "RO" enqueue contention이 발생할 수 있겠죠..

KO-Multiple Object Checkpoint (fast object checkpoint)

얼마전에 PDML 관련해 posting 한 내용 중 다음과 같은 글을 쓴적 있습니다.

"direct-path read가 발생하면 변경되었거나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다."
Parallel Query Execution

oracle 10g R2이전엔 direct-path read가 발생하면 관련 segment가 저장된 tablespace를 대상으로 flush를 하였습니다. 그러나 oracle 10g R2이후엔 관련 object만 disk로 flush하게 바뀌었습니다.
tablespace 단위로 flush 할 경우 해당 tablespace에 많은 데이터가 저장되어 있거나 한 tablespace에 데이터를 몰아 넣었다면 엄청 불필요한 dirty buffer write가 발생할 수 있겠죠.

KO enqueue는 10g R2 부터 바뀐 이러한 동작에 발생하는 enqueue lock type 입니다. 즉 object에 대한 checkpoint 시 대기할 때 발생하는 enqueue lock입니다.


다음은 일반적인 Enqueue 성능을 확인하는 script 들 입니다.
Enqueue는 v$system_event  v$session_wait  v$enqueue_stat을 통해 각종 statistic 정보를 확인할 수 있습니다.

Oracle8i의 경우 하나의 테이블 파티션은 병렬 삽입(PDML) 중 오직 하나의 슬래이브 만을 따른다. 이에 따라 파티 션 간의 데이타 스큐가 발생할 경우, 로드 밸런싱은 불가능하게 된다. 예를 들어 여러 애플리케이션에서 테이블은 날 짜 열 범위에 의해 파티셔닝되며 행은 주로 마지막 파티션에 삽입되다고 할 경우를 마지막 파티션에서 운영되는 슬래 이브는 다른 슬래이브에 비해 훨씬 많은 작업을 해야 한다.

Oracle9i는 인트라 파티션 병렬 기능(intra-partition parallelism)을 채용했다.

여러 슬래이브가 하나의 파티션에서 작업하도록 지원할 경우 성능 병목을 완화하는데 도움이 된다.
이는 병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용하는 것이다. 이는 PDML이 지원될 경우 내부에 서 모두 자동으로 처리된다.

인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)의 이점은 많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘된다. 삽입된 데이타가 균일할 경우 일반 병렬 삽입에 비해 이점이 거의 없거나 전혀 없다.

샘플 문제:
Operational Data Store 테이블 SALES_ODS의 행이 월별로 분할되는 fact 테이블 SALES_1999 에 삽입되어야 한다. 데이타 배포는 스큐가 되며 아래와 같은 그래프로 나타난다.


구현:
Oracle8i와 Oracle9i, 모두에서 이는 다음과 같이 구현된다.


다음 표는 증가하는 데이타 양에 따른 병렬 삽입의 경과 시간을 비교한 것이다.




데이타 스큐를 증가시켜 1999년 11월에 거의 스파이크에 도달하게 된다면 이보다 더 큰 성능 이점을 기대할 수 있다. Oracle8i의 병렬 삽입의 성능은 스큐의 증가에 따라 저하되지만 Oracle9i를 이용할 경우 그 성능은 일정하 다. 다음 결과는 보다 많은 스큐가 적용된 데이타 분배에 대한 것이다(아래의 배포 그래프 참조).




Oracle9i에서 인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)은 다음과 같은 이점을 통해 Oracle8i Parallel Insert(PDML) 보다 나은 성능을 발휘할 수 있다.



이 POST는 "오라클 기술백서 : Oracle 9i와 DSS 환경에서의 성능과 확장성"의 일부 내용을 발췌한 것입니다.

Parallel DML은 기본적으로 session에 "enable" 되어 있지 않습니다. PDML과 serial DML의 locking, transaction, disk space requirement 등의 차이에 의해 PDML mode의 "enable"이 요구됩니다.

ALTER SESSION ENABLE PARALLEL DML;

따라서 Parallel DML이 "disable"되있을 경우 parallel hint나 table/index에 degree가 설정되어 있어도 이는 무시되게 됩니다. 물론 PDML mode가 "enable"되어 있어도 parallel hint나 table/index에 대한 degree가 설정되어 있어야 PDML로 수행 가능하다.


한 Transaction은 서로 다른 table에 대해 여러 PDML이 수행될 수 있습니다. 그러나 PDML로 변경된 table에 대해 해당 transaction 내에서 serial/parallel 명령(DML or Query)으로 access를 할 수 없습니다. 즉 commit/rollback 등으로 transaction을 완료 후에야 동일 table에 대한 operation이 가능합니다.
(참조 Note 201978.1 PDML Restrictions on Parallel DML)


Oracle 9i 이후에 intra-partition parallelism 개념이 소개되었습니다.
이 개념은 partition당 한개씩만 수행되는 parallel execution server의 제한을 완화(?) 시키는 개념입니다.
(참고 Note 241376.1 What is Intra-partition parallelism )

특정 세션의 PDML의 "enable" 여부는 v$session의 PDML_STATUS, PDDL_STATUS, PQ_STATUS column으로 확인 할 수 있습니다.

SQL> SELECT SID,PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$SESSION;

SID	PDML_STATUS	PDDL_STATUS	PQ_STATUS
----------	------------	------------	------------
141	DISABLED	DISABLED	DISABLED
143	DISABLED	ENABLED	ENABLED
145	DISABLED	ENABLED	ENABLED
148	DISABLED	ENABLED	ENABLED
150	DISABLED	DISABLED	DISABLED

이 POST는 metalink note를 참고하여 작성되었습니다.
Note 201457.1- Introduction to PDML

1. Introduction to Parallel Execution

대용량 table에 대한 Full table scan이나 큰 size의 index 생성 등 많은 데이터를 handle할 때 작업들은 여러 process에게 나누어 수행하도록 할 수 있습니다.

이러한 작업 방식을 parallel execution 또는 parallel processing라고 합니다. 

parallel execution은 여러가지 유형의 작업에서 쓸만합니다. 

• large table scan이나 join 또는 partitioned index scan등의 query 
  
• large table이나 index 생성 
  
• Bulk insert나 update, delete
• Aggregations 

2. How Parallel Execution works

(1) 처음 query가 oracle server로 들어오면 query를 분석하는 parse 단계를 거치게 됩니다. 이때 여러개의 access path 중 가장 성능이 좋다고 판단되는 access path가 결정되게 됩니다. parallel execution이 선택되게 되면, 

(2) 수행 단계에서 query를 수행한 user shadow process는 query coordinator(QC)가 되고 parallel slave 들이 요청한 session에 할당됩니다.

(3) query coordinator는 할당된 slave process에 ROWID나 partition 단위로 데이터를 나눠줍니다.

(4) "producer slave"는 데이터를 읽어 "table queue"로 데이터를 보내는데, "consumer slave"나 query coordinator가 데이터 처리를 위해 이 table queue의 데이터를 대기하게 됩니다.

 (5) 만약 sort가 필요한 parallel execution이라면 이들 table queue의 데이터는 "consumer slave process"에 의해 읽혀져 sort 되며,sort 된 데이터는  새로운 "table queue"에 보내지게 됩니다. 이들 sort 된 데이터는 다시 Query coordinator에 의해 읽혀집니다.

만약 sort가 필요 없는 parallel execution이라면 producer slave가 보낸 table queue데이터를 query coordinator가 직접 읽어 처리합니다.

[그림] parallel executions with/without SORT

용어 설명 :

* Query Coordinator(QC)

parallel execution을 수행한 foreground process, 즉 query를 수행한 session으로 query slave로 부터 데이터를 받게 된다. 

* Slaves

slave는 disk로 부터 바로 데이터를 읽거나 다른 slave에 의해 만들어진 table queue 구조로 부터 읽어 그것을 자신의 queue에 write한다.  slave 가 disk로 부터 데이터를 읽을 때 buffer cache를 거치지 않는 direct I/O path read를 수행한다. slave는 이미 변경되었으나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다. 

 slave는 producers slave와 consumer slave의 두개의 종류가 있다. 

 Producers slave는 QC로 부터의 주어진 ROWID range나 partition을 통해 data block을 읽어 관련 데이터를 읽어 온다. 이 데이터는 table queue에 보내지며 이를 다시 QC나 consumer slave가 처리하게 된다.

Consumer slave는 producer slave에 의해 보내진 table queue의 데이터를 처리하여 QC로 dequeue 하게 된다.

consumer slave와 producer slave로 나눠져있기 때문에 sort가 필요한 parallel execution에는 두배의 parallel query slave가 필요하게 된다.

 

* Table Queues

(TQ)는 process가 다른 process에레 row를 보내기 위한 queue이다. Table queue는 producer slave가 consumer slave에게, consumer slave가 query coordinator에게 데이터를 보내기 위해 사용된다.

3. Database Parameter Setup for Parallel Execution

PARALLEL_MAX_SERVERS 
인스턴스 당 최대 사용가능한 slave 개수. 0 설정시 parallel query 사용 불가.

PARALLEL_MIN_SERVERS 
instance startup 시 미리 띄워 놓을 slave 개수.

PARALLEL_MIN_PERCENT 

기본적으로 optimizer가 query를 parallel로 수행하도록 SQL 수행 계획을 생성되더라도 수행시 요구되는 parallel slave를 띄우기 위한 충분한 resource가 없을 경우, 사용자에게 특별한 메세지 없이 serial하게 query를 수행한다. 이러한 경우가 발생하면 query 수행시간이 예상보다 더 늦어지는 일이 발생할 수 있다.

PARALLEL_MIN_PERCENT는 수행시 충분치 못한 resource로 인한 parallel execution이 무시되는 경우를 방지하고 에러를 출력한다. PARALLEL_MIN_PERCENT는 가능한 parallel execution slave의 percent로 설정한다.

만약 설정한 percentage 만큼의 query slave를 띄울 수 없다면 serial로 수행하지 않고 ORA-12827 에러를 발생한다.

예) 만약 resource 부족으로 slave를 띄우지 못했을 경우:

OPTIMIZER_PERCENT_PARALLEL 

PARALLEL_ADAPTIVE_MULTI_USER

PARALLEL_ADAPTIVE_MULTI_USER init parameter가 TRUE로 설정되어 있을 경우 parallel execution 사용할 때 multi-user 환경에서의 성능 향상을 위한 algorithm을 사용하게 된다. 이 algorithm은 query가 수행되는 시점에 system load에 따라 자동으로 요청하는 parallel의 degree를 줄여 query를 수행한다. 

예를 들어 17 CPU 시스템에서 default parallel degree가 32로 설정되어 있다면 첫번째 사용자는 32개의 parallel slave process를 사용해 query가 수행된다. 그러나 두번째 사용자가 query를 수행할 경우 16개의 parallel slave process가 사용되며, 세번째 사용자는 8개, ..

결국 32번째 user는 1개의 parallel slave process를 사용하게 된다.

 

PARALLEL_AUTOMATIC_TUNING  new with 8i

다음의 내용은 PARALLEL_AUTOMATIC_TUNING에 의해 영향을 받는 Parallel execution parameter와 변경 방식이다.

Parameter	Default Values  When parallel_automatic_tuning=   FALSE	Default Values When parallel_automatic_tuning= TRUE
parallel_execution_message_size	2KByte	4KByte
parallel_adaptive_multi_user	FALSE	TRUE
large_pool_size	no effect	is incerased based on a complicated computation using  various other parameters
processes	no effect	if processes < parallel_max_servers The processes parameter is increased
parallel_max_servers	5	if parallel_adaptive_multi_user==true (cpus * parallel_threads_per_cpu *  _parallel_adaptive_max_users * 5) else  (cpus * parallel_threads_per_cpu * _parallel_adaptive_max_users * 8)

4. Parallel Execution Performance

Parallel query의 수행은 performance 상의 이점을 얻을 수 있으나 parallel queyr를 수행하기 앞서 몇가지 고려할 만한 사항이 있다.

multi-slave process는 당연한 얘기지만 single process 보다 많은 CPU resource와 slave process 각각의 private memory를 사용하게 된다. 만약 CPU 자원이 부족하게 되면 oracle은 parallel operation을 serial operation으로 변경해 작업을 수행한다. 따라서 parallel execution은 현재 system의 resource 상태를 고려해 parallel degree를 고려해야 한다.

또 I/O stress가 많은 시스템이라면 slave process에 의한 추가적인 I/O요구가 부담이 될 수 있습니다.
특히 I/O가 특정 disk에 집중된다면 disk I/O의 bottleneck이 발생할 수 있으므로 I/O의 분산 등도 고려되어야 한다.

parallel query는 Full Table Scan으로 데이터를 처리한다. 따라서 index의 사용이 유리한 경우에는 오히려 parallel execution의 성능이 더 나쁠 수 있다. 

이러한 성능의 차이는 index에 의해 query 조건의 선처리로 대상 데이터량을 줄이는데서 발생한다.

•  Nested Loops vs. Hash/Sort Merge Joins
  Nested loop join의 경우 query 조건에 의한 "row elimination"으로 driviing table의 대상 row를 줄이기 때문에 FTS 보다는 index scan에 적합하다. 반면 Hash join과 sort merge의 경우 일반적으로 대량의 데이터를 처리하는데 더 효과적이다. 이는  Hash join과 Sort Merge join은 driving row source에 대해서 조건에 의한 데이터의 "row eliminate"를 하지 않기 때문이다. 
  
•  slave process를 생성하고, data를 분할하고, 여러 slave process로 데이터를 전송하고 결과를 취합하는 등의 비용이 data를 serial하게 처리하는 것보다 많을 수 있다.
  
•  Data skew
  parallel query는 데이터를 ROWID range를 기본으로 slave process 간에 할당한다. 각각의 slave에게 같은 개수의 block을 할당한다는 것은 같은 수의 row를 할당한다는 말과는 다른 의미이다. 예를 들어 대량의 데이터가 수집되고 삭제되는 업무의 경우 특정 블록들에는 데이터가 전혀 들어 있지 않을 수 있다. 이러한 균등하지 않은 데이터 분할로 인해 특정 slave query의 성능이 늦어질 수 있으며 이는 전체 PQ 처리 시간에 영향을 미치게 된다.
  

이 내용은 metalink.oracle.com의 note를 참조했습니다.

Note 203238.1 - Using Parallel Execution

19 Direct-Path INSERT

Introduction to Direct-Path INSERT

오라클은 table에 데이터를 입력하는데 두가지 방식을 사용합니다.

• 전통적인 insert operation은 table내의 free space를 재사용합니다. 기존의 데이터와 새로 insert 되는 데이터가 같이 저장되며, 참조 constraint도 자동으로 관리합니다.
  
• direct-path insert operation은 새로 입력되는 데이터를 기존의 table내에 존재하는 데이터 이후에 저장합니다. buffer cache를 거치지 않고 바로 datafile에 저장되며, table내의 free space를 사용하지 않습니다. 또한 참조 constraint도 무시됩니다. 이러한 일련의 procedure로 인해 performance가 향상됩니다.
  

이러한 direct-path insert operation은 direct-path insert 구문이나 sql loader의 direct-path option을 이용해 사용될 수 있습니다. 

Advantages of Direct-Path INSERT

Direct-path insert는 다음의 performance 잇점을 갖습니다.

• direct-path insert 시 redo와 undo entry들의 logging을 disable 할 수 있습니다. 반면 전통적인 insert operation은 free space 사용과 참조무결성을 위해 반드시 이러한 entry들에 대해서 logging을 해야 합니다.
  
• 새 로운 데이터가 저장된 테이블을 생성하기 위해서 create table과 insert into를 사용하거나 create table .. as select 구문을 사용할 수 있습니다. create table 과 direct-path insert를 이용해 데이터를 insert 한 경우 기존 테이블에 존재하는 인덱스도 자동으로 변경됩니다. 반면 create table as select 구문을 사용한 경우 insert는 추후 재생성해야 합니다.
  
• Direct-path insert는 serial/parallel mode 둘다 transaction이 하나의 단위로 처리된다 (transaction atomicity). 그러나 sql loader의 parallel-patch load의 경우 transaction atomicity를 보장하지 않는다.
  
• parallel direct-path load의 경우 에러가 발생하면 index는 load 후 UNUSABLE로 빠질 수 있다. 반면 parallel direct-path INSERT 구문의 경우 index update시 에러가 발생하면 rollback 된다.
  

Serial and Parallel Direct-Path INSERT

parallel DML mode에서 insert 할 경우 direct-path INSERT가 default 이다. parallel DML mode로 사용하기 위해서 다음의 요구사항이 필요하다.

• Oracle Enterprise Edition을 사용해야 한다.
  
• ALTER SESSION { ENABLE | FORCE } PARALLEL DML; 명령으로 parallel DML 기능을 enable 해야한다. 
• table 생성시 또는 그 이후 table에 대한 parallel attribute를 지정하거나 insert 구문내에 parallel hint를 지정한다.
  

direct-path INSERT를 disable 하기 위해 NOAPPEND hint를 insert 구문에 지정해 줘야 한다.

serial mode로 insert 할때 INSERT 구문 뒤에 APPEND hint를 사용하거나 INSERT 구문 내의 subquery의 SELECT 구문 바로 뒤에 APPEND hint를 지정해 줘야 한다.

Direct-Path INSERT Into Partitioned and Nonpartitioned Tables

Direct-path INSERT는 partition/non-partition table 모두 사용가능하다.

Serial Direct-Path INSERT into Partitioned and Nonpartitioned Tables

Single process insert의 경우 table segment나 각 partition segment의 HWM 뒤에 데이터가 insert 된다.

commit이 수행되면 HWM는 새로운 값으로 update 되며 user에게 데이터가 보이게 된다.

Parallel Direct-Path INSERT into Partitioned Tables

Partition table에 대한 parallel direct-path insert는 serial direct-path insert와 유사하다.

각각의 parallel execution server는 하나 또는 여러 partition에 할당된다. 하나의 partition에 대해 한개 이상의 parallel execution server process가 할당되진 않는다.

(Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition.)

각 parallel execution server는 할당된 partition segment의 HWM 이후에 데이터를 insert 한다.

commit이 수행되면 HWM는 update 되며 새로운 데이터는 user에게 보이게 된다.

Parallel Direct-Path INSERT into Nonpartitioned Tables

Non-partition table에 대한 parallel direct-path insert는 각각의 parallel execution server가 temporary segment를 만들며 이 temporary segment에 데이터를 insert 한다.

commit이 수행되면 parallel execution coordinator가 temporary segment를 table segment로 merge 한다.

Direct-Path INSERT and Logging Mode

Direct-path INSERT는 redo entry와 undo entry를 logging 할지를 선택 할 수 있다.

• table, partition, index, LOB storage에 대해 생성시나 추후 logging mode를 설정할 수 있다. 
• 만약 LOGGING이나 NOLOGGING을 설정하지 않았다면..
  
  • partition 의 경우 해당 table의 logging attribute를 따른다.
    
  • table이나 index의 경우 tablespace의 logging attribute를 따른다.
    
  • LOB storage의 경우 만약 CACHE option으로 정의 된 경우 LOGGING이 default attribute로 설정된다. 만약 CACHE option이 지정되지 않은 경우 해당 tablespace의 logging attribute를 따른다.
    
• CREATE TABLESPACE 나 ALTER TABLESPACE 명령으로 logging attribute를 지정할수 있다.
  
  

Direct-Path INSERT with Logging

logging mode에서 direct-path insert가 수행될 경우 oracle은 full redo logging을 수행한다.

Direct-Path INSERT without Logging

NOLOGGING 으로 Direct-path insert 할 경우 oracle은 redo entry와 undo retry를 logging 하지 않습니다.

그러나 새로운 extent를 invalid로 marking하기 위한 최소한은 log와 data dictionary 변경에 대한 내용은 logging 합니다.

NOLOGGING은 performance를 증가시키지만 media recovery가 필요한 경우 redo 정보가 없기때문에 변경된 데이터에 대해 logical corruption을 발생하게 됩니다. 

따라서 NOLOGGING mode에서 direct-insert 한 경우 반드시 backup이 필요합니다.

Additional Considerations for Direct-Path INSERT

Index Maintenance with Direct-Path INSERT

오라클은 direct-patch INSERT 이후 table의 index에 대한 변경 사항을 적용합니다.

parallel direct-path INSERT의 경우 parallel execution server에 의해, serial direct-path INSERT의 경우 single process에 의해 수행됩니다.

Index 관리에 따른 performance 저하를 피하기 위해 insert 수행 전 index를 drop하고 rebuild 할 수 있습니다.

Space Considerations with Direct-Path INSERT

direct-patch INSERT의 경우 table의 free-list내의 공간을 사용하지 않기때문에 기존의 INSERT 방식에 비해 많은 공간을 요구하게 된다. 

serial direct-path INSERT와 partition table에 대한 parallel direct-path INSERT의 경우 해당 segment의 HWM 이후에 데이터를 저장하게 됩니다. 

이로 인해 추가적인 공간이 더 필요하게 된다. 

non-partition table에 대한 parallel direct-path INSERT의 경우 각각의 parallel degree 만큼의 temporary segment가 생성되어 추가적인 공간이 더 필요하다.

만약 non-partition table이 locally managed tablespace내에 automatic mode로 생성되지 않았다면 NEXT, PCTINCREASE, MININUM EXTENT 등의 storage parameter를

적정한 수치로 지정해 주어야 한다.

• 각각의 extent size가 너무 작을 경우 object의 extent 갯수에 영향을 미친다.
  
• parallel insert 결과가 segment내의 공간을 낭비하지 않도록 적당한 extent size를 갖어야 한다.
  

direct-path insert 이후 이들 storage parameter를 serial operation에 맞도록 다시 설정한다.

Locking Considerations with Direct-Path INSERT

direct-path INESRT 시 해당 table에 대한 exclusive lock이 요구된다. 따라서 다른 user들은 해당 table에 대해 insert, update, delete, index create/build operation이 제한된다. 

데이터에 대한 query는 지원되며 direct-insert 이전의 데이터만 보여진다.