해결사 되기

정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)


좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

 

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT s.cust_id,

               COUNT (DISTINCT s.prod_id) AS prod_cnt,

               COUNT (DISTINCT s.channel_id) AS channel_cnt,

               SUM (s.amount_sold) AS tot_amt

          FROM sales s

         GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id

   AND ROWNUM <= 10;

 

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |               |     10 |    5035 |          |
|*  2 |   HASH JOIN                   |               |     10 |    5035 | 1150K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    151 |     153 |          |
|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH |    151 |       2 |          |
|   5 |    VIEW                       |               |    523 |    4882 |          |
|   6 |     SORT GROUP BY             |               |    523 |    4882 | 8288K (0)|
|   7 |      PARTITION RANGE ALL      |               |    918K|    4882 |          |
|   8 |       TABLE ACCESS FULL       | SALES         |    918K|    4882 |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

 

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다
JPPD 기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다. 스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.
 

정답: 아래를 드래그 하면 된다.

 

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

 channel_cnt NUMBER(2),     

 tot_amt NUMBER(15,2));

/

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       s.cust_id, s.cust_first_name, s.cust_last_name,

       s.sales_cust.prod_cnt,  -- Alias 가 여기에 사용 되었다.

       s.sales_cust.channel_cnt,

       s.sales_cust.tot_amt

  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

               c.cust_id, c.cust_first_name, c.cust_last_name,

               (SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다

                          (COUNT (DISTINCT s.prod_id),

                           COUNT (DISTINCT s.channel_id),

                           SUM (s.amount_sold)

                          )

                  FROM sales s

                 WHERE s.cust_id = c.cust_id

) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.

          FROM customers c

         WHERE c.cust_year_of_birth= 1987

           AND ROWNUM <= 10) s  ;

    

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.
          

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

 

-------------------------------------------------------------------------------

| Id  | Operation                     | Name               | A-Rows | Buffers |

-------------------------------------------------------------------------------

|   1 |  VIEW                         |                    |     10 |    1104 |

|*  2 |   COUNT STOPKEY               |                    |     10 |      13 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |     10 |      13 |

|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH_CUST |     10 |       3 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<=10)

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.
 

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

 

PS:

만약 Oracle9i 나 Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.