ADWCML/Lab200.md at master · OracleCloudDigital/ADWCML

소개

이 실습에서는 해미라는 인물을 사용합니다. 해미는 ADWC가 프로젝트에 들어가는 막대한 시간을 단축시키고 빠르게 프로젝트가 마무리되는 것을 기대합니다. 그러나 그녀는 ADWC가 대량의 데이터에 대해 수행할 수 있다는 것을 증명해야 합니다. 이를 위해 그녀는 사내 DB 중 테스트 스키마를 일부 추출하여 이것을 Object Storage에 저장하고 ADWC 인스턴스에 로드하였습니다.

Autonomous Data Warehouse Cloud는 데이터베이스에 연결할 때 선택할 수있는 세 가지 데이터베이스 서비스를 제공합니다. 사용자는 HIGH, MEDIUM, LOW 서비스 중 하나로 접속할 수 있고 이 접속 종류에 따라 다양한 수준의 성능과 동시성을 제공합니다.

HIGH 데이터베이스 서비스는 쿼리에 대해 최대 리소스를 제공하므로이 서비스에서 실행할 수있는 동시 쿼리 수(concurrency - 실행관점)가 다른 서비스만큼 많지 않음을 의미합니다.

MEDIUM 데이터베이스 서비스는 쿼리에 대해 여러 컴퓨팅 및 IO 리소스를 제공합니다. 또한 이 서비스는 HIGH 데이터베이스 서비스와 비교하여 더 많은 동시성(concurrency - 실행관점)을 제공합니다.

LOW 데이터베이스 서비스는 쿼리에 대해 최소한의 리소스를 제공하므로이 서비스에서 실행할 수있는 동시 쿼리 수가 다른 서비스보다 많다는 것을 의미합니다.

사용자는 성능 및 동시성 요구 사항에 따라 데이터베이스 서비스를 선택해야합니다. 이러한 서비스 레벨 간의 차이점은 작업 부하 및 특정 쿼리에 따라 다르지만 'LOW'서비스에 대한 쿼리가 'HIGH'서비스에 대해 두 배 빠른 속도로 실행되는 것을 볼 수 있습니다. 이 실습에서는 'HIGH'서비스를 사용합니다.

우리는 ADWC에 내장되어 있는 SSB 스키마를 사용할 것입니다. 이 데이터는 5년 동안의 주문 내역을 담고 있습니다. Data Scientist 나 마케팅팀은 종종 중장기 경향을 연구하기 위해 이러한 데이터를 사용합니다.

SSB.DWDATE - 2556 개의 행
SSB.SUPPLIER - 2,000,000 개의 행
SSB.PART - 2,000,000 개의 행
SSB.CUSTOMER - 30,000,000 개의 행
SSB.LINEORDER - 6,000,000,000 개의 행

다음은 lab1에서 샘플링한 데이터는 현재 데이터라고 가정합니다.

ADMIN.DWDATE - 2556 행
ADMIN.SUPPLIER ~ 2,000,000 개의 행
ADMIN.PART - 1,400,000 개의 행
ADMIN.CUSTOMER - 6,200,000 개의 행
ADMIN.LINEORDER - 행 100,000,000 개

목표

현재 데이터 및 historical한 데이터에 대한 쿼리 성능을 테스트함으로써 대용량의 분석이 얼마나 빠르게 처리되는지 확인합니다.

성능 테스트

위에서 언급했듯이 해미는 최종 사용자가 1억개의 현재 주문 건수 및 140만개의 부품, 600만 명의 고객 및 200만개의 공급 업체에 대한 쿼리를 문제 없이 실행할 수 있도록 보장하고자 합니다. 여기서 테이블 및 데이터베이스에 대한 튜닝은 전혀하지 않고 테스트할 것입니다. 또한 데이터 엑세스 방식에 관계없이 성능 유지를 확인하는 다양한 쿼리를 실행할 것입니다.

1 단계 : SQLDeveloper를 사용하여 ADWC에 연결

테이블 확장

2단계 : 쿼리 실행

아래를 실행하여 result cache를 flush한 다음 일반적인 집계형 쿼리를 실행합니다.
월 별 수익 합계를 추출하라.

exec DBMS_RESULT_CACHE.flush;

set pagesize 1000;

select d_month, d_monthnuminyear
, sum(lo_revenue)
from lineorder_100M
, dwdate
where lo_orderdate = d_datekey
group by d_month, d_monthnuminyear
order by d_monthnuminyear

쿼리는 1억건의 주문을 쿼리하고 요약하는데 몇 초가 걸립니다.
이제 동일하거나 유사한 쿼리를 실행하는 다른 사용자가 현재 캐시 된 결과를 사용하는지 확인해 봅시다. 이렇게 하면 DB가 캐시된 결과를 검색하기만 하면 되므로 많은 양의 처리를 하지 않아도 됩니다. 캐시를 지우지 않고 동일한 쿼리를 실행해 봅니다.

쿼리가 훨씬 빠르게 실행됩니다.
다음 쿼리는 국가 별, 연도 별, 지역 별 필터링을 걸어 수익을 뽑아보도록 하겠습니다. 국가는 미국으로 한정지어서 말이죠.

select c_nation
, d_month
, sum(lo_revenue) as revenue
from customer, lineorder_100M, dwdate
where lo_custkey = c_custkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
group by c_nation
, d_month
order by revenue desc;

다시 한번 쿼리에 몇 초가 걸리고 월별 및 국가 별 그룹화가 이루어져서 매출이 내림차순으로 정렬됩니다.
좀 더 선택적으로 필요한 값만 뽑아내는 쿼리는 어떨까요? 다음을 실행해 봅시다.

select d_date
    , p_partkey
    , p_name
    , p_color
    , sum(lo_quantity) quantity
    , sum(lo_revenue) revenue
    , sum(lo_supplycost) supplycost
    , sum(lo_revenue-lo_supplycost) profit
from lineorder_100m
, DWDATE
, part
where p_partkey = lo_partkey
and d_datekey = lo_orderdate
and d_yearmonth = 'Sep1996'
AND d_dayofweek = 'Friday   '
AND p_color in ('ivory','coral')
and lo_orderpriority = '1-URGENT'
group by d_date
, p_partkey
, p_name
, p_color

인덱스, pre-sorting, pre-load processing 없이도 훨씬 빨리 수행되는 것을 확인할 수 있습니다.

주문 내역에 대한 쿼리 실행

주문 내역은 60억개의 행을 가진 데이터로 위에서 다뤘던 현재의 데이터보다 60배 가량 더 큽니다. 이 데이터에 일시적으로 엑세스 권한을 주고 쿼리를 하는 유형은 언제 발생할까요? 예를 들어 장기간에 걸친 패턴을 밝히기 위한 용도로 Data Scientist 들이 해당 데이터에 대한 엑세스 요청을 할 수 있습니다. 이들은 과거에 데이터를 추출하고 데이터 전처리에 수 주일이 걸린 소규모 그룹입니다. 그들은 실시간 데이터에 즉시 엑세스하고 몇 분안에 결과를 얻을 수 있습니다.

3 단계 : Historical 쿼리 실행

result cache를 지워 데이터베이스 성능을 정확하게 측정합니다. SQLDevelopler에서 다음을 입력하세요.

exec DBMS_RESULT_CACHE.flush;

select d.d_date
    , p.p_partkey
    , p.p_name
    , p.p_color
    , sum(l.lo_quantity) quantity
    , sum(l.lo_revenue) revenue
    , sum(l.lo_supplycost) supplycost
    , sum(l.lo_revenue-l.lo_supplycost) profit
from ssb.lineorder l
, SSB.DWDATE d
, ssb.part p
where p.p_partkey = l.lo_partkey
and d.d_datekey = l.lo_orderdate
and d.d_yearmonth = 'Aug1996'
AND d.d_dayofweek = 'Friday   '
AND p.p_color in ('ivory','coral')
group by d.d_date
    , p.p_partkey
    , p.p_name
    , p.p_color;

제 경우는 2OCPU 에서 61초 정도의 elapsed_time이 있었습니다. 응답 시간을 좀 더 단축시키는 방법은 없을 까요?

4단계 : auto-scaling 활용하기

ADWC에는 사용하는 리소스(cpu, io 등)에 따라 자동으로 scaling하는 기능이 있습니다. 콘솔에서 auto-scaling 을 체크하면 자동 확장 기능을 사용할 수 있습니다.

auto-scaling 을 실행할 때 ADWC는 Scale Up/Down 팝업 창에 현재 표시된 OCPU 수에 지정된 것보다 최대 3배의 CPU 및 IO 리소스를 사용할 수 있습니다. 자동 확장 기능이 활성화되면 작업 부하에 따라 추가 CPU 및 IO리소스가 필요한 경우 데이터베이스는 수동 개입없이 리소스를 자동으로 사용합니다. 한동안 자원을 사용하지 않을 시 데이터베이스는 다시 원래 지정된 자원(CPU 및 IO 리소스)로 돌아옵니다.

비용은 한시간 동안 사용한 CPU 개수의 평균을 내어 책정됩니다.

다시 클라우드 콘솔로 돌아가서 [Scale Up/Down] 을 들어갑니다.
AUTO SCALING 에 체크하고 Update 합니다.

이렇게 파라미터를 변경하면 인스턴스는 온라인 상태에서 연결을 끊지 않고 변경 사항을 적용하기 때문에 기존에 실행하던 쿼리나 실행은(read/write 모두) 끊기지 않고 계속 적용됩니다.

다시 SQLDeveloper로 돌아와 동일한 쿼리를 실행합니다. 정확한 database 성능 측정을 위해 result cache를 flush한 후 실행합니다.

exec DBMS_RESULT_CACHE.flush;

select d.d_date
    , p.p_partkey
    , p.p_name
    , p.p_color
    , sum(l.lo_quantity) quantity
    , sum(l.lo_revenue) revenue
    , sum(l.lo_supplycost) supplycost
    , sum(l.lo_revenue-l.lo_supplycost) profit
from ssb.lineorder l
, SSB.DWDATE d
, ssb.part p
where p.p_partkey = l.lo_partkey
and d.d_datekey = l.lo_orderdate
and d.d_yearmonth = 'Aug1996'
AND d.d_dayofweek = 'Friday   '
AND p.p_color in ('ivory','coral')
group by d.d_date
    , p.p_partkey
    , p.p_name
    , p.p_color;

61초 걸리던 쿼리가 23초로 단축되었습니다.

cpu_count 파라미터를 조회해보면 12개로 할당된 것을 확인할 수 있습니다.
이것은 auto-scaling 체크박스를 해제하지 않는 이상 줄어들지 않습니다.
이유는 오라클이 미리 auto-scaling 을 위해 3배 만큼의 cpu 할당량을 예약해 놓았기 때문입니다. 이것은 실제로 cpu가 할당되어 해당 리소스를 사용했을 때만 사용시간으로 측정되고 1시간에 사용한 cpu 리소스의 평균으로 비용이 계산됩니다.

5단계 : Analytic Views (계층구조 쿼리) 쿼리하기

오라클의 Analytic Views 기능을 활용하여 데이터를 좀 더 다각도로 분석해 보도록 합니다.

Analytic View는 OLAP cube를 encapsulate한 형태로 저장된 오라클 데이터베이스 구조입니다. 이를 통해 데이터를 계층적 모델(hierarchical model)로 조직화하고 집계 데이터 및 계산된 measures(차원)값으로 확장할 수 있습니다. BI(Business Intelligence)의 데이터 모델링을 해보셨던 분들이라면 Oracle Business Intelligence의 RPD라고 생각하면 좋을 것 같습니다. 데이터는 view 형태로 제공되기 때문에 쉽게 SQL 문으로 쿼리할 수 있습니다.

앞으로의 실습을 이해하기 위해 Analytics Views(AV)에서 제공하는 3개의 object type을 소개합니다.

Dimension Attribute - 계층적 레벨과 함께 차원(dimension)을 정의합니다. (여러 계층 구조 가능)
Hierarchy - 차원의 계층 구조(상위/하위)를 정의합니다.
Analytic View - 계층을 하나로 결합하고 측정 값(measures)을 정의합니다.

아래의 그림은 위에서 설명한 바를 다이어그램으로 표현합니다.

Analytics View의 장점은 무엇일까요? 테이블에서 선택하는 일반적인 쿼리에는 FROM 절, 조인 및 집계 (GROUP BY)의 각 테이블이 포함됩니다. 계수 계산이 필요한 경우 SELECT 계산식을 SELECT 목록에 표시해야합니다. FROM 절, 조인, 집계 및 계수 계산은 분석 뷰의 정의에 포함됩니다. 따라서 분석 뷰에서 선택하는 쿼리가 훨씬 간단해질 수 있습니다. 이 방법에는 다음과 같은 몇 가지 이점이 있습니다.

복잡한 SELECT 문을 작성하거나 생성하는 것 보다 AV를 사용하는 것이 훨씬 쉽습니다.
계산 규칙은 AV에 한번 정의할 수 있고 계속 재사용할 수 있습니다.
Analytics View에 대한 자세한 사항은 이 링크(https://www.wegobeyond.co.uk/blog/an-introduction-to-analytic-views-in-oracle-12-2) 를 참조하세요.
해미는 현업으로부터 분석 쿼리에 대한 지원 요청을 받고 있습니다. 오라클 데이터베이스에서 지원하는 Analytic View를 통해 요청받은 분석을 신속하고 쉽게 처리하도록 하겠습니다.
SQLDeveloper 를 열고 접속 중인 session 에 '다른 사용자'를 클릭하여 view를확장합니다.
분석 뷰(Analytics View)를 클릭하여 펼치면 아래처럼 이미 생성된 각각 속성에 대한 Dimension view와 Hierarchy view, Analytics View(이 예제에서는 lineorder에서 추출한 SSB_AV 라는 view)에 대한 Analytic view 를 확인할 수 있습니다.
이제 아래의 분석 쿼리를 실행해보세요.
1998년도에 발생한 지역 및 나라별로 발생한 주문 수량(lo_quantity)과 수익(lo_revenue)을 뽑는데 파트별로 보고 싶습니다.

SELECT
  dwdate_hier.member_name as 월,
  part_hier.member_name as 파트,
  customer_hier.c_region as 지역,
  customer_hier.member_name as 고객,
  lo_quantity,
  lo_revenue
FROM  ssb.ssb_av
  HIERARCHIES (
    dwdate_hier,
    part_hier,
    customer_hier)
WHERE
  dwdate_hier.d_year = '1998'
  AND dwdate_hier.level_name = 'MONTH'
  AND part_hier.level_name = 'MANUFACTURER'
  AND customer_hier.c_region = 'AMERICA'
  AND customer_hier.level_name = 'NATION';

저의 경우는 34초가 소요되었습니다.

Note : 몇몇 쿼리는 처리하는 행수와 aggregation level에 따라 더 오래 걸릴 수도 있습니다.

Database Activity 모니터링 하기

클라우드 콘솔에 로그인 하고 Autonomous Data Warehouse 를 클릭하여 Service Console 을 open합니다.
ADW 데이터베이스의 service console에 들어갑니다.

자신의 ADWC 서비스 콘솔에 들어오시면 DB 관리에 필요한 여러가지 메뉴가 있습니다. 그 중 DB의 성능 관련 모니터링과 트러블 슈팅을 할 수 있는 [Performance Hub] 메뉴를 클릭합니다.

이 Performance Hub은 다음과 같은 용도로 사용할 수 있습니다.

Active Session History (ASH) 분석
- 애플리케이션 성능 이슈 진단
- wait event에 대한 drill down 원인 분석
실시간 SQL 모니터링
- 리소스를 많이 사용하는 parallel 쿼리 및 복잡한 SQL문에 대한 성능 진단
- global 레벨(PL/SQL) 및 각 SQL 레벨에서의 성능 진단
- 각 SQL문에 대한 실행계획 분석

Performance Hub 화면입니다.

time range slide를 조정해가면서 원하는 시간대에 위치시킵니다.
ASH 분석 탭에서는 활성 세션에 대한 평균 활동 시간 등을 볼 수있습니다.
SQL Monitoring을 선택하시면 실행중인 (또는 실행이 끝난) SQL문에 대한 내용을 볼 수 있습니다.

결론

해미는 ADWC가 다른 주요 클라우드 공급 업체에 비해 현명한 대안 성능이라고 확신하고 있습니다. 그는 다운 타임없이 스토리지를 확장하고 리소스를 위 또는 아래로 조정할 수 있으며 데이터 과학자가 데이터에 액세스하여 마케팅 프로젝트의 머신러닝 모델을 실행할 준비가되었습니다.

lab 200을 완료했으며 lab 300으로 이동할 수 있습니다.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

소개

목표

성능 테스트

주문 내역에 대한 쿼리 실행

Database Activity 모니터링 하기

결론

FilesExpand file tree

Lab200.md

Latest commit

History

Lab200.md

File metadata and controls

소개

목표

성능 테스트

주문 내역에 대한 쿼리 실행

Database Activity 모니터링 하기

결론