지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.


Partition
RANGE Access Pattern  

1.PARTITION RANGE SINGLE
2.2.PARTITION RANGE ITERATOR
3.3.PARTITION RANGE INLIST
4.4.PARTITION RANGE ALL
5.5.PARTITION RANGE EMPTY
6.6.PARTITION RANGE OR
7.7.PARTITION RANGE SUBQUERY
8.8.PARTITION RANGE JOIN-FILTER
9.9.PARTITION RANGE MULTI-COLUMN


Partition
LIST Access Pattern
1.
1.PARTITION LIST SINGLE
2.2.PARTITION LIST ITERATOR
3.3.PARTITION LIST INLIST
4.4.PARTITION LIST ALL
5.5.PARTITION LIST EMPTY
6.6.PARTITION LIST OR
7.7.PARTITION LIST SUBQUERY
8.8.PARTITION LIST JOIN-FILTER


Partition
HASH Access Pattern
1.
1.PARTITION HASH SINGLE
2.2.PARTITION HASH ITERATOR
3.3.PARTITION HASH INLIST
4.4.PARTITION HASH ALL
5.5.PARTITION HASH SUBQUERY
6.6.PARTITION HASH JOIN-FILTER

총 23가지이다.
이모든 것을 언제 다 배운단 말인가?
이럴때 필자가 정리한 파일이 도움이 될것이다.
반드시 Partition Pruning 과 Access Pattern 을 정복하기 바란다.

사용자 삽입 이미지
사용자 삽입 이미지

invalid-file

Partition Access Pattern





PS :
첨부된 문서는 Oracle 11.1까지의 Partition Pruning 과 Access Pattern을 정리한 것이다.
배포시 출처를 밝혀주기 바란다.

참조서적:
1.Trouble Shooting Oracle Performance (Christian Antognini)
2.오라클 메뉴얼
Posted by extremedb
,

아래의 글은 모델러에 따라 생각이 다를 수 있음을 밝혀둔다. 모델링에는 정답이 없다. 하지만 모범답안을 찾기 위해 노력해야 한다.

역정규화는 정합성과 확장성을 방해한다.
많은 종류의 모델링 책에 위와 같은 이유로 물리모델링단계에서 역정규화를 해야 한다고 되어 있다. 즉 정합성과 확장성이 나빠짐으로 논리모델링시에는 정규화를 하고 물리모델링 시에는 역정규화를 해야 한다는 것이다. 하지만 과연 그럴까? 몇몇의 경우는 물리모델링에서 역정규화를 적용하는 것이 옳을 수도 있다. 하지만 항상 그런 것은 아니다. 오늘은 역정규화에 잘못된 관행에 대하여 알아보도록 하자
.

역정규화를 해야 하는 이유는 두 가지이다.

첫 번째, 성능관점이다. 대표적인 것이 항상 조인이 발생함으로 조인에 의한 성능저하를 막아보자는 것이다. 또 다른 예제는 목적성(집계) 테이블이다. Source 테이블을 직접 Group By + SUM 을 한다면 SQL의 성능이 느려지는 것은 불을 보듯 뻔한 것이다. 목적성 테이블에는 상태를 나타내는 테이블도 있다. 대표적인 것이 상품의 현재고 테이블이다.

두 번째, 개발 생산성 관점이다. 대표적인 예제는 고객테이블의 고객전화번호이다. 예를 들면 자택전화번호, 직장본사전화번호, 직장 파견지 전화번호, 핸드폰번호, 기타전화번호로 전화번호가 여러 개인경우에 고객연락처를 별도로 분리하여 정규화를 하게 된다. 이럴 때 PK 는 고객번호 + 연락처구분코드 가 되며 테이블의 확장성이 좋아진다. 하지만 모든 조회 화면에서 고객의 전화번호를 가로로 나열한다면? 성능은 물론이고 개발자의 생산성 또한 좋지 못할 것이다.


위의 두 가지 관점을 모두 가지고 있는 경우가 있다. 대표적인 것이 재고 테이블이다. 재고테이블은 성능의 관점과 개발생산성의 관점을 모두 고려한 것이다. 제조나 유통 프로젝트의 경우 재고 테이블이 없다면 개발하는데 엄청난 공수를 들여야 할 것이다. 이론적으로 상품의 현재고는 다음과 같이 구할 수 있다.

현재고 = 전월 이월수량 + 이번 달 입고수량 - 이번 달 출고수량 - 출고 되지 않은 주문 수량 + 재고로 사용할 수 있는 이번 달 반품수량 + 기타


논리모델링에서 역정규화를 해야 되는 경우
논리 모델링 단계에서 재고엔티티가 없어도 위의 식을 사용한다면 논리적으로 문제될 것이 없다. 하지만 업무의 핵심인 입고, 출고, 반품, 주문 등에 의하여 지속적으로 영향을 받는 업무적으로도 중요한 테이블이 된다. 또한 재고실사(실제로 재고가 몇 개인지 검증)를 하게 되면 재고테이블의 수량에 직접 영향을 끼친다. 이러한 여러 가지 이유로 재고 테이블은 논리모델링에서 나오는 것이 좋다. 이유는 성능이나 개발생산성 관점뿐만 아니라 업무적인 관점과 집합의 중요성을 감안 해야하며 중요한 엔티티가 없을 경우 Communication 이 어려워지기 때문이다. 심지어 개념모델링에서도 재고를 엔티티로 나타내는 모델러도 있다.

Table Generation
이후에 역정규화를 해야 하는 경우

성능상의 문제로 역정규화를 고려하는 경우이다. 필자가 역정규화를 적용한 대부분의 경우가 여기에 해당한다. 예를 들어 고객테이블의 고객명을 역정규화 해야 한다고 생각해 보자. 물리모델링단계 이후에 Table Generation 이 끝나고 데이터 이관작업이 끝나고 인덱스 디자인 작업이 끝나야 SQL 의 성능이 느린지 아닌지 알 수 있다.

나는 질문한다
나는 물리모델링단계에서 모든 역정규화를 적용했다는 사람에게 묻고 싶다.  도대체 무엇을 보고 물리모델링 단계에서 SQL이 느린지 알 수 있단 말인가?  물리 모델링 단계에서는 SQL을 알 수 없는 것은 물론이고 물리적인 테이블이나 인덱스가 없다. SQL을 실행시킬수도 없는 환경에서 모든 역정규화를 적용했단 말인가? 책에 나와 있다고 혹은 고참이 그렇게 한다고 혹은 경험이나 감으로 역정규화를 적용 했는가? 이런 일이 반복되어서는 안된다.


구체적인 예를 들어보자

성능목표를 모든 조회화면의 90% 2.5초 이내에 수행되어야 한다. 라고 정의 했다면 해당 SQL 2.5 초가 걸리는지 안 걸리는 지는 데이터가 이행이 되고 쓸만한 인덱스가 설계된 이후에나 알수 있다.

뻔한 것은 물리모델링 시에 역정규화 할 수 있다

대표적인 것이 고객테이블의 고객전화번호이다. 예를 들면 자택전화번호, 직장 본사 전화번호, 직장 파견지 전화번호, 핸드폰번호로 전화번호가 여러 개인경우에 고객연락처를 별도로 분리하여 논리 모델링시에 정규화를 하게 된다. 이럴 때 PK 는 고객번호 + 연락처구분코드 가 되며 테이블의 확장성이 좋아진다.

하지만 물리모델링 시에 화면 설계서를 볼 수 있고 거의 모든 조회 화면에서 고객의 전화번호를 가로로 나열한다면? 이때는 물리모델링 시에 역정규화를 하는 것이 옳다. 개발생산성과 성능의 향상은 불을 보듯 뻔한 것이므로 하지만 이 경우에도 전화번호의 종류가 계속하여 늘어난다면 역정규화를 적용하는 것은 다시 생각해보아야 한다
.

일부의 집계테이블도 물리모델링 단계에서 역정규화가 가능하다

화면 정의서에서 원본 테이블을 지속적으로 Group By + Sum 하는 형태라면 물리모델링시에 집계테이블을 생성 할 수 있다. 하지만 이런 경우라 할지라도 화면에 해당하는 SQL   성능 목표인 2.5초가 넘는지 정확히 알 수 없다. 인덱스 나 파티션, 클러스터링 팩터에 의하여 달라진다. 또한 화면의 조회조건이 어떻게 되느냐에 따라 SQL의 성능은 달라진다. 어느 프로젝트의 경우 성능목표보다 6배 이상 빠른 경우에도 역정규화를 적용하여 데이터의 정합성에 문제가 된 적이 있다.

역정규화는 최소화 되어야 해
개발생산성과 성능이란 두 가지 관점에서 역정규화는 필요하다. 하지만 역정규화는 정합성과 확장성을 해치기 때문에 최소화 되어야 한다. 모델러의 경험이나 감으로 역정규화를 하는 경우가 많다. 그렇게 하면 필요치 않은 역정규화가 발생할 수 있다. 모델링의 각 단계(논리모델링/물리모델링/Generation 이후)에서 해야 할 것과 하지 말아야 할 것을 잘 따져서 해야 한다.

성능관점의 역정규화는 데이터 이행 이후에 고려하라

특히 성능관점에서는 Table Generation 이후 + 인덱스 디자인 이후 + 데이터 이행 이후에 목표성능을 만족하지 않는 경우에만 적용하는 것이 역정규화를 최소화 한다는 관점에서 가장 바람 직 하다. 필자가 이런 글을 쓰는 이유는 역정규화는 무조건 물리모델링단계에서 하는 것 으로 생각하는 모델러가 대부분이기 때문이다.

결론:

바둑을 잘 두려면 정석은 모두 알아야 한다. 하지만 정석을 알고 난 이후에 프로기사가 되려면 정석을 까먹어야 한다. 틀에 박하지 않은 새로운 정석을 만들어야 하기 때문이고 실제로 프로기사들은 새로운 정석을 계속 만들어 낸다. 역정규화는 무조건 물리모델링단계에서 하는 것 이란 말이 아주 잘못된 말은 아니다. 하지만 개념을 알았다면 까먹어라. 더 좋은 새로운 정석을 만들기 위해서  

PS
1.
현실적으로 모델링의 기간이 매우 짧으므로 물리모델링단계에서 모든 것을 해야 하는 경우도 있다. 하지만 이런 경우를 원칙으로 가이드를 하거나 책을 쓰는 것은 옳지 않다
.
2.
프로젝트 에서 물리모델링 기간을 시스템 Open 직전까지 가져가는 경우가 있다. 이런 경우는 물리모델링단계에서 역정규화를 하는 것이 옳다. 하지만 이때에도 뻔한 경우가 아니라면 데이터 이행 이후에 쓸만한 인덱스가 있어도 성능이 나오지 않는 경우에  역정규화를 고려하는 것이 가장 정확하다.


Posted by extremedb
,

이번 내용은 난위도가 있으므로  'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼

사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다
.

Interleaving
은 선수조건이 필요하다

하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다
.

숫자표기법의 이해

Interleaving
에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 1 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting
CSU(0) :
서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2(SUBQ2, SUBQ1)라면 아래처럼 표현된다
.
CSU(1,1) :
모든 서브쿼리가 Unnesting

CSU(1,0) : SUBQ2
Unnesting
CSU(0,1) : SUBQ1
Unnesting
CSU(0,0) :
모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다
.

Interleaving
의 용도

Interleaving
은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */ 

       e1.employee_id, e1.manager_id, e1.salary

  FROM employee e1

 WHERE e1.department_id = 10

     and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

                                             e2.manager_id,  max(e2.salary) 

                                        from employee e2

                                       group by e2.manager_id )

     and rownum = 1

 

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM JPPD Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

 

*****************************

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

SU: Considering interleaved complex view merging

CVM:     CVM bypassed: Outer QBc referencing ROWNUM.

  Cost: 5.0035  Degree: 1  Card: 1.0000  Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

JPPD: Starting iteration 2, state space = (2) : (1)

  Cost: 4.0010  Degree: 1  Card: 1.0000  Bytes: 30

 

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

 

SU: Starting iteration 2, state space = (2) : (0)

  Cost: 6.0037  Degree: 1  Card: 1.0000  Bytes: 15

 

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.


우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다
.

CVM과 JPPD 는 동시에 고려된다
CVM
과정이 끝나면 JPPDCosting 과정이 진행된다. Interleaving CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) Cost 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010 JPPD Cost가 되는 것이 아니라 CSU Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. CSU Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다
.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.


Posted by extremedb
,