일반적으로 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분 이상 소요되는 쿼리를 실행한 세션을 자동적으로 다른 그룹으로 변경함으로써 그룹 내 다른 세션의 성능에 영향을 미치지 않게 한다.
대량의 데이타를 가지는 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 |
Bitmap Join Index
Oracle9i에서 소개된 Bitmap Join Index는 테이블들 사이의 조인에 인덱스를 생성하는 것으로 Join Cost를 줄임으로써 월등한 성능향상을 가져왔다. Sales Fact Table과 Customer Dimension 사이에 Join Index를 생성하는 예는 다음과 같다.
CREATE BITMAP INDEX cust_sales_bji |
이 인덱스를 사용하면 아래의 쿼리는 심지어 Customer Table을 액세스하지 않아도 된다.
SELECT SUM(Sales.dollar_amount) |
분할 기법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) |
List Partitioning
이는 Oracle9i에서 소개된 것으로 기존의 Range Partitioning을 보강한 개념이다. 즉, 파티션의 기준 칼럼 값의 범위에 따라 테이블을 분할하는 것이 아니라 아래 예에서 보듯이 특정 값들을 파티션마다 지정하여 그 값에 따라 테이블을 분할하는 것이다.
CREATE TABLE sales_history ( ... ) |
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 |
위와 같이 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) 예제 |
참고로, 한 테이블의 대부분의 데이타를 업데이트해야 하지만 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를 통해 데이타를 스트림으로 테이블에 디렉트 로딩하는 것이다.
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) |
와 같은 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, 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 이동평균
| ||||||||||||||||||||
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 판매량,
| |||||||||
LAG/LEAD Family
이 패밀리는 하나의 테이블에 속해 있는 서로 다른 로우들의 비교에 관한 것이다. 이렇게 하나의 테이블에 속한 서로 다른 로우를 비교하려면 이전에는 Self-join을 사용할 수밖에 없었는데, 이것은 효율적이지 못하였고, 만들기도 그리 쉽지 않았다. 이 함수를 사용하면 전년대비 당해 연도의 매출액 비교와 같은 쿼리에 답할 수 있다.
SELECT year, sum(sales) as 당해년도 매출액,
| ||||||||||||
이외에 Group by를 확장한 Aggregation 함수로서 Rollup, Cube가 있고, Data Mining이나 통계분석용으로 유용한 Random Sampling 기능이 있다.
Rollup & Cube
Rollup은 GROUP BY 순서에 의한 멀티레벨 소계 및 총계를 구하는 함수이며, Cube는 CrossTab 형태의 소계를 계산할 때 사용한다.
SELECT 연도, 공급자, SUM(수량)
| ||||||||||||||||||
Random Sampling 기능
이는 테이블 전체의 데이타를 가져오는 것이 아니라 정해준 퍼센티지만큼의 데이타만을 임의로 추출해 오는 기능으로 데이타 마이닝이나 통계 분석용으로 샘플 세트를 구성할 때 유용하다.
SELECT date, product, region, sales_amount |
이는 전체 로우의 1%만을 임의로 추출하는데 sample block(1)로 지정하면, 블록 단위로 추출하며 .000001%에서 100%미만까지 지정할 수 있다.
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)
'wif LiNoUz > Oracle,Sql' 카테고리의 다른 글
| 데이터 웨어하우스 (DW) 구축을 위한 노하우 (0) | 2014.11.04 |
|---|---|
| 옵티마이저와 신나게 노는 방법 [최미영 엔코아정보컨설팅] (0) | 2014.10.31 |
| ERD 커멘트 이용(펌) (0) | 2014.10.28 |
| 힌트 모음 (0) | 2014.06.19 |
| Undo 정리 (1) | 2014.06.19 |