SQL BOOSTER 이어지는 이야기 .06

<SQL을 변경한 성능 개선 .03>

 

SQL을 성능 개선 할 때, 가장 손쉬운 방법은 인덱스를 추가하는 것이다. 물론 인덱스로 성능이 개선될 수 있다면 말이다. 하지만, 그런 식으로 인덱스를 만들다 보면 데이터베이스에는 인덱스가 테이블보다 더 많은 용량을 차지하기 시작한다. SQL BOOSTER 본서 183페이지, ‘6.4.3 너무 많은 인덱스의 위험성’에서 설명했던 내용이다.

손쉬운 인덱스 추가보다는, 주어진 인덱스에서 성능을 개선할 방법을 찾는 것이 SQL 성능 개선의 첫 단계다. 인덱스는 그 다음 단계다.

 

SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

SQL_Booster_이어지는이야기06.pdf

0.26MB

 

아래 SQL을 살펴보자.

[SQL-1] T_ORD_JOIN을 집계 조회
SELECT  /*+ GATHER_PLAN_STATISTICS */
        T1.ORD_ST
        ,SUM(T1.ORD_QTY * T1.UNT_PRC) ORD_AMT
FROM    T_ORD_JOIN T1
WHERE   T1.ORD_YMD BETWEEN '20170101' AND '20170930'
AND     T1.ITM_ID = 'ITM020'
AND     T1.CUS_ID = 'CUS_0004'
GROUP BY T1.ORD_ST;

특정 고객의, 특정 아이템에 대해 1월1일부터 9월30일까지의 판매금액을 주문상태(ORD_ST)별로 집계하고 있다. 실행계획은 다음과 같다.

[실행계획-1] T_ORD_JOIN을 집계 조회
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | A-Rows |   A-Time   | Buffers | OMem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |      1 |00:00:00.11 |     257 |      |
|   1 |  HASH GROUP BY               |                |      1 |      1 |00:00:00.11 |     257 |  934K|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   2000 |00:00:00.01 |     257 |      |
|*  3 |    INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |  19000 |00:00:00.03 |      80 |      |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ITM_ID"='ITM020')
   3 - access("T1"."CUS_ID"='CUS_0004' AND "T1"."ORD_YMD">='20170101' AND "T1"."ORD_YMD"<='20170931')

원하는 결과를 얻기 위해 257번의 논리IO가 발생했다. 성능이 나쁜 SQL은 아니다. 하지만, 더 성능을 개선할 수 없을까 고민해보자. 성능을 개선하려면 비효율을 먼저 찾아야 한다. 비효율은 실행계획에 빨간색으로 표시해 놓았다. 실행계획의 3번 단계에서 19,000건을 찾았고, 2번 단계에서 ITM_ID 조건이 필터 되면서 17,000건이 버려졌다. T_ORD_JOIN에 19,000번 접근했지만, 그 중에 17,000번이 불필요한 접근인 것이다. 만약에 CUS_ID, ORD_YMD의 인덱스에 ITM_ID 컬럼도 있었다면 테이블에 접근한 후 버려지는 비효율이 발생하지 않았을 것이다. 그리고 CUS_ID, ITM_ID, ORD_YMD, ORD_ST 순서의 인덱스가 있었다면, 테이블을 접근하는 비효율 자체가 없었을 것이다. 하지만, 현재 가진 인덱스에서 해결해야 한다면 어떻게 해야 할까? 우선 T_ORD_JOIN에 어떤 인덱스가 있는지 살펴보자.

T_ORD_JOIN의 인덱스
INDEX_OWNER         TABLE_NAME         INDEX_NAME       IND_COLS             
============        ==========         =============    =====================
ORA_SQL_TEST	    T_ORD_JOIN	       PK_T_ORD_JOIN	ORD_SEQ
ORA_SQL_TEST	    T_ORD_JOIN	       X_T_ORD_JOIN_1	CUS_ID
ORA_SQL_TEST	    T_ORD_JOIN	       X_T_ORD_JOIN_2	CUS_ID,ORD_YMD
ORA_SQL_TEST	    T_ORD_JOIN	       X_T_ORD_JOIN_3	ORD_YMD
ORA_SQL_TEST	    T_ORD_JOIN	       X_T_ORD_JOIN_4	ITM_ID,ORD_YMD

그리고 다시 한번 [SQL-1]을 살펴보면서 어떻게 성능을 좀 더 개선 할 수 있을지 고민해보자. 실력을 키우고 싶은 독자라면 스스로 고민해보고 SQL을 작성해보기 바란다.

 

 

고민이 끝났다면, 필자의 생각과 같은지 다음 내용들을 읽어보기 바란다. 먼저 [SQL-1]의 WHERE절 조건들과 사용 가능한 인덱스를 정리해보면 아래와 같다.

- X_T_ORD_JOIN_1 : CUS_ID(=), ITM_ID(=), ORD_YMD(><)

- X_T_ORD_JOIN_2 : CUS_ID(=), ITM_ID(=), ORD_YMD(><)

- X_T_ORD_JOIN_3 : CUS_ID(=), ITM_ID(=), ORD_YMD(><)

- X_T_ORD_JOIN_4 : CUS_ID(=), ITM_ID(=), ORD_YMD(><)

 

노란색으로 표시된 부분은 해당 인덱스에서 사용할 수 있는 컬럼이다. [SQL-1]을 처리하기에 가장 좋은 인덱스는 X_T_ORD_JOIN_2와 X_T_ORD_JOIN_4다. 위의 실행계획에서도 X_T_ORD_JOIN_2 인덱스를 사용했다.

인덱스의 리프 블록에는 ROWID가 있다는 사실을 기억하고 있는가? 이 점을 항상 기억하기 바란다. 하나의 테이블에 종속된 인덱스들은 ROWID라는 공통 분모를 가지고 있다. 이 점을 이용하면 가지고 있는 인덱스 안에서 어느 정도의 성능 개선이 가능하다. 바로 아래와 같이 SQL을 바꿔보는 것이다.

[SQL-2] T_ORD_JOIN을 집계 조회 – 성능 개선
SELECT  /*+ NO_MERGE(T0) LEADING(T0 T3) USE_NL(T3) */
        T3.ORD_ST
        ,SUM(T3.ORD_QTY * T3.UNT_PRC) ORD_AMT
FROM    (
        SELECT  /*+ LEADING(T2) USE_HASH(T1) INDEX(T1 X_T_ORD_JOIN_2) INDEX(T2 X_T_ORD_JOIN_4) */
                T1.ROWID RID
        FROM    T_ORD_JOIN T1
                ,T_ORD_JOIN T2
        WHERE   T1.ORD_YMD BETWEEN '20170101' AND '20170930'
        AND     T1.CUS_ID = 'CUS_0004'
        AND     T2.ORD_YMD BETWEEN '20170101' AND '20170930'
        AND     T2.ITM_ID = 'ITM020'
        AND     T1.ROWID = TRIM(T2.ROWID)
        ) T0
        ,T_ORD_JOIN T3
WHERE   T3.ROWID = T0.RID
GROUP BY T3.ORD_ST

SQL이 굉장히 길고 복잡해졌다. 주의 깊게 볼 부분을 빨간색과 노란색으로 표시해 놓았다. T_ORD_JOIN 테이블이 세 번이나 출현하고 있다. SQL 성능을 위해서는 같은 테이블을 불필요하게 반복 사용해서는 안 된다. 필자가 줄 곧 해온 이야기며, 일반적으로 맞는 말이다. 하지만 인덱스의 구조를 정확히 알면, 위와 같이 SQL을 작성해 성능을 개선할 수 있다.

위 SQL은 X_T_ORD_JOIN_2 인덱스로 CUS_ID와 ORD_YMD 조건이 맞는 ROWID를 찾아내고, X_T_ORD_JOIN_4 인덱스로 ITM_ID와 ORD_YMD 조건에 맞는 ROWID를 찾아내 해시 조인 처리하고 있다. 해시 조인으로 얻은 두 인덱스간에 공통된 ROWID를 T_ORD_JOIN(T3)에 공급해 최종 결과를 얻어내는 방법이다. 실행계획을 살펴보자. 논리IO가 257에서 188로 줄어들었다.

[실행계획-2] T_ORD_JOIN을 집계 조회 – 성능 개선
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts |A-Rows |   A-Time   | Buffers |  OMem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |     1 |00:00:00.02 |     188 |       |
|   1 |  HASH GROUP BY               |                |      1 |     1 |00:00:00.02 |     188 |   934K|
|   2 |   NESTED LOOPS               |                |      1 |  2000 |00:00:00.02 |     188 |       |
|   3 |    VIEW                      |                |      1 |  2000 |00:00:00.02 |     170 |       |
|*  4 |     HASH JOIN                |                |      1 |  2000 |00:00:00.02 |     170 |  1814K|
|*  5 |      INDEX RANGE SCAN        | X_T_ORD_JOIN_4 |      1 | 23000 |00:00:00.01 |      90 |       |
|*  6 |      INDEX RANGE SCAN        | X_T_ORD_JOIN_2 |      1 | 19000 |00:00:00.01 |      80 |       |
|   7 |    TABLE ACCESS BY USER ROWID| T_ORD_JOIN     |   2000 |  2000 |00:00:00.01 |      18 |       |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1".ROWID=CHARTOROWID(TRIM(ROWIDTOCHAR("T2".ROWID))))
   5 - access("T2"."ITM_ID"='ITM020' AND "T2"."ORD_YMD">='20170101' AND "T2"."ORD_YMD"<='20170931')
   6 - access("T1"."CUS_ID"='CUS_0004' AND "T1"."ORD_YMD">='20170101' AND "T1"."ORD_YMD"<='20170931')
    6 - access("T2"."ITM_ID"='ITM002' AND "T2"."ORD_YMD">='20170101' AND "T2"."ORD_YMD"<='20170228')

 

논리IO가 개선되었지만, 해시조인으로 인해 메모리 사용은 늘어날 수 밖에 없다. 보통은 IO 개선이 SQL 성능에 많은 도움이 되기 때문에 이와 같은 방법을 사용해야 할 때가 있다.

 

이와 같이 SQL을 많이 변경해야 한다면 좋은 방법이라고 말하기는 어렵다. SQL이 길어지고, 조건이 늘어났으며 힌트도 많이 사용되었다. 사실, NO_MERGE 정도의 간단한 힌트만 사용해 원하는 결과를 얻고 싶었지만, 그럴 수 없어 힌트를 많이 사용하게 되었다. SQL의 13번 라인에서 T2.ROWID를 TRIM 처리하기도 했다. 실행계획이 원하는 대로 만들어지지 않아 강제 처리한 것이다.

오라클에는 INDEX_JOIN이나 INDEX_COMBINE 힌트가 있다. [SQL-2]처럼 복잡하게 SQL을 변경하지 않아도 해당 힌트를 사용할 수 있다. 하지만, 힌트가 먹지 않는 경우가 있다. 또는 작동하던 힌트가 어느 순간부터 작동하지 않을 수도 있다. 그리고, 힌트를 사용할 수 없는 DBMS도 있다. 그러므로 이와 같이 SQL을 변경할 수 있다면, 힌트가 작동하지 않아도, 힌트가 없어도 성능 개선을 할 수 있다.

 

 

오늘은 여기까지입니다. 감사합니다.