'OLTP'에 해당되는 글 2건

  1. 2009.02.24 데이터 마이닝을 위한 데이터 웨어하우스와 OLAP 기술
  2. 2009.02.24 데이터 웨어하우스 (DW) 구축을 위한 노하우
2009. 2. 24. 14:13

데이터 마이닝을 위한 데이터 웨어하우스와 OLAP 기술

1장 : 개론

 

1.1 데이터 마이닝의 필요성과 중요성은 무엇인가?

데이터 마이닝이 주목을 받고 있는 주된 이유는 데이터의 양적 팽창과 그러한 데이터를 유용한 정보와 지식으로 바꿔야 하는시급한 필요성에 기인한다.

1.2 과연, 데이터 마이닝이란 무엇인가?

데이터 마이닝(Data Mining) : 대량의 데이터로부터 지식을 추출하는(또는 캐내는) 것을 말한다.

◎ 데이터베이스에서의 지식발견(Knowledge Discovery in Database : KDD) 과정

    1. 데이터 정제 : 잡음과 불일치 데이터의 제거
    2. 데이터 통합 : 다수의 데이터 소스들의 결합
    3. 데이터 선택 : 분석작업과 관련된 데이터들이 데이터베이스로부터 검색된다.
    4. 데이터 변환 : 요약이나 집계 등과 같은 연산을 수행함으로써, 마이닝을 위해 적합한 형태로 데이터를 변환하거나 합병정리한다.
    5. 데이터 마이닝 : 데이터 패턴을 추출하기 위하여 지능적 방법들이 적용되는 필수적 과정
    6. 패턴 평가 : 몇 가지 흥미 척도들을 기초로, 지식을 나타내는 진짜 흥미로운 패턴들을 구별한다.
    7. 지식 표현 : 사용자에게 채굴된 지식을 보여주기 위하여 시각화와 지식표현 기법들이 사용된다.

1.3 데이터 마이닝 - 어떤 종류의 데이터에 대한 마이닝인가?

  1. 관계 데이터베이스 : 고유한 이름을 가진 테이블들의 모임
  2. 데이터 웨어하우스 : 기업경영의 의사결정을 위하여 한 장소에 통일된 스키마로 조직된, 다수의 이질 데이터 소스들의 창고
  3. 트랜잭션 데이터베이스 : 각 레코드가 하나의 트랜잭션을 타나내는 파일로 구성

1.4 데이터 마이닝의 기능

개념/클래스 기술, 연관성, 분류, 예측, 군집화, 경향분석, 편차 분석 및 유사성 분석등을 포함

◎ 마이닝 될수 있는 데이터 패턴

  1. 서술형 마이닝 작업 : 데이터베이스에 있는 데이터의 일반적 특성들을 설명
  2. 예측형 마이닝 작업 : 예측을 위하여 현재 데이터들에 대한 추론을 수행

◎  데이터 요약의 형식

  1. 데이터 특성화 : 목표 클래스의 데이터들을 일반적인 용어들로 요약하기

2장 : 데이터 마이닝을 위한 데이터 웨어하우스와 OLAP 기술

 

2.1 데이터 웨어하우스란 무엇인가?

경영 의사결정을 지원하기 위해 조직된 주제지향적이고, 통합된, 시간에 따라 변하는, 비휘발성 데이터의 집합.

◎ OLTP와 OLAP의 구별되는 특징

 

 OLTP

OLAP 

사용자와 시스템 지향

*고객지향 *시장지향 

데이터 내용

* 너무 세세해서 의사결정에 쉽게 사용할 수 없는 최근데이터 *방대한 양의 이력 데이터 

데이터베이스 설계

*ER(entity-relationship)데이터모델

*응용지향(application-oriented) 데이터베이스 설계

*스타(star) 모델

*눈송이(snowflake) 모델

*주제지향 데이터베이스 설계

범위

*한 기업 또는 부서 내의 최근데이터 *한 조직이 발전해 오는 과정으로 인하여 생긴 여러 버젼의 데이터베이스 스키마 

접근패턴

*원자성(atomic) 트랜잭션 *읽기전용연산 

2.2 다차원 데이터 모델

◎ 스타스키마(star schema)

  • 각장 일반적인 모델링 패러다임
  • 중복없는 대량의 데이터를 포함하고 있는 대형 중심테이블(사실테이블)과 각 차원에 대하여 하나씩, 비교적 작은 차원 테이블들의 집합으로 구성

◎ 눈송이 스키마(snowflake schema)

  • 스타 스키마 모델의 한 변형으로, 몇몇 차원 테이블들이 정규화되어, 데이터를 추가적인 테이블들로 분할

◎ 사실성군(fact constellation)

  • 복잡한 응용에서는 차원 테이블들을 공유하는 여러개의 사실 테이블들을 필요로 할수 있다.
  • 이러한 종류의 스키마는 스타의 집합으로 볼수 있으므로, 은하수스키마(galaxy schema) 또는 사실성군이라 부른다.

3장 : 데이터 전처리

 

◎ 전처리 기법

  1. 데이터 정제(data cleaning) : 잡음을 제거하고, 데이터에 있는 불일치를 바로잡는데 적용
  2. 데이터 통합(data integration) : 여러 소스에서 온 데이터들을 데이터 웨어하우스나 데이터 큐브같은 하나의 통일된 데이터 저장소로 융합
  3. 데이터 변환(data transformation) : 거리 측정을 수반하는 마이닝 알고리즘의 정확성과 효율성을 증진시킬수 있음
  4. 데이터 축소(data reduction) : 집계, 중복 특징 제거, 또는 군집화(clustering)을 통하여 데이터의 크기를 줄일수 있음

◎ 왜 데이터를 전처리 하는가?

데이터 마이닝 기법들을 이용하여 분석하고자 원하는 데이터들이 불완전하고, 잡음이 있고, 일치성이 없는 실제세상이기 때문

  • 불완전하다 : 관심있는 속성값이나 특정 속성이 없거나, 또는 집계 데이터만을 포함
  • 잡음이있다 : 예상치에서 이탈된 이상치 또는 오류가 포함되어 있음
  • 일치성이 없다 : 제품을 분류하기 위해 사용되는 부서 코드들에 모순이 포함되어있음

4장 : 데이터 마이닝 요소, 언어, 시스템 구조

 

데이터 마이닝 요소

◎ 데이터 마이닝 작업 정의

  1. 작업-관련 데이터(task-relevant data) : 조사할 데이터베이스의 부분
  2. 마이닝할 지식의 종류 : 특성화, 판별, 연관, 분류, 군집화 또는 진화분석과 같은 수행할 데이터 마이닝 기능을 명세
  3. 배경지식(background knowledge)
  4. 흥미도(interestingness measure) : 관심없는 패턴을 지식으로부터 분리하는데 사용
    • 지지도(support) : 규칙 패턴이 나타나는 작업관계 데이터 투플들의 백분율
      • 지지도(A=>B) = A와 B를 포함하는 투플수 / 총 투플수
      • (A,B 는 아이템의 집합)
    • 신뢰도(confidence) : 규칙의 관련 정도의 추정
      • 신뢰도(A=>B) = A와 B를 포함하는 투플수 / A를 포함하는 투플수
      • (A,B 는 아이템의 집합)
  5. 탐사 패턴의 프리젠테이션과 가시화 : 발견된 패턴을 표현하는 형식

5장 : 개념서술(특성화와 비교)

 

5.1 개념서술(concept description)

  • 특성화(characterization) : 주어진 데이터 집합의 간략하고 간결한 요약을 제공
  • 개념이나 클래스 비교(혹은 판별) : 두개 이상의 데이터 집합의 비교에 대한 설명을 제공
  • 개념서술 : 데이터 마이닝의 가장 기본적인 형태로 작업 관련 데이터를 짧은 요약을 제공하며 데이터에 대한 일반적인 특성을 제시

5.2 데이터 일반화와 요약에 기반한 특성화

데이터 일반화 : 데이터베이스내의 대규모 작업 관련 데이터 집합을 상대적으로 낮은 개념 수준에서 높은 개념 수준으로 추상화 하는 과정

5.3 분석특성화 : 속성 관련 분석

분석특성화 : 속성이나 차원 관련 분석을 사용한 클래스 특성화

◎ 속성관련 분석의 종류 : 통계학, 퍼지이론, 러프(rough)집합 이론

◎ 속성관련 분석의 척도 : 정보이득(information gain), 지니계수(Gini index), 불확실지수(uncertainty), 상관계수(correlation coefficient)

5.4 클래스 비교 마이닝 : 상이한 클래스들을 차별화 하기

클래스 비교(clanss comparison) : 목표 클래스를 대조 클래스와 구별하게 하는 서술을 마이닝 하는것


6장 : 대용량의 데이터베이스에서의 연관규칙 마이닝

 

연관규칙 마이닝 : 대규모 데이터 항목의 집합 사이에서 유용한 연관성과 상관관계를 찾는 방법

장바구니 분석(market basket analysis)

  • 연관규칙 마이닝의 한 형태
  • 고객들의 장바구니에서 서로 다른 품목들 사이의 연관관계를 발견함으로써 고객의 구매습관을 분석

http://www.reportnet.co.kr/knowledge/pop_preview.html?dn=3234940

Trackback 0 Comment 0
2009. 2. 24. 11:20

데이터 웨어하우스 (DW) 구축을 위한 노하우

데이타 웨어하우스란?
- 수년간의(historical data)
- 기업의 운영계 시스템에서 생긴 내부 데이타와 (internal data)
- 외부 데이타를(external data)
- 제주별로 통합하여(subject-oriented)
- 별도의 프로그래밍 없이(end-user computing)
- 즉시(on-line)
- 여러 각도에서 분석 가능케 하는(multi-dimensional analysis)통합 시스템이다.

자원 관리
일반적으로 DW를 위한 서버를 따로 구성하는 것이 효율적이지만, 기존의 OLTP OnLine Transaction Processing 서버에 DW를 구축해야만 하는 경우가 있다. 이때 DW OLAP : OnLine Analytic Processing 사용자와 OLTP 시스템 사용자를 위한 CPU, 병렬 처리 서버 수, 최대 활동 세션 수 등의 자원 활용 정책은 각각에 맞게끔 최적으로 구성되어야 한다. 즉, DW 사용자는 주로 많은 양의 데이타를 가지는 Fact Table과 여러 Dimension Table들을 조인 Join 해야 하는 배치성 Batch 위주의 작업을 하므로 많은 자원을 필요로 하는 트랜잭션을 하고, OLTP 시스템 사용자는 주문 입력과 같은 적은 자원을 요하는 트랜잭션을 자주 하게 된다.

이를 위해 Oracle DBMS는 ‘자원 관리 Resource Manager’라는 기능을 제공하는데, Resource Consumer Group을 생성하여 그룹별, 시간대별로 사용할 수 있는 CPU, 병렬 서버 수, 최대 활동 세션 수 등을 할당할 수 있다.

사용되는 패키지는 그룹과 플랜 Plan 생성을 위한 DBMS_RESOURCE_MANAGER 패키지, 사용자 혹은 롤Role을 Consumer Group에 할당하는 DBMS_RESOURCE_MANAGER_PRIVS 패키지가 있다. RESOURCE_MANAGER_PLAN = day_plan과 같이 패러미터를 세팅하여 인스턴스가 사용할 플랜을 지정하고, ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = night_plan; 와 같이 필요 시에 플랜을 바꿀 수 있다. Oracle9i부터는 그룹별로 활동 가능한 세션을 정함으로써 자원 사용의 한도를 둘 수 있는데, 액티브 세션 수가 정해둔 세션 수에 도달했을 때 새로운 쿼리가 있으면 큐 Queue 에 저장되었다가 기존의 세션이 끝나면 실행된다. 그리고, 그룹별로 쿼리당 가능한 최대 실행 시간을 정해둘 수 있는데, 어떤 쿼리의 예상시간이 이 시간보다 크면 중지되고, 사용자에게 에러 메시지를 보낸다. 이는 오래 걸리는 쿼리를 실행전에 차단함으로써 불필요한 자원 소요를 사전에 막을 수 있다. Oracle9i에서 추가된 또 다른 기능은 특정 조건에 해당되는 쿼리를 실행한 세션을 다른 그룹으로 옮기게 하는 것이다. 예를 들어, 5분 이상 소요되는 쿼리를 실행한 세션을 자동적으로 다른 그룹으로 변경함으로써 그룹 내 다른 세션의 성능에 영향을 미치지 않게 한다.
 

 

Materialized Views & Query Rewrite

대량의 데이타를 가지는 Fact와 여러 Dimension들을 조인하여 집계성 보고서를 작성하는 경우와 같이 시간이 오래 걸리는 쿼리를 위해 테이블과 같이 실제 데이타를 가지는 Materialized View를 생성할 수 있다. 이는 스냅샷 Snapshot의 확장된 형태로 볼 수 있는데, 이미 Join과 Aggregation이 되어 있으므로 쿼리의 속도가 훨씬 빨라진다. 예를 들어, 지역별/제품별/월별 판매량을 가지는 MView가 만들어져 있고, 사용자가 지역별/연도별로 제품의 총판매액을 보는 쿼리를 실행할 경우 Optimizer는 이 쿼리를 수행하기 위해 판매 Fact Table과 제품, 시간, 지역 테이블들을 Join & Aggregation하는 비용과 MView에서 그 결과를 만드는 비용을 비교하게 되는데, MView에서 가져오는 것이 효율적이라면 자동적으로 SQL문을 재작성한다Query Rewrite기능.

이렇게 함으로써 Join과 Aggregation Cost를 줄일 수 있으므로 월등한 성능 향상을 기대할 수 있는 것이다. 이 경우 월별로 MView가 생성되어 있으므로 연도별로 Aggregation이 필요한데, 이는 시간에 대해 ‘일 < 월 < 연도’와 같이 계층을 가지는 Dimension을 생성해 두면 된다.

MView에도 성능 향상을 위해 인덱스를 생성할 수 있으며 파티션도 가능하다. 그리고, Base Table의 변경을 MView에 반영하는 방법은 일정 시간 혹은 특정 이벤트별로 전체를 읽어와서 재작성하는 Complete Refresh 방식과 변경된 부분만 반영시키는 Fast Refresh 방식이 있다.

 

 

향상된 인덱스 기능

Bitmap Index
Bitmap Index는 Update와 같이 특정 값을 찾아야 하는 OLTP 시스템에서는 적합하지 않으며, OLAP와 같이 주로 여러 조건을 가지는 Select가 많이 발생될 때 유리하다. 값의 종류만큼의 Digit이 생기며 각각의 Digit은 각 값의 상태를 나타낸다. |표 1|에서와 같이 100은 서부 지역을 의미하며 010은 중부, 001은 동부 지역을 의미하는 것이다.

기혼이면 1, 미혼이면 0을 의미한다고 할 때 중부나 서부에 살면서 결혼한 고객의 수를 구하는 쿼리가 있다고 가정해 보자.

결과값이 1인 것의 총 수가 중부나 서부에 사는 기혼 고객의 수가 된다.

|표 2|에서 보듯이 Bitmap Index를 이용하면 모든 경우에 대해 조건을 비교해 보는 대신 Bitmap 연산을 통해 간단히 결과를 도출해 낼 수 있다. Bitmap Index는 칼럼 Column의 Cardinality 인덱스를 생성하려는 칼럼들의 값의 가능한 조합 수, 한 칼럼이면 다른 값의 수가 적은 경우에 유리하며, 비교적 적은 CPU 수와 메모리 환경에서도 훌륭한 성능을 보장하며, 다른 인덱스에 비해 저장공간을 훨씬 적게 차지한다. |그림 3|의 왼쪽 그래프는 총 레코드 건수가 1,000,000건일 때 Cardinality에 따른 저장 공간을 비교한 것이고, 오른쪽 그래프는 총 5,000,000건의 레코드에 대해 비교한 것이다.



Function Based Index
Function Based Index는 함수의 결과를 인덱스 구조로 저장하는 것인데, 연산의 결과에 인덱스가 생성되므로 조건절에서 자주 이용되는 함수에 인덱스를 생성하면 성능을 월등히 향상시킬 수 있다. 이는 한 테이블에 대한 Mview 기능과 동일하며, Derived Column보다 적은 저장공간을 차지한다.

CREATE INDEX SALES_MARGIN_IDX
ON SALES (REVENUE - COST);

SELECT CUSTOMER_ID FROM SALES
WHERE REVENUE - COST > 10000 ;
==> sales_margin_idx 인덱스를 이용한다.



Bitmap Join Index
Oracle9i에서 소개된 Bitmap Join Index는 테이블들 사이의 조인에 인덱스를 생성하는 것으로 Join Cost를 줄임으로써 월등한 성능향상을 가져왔다. Sales Fact Table과 Customer Dimension 사이에 Join Index를 생성하는 예는 다음과 같다.

CREATE BITMAP INDEX cust_sales_bji
ON Sales(Customer.state)
FROM Sales, Customer
WHERE Sales.cust_id = ustomer.cust_id;

이 인덱스를 사용하면 아래의 쿼리는 심지어 Customer Table을 액세스하지 않아도 된다.

SELECT SUM(Sales.dollar_amount)
FROM Sales, Customer
WHERE Sales.cust_id = Customer.cust_id
AND Customer.state = ‘California’;

 

분할 기법

분할 기법Partitioning은 테이블이나 인덱스를 ‘파티션 Partition’이라는 더 작은 단위로 분할하는 것으로 병렬처리와 맞물려 엄청난 성능 향상을 보장한다. 한 테이블이나 인덱스를 가용한 여러 디스크에 균등하게 분배함으로써 디스크 경합이 최소화되고 CPU 작업의 로드 밸런싱 Load Balancing이 가능해진다.

성능
입력, 수정, 삭제의 DML 작업이 파티션 단위로 병렬 실행이 가능하고, 병렬 인덱스 스캔도 가능하다. 또 Optimizer는 테이블이 파티션으로 구성되어 있음을 인식하고 전체 테이블이 아닌 조건에 해당되는 파티션만을 참조하도록 질의를 최적화한다 Partition Pruning.

관리
데이타 로딩, 백업과 복구, 인덱스 생성, Import, Export 작업이 파티션 단위로 수행이 가능하다. 즉 주기적인 유지 보수 작업은 좀더 작은 단위로 최소 시간 내에 병렬로 수행 가능하며, 다른 파티션에 있는 데이타에 대한 질의, DML 및 유지 보수 작업에 영향을 주지 않는다.

가용성
일부분의 손상에 의해 데이타 전체를 사용할 수 없는 일반적인 테이블과는 달리 여러 개의 파티션으로 나뉘어진 테이블의 경우, 손상된 파티션을 제외한 다른 파티션은 정상적으로 사용할 수 있다.

Table Partitioning
특정 칼럼 값의 범위에 따라 테이블을 분할하는 Range Partitioning, Hash Function에 근거하여 분할하는 Hash Partitioning, 그리고 이 두가지 방법을 같이 사용하는 Composite Partitioning 방법이 있다. | 그림 4 |
이를 위한 생성 SQL문은 아래와 같다.

CREATE TABLE sales(s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER)
TABLESPACE TS_SALE_DATA01
   PCTFREE 0   PCTUSED 40
   INITRANS 1   MAXTRANS 255
STORAGE (
   INITIAL 10M NEXT 10M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS unlimited )
nologging
parallel 16
PARTITION BY RANGE (saledate)
SUBPARTITION BY HASH (customerid) SUBPARTITIONS 16
store in
(TS_SALE_DATA01,TS_SALE_DATA02,TS_SALE_DATA03,TS_SALE_DATA04)
(PARTITION sal94q1 VALUES LESS THAN TO_DATE (01-APR-1994, DD-MON-YYYY),
PARTITION sal94q2 VALUES LESS THAN TO_DATE (01-JUL-1994, DD-MON-YYYY),
PARTITION sal94q3 VALUES LESS THAN TO_DATE (01-OCT-1994, DD-MON-YYYY),
PARTITION sal94q4 VALUES LESS THAN TO_DATE (01-JAN-1995, DD-MON-YYYY))
(PARTITION sal95q1 VALUES LESS THAN TO_DATE (01-APR-1995, DD-MON-YYYY),
PARTITION sal95q2 VALUES LESS THAN TO_DATE (01-JUL-1995, DD-MON-YYYY),
PARTITION sal95q3 VALUES LESS THAN TO_DATE (01-OCT-1995, DD-MON-YYYY),
PARTITION sal95q4 VALUES LESS THAN TO_DATE (01-JAN-1996, DD-MON-YYYY));



List Partitioning
이는 Oracle9i에서 소개된 것으로 기존의 Range Partitioning을 보강한 개념이다. 즉, 파티션의 기준 칼럼 값의 범위에 따라 테이블을 분할하는 것이 아니라 아래 예에서 보듯이 특정 값들을 파티션마다 지정하여 그 값에 따라 테이블을 분할하는 것이다.

CREATE TABLE sales_history ( ... )
PARTITION BY LIST (country) (
PARTITION europe VALUES (‘United Kingdom’, ‘Germany’, ‘France’),
PARTITION north_america VALUES (‘United States’, ‘Canada’, ‘Mexico’),
PARTITION south_america VALUES (‘Brazil’, ‘Argentina’),
PARTITION asia VALUES (‘Korea’, ‘China’,’Japan’);



Partition-wise Join
|그림 5|에서 보듯이 해당 파티션끼리의 조인 16개가 동시에 진행된다. |그림 4|의 예에서 Sales Table을 Customerid에 대해서 16레벨로 Hash Partitioning하였으므로 Customer Table도 Customerid에 대해서 16레벨로 Hash Partitioning이 되어 있어야 |그림 5|와 같이 Full Partition-wise Join을 하고, 그렇지 않을 경우 Partial Partition-wise Join이 일어난다.

인덱스 생성
인덱스 생성 방법은 Local, Global Partitioning, Global Non-partitioning 등이 있으며, 파티션된 테이블에는 로컬 인덱스가 최상의 성능을 보장한다. 아래 예는 파티션된 테이블에 인덱스를 Parallel하게 생성하는 것을 보여 준다. Nologging으로 Rollback을 위한 과정을 생략함으로써 성능을 향상시키고, Compute Statistics로 인덱스 생성 시에 cost 기반 Optimizer가 사용하는 통계치 생성을 위한 분석 작업을 동시에 수행하게 된다.

CREATE BITMAP INDEX sales_1999JAN_cust idx on sales(cust_id) tablespace (sales_index) NOLOGGING parallel (degree 8) COMPUTE STATISTICS local;

  

병렬 처리

병렬 처리 Parallel Processing는 DW와 같이 대용량의 데이타를 다루기 위한 필수적인 방법으로, 대량의 데이타를 가지는 Table Scan, Join, 큰 테이블과 인덱스 생성, Bulk Loading, Insert, Update, Delete 등에서 하나의 작업을 여러 개의 개별적인 작업으로 동적 재분할하고 작업량을 여러 개의 프로세서에 고르게 분산시켜 CPU, 메모리 등의 자원을 최대한 활용함으로써 작업 성능을 극대화시킨다. 그리고, 파티션 단위의 Lock을 제공함으로써 Parallel DMLInsert, Update, Delete 작업은 파티션 단위로 나뉘어 수행된다. 병렬 처리가 충분한 효율을 보장하기 위해서는 다음과 같은 조건들이 만족되어야 한다.

  • SMP, MPP, Cluster 혹은 NUMA Architecture
  • 충분한 I/O Bandwidth
  • 낮은 CPU 사용률
  • Sort, Hasing 같은 메모리 집약적인 작업을 위한 충분한 메모리와 I/O Buffers

만약 위의 특성들 중에 하나라도 만족되지 않는 부분이 있다면, 병렬 처리로 인한 월등한 시스템 성능 향상을 기대하지 못할 수도 있으며, 과부하의 시스템이나 작은 I/O Bandwidth에서는 오히려 시스템의 성능을 저하시킬 수 있음을 명심해야 할 것이다.


병렬 처리 관련 패러미터 자동 세팅
PARALLEL_AUTOMATIC_TUNING = true로 세팅하면 병렬 처리에 관련된 모든 패러미터들이 자동으로 세팅된다. 즉, Parallel_adaptive_multi_user, Processes, Sessions, Parallel_max_servers, Parallel_execution_message _size, large_pool_size 등이 정해진 룰에 따라 자동으로 세팅된다. 물론 위의 패러미터들을 수정할 수 있으나 최적의 시스템을 위해 검증된 룰을 사용하므로 따로 수정하지 않을 것을 권고한다.


병렬 데이타 로딩
데이타를 SQL*Loader로 병렬 로딩Parallel Loading함으로써 로딩 시간을 줄일 수 있다. 파티션된 테이블에 병렬로 로딩하는 방법은 4가지 방법이 있는데 각각의 경우에 대해서 살펴보자.


① 입력되는 데이타 파일이 파티션된 것과 똑같은 방법으로 나눠져 있을 경우.

SQLLDR DATA = 95Q1.dat direct = true control = 95Q1.ctl
...
SQLLDR DATA = 95Q4.dat direct = true control = 95Q4.ctl


위와 같이 4개의 SQLLDR 프로세스를 동시에 실행시키면 된다. 이 방식은 인덱스가 있어도 상관없으며 I/O 경합, Space Fragment 등이 발생할 염려도 없다. 단, Load into fact partition(95Q1)와 같이 Control File에 파티션명을 지정해야 한다.

② 입력되는 데이타 파일이 임의의 방법으로 임의의 개수로 나눠져 있을 경우.
입력되는 파일의 수만큼 PARALLEL = true를 명시하여 SQLLDR 프로세스를 실행한다.

SQLLDR DATA = File1.dat DIRECT = true PARALLEL = true </TD< tr>

이때 인덱스는 없어야 하며, 한 파티션에 여러 프로세스가 동시에 쓰기(Write)를 시도할 수 있으므로 I/O 경합이 발생한다.


③ 입력되는 데이타 파일이 테이블스페이스를 구성하는 데이타 파일과 같은 방법으로 나눠져 있을 경우.
예를 들어, 테이블스페이스가 4개의 디스크에 걸쳐 생성되어 있으며, 한 테이블이 8개의 파티션으로 나눠져 2개의 테이블스페이스에 분배되어 있을 경우이다. 이때 Input File을 8개로 나눈 뒤에 SQLLDR DATA = file1.dat DIRECT = true PARALLEL = true FILE = /dev/D1.dbf 와 같은 프로세스 8개를 동시에 실행한다.
이 방법은 로드에 대해 정확한 통제를 할 수 있으므로 정확하게 데이타를 골고루 분배할 수 있다는 장점을 가지나 두 번째 방법과 같이 I/O 경합이 발생한다.


④ 모든 파티션들이 하나의 테이블스페이스에 있을 경우
이 경우는 Input File을 데이타 파일 수만큼 나눈 뒤 파일별로 SQLLDR을 방법 ③과 같이 실행시킨다. 이 방법은 세 번째 방법과 같은 장점을 지니나, 모든 파티션이 한 테이블스페이스에 존재하므로 가용성(Availability)이 떨어진다는 단점이 있다.


병렬 DDL, DML
테이블, 인덱스를 병렬로 생성DDL 할 수 있으며, Insert, Update, Delete를 병렬로 작업DML 할 수 있다. 이때 보통 CPU의 2배로 Parallel Degree를 잡을 때 최상의 성능이 보장된다.

* PCTAS(Parallel Create Table As Select) 예제
CREATE TABLE summary PARALLEL NOLOGGING AS SELECT /*+ parallel (degree, 4) */ dim_1, dim_2, SUM (meas_1)
FROM facts
GROUP BY dim_1, dim_2;

* Parallel Update Table 예제
UPDATE /*+ PARALLEL (emp,5) */ emp
SET sal=sal * 1.1
WHERE job=’CLERK’
and deptno in
(SELECT deptno FROM dept
WHERE location=’DALLAS’);
</TD< tr>

참고로, 한 테이블의 대부분의 데이타를 업데이트해야 하지만 CTAS로 재생성이 가능한 상황이라면 기존의 테이블을 지우고 PCTAS로 재생성하는 것이 속도나 자원 소요 측면에서 훨씬 유리할 수 있다. 단 그 테이블을 CTAS하기 위해서 대용량의 테이블들을 조인해야 한다면 그 비용과 업데이트 비용을 비교해봐야 할 것이다.


병렬 분석
DW에서의 Optimizer Mode는 Cost BaseOPTIMIZER_ MODE = CHOOSE로 해야 DW의 유용한 기능을 제대로 적용시킬 수 있다. 그러나, Optimizer가 제대로 기능을 발휘하기 위해서는 모든 오브젝트에 대해서 분석이 되어 있어야 한다. DW의 테이블은 대부분 대용량의 데이타를 가지고 있기 때문에 분석하는 데 상당한 시간을 소비하게 된다.

Oracle8i 이후로 병렬 분석을 지원하며, Oracle9i부터는 자동으로 적절한 Sampling Percentage와 Histograms을 위한 칼럼들을 결정한다. DBMS_STATS란 패키지를 통해 이 기능들을 제공하는데, 인덱스에 대한 통계치를 모으는 DBMS_STATS.GATHER _INDEX_STATS, 테이블, 칼럼, 인덱스에 대한 통계치를 모으는 DBMS_STATS.GATHER_ TABLE_STATS, 그 스키마Schema내의 모든 오브젝트에 대한 통계치를 모으는 DBMS_STATS.GATHER_SCHEMA_STATS, 그리고, 해당 데이타베이스 내의 모든 오브젝트에 대한 통계치를 모으는 DBMS_STATS.GATHER_DATABASE _STATS가 있다. 그리고, 이 패키지는 전 데이타의 0.000001%부터 100%까지 샘플링할 수 있다.

다음은 Scott 사용자의 D_Sales 테이블을 Parallel Degree 32로 전체 데이타의 0.1%에 대해서 분석하는 예제이다.

exec dbms_stats.gather_table_stats(‘scott’, ‘D_Sales’, NULL, 0.1, TRUE, ‘FOR ALL COLUMNS SIZE 1’, 32, ‘DEFAULT’, FALSE, NULL, NULL, NULL);

이렇게 분석된 통계정보는 Export 받은 뒤에 다시 Import 할 수 있으므로 최적의 플랜이 구성되었다면 계속 이를 활용할 수 있으며, 분석하는 시간을 절약할 수 있다.

 

로딩의 편리성

실제 대용량의 DW 프로젝트에서 전 단계의 소요시간 중 60% 이상이 ETL Extraction, Transformation, Loading 단계에서 소요된다. 이 단계에서의 시간를 최소한으로 줄이고, 편리한 관리를 위해 Oracle에서는 다양한 방법과 제품을 제공한다. OWBOracle Warehouse Builder, Parallel DDL & DML, Symmetric Replication, Transportable Tablespace, Direct-path Load API, 새로운 DML 기능, External Table, 대량의 데이타를 벌크 로딩하는 SQL*Loader, 이기종 데이타베이스 로딩을 위한 게이트웨이 등이 그것이다.

Transportable Tablespace
기존 OLTP의 대량의 데이타를 DW 개발용 Stage DB로 로딩하기 위해서 별도의 프로그램이나 노력없이 테이블스페이스를 바로 이동하는 방법이다. 이는 데이타 파일을 카피하고 테이블스페이스의 메타 데이타를 Export/Import하면 되므로 빠른 시간 내에 쉽게 로딩하는 방법이다. 단, 이 기능은 양쪽의 DBMS 버전이 Oracle8i 이상이고, Character Set, OS, Block 크기 등이 같아야 한다는 제약이 있다. 그러나, 이로 인한 기대효과는 이 제약들을 충분히 상쇄하고도 남을 만한 것이다.

External Table
Oracle9i에서 소개된 개념으로, 외부 데이타, 예를 들어 Flat Files을 보통 DB 테이블과 같이 다룰 수 있게 해주는 기능이다. External Table은 SQL로 접근하게 되는데, SQL, PL/SQL, Java로 바로 병렬 조회가 가능하다. 이는 한 번의 작업으로 데이타의 변형과 로딩을 병렬로 처리할 수 있으므로 ETL을 위한 획기적인 기능이라 할 수 있다.

새로운 DML 기능
Oracle9i에서 소개된 새로운 DML로 Upsert, Multi-table Inserts가 있다. Upsert는 조건에 따라서 입력되는 로우Row 들을 선택적으로 업데이트하거나 Insert하는 기능이다. 한 번의 명령으로 업데이트 혹은 Insert를 수행하므로 속도를 향상시킬 수 있고, 편리성을 제공한다. 그리고, 한 번의 Insert로 여러 테이블에 동시에 데이타를 입력하는 기능의 추가로 속도와 편리성에서 향상을 꾀하였다.

Oracle9i에서의 이런 기능들을 조합해서 사용함으로써 엄청난 성능의 향상과 편리성을 기대할 수 있다. 예를 들어, 하나의 SQL문으로 Flat File로부터 데이타를 직접 조회하여External Table 데이타베이스 내의 다른 테이블의 데이타와 조인하며, 테이블 함수를 통해 복잡한 트랜스포메이션Transformation을 적용하면서 여러 테이블에 동시에 결과를 입력한다. 이런 모든 작업이 한 SQL문으로 그리고 병렬로 처리되므로 속도와 편리성을 생각해보면 엄청난 효과를 기대할 수 있는 것이다.

Direct-path Load API
이는 써드파티 제품에 Direct-Path Loading 기능을 부여해 주는 Program Interface Set으로 파일 대신 스트림Stream으로 데이타를 변형해서 직접 테이블로 로딩할 수 있게 해주므로 ETL 단계에서의 복잡성을 줄이고, 훌륭한 성능과 편리성을 제공한다. |그림 6|은 OCIOracle Call Interface API를 통해 데이타를 스트림으로 테이블에 디렉트 로딩하는 것이다.

 

DW용 분석 함수

Oracle8i부터 표준 SQL에서 제공하지 않던 분석용 함수Analytic Function들을 지원한다. 이를 통해 Ranking, 이동 평균, 작년대비 올해 매출액비교와 같은 Lead/Lag Comparison 등을 지원하므로 이를 위한 개발 부담을 없애고 성능을 크게 향상시켰다.

분석 함수를 이용한 조회는 3단계로 이루어지는데, |그림 7| 에서 보듯이 첫 단계는 모든 Join, Where, Group by, Having 등을 수행하고, 두 번째 단계에서는 첫 단계의 결과 세트를 분석 함수에 적용시키고, 세 번째 단계에서는 Order by를 위한 정렬을 수행한다. 우선 새로운 그루핑Grouping 방법에 대해 소개한 뒤, 분석 함수를 크게 4개 Family로 구분하여 설명하고, 예제를 통해 이해하도록 하자.

새로운 그루핑 방법
Oracle9i에서 새로 소개된 그루핑 방법으로, Grouping Sets, Concatenated Grouping Sets, Composite Columns가 있다.

Grouping Sets
이는 정확한 Aggregation Level을 지정할 수 있게 해준다. 예를 들어,

SELECT year, region, product, sum(sales)
FROM Fact_Sales
GROUP BY
GROUPING SETS((year, region, product),
(year, product), (region, product));

와 같은 SQL문은 (year, region), (product, year), (pro- duct), (region, product) 각각의 그룹에 대한 Aggregation을 계산해준다.

Concatenated Grouping Sets
이 기능은 더 많은 그룹의 조합을 정확하게 생성할 때 사용하는데, 예를 들어, 위의 예에서 GROUP BY GROUPING SETS(month, year), GROUPING SETS(region, country)로 하게 되면 (month, region), (month, country), (year, region), (year, country)에 대한 4개의 그룹 세트를 만들고 각각에 대한 Aggregation을 계산하게 된다. 즉, GROUPING SETS끼리 Cross-product가 실행되고 가능한 모든 조합에 대한 그룹 세트가 생성되고 Aggregation이 수행되는 것이다.

Composite Columns 여러 칼럼들을 묶어서 하나의 Composite Column을 만들고 그루핑 시에 하나의 단위로 취급되게 한다. 예를 들어, GROUP BY ROLLUP (year, (quarter, month), day)에 대한 결과는 (year, quarter, month, day), (year, quarter, month), (year), ()으로 그루핑되어 계산된다. 여기서 (quarter, month)는 Composite Column으로 하나의 칼럼으로 취급된다.

Ranking Family
이 패밀리는 ‘각 지역별 판매량 상위 10위, 하위 10위의 세일즈맨을 보여라’, 혹은 ‘각 지역별 25% 이상 판매 증가율을 보인 세일즈맨은 누구인가?’와 같은 질문에 대한 답을 줄 수 있는 함수들이다. 여기에 속하는 함수들로는 RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE 등이 있다.

서울지역의 제품별 판매량의 순위를 보는 쿼리로 RANK 함수를 사용한 예를 보자.

SELECT region, product, amount, salesmanid,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) as rank
from sale
where region=’서울’;
[결과]

REGION    PRODUCT AMOUNT  SAL          RANK
---------------------------------------------
서울         TV           531           i02            1
서울         TV           523           i04            2
서울         TV           123          i01            3
서울         TV           12            i03            4
서울         video        223           i03            1
서울         video        123           i01            2
서울         video       13            i02            3
서울         video        12            i04            4

Window Aggregate Family
이 패밀리는 ‘13주의 주식 값의 이동 평균은?’ 혹은 ‘각 지역별 누적 판매량?’과 같은 질문에 답을 주는 함수들이다. 기존에 있던 모든 Aggregate Function들에 대해서 Moving & Cumulative Processing을 지원한다. 다음 예제는 현재일을 기준으로 이전 30일 값들의 평균을 계산한다. 아래는 현재일을 기준으로 30일간의 판매량에 대한 평균을 구하는 예제이다.

SELECT ordid, orderdate, productid, quantity as 판매량, avg(quantity) over (order by orderdate RANGE INTERVAL ‘30’ DAY PRECEDING) as 이동평균
from orders;

결과

ORDID

ORDERDATE

PRODUCTID

판매량

이동평균


0
1
12
2
8
3
4

01-JAN-98
02-JAN-98
31-JAN-98
05-FEB-98
14-FEB-98
07-MAR-98
11-MAY-98

2
1
3
3
1
2
1

35
23
62
123
25
51
35

35
29
40
92.5
70
66.333
35


Reporting Aggregate Family
Percent-of-total, Market share등과 같은 계산을 보다 간결하게 해주는 함수들로서, 사용자로 하여금 같은 로우에 대해 서로 다른 Aggregation Level 상에서 계산한 값을 사용할 수 있도록 해 준다. Join Operation을 할 필요 없이 사용자는 각 세부 로우에 Aggregate Value를 비교할 수 있게 된다. 이 패밀리는 AVG, SUM, MIN, MAX, COUNT, VARIANCE, STDDEV 등의 모든 SQL 함수들에 대해 Report Aggregate Processing을 제공한다. 아래는 전체 매출에서 각 제품의 판매량이 차지하는 비율을 구하는 예제이다.

SELECT product, SUM(amount) as 판매량,
RATIO_TO_REPORT(SUM(amount)) OVER( ) AS 전체 매출대비 비율
from sale
group by product;

PRODUCT

판매량

전체 매출대비 비용


TV
video
세탁기
에어컨
전화기

1683
1250
882
1747
397

.282429938
.209766739
.148011411
.293169995
.066621916

LAG/LEAD Family
이 패밀리는 하나의 테이블에 속해 있는 서로 다른 로우들의 비교에 관한 것이다. 이렇게 하나의 테이블에 속한 서로 다른 로우를 비교하려면 이전에는 Self-join을 사용할 수밖에 없었는데, 이것은 효율적이지 못하였고, 만들기도 그리 쉽지 않았다. 이 함수를 사용하면 전년대비 당해 연도의 매출액 비교와 같은 쿼리에 답할 수 있다.

SELECT year, sum(sales) as 당해년도 매출액,
LAG(SUM(sales),1) OVER (ORDER BY time_key) as 전년도 매출액,
sum(sales)-LAG(SUM(sales),1) OVER (ORDER BY time_key) as 매출증감액
from sales_fact
group by time_key;

[결과]

연도

당해년도 매출액

전년도 매출액

매출증감액


93
94
95
96
97
98
99

2490.143
1960.7955
1340.7115
1201.061
3030.645
4203.163
3922.994


2490.143
1960.7955
1340.7115
1201.061
3030.645
4203.163


-529.3475
-620.084
-139.6505
1829.584
1172.518
-280.169

이외에 Group by를 확장한 Aggregation 함수로서 Rollup, Cube가 있고, Data Mining이나 통계분석용으로 유용한 Random Sampling 기능이 있다.

Rollup & Cube
Rollup은 GROUP BY 순서에 의한 멀티레벨 소계 및 총계를 구하는 함수이며, Cube는 CrossTab 형태의 소계를 계산할 때 사용한다.

SELECT 연도, 공급자, SUM(수량)
FROM 판매
GROUP BY ROLLUP(연도, 공급자) ;

연도

공급자

SUM(수량)


1998
1998
1998


1999
1999
1999


 

S
L
 


S
L
 


250
251
501


300
299
599


1100



SELECT 연도, 공급자, SUM(수량)
FROM 판매
GROUP BY CUBE(연도, 공급자) ;

연도

공급자

SUM(수량)


1998
1998
1998


1999
1999
1999


 

S
L
 


S
L
 


S
L

250
251
501


300
299
599


550
550
1000

Random Sampling 기능
이는 테이블 전체의 데이타를 가져오는 것이 아니라 정해준 퍼센티지만큼의 데이타만을 임의로 추출해 오는 기능으로 데이타 마이닝이나 통계 분석용으로 샘플 세트를 구성할 때 유용하다.

SELECT date, product, region, sales_amount
FROM sales_fact sample (1)
ORDER BY date;

이는 전체 로우의 1%만을 임의로 추출하는데 sample block(1)로 지정하면, 블록 단위로 추출하며 .000001%에서 100%미만까지 지정할 수 있다.

 

DW용 서버 패러미터 설정

OLTP와 다르게 DSS용 쿼리는 보통 대용량의 데이타를 다뤄야 하므로 이에 맞게끔 자원 사용을 위한 패러미터를 설정해야 한다. 모든 패러미터가 DW용으로 적절히 구성되어야 하겠지만, 여기서는 꼭 고려해야 할 패러미터들에 대해서만 언급하겠다.

·  DB_BLOCK_SIZE

이는 데이타베이스 생성시에 결정되는데, DW용으로는 되도록 크게 잡는 것이 좋다. 크게 함으로써 I/O 로드가 감소하고, Chaining을 줄이며, Space 사용의 효율성을 높일 수 있다.

·  DB_BLOCK_BUFFERS

DB_BLOCK_SIZE와 DB_BLOCK_BUFFERS를 곱한 값이 DATA BUFFER CACHE의 크기가 되므로 DB_BLOCK_BUFFERS를 통해 DATA BUFFER CACHE의 크기를 조정한다. 이것은 디스크의 데이타를 메모리에 저장하는 공간이므로 크게 잡음으로써 디스크 I/O를 줄일 수 있다.

·  PARALLEL_MAX_SERVERS

병렬 처리 시에 가용한 최대 프로세스 수를 지정하는 것으로 ‘2 [ykpark1]( DOP (Degree Of Parallelism) ( Current User수’로 세팅할 것을 권고한다. 이 값은 PARALLEL_ AUTOMATIC_TUNING이 TRUE로 설정되면 자동으로 그 값이 정해진다.

·  PARALLEL_MIN_SERVERS

인스턴스가 기동될 때 병렬 처리를 위해 기동되는 프로세스 수를 정하는 것으로 0으로 세팅할 것을 권고한다.

·  HASH_JOIN_ENABLED

Hash Join을 수행할지를 정하는 것으로 True로 설정한다.

·  ALWAYS_ANTI_JOIN

Anti Join을 수행할 때 사용할 조인 타입을 정하는 것으로 HASH로 설정한다.

·  SORT_AREA_SIZE

Sort용으로 사용될 PGA 메모리의 최대 크기를 설정하는 것으로 되도록이면 크게 설정한다(Bytes 단위).

·  HASH_AREA_SIZE

Hash Join으로 사용될 최대 메모리 크기를 설정하는 것으로, 디폴트는 SORT_AREA_SIZE의 2배이다.

·  STAR_TRANSFORMATION_ENABLED

Cost-based Optimizer가 쿼리를 분석할 때 Star Transformation를 가능하게 하는 것으로 TRUE로 설정한다.

·  SHARED_POOL_SIZE

PARALLEL_AUTOMATIC_TUNING이 FALSE로 설정되면 Large Pool에 대한 설정을 하지 않으므로, 프로세스는 Large Pool에서 버퍼를 할당받지 않고, Share Pool에서 할당받는데, 여러 사용자가 동시에 사용한다면 이를 충분히 크게 설정하여 성능을 향상시킨다. 단, 필요에 따라 Large Pool을 직접 설정해도 된다.

 

결론

이상에서 DW를 위한 많은 고려 사항들 중 대표적인 기능들에 대해서만 살펴보았다. 그러나, 실제로 하드웨어와 OS의 특성이 동시에 고려되어야만 최상의 시스템 구성이 가능할 것이다. 소개된 여러 기능들이 DW를 이해하는 데 많은 도움이 되고, 업무에 많은 참조가 되길 바란다.


참고문헌
시스템 통합의 핵심기술 - 복병학, 이재우 지음 - 대청
데이터 웨어하우징 정보 센터 (http://pwp.starnetinc.com/larryg/index.html)
데이터 웨어하우징 협회 (http://www.dw-institute.com)
국제 데이터 웨어하우징 협회 (http://www.idwa.org)
데이터 웨어하우스 프로젝트 (http://www.env.gov.bc.ca/ssb/projects/warehouse)
데이터 웨어하우스 전략을 사용한 분산 데이터 액세스 구현
(http://www.psgroup.com/cases/1994/cs994d.htm)
美 CIO지에서는 CIO가 관심을 가질만한 이벤트 (http://www.cio.com/CIO/rc_event.htm)
DCI 홈페이지 (http://www.dciexpo.com/)
OLAP 용어 가이드 (http://www.kenan.com/acumate/olaptrms.htm)
OLTP와 OLAP 기술에 관련된 용어를 담고 있다. 프라임 마케팅 출판
(http://www.pmp.co.uk/feb48.htm)
데이터 웨어하우징 백서 (http://pwp.starnetinc.com/larryg/whitepap.html)

Trackback 1 Comment 0