쿼리 튜닝 실습(partiton, use_hash 힌트, like/between, 윈도우 함수)

※ 문제

CREATE TABLE DEV.T_ORDER61

   (CUST_NO     VARCHAR2(6)   ,

    ORDER_DT    VARCHAR2(8)   ,

    ORDER_SN    NUMBER        ,

    ORDER_AMT   NUMBER DEFAULT 0,

    CONSTRAINT PK_T_ORDER61 PRIMARY KEY(CUST_NO, ORDER_DT, ORDER_SN)

   );

 CREATE TABLE DEV.T_ORDER_PROD61

   (CUST_NO     VARCHAR2(6)   ,

    ORDER_DT    VARCHAR2(8)   ,

    ORDER_SN    NUMBER        ,

    PROD_NO     VARCHAR2(5)   ,

    AMT         NUMBER DEFAULT 0  ,

    CNT         NUMBER ,

    CONSTRAINT PK_T_ORDER_PROD61 PRIMARY KEY(CUST_NO, ORDER_DT, ORDER_SN, PROD_NO)

   );   /*

T_ORDER61,  T_ORDER_PROD61은 ORDER_DT로 월별 파티션되어 있음.    파티션명칭 : PTYYYYMM

아래 SQL을 보고 튜닝하세요.

※ 실제 오라클에 환경 구성을 하고 아래의 SQL을 수행해도 아래와 같은 실행계획은 나오지 않습니다.

   아래와 같이 실행계획이 나왔다고 가정하고 최적의 튜닝을 수행해 보세요.  */

INSERT INTO T_AGGRE61

SELECT /*+ NO_QUERY_TRANSFORMATION LEADING(C A B) USE_NL(A)  USE_NL(B)

           INDEX(A PK_T_ORDER61) INDEX(B PK_T_ORDER_PROD61) */

       A.CUST_NO, A.ORDER_DT, A.ORDER_SN, A.ORDER_AMT,

       B.PROD_NO, B.PRICE,

       C.ORDER_CNT, C.ORDER_PROD_CNT

FROM T_ORDER61 A,  T_ORDER_PROD61 B,

     (SELECT /*+ INDEX(X IX_T_ORDER_PROD61) */

             CUST_NO, ORDER_DT, COUNT(*) ORDER_CNT

         , COUNT(DISTINCT PROD_NO) ORDER_PROD_CNT

      FROM  T_ORDER_PROD61 X

      WHERE ORDER_DT LIKE '201605%'

      GROUP BY CUST_NO, ORDER_DT

      ) C

WHERE B.ORDER_DT  LIKE '201605%'

 AND  A.CUST_NO  = C.CUST_NO

 AND  A.ORDER_DT = C.ORDER_DT

 AND  B.CUST_NO  = A.CUST_NO

 AND  B.ORDER_DT = A.ORDER_DT

 AND  B.ORDER_SN = A.ORDER_SN

;

COMMIT;

 

실행계획의 Buffers 값이 크게 나오기 때문에 해당 쿼리는 튜닝이 필요한 것을 알 수 있습니다. 

T_ORDER61과 T_ORDER_PROD61 두 테이블이 파티션이기 때문에 조회 조건이 적절한지 확인해야 합니다.

LIKE는 인덱스 사용이 안되기 때문에 영어로 BETWEEN 변경 후 결과를 봐야합니다. 

인라인 뷰를 수정하여 HASH 조인 등 변경하여 결과를 봐야합니다. 

 

※ 정답 

/* 주문일자별  년월 RANGE PARTITION  

   튜닝 키

     - 인라인 뷰를 없애고  윈도우 함수로 변경

     - 인덱스 스캔이 아닌 파티션 풀 스캔으로 수정

     - LIKE가 아닌 BETWEEN 으로 수정

*/

ALTER SESSION SET STATISTICS_LEVEL = ALL;

ALTER TABLE T_AGGRE61 NOLOGGING ;

INSERT /*+ APPEND */ INTO T_AGGRE61

SELECT /*+ ORDERED  USE_HASH(B) */

       A.CUST_NO, A.ORDER_DT, A.ORDER_SN, A.ORDER_AMT,

       B.PROD_NO, B.CNT,

       COUNT(*) OVER(PARTITION BY B.CUST_NO, B.ORDER_DT)                   ORDER_CNT,

       COUNT(DISTINCT B.PROD_NO) OVER (PARTITION BY B.CUST_NO, B.ORDER_DT) ORDER_PROD_CNT

FROM  T_ORDER61      PARTITION (PT201605) A,

      T_ORDER_PROD61 PARTITION (PT201605) B

WHERE A.ORDER_DT BETWEEN '20160501' AND '20160531'

 AND  B.CUST_NO   = A.CUST_NO

 AND  B.ORDER_DT  = A.ORDER_DT

 AND  B.ORDER_SN  = A.ORDER_SN

;

ALTER TABLE T_AGGRE61 LOGGING ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS PARTITION LAST'));

 

파티션 테이블은 해당 파티션을 풀스캔하는 것이 더 효율적일 수 있습니다. 

인라인 뷰를 수정하여 HASH 조인을 해주었으며, USE_HASH(B)를 선언하였고, T_ORDER_PROD61을 HASH 영역에 올리는 것을 의미합니다. T_ORDER61에 비교하여 T_ORDER_PROD61이 더 작기 때문에 HASH 대상 테이블이 됩니다.

LIKE가 아닌 BETWEEN을 사용해주었습니다. 또한, 대량 데이터를 INSERT하는 경우 APPEND 힌트를 사용하면 성능을 높일 수 있습니다.

 

※ 문제 실습 환경 (테이블 생성, 데이터 입력, 세션 설정 변경, 함수)

ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;

ALTER SESSION SET SORT_AREA_SIZE = 2000000000;

DROP TABLE   DEV.T_CUST61;

CREATE TABLE DEV.T_CUST61

   (CUST_NO    VARCHAR2(6) ,

    CUST_NM    VARCHAR2(50),

    ADDR       VARCHAR2(100),

    TEL        VARCHAR2(11),

    CONSTRAINT PK_T_CUST61 PRIMARY KEY(CUST_NO)

    );

   

CREATE OR REPLACE PUBLIC SYNONYM T_CUST61 FOR DEV.T_CUST61;

INSERT INTO T_CUST61

SELECT 'C'||LPAD(TO_CHAR(LEVEL), 5, '0')  CUST_NO ,

       'ABCDEF' || TO_CHAR(LEVEL)         CUST_NM ,

       '1234567ASDFFDSAASDFFDSAASDFFDSAA' ADDR    ,

       '01012341234'                      TEL

FROM DUAL

CONNECT BY LEVEL <= 10000;

COMMIT;

DROP   TABLE DEV.T_ORDER61;

CREATE TABLE DEV.T_ORDER61

   (CUST_NO     VARCHAR2(6)   ,

    ORDER_DT    VARCHAR2(8)   ,

    ORDER_SN    NUMBER        ,

    ORDER_AMT   NUMBER DEFAULT 0,

    CONSTRAINT PK_T_ORDER61 PRIMARY KEY(CUST_NO, ORDER_DT, ORDER_SN)

   )

PARTITION BY RANGE(ORDER_DT)

   (PARTITION PT201601 VALUES LESS THAN ('20160201'),

    PARTITION PT201602 VALUES LESS THAN ('20160301'),

    PARTITION PT201603 VALUES LESS THAN ('20160401'),

    PARTITION PT201604 VALUES LESS THAN ('20160501'),

    PARTITION PT201605 VALUES LESS THAN ('20160601'),

    PARTITION PT201606 VALUES LESS THAN ('20160701')

   );

CREATE OR REPLACE PUBLIC SYNONYM T_ORDER61 FOR DEV.T_ORDER61;

DROP TABLE DEV.T_DATE;

CREATE TABLE DEV.T_DATE AS

SELECT TO_CHAR(TO_DATE('20160101', 'YYYYMMDD') + LEVEL , 'YYYYMMDD') DT

FROM DUAL

CONNECT BY LEVEL <= 177

;

select * from t_date order by dt;

CREATE OR REPLACE PUBLIC SYNONYM T_DATE FOR DEV.T_DATE;

INSERT INTO T_ORDER61

SELECT A.CUST_NO, B.DT,

       ROW_NUMBER() OVER(PARTITION BY A.CUST_NO, B.DT ORDER BY CUST_NO, B.DT) ORDER_SN,

       ROWNUM * 10000 ORDER_AMT

FROM T_CUST61 A, T_DATE B,

     (SELECT LEVEL SNO

      FROM DUAL

      CONNECT BY LEVEL <= 10

     ) C

;

COMMIT;

DROP   TABLE DEV.T_ORDER_PROD61;

CREATE TABLE DEV.T_ORDER_PROD61

   (CUST_NO     VARCHAR2(6)   ,

    ORDER_DT    VARCHAR2(8)   ,

    ORDER_SN    NUMBER        ,

    PROD_NO     VARCHAR2(5)   ,

    AMT         NUMBER DEFAULT 0  ,

    CNT         NUMBER ,

    CONSTRAINT PK_T_ORDER_PROD61 PRIMARY KEY(CUST_NO, ORDER_DT, ORDER_SN, PROD_NO)

   )

PARTITION BY RANGE(ORDER_DT)

   (PARTITION PT201601 VALUES LESS THAN ('20160201'),

    PARTITION PT201602 VALUES LESS THAN ('20160301'),

    PARTITION PT201603 VALUES LESS THAN ('20160401'),

    PARTITION PT201604 VALUES LESS THAN ('20160501'),

    PARTITION PT201605 VALUES LESS THAN ('20160601'),

    PARTITION PT201606 VALUES LESS THAN ('20160701')

   );

CREATE OR REPLACE PUBLIC SYNONYM T_ORDER_PROD61 FOR DEV.T_ORDER_PROD61;

INSERT INTO T_ORDER_PROD61

SELECT A.CUST_NO, A.ORDER_DT, A.ORDER_SN,

       'P'|| LPAD(TO_CHAR(B.LVL), 4, '0') PROD_NO,

       ROWNUM * 1000 PRICE,

       MOD(ROWNUM, 5) AMT

FROM T_ORDER61 A,

     (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL <= 3) B

;

COMMIT;

CREATE INDEX DEV.IX_T_ORDER_PROD61 ON DEV.T_ORDER_PROD61(ORDER_DT) ;

DROP   TABLE DEV.T_PROD61;

CREATE TABLE DEV.T_PROD61 AS

SELECT  'P' || LPAD(LEVEL, 4, '0')  PROD_NO

       , 'ASDFFDSAASDFFDSA'         PROD_NM

      , TRUNC(DBMS_RANDOM.VALUE(1, 100))  DIV_CD

FROM    DUAL

CONNECT BY LEVEL <= 1000;

ALTER TABLE DEV.T_PROD61 ADD CONSTRAINT PK_PROD61 PRIMARY KEY(PROD_NO);

CREATE OR REPLACE PUBLIC SYNONYM T_PROD61 FOR DEV.T_PROD61;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_CUST61');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_ORDER61');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_ORDER_PROD61');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_PROD61');

DROP TABLE DEV.T_AGGRE61;

CREATE TABLE DEV.T_AGGRE61

  (CUST_NO        VARCHAR2(6)   ,

   ORDER_DT       VARCHAR2(8)   ,

   ORDER_SN       NUMBER        ,

   ORDER_AMT      NUMBER        ,

   PROD_NO        VARCHAR2(5)   ,

   PRICE          NUMBER        ,

   ORDER_CNT      NUMBER        ,

   ORDER_PROD_CNT NUMBER  

   );

   

CREATE PUBLIC SYNONYM T_AGGRE61 FOR DEV.T_AGGRE61;

ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;