OCI Autonomos Database - Automatic Partitioning

자동 파티셔닝(Automatic Partitioning)는 사전에 지정된 스키마에 포함된 테이블과 관련 인덱스에 대해 자동으로 워크로드를 분석 후 결과에 따라 파티셔닝을 적용하는 기능입니다. 적절한 파티셔닝 전략을 찾으려면 애플리케이션 워크로드와 데이터 배포에 대한 깊은 지식이 필요합니다. 수동 파티셔닝을 수행할 때는 워크로드를 분석하고 테이블과 인덱스에 파티셔닝을 적용하는 방법을 선택하여 애플리케이션의 성능을 향상시켜야 합니다. 자동 파티셔닝을 통해 Autonomous Database 사용자는 수동 스키마 및 워크로드 분석을 수행하지 않고도 파티셔닝의 이점을 얻을 수 있습니다.

해당 기능에 대해 아래와 같은 순서로 정리 해보도록 하겠습니다.

1. 자동 파티셔닝 동작 방식
2. 자동 파티셔닝 테스트 시나리오

1. 자동 파티셔닝 동작 방식

자동 파티셔닝 활성화 시 기존 파티셔닝 테이블에는 대상에서 제외되고 아직까지는 파티션 키는 단일키로만 지정이 되며 복합 파티셔닝은 지원되지 않습니다. AUTOMATIC INTERVAL, LIST AUTOMATIC, HASH 3가지 파티셔닝 방법을 제공하며, 해당 기능 관리 및 보고서 생성을 위해 PL/SQL API를 데이터베이스 내에서 제공합니다.

자동 파티셔닝은 자동 인덱싱과 다르게 백그라운드 작업으로 주기적으로 수행되지 않고, DBMS_AUTO_PARTITION.REGEND_PARTION_METHOD 함수를 호출 할 때만 실행되어 대상 테이블을 식별하고 파티셔닝을 권고해 줍니다.

전반적인 수행 순서는 아래와 같습니다.

1. 선택된 스키마, 테이블의 워크로도를 분석합니다. ADB인 경우는 기본적으로 자동 파티셔닝에 필요한 정보가 데이터베이스 내에서 자동으로 수집되고 있습니다.

2. 평가된 내용으로 파티셔닝 권고에 대한 검증을 시작합니다.

1) 테이블 통계정보를 가지고 있는 빈 파티션 테이블을 생성한 후 수집된 SQL를 이용해서 성능 검증을 합니다.

2) IO 감소 추정치가 제일 높은 파티셔닝 방안을 내부적으로 실제 구현하여 다시 한번 검증합니다.

3) 만약, 후보 파티셔닝의 성능 개선 추정치가 회귀 기준으로 향상되지 않는 경우 권장하지 않습니다.

3. 평가된 내용으로 파티셔닝 권고에 대한 검증을 시작합니다.

일반적으로 자동 파티셔닝 수행 시 워크로드 분석이나 권고안 도출/검증등의 작업은 데이터베이스 입장에서 고비용의 작업이여서 성능을 고려한 사용전략이 필요합니다.

우선, 해당 기능을 실제 운영 데이터베이스에서 활성화 할지에 대한 고려가 필요합니다. 운영 정책에 의해 운영 데이터베이스가 아닌 테스트나 검증 용도의 Clone DB를 사용한다면 기본적으로 운영 워크로드가 수집되어 포함된 SQL Tuning Set (SYS_AUTO_STS)이 필요합니다. ADB인 경우 이를 자동으로 수집하고 Clone DB 생성 시 함께 복제되기 때문에 바로 사용할 수 있습니다. 또한 분석을 진행할 시 목표로 하는 스키마나 테이블을 분류하여 명시하면 최적화된 작업에 도움이 됩니다.

파티셔닝 권고안을 검증하거나 적용하는 경우에도 내부적으로 검증을 위해 auxiliary table 생성하는 경우 기존 테이블의 1~1.5배의 공간이 더 필요하며, 파티션 테이블로 온라인 전환을 하는 경우도 대상 테이블의 사이즈와 수행되고 있는 DML수준에 따라 추가적인 CPU와 IO 리소스가 필요하여 업무적으로 peak time을 피해 수행하는 것이 좋습니다.

2. 자동 파티셔닝 테스트 시나리오

1) 우선 테스트 테이블을 생성하고 데이터를 입력하고 가상의 워크로드 즉 쿼리를 수행하여 STS에 기록될 수 있도록 합니다.

1) 테스트 테이블 생성

create table apart (
        a   number(10), 
        b   number(10), 
        c   number(10), 
        d   date, 
        pad varchar2(1000));


2) 생성된 테이블에 샘플 데이터 입력

insert /*+ APPEND */ into apart
with
r as ( select /*+ materialize */ dbms_random.string('x',500) str 
     from dual connect by level <= 2000 ),
d as ( select /*+ materialize */ to_date('01-JAN-2020') + mod(rownum,365) dte 
     from dual connect by level <= 2500 ),
m as ( select 1 
     from dual connect by level <= 3 )
select /*+ leading(m d r) use_nl(d r) */
rownum, rownum, rownum, dte, str
from m,d,r;

-- Commit the transaction
commit;



 3) 테스트 워크로드 수행
 
-- 입력한 데이터에 맞추어 조회 조건을 바꾸어 실제 어플리케이션 워크로드를 재현한다.
 select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-MAR-2020') and to_date('05-mar-2020');
 select /* TEST_QUERY */ sum(a) from apart 
 where d = to_date('01-MAR-2020');
 select /* TEST_QUERY */ sum(b) from apart 
 where d between to_date('01-JAN-2020') and to_date('05-JAN-2020');
 select /* TEST_QUERY */ sum(c) from apart 
 where d between to_date('01-APR-2020') and to_date('05-APR-2020');
 select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-JUN-2020') and to_date('02-JUN-2020');
 select /* TEST_QUERY */ sum(b) from apart 
 where d between to_date('01-DEC-2020') and to_date('31-DEC-2020');
 select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-AUG-2020') and to_date('31-AUG-2020');
 select /* TEST_QUERY */ sum(b) from apart 
 where d between to_date('01-OCT-2020') and to_date('01-OCT-2020');
 select /* TEST_QUERY */ sum(c) from apart 
 where d between to_date('01-FEB-2020') and to_date('05-FEB-2020');
 select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-MAY-2020') and to_date('02-MAY-2020');
 select /* TEST_QUERY */ avg(a) from apart 
 where d between to_date('01-JUL-2020') and to_date('02-JUL-2020');



4) ADB에서 자동으로 수집한 Automatic SQL Tuning Set 확인

-- 자동 파티셔닝에서 워크로드 분석을 위해 STS에 SQL정보가 수집이 되었는지 확인한다
 select current_timestamp now from dual;

 select task_name,
        status,
        enabled,
        interval,
        last_schedule_time, 
        systimestamp-last_schedule_time ago 
 from dba_autotask_schedule_control 
 where dbid = sys_context('userenv','con_dbid') 
 and   task_name like '%STS%';


/*

TASK_NAME             STATUS    ENABLED INTERVAL LAST_SCHEDULE_TIME       AGO              
--------------------- --------- ------- -------- ------------------------ ---------------- 
Auto STS Capture Task SUCCEEDED TRUE         900 2024-06-18T06:03:08.554Z +0 0:2:16.599323 
*/



 select sql_text 
 from   dba_sqlset_statements 
 where  sql_text like '%TEST_QUERY%'
 and    sqlset_name = 'SYS_AUTO_STS';

/*
~~~~
"select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-MAY-2021') and to_date('02-MAY-2021')"
"select /* TEST_QUERY */ sum(b) from apart 
 where d between to_date('01-DEC-2021') and to_date('31-DEC-2021')"
"select /* TEST_QUERY */ sum(b) from apart 
 where d between to_date('01-JAN-2021') and to_date('05-JAN-2021')"
"select /* TEST_QUERY */ sum(a) from apart 
 where d = to_date('01-MAR-2022')"
"select /* TEST_QUERY */ avg(a) from apart 
 where d between to_date('01-JUL-2021') and to_date('02-JUL-2021')"
"select /* TEST_QUERY */ sum(a) from apart 
 where d between to_date('01-MAR-2022') and to_date('05-mar-2022')"
 ~~~~~
 */

2. Validate 함수를 이용해서 사전 검증을 합니다.

검증 통과에 필요한 사전 조건은 아래 URL에서 참고하시면 됩니다.

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/ref-dbms_auto_partition-package.html#GUID-FFB58355-5A90-4506-AF5F-8615A0A3CCEF

 declare
 ret varchar2(1000);
 begin
     ret := dbms_auto_partition.validate_candidate_table (table_owner=>'ADMIN',table_name=>'APART');
     dbms_output.put_line(' ');
     dbms_output.put_line(' ');
     dbms_output.put_line('Auto partitioning validation: ' || ret);
 end;
 /

-- 통계정보 생성 필요한 경우
-- Auto partitioning validation: INVALID: table statistics are stale 

-- 테이블이 너무 작아 자동 파티셔닝 대상이 안되는 경우
-- Auto partitioning validation: INVALID: table is too small (8.8 GB actual, 64 GB
required)


-- 사전 검증이 성공할 경우
Auto partitioning validation: VALID

3. 권고안 분석 후 바로 파티셔닝 적용보다는 보고서 형태로 먼저 권고안을 확인하는 설정과 대상 스키마를 설정합니다.

-- 실제 파티셔닝을 바로 적용하지 않고 보고서 생성만 하도록 설정
exec dbms_auto_partition.configure('AUTO_PARTITION_MODE','REPORT ONLY');

-- ADMIN 스키마만 대상으로 적용
BEGIN
    DBMS_AUTO_PARTITION.CONFIGURE(
         PARAMETER_NAME  => 'AUTO_PARTITION_SCHEMA', 
         PARAMETER_VALUE => 'ADMIN',
         ALLOW           => TRUE);
END;
/

-- 설정내용은 아래 뷰에서 확인가능
select * from DBA_AUTO_PARTITION_CONFIG  ;

/*
PARAMETER_NAME                  PARAMETER_VALUE               LAST_MODIFIED        MODIFIED_BY 
------------------------------- ----------------------------- -------------------- ----------- 
AUTO_PARTITION_SCHEMA           schema IN (&quot;ADMIN&quot;) 2024-06-18T09:19:14Z ADMIN       
AUTO_PARTITION_TABLE            0                             null                 null        
AUTO_PARTITION_REPORT_RETENTION 90                            null                 null        
AUTO_PARTITION_MODE             REPORT ONLY                   2024-06-18T09:17:21Z ADMIN     
*/

4. 파티셔닝 권고안 분석을 수행합니다.

자동 파티셔닝 권고안은 자동 인덱싱과 다르게 dbms_auto_partition.recommend_partition_method 을 호출할 때 1회성으로 수행됩니다. 수행 이후에 TEXT나 HTML 형태로 보고서를 확인할 수 있습니다.

 declare
   r raw(100);
   cursor c1 is
        select partition_method, partition_key, report
        from   dba_auto_partition_recommendations
        where recommendation_id = r;

 begin
    r :=
       dbms_auto_partition.recommend_partition_method(
        table_owner    => 'ADMIN',
        table_name     => 'APART',
        report_type    => 'TEXT',
        report_section => 'ALL',
        report_level   => 'ALL');

 for c in c1
 loop
    dbms_output.put_line('=============================================');
    dbms_output.put_line('ID:     '||r);
    dbms_output.put_line('Method: '||c.partition_method);
    dbms_output.put_line('Key   : '||c.partition_key);
    dbms_output.put_line('=============================================');
 end loop;

 end;
 /
 
 
 
 -- 리포트를 HTML 형태로 저장할 경우
 spool autoPartitionFinding.html
select dbms_auto_partition.report_last_activity(type=>'HTML') from dual;
exit;

-- 관련 뷰에서 조회
 select partition_method,partition_key 
from dba_auto_partition_recommendations
where generate_timestamp = 
  (select max(generate_timestamp) 
   from   dba_auto_partition_recommendations);

5. 보고서 내용을 리뷰하고 적용을 결정할 경우 아래와 같이 수행하여 적용합니다.

 -- 적용 시 수행이 예상되는 DDL 조회
 
 select modify_table_ddl 
from  dba_auto_partition_recommendations
where generate_timestamp = 
   (select max(generate_timestamp) 
    from   dba_auto_partition_recommendations)
order by recommendation_seq;


-- 파티션 권고 ID를 확인하고 적용

exec dbms_auto_partition.apply_recommendation('D28FC3CF09DF1E1DE053D010000AF8F8');

-- 파티션 정보 조회

dba_tables, dba_tab_partions, dba_segments 뷰등을 이용해서 최종 확인

감사합니다.

개인 시간을 투자하여 작성된 글로서, 글의 내용에 오류가 있을 수 있으며, 글 속의 의견은 개인적인 의견입니다.

'3. 데이터관리' 카테고리의 다른 글

OCI Autonomous Database 소개 - 1) 관리 콘솔 살펴보기 (0)	2024.06.19
AI Vector Search Overview (0)	2024.06.19
OCI Data Integration 활용 - Data Integration Task 작업 모니터링 하기 (0)	2024.06.19
Oracle 데이터베이스 보안 솔루션 #3 - Data Safe (3) - Data Masking (0)	2024.06.02
OCI PostgreSQL Backup & Recover (feat. 접속 방법) (0)	2024.05.22

오라클 클라우드 여행

OCI Autonomos Database - Automatic Partitioning

'3. 데이터관리' 카테고리의 다른 글

댓글

티스토리툴바

OCI Autonomos Database - Automatic Partitioning

'3. 데이터관리' 카테고리의 다른 글

관련글

댓글

티스토리툴바