알프레스코 ORACLE 파티셔닝 테스트

알프레스코의 기본 테이블인 ALF_NODE 와 ALF_PROPERITES 및 TRANSACTION 테이블은 과도한 데이터가 쌓일 경우, 조회뿐만 아니라, CRUD에서 WAIT 때문에, 도저히 성능이 안되더군요. 첨엔 Creator 기준으로 사용자별로 분배를 했지만, System/Admin 계정 문제등으로 제대로 파티셔닝이 안되어, 그냥 ID 기준으로 Hashing 을 사용하고, properties 는 alf_node 기준으로 FK대상으로 분배했습니다. 

1. 주테이블을 파티셔닝으로 생성

//1. alf_node 를 생성자별로 파티션을 분할함. 

CREATE TABLE ALFRESCO.ALF_NODE_PART2 ( 

    ID             NUMBER(19,0) NOT NULL,

    VERSION       NUMBER(19,0) NOT NULL,

    STORE_ID       NUMBER(19,0) NOT NULL,

    UUID           VARCHAR2(36) NOT NULL,

    TRANSACTION_ID NUMBER(19,0) NOT NULL,

    TYPE_QNAME_ID NUMBER(19,0) NOT NULL,

    LOCALE_ID     NUMBER(19,0) NOT NULL,

    ACL_ID         NUMBER(19,0) NULL,

    AUDIT_CREATOR VARCHAR2(255) NULL,

    AUDIT_CREATED VARCHAR2(30) NULL,

    AUDIT_MODIFIER VARCHAR2(255) NULL,

    AUDIT_MODIFIED VARCHAR2(30) NULL,

    AUDIT_ACCESSED VARCHAR2(30) NULL,

    PRIMARY KEY(ID)

NOT DEFERRABLE

VALIDATE

)

PARTITION BY HASH (ID) 

PARTITIONS 128

CREATE TABLE ALFRESCO.ALF_NODE_PROPERTIES_PART2 ( 

    NODE_ID           NUMBER(19,0) NOT NULL,

    ACTUAL_TYPE_N     NUMBER(10,0) NOT NULL,

    PERSISTED_TYPE_N   NUMBER(10,0) NOT NULL,

    BOOLEAN_VALUE     NUMBER(1,0) NULL,

    LONG_VALUE         NUMBER(19,0) NULL,

    FLOAT_VALUE       FLOAT NULL,

    DOUBLE_VALUE       FLOAT NULL,

    STRING_VALUE       VARCHAR2(2048) NULL,

    SERIALIZABLE_VALUE BLOB NULL,

    QNAME_ID           NUMBER(19,0) NOT NULL,

    LIST_INDEX         NUMBER(10,0) NOT NULL,

    LOCALE_ID         NUMBER(19,0) NOT NULL,

    PRIMARY KEY(NODE_ID,QNAME_ID,LIST_INDEX,LOCALE_ID) NOT DEFERRABLE VALIDATE,

    CONSTRAINT FK_ALF_NPROP_N_PART2 FOREIGN KEY(NODE_ID) REFERENCES ALFRESCO.ALF_NODE_PART2(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE

)

PARTITION BY REFERENCE (FK_ALF_NPROP_N_PART2);

ALTER TABLE ALFRESCO.ALF_NODE_PROPERTIES_PART2

    ADD ( CONSTRAINT FK_ALF_NPROP_N_PART2

FOREIGN KEY(NODE_ID)

REFERENCES ALFRESCO.ALF_NODE_PART2(ID)

NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )

//drop table ALFRESCO.ALF_NODE_PARTTEST

//drop table ALFRESCO.ALF_NODE_PROPERTIES_PARTTEST

insert into ALF_NODE_PART2

select * from alf_node

insert into ALF_NODE_PROPERTIES_PART2

select * from ALF_NODE_PROPERTIES

2. 분리한 테이블 기준으로 인덱스 재생성 및 Analyzing 

//인덱스와 analyze 는 재분석해야 한다. 

select 'ALTER INDEX ' || index_name || ' REBUILD;' from user_indexes where 

table_name in ('ALF_NODE_PROPERTIES_PART2', 'ALF_NODE_PART2')

//and tablespace_name = 'SWPINDEX';

select distinct 'ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;' from user_indexes where 

table_name in ('ALF_NODE_PROPERTIES_PART2', 'ALF_NODE_PART2')

and tablespace_name = 'SWPINDEX';

ANALYZE TABLE ALF_NODE_PROPERTIES_PART2 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

ANALYZE TABLE ALF_NODE_PART2 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

3. 분할된 테이블 데이터 확인

//TABLE 파티셔닝 정보 상세 확인

SELECT * 

FROM DBA_TAB_PARTITIONS 

where table_name = 'ALF_NODE_PROPERTIES_PART2'

;

//TABLE 인덱스 파티셔닝 정보 상세 확인

SELECT * 

FROM DBA_IND_PARTITIONS

;

4. 끝으로, 병렬 쿼리 수행 테스팅 (PLAN)

//===========================================================

//                                  병렬 쿼리 

//===========================================================

explain plan set statement_id = 'SQL1' for 

    select

            /*+ PARALLEL(alf_node_part2 4, alf_node_properties_part2 4)*/

  parentNode.uuid as id, 

  count(*) as totalMailCount, 

  sum(case when (prop_readYN.boolean_value is null or prop_readYN.boolean_value = 0) then 1 else 0 end) as unreadMailCount, 

  sum(prop_mailSize.long_value) as boxSize

from

   alf_child_assoc assoc

   join alf_node_part2 parentNode on (parentNode.id = assoc.parent_node_id) 

   join alf_node_part2 childNode on (childNode.id = assoc.child_node_id)

   left join alf_node_properties_part2 prop_readYN on (prop_readYN.node_id = childNode.id and prop_readYN.qname_id = 336)

   left join alf_node_properties_part2 prop_mailSize on (prop_mailSize.node_id = childNode.id and prop_mailSize.qname_id = 355)

where

    assoc.parent_node_id = 16149

and childNode.type_qname_id = 327

group by parentNode.uuid

order by totalMailCount

;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'TYPICAL'));