▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법

Pagination에서 Sort의 부하 줄이기

주의사항

 

Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.

 

인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.

 

단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 는 비례한다는 의미이다.

 

Sort의 부하량(PGA 사용량) ∝ 세로(결과 건수) X 가로(컬럼 Size 합계)

 

공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. , Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?


위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD (char_100  CHAR(100)  DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_1000 CHAR(1000) DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_2000 CHAR(2000) DEFAULT 'a' NOT NULL );

 

추가된 컬럼은 모두 Char Type이며 Default 값이 ‘a’ 이다. Char Type이므로 Default값인 ‘a’가 들어오는 경우 컬럼 size는 각각 100, 1000, 2000 바이트씩 채워진다. 이제 이 컬럼들을 이용하여 SQL을 각각 실행해보자. 100 byte, 1000 byte, 2000 byte 컬럼으로 각각 Sort하여 Sort의 부하가 어떻게 달라지는지 알아보자.

 

CREATE TABLE SORT_100 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_100

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_100 ;

 

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

| Id  | Operation              | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:01.41 |    7323 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:01.41 |    7323 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.23 |    5915 | 4708K (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.15 |    5915 | 3471K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:00.66 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.01 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.59 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

100 byte컬럼으로 Sort하니 PGA4.7MB 사용하였다. 이제 100 byte보다 10배나 큰 1000 byte 컬럼으로 Sort 하여 PGA 사용량을 비교해보자.

 

CREATE TABLE SORT_1000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_1000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_1000 ;

 

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

| Id  | Operation              | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:02.17 |   13162 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:02.17 |   13162 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.75 |    5915 |   30M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.56 |    5915 | 3486K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.05 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.98 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

Sort 컬럼의 size 100에서 1000 byte로 늘리자 PGA 사용량도 4.7 MB에서 30 MB로 크게 늘었다. 내친김에 Order By절을 2000 byte 컬럼으로 바꿔서 PGA 사용량이 얼마나 늘어나는지 테스트 해보자.

 

CREATE TABLE SORT_2000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_2000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_2000 ;

 

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

| Id  | Operation              | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:03.16 |   19298 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:03.16 |   19298 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:02.06 |    5915 |   58M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.74 |    5915 | 3515K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.24 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.19 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:01.12 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

예상대로 Sort 대상 컬럼을 1000에서 2000 byte로 바꾸자 PGA 사용량이 30 MB에서 58MB 로 약 두 배 늘었다. 위의 결과를 언뜻 보면, Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각할 수 있다. 왜냐하면, Sort 대상컬럼의 Size에 비례하여 PGA 사용량이 증가되었다고 판단하기 때문이다. 하지만 이런 생각은 절반만 옳고 나머지 절반은 틀렸다. 제대로 된 식은 다음과 같다.

 

Sort의 부하를 좌우하는 원리

Sort
의 부하량(PGA 사용량) ∝ 세로 X 가로

세로: SQL의 결과 건수

가로: Order by 절의 컬럼 size + Order by 절을 제외한 나머지 컬럼의 size

 

근거 있는 주장인가?
이 공식이 글 전체의 핵심이다. 하지만, 많은 사람들이 위와 같은 가로 세로 개념을 주장할 수 있는 근거가 무엇인지 궁금해한다. 이제 가로가 Order by 절의 컬럼 size + 나머지 컬럼의 size라는 주장에 대한 근거를 보자.

 

SELECT s.channel_id, s.char_2000

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

| Id  | Operation          | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.84 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.84 |    4440 |   56M (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.68 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=30)

 

Order By절에는 Size가 작은 channel_id 컬럼 뿐이다. 그런데 PGA 사용량은 56 MB나 된다. 세로가 3만 건도 안 되는 집합을 Sort하는데 그 부하는 56 MB나 된다. 이상하지 않은가? 과부하의 이유는 Select절의 char_2000 컬럼 때문이다. 이 컬럼을 Select 절에서 제거하고 다시 실행해 보자.

 

SELECT s.channel_id

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

| Id  | Operation          | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.64 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.64 |    4440 |  424K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.58 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=30)

 

Order By절 이외의 컬럼에 주목하라 
Select
절의 char_2000 컬럼을 제거하자 Sort의 부하는 424K로 급격히 줄어들었다. 왜냐하면, Sort Area에는 Order By절의 컬럼을 Sort할뿐만 아니라 나머지 컬럼 List Loading 되기 때문이다. Order By절 뿐만 아니라, Select 절에도 size가 큰 컬럼이 있다면 성능이 급격히 저하됨을 알 수 있다. 지금까지 Sort부하량 공식에 의해 가로는 Order By절 컬럼 Size + Sort 대상 이외의 컬럼 Size가 됨을 증명해 보았다.

 

이제 이 개념을 실제 SQL 프로그래밍에 적용해보자. 아래는 전형적인 Pagination SQL이다. 아래의 예제에서 처음의 약속을 지키기 위해 인덱스를 만들지도, 사용하지도 않을 것이다.
 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

                       s.char_100, s.char_1000, s.char_2000

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) --> 200 대입

 WHERE rnum >= :v_min_row ;           --> 1 대입

 

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

| Id  | Operation                | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT         |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  1 |  VIEW                    |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  2 |   COUNT STOPKEY          |           |      1 |    200 |00:00:08.71 |    5915 |          |

|   3 |    VIEW                  |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  4 |     SORT ORDER BY STOPKEY|           |      1 |    200 |00:00:08.71 |    5915 | 3321K (0)|

|*  5 |      HASH JOIN           |           |      1 |    540K|00:00:06.30 |    5915 | 1176K (0)|

|   6 |       TABLE ACCESS FULL  | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|*  7 |       HASH JOIN          |           |      1 |    540K|00:00:03.91 |    5908 | 3568K (0)|

|   8 |        TABLE ACCESS FULL | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|*  9 |        TABLE ACCESS FULL | SALES_T   |      1 |    540K|00:00:01.14 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

   5 - access("S"."PROD_ID"="P"."PROD_ID")

   7 - access("S"."CUST_ID"="C"."CUST_ID")

   9 - filter("S"."CHANNEL_ID"=3)

 

페이징 처리된 SQL Sort 부하량은 3321K 이다. 이제 Sort의 부하를 줄이기 위해 select 절의 모든 컬럼을 제거하자.

 

SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

        s.char_100, s.char_1000, s.char_2000

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.rowid as s_rid, p.rowid as p_rid, c.rowid as c_rid

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) a,   --> 200 대입   

       sales_t s, customers c, products p

   WHERE s.rowid  = a.s_rid

   and c.rowid = a.c_rid

   and p.rowid = a.p_rid

   and rnum >= :v_min_row ;                --> 1 대입

 

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

| Id  | Operation                     | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   1 |  NESTED LOOPS                 |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   2 |   NESTED LOOPS                |           |      1 |    200 |00:00:06.51 |    5969 |          |

|   3 |    NESTED LOOPS               |           |      1 |    200 |00:00:06.51 |    5918 |          |

|*  4 |     VIEW                      |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  5 |      COUNT STOPKEY            |           |      1 |    200 |00:00:06.51 |    5915 |          |

|   6 |       VIEW                    |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  7 |        SORT ORDER BY STOPKEY  |           |      1 |    200 |00:00:06.51 |    5915 |96256  (0)|

|*  8 |         HASH JOIN             |           |      1 |    540K|00:00:05.81 |    5915 | 1193K (0)|

|   9 |          TABLE ACCESS FULL    | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|* 10 |          HASH JOIN            |           |      1 |    540K|00:00:03.65 |    5908 | 4514K (0)|

|  11 |           TABLE ACCESS FULL   | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|* 12 |           TABLE ACCESS FULL   | SALES_T   |      1 |    540K|00:00:01.06 |    4440 |          |

|  13 |     TABLE ACCESS BY USER ROWID| CUSTOMERS |    200 |    200 |00:00:00.01 |       3 |          |

|  14 |    TABLE ACCESS BY USER ROWID | PRODUCTS  |    200 |    200 |00:00:00.01 |      51 |          |

|  15 |   TABLE ACCESS BY USER ROWID  | SALES_T   |    200 |    200 |00:00:00.01 |     199 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - filter("RNUM">=:V_MIN_ROW)

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - filter(ROWNUM<=:V_MAX_ROW)

   8 - access("S"."PROD_ID"="P"."PROD_ID")

  10 - access("S"."CUST_ID"="C"."CUST_ID")

  12 - filter("S"."CHANNEL_ID"=3)

 

 

Trade Off가 유리한 경우

Sort 부하량이 3321K에서 96K로 약 34.5배 줄어들었다. 이렇게 ROWID만 남기고 select 절의 모든 컬럼을 제거해도 결과는 같다. 왜냐하면, Sort된 상태로 rowid가 보관되어있기 때문이다. 페이징 처리가 모두 끝나고 200건에 대해서만 rowid로 테이블에 접근하기 때문에 테이블의 중복사용에 의한 비효율은 매우 적다. Buffers 항목을 비교해보면 5915 블록에서 6168 블록으로 비효율은 253 블록(4%) 밖에 차이가 나지 않는다. 하지만 Sort의 부하는 34.5배나 줄어들었다. 약간의 Block I/O를 손해 보더라도 Sort의 부하가 아주 큰 경우는 같은 블록을 중복해서 읽어야 함을 알 수 있다.

 

장점 + 장점

이렇게 Rowid를 제외한 Select List를 인라인뷰 외부로 빼면, Sort 부하의 최소화 이외에 또 다른 효과를 누릴 수도 있다. 인덱스만 읽고 테이블로의 접근을 하지 않을 수 있다. Where 조건에 최적화된 인덱스가 존재하고, 그 인덱스가 Order By절 컬럼을 포함 한다면 인라인뷰 내부에서는 테이블 접근을 하지 않는다. 물론 Select List의 모든 컬럼들을 가져오려면 테이블을 접근해야 한다. 하지만 위의 예제처럼 Rowid를 사용했다면 페이징 처리가 끝난 후에, 특정 페이지에 해당하는 건들만 테이블로 접근할 수 있으므로 Random Access 도 최소화 된다. Sort를 최소화 하려고 했더니 Block I/O를 최소화 하는것까지 덤으로 얻을 수 있는 것이다.    

 

주의사항

SELECT 절에 상수나 변수 심지어 NULL이 오더라도 PGA 사용량은 증가하므로 주의해야 한다.
 

SELECT s.cust_id

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

| Id  | Operation          | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.38 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.38 |    4440 |   13M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.38 |    4440 |          |

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

 

Select 절에 다른 컬럼이 없기 때문에 PGA 13MB 사용 하였다. 이번에는 Select절에 Null을 추가해보자.

 

SELECT s.cust_id, null

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

| Id  | Operation          | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.48 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.48 |    4440 |   17M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.37 |    4440 |          |

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

 

Select List Null을 추가하자 PGA사용량이 4MB 증가했다. 오라클은 Null이나 상수도 컬럼처럼 취급함을 알 수 있다. 따라서 Order by절이 있는 인라인뷰의 Select List에 상수나 변수 혹은 Null을 집어넣는 것은 Sort의 부하를 증가시킨다. 상수나 변수는 Order By가 있는 인라인뷰에 넣지 말고 외부로 빼서 사용하면 된다.

결론
Sort의 부하를 최소화 하려면 Order By절의 컬럼에만 집중해서는 안되며, 전체 컬럼 List를 바라보아야 한다. 또한 프로그래밍을 할 때 상수 하나, 변수 하나의 위치도 고려해야 최적의 성능을 가진 프로그램이 됨을 알 수 있다. 즉 Sort의 최적화는 튜닝의 문제일 뿐만 아니라 프로그래밍의 문제인 것이다. 이점은 Sort 부하량이 무엇으로 결정되는지 개발자도 알아야 하는 이유가 된다.

면적은 가로와 세로로 구성된다.

Sort의 부하량은 면적의 크기에 비례한다.

Sort의 부하량 ∝ 결과 건수 X 전체 컬럼 Size

'Oracle > SQL Tuning' 카테고리의 다른 글

COPY_T 테이블 필요한가?  (6) 2011.04.04
Sort 부하를 좌우하는 두 가지 원리  (10) 2011.03.29
SQL튜닝 방법론  (18) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2011.03.29 17:50  댓글주소  수정/삭제  댓글쓰기

    안녕하십니까? 오동규님 ^^ 잘 지내시죠?
    그 동안 개인적으로 일이 있어서, 동규님 글을 잘 보지 못했는데, 오늘 동규님 포스팅 제목이 눈에 확 들어와 바로 보았습니다. ^^
    sort 부하를 제거하기 위한 방법으로 인덱스를 생성하는 솔루션은, 대용량 책에서부터 시작이 된 것 같은데, 그 이후에 추가 방법론에 대해서는 다루지 않은 것 같습니다. 그런데 동규님께서 이런 좋은 솔루션을 포스팅 해 주시다니, 여러 사람들에게 도움이 많이 될 것 같습니다. ^^

    제 경우는 rowid를 이용해서, 아래와 같이 튜닝을 한 적은 있었는데, Sort 부하를 줄이기 위한 rowid 사용법을 보게 되서 너무 좋았습니다.

    -- 1. 튜닝전
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    WHERE XPRH.REQUEST_ID = XBR.REQUEST_ID
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.019 0 7 0 0
    Execute 1 0.040 0.038 0 12 0 0
    Fetch 12 2.010 46.904 7262 44619 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 2.060 46.961 7262 44638 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=44619 pr=7262 pw=0 time=46911777 us)
    108 SORT ORDER BY (cr=44619 pr=7262 pw=0 time=46911758 us)
    108 HASH UNIQUE (cr=44619 pr=7262 pw=0 time=46910961 us)
    108 FILTER (cr=42600 pr=7165 pw=0 time=22479986 us)
    108 NESTED LOOPS (cr=42600 pr=7165 pw=0 time=22479338 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=161365 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=147679 us)
    108 TABLE ACCESS BY INDEX ROWID TABLE_2 (cr=41969 pr=6536 pw=0 time=45016626 us)
    13620 INDEX UNIQUE SCAN TABLE_2_PK (cr=27250 pr=966 pw=0 time=6201521 us)(Object ID 480908)
    ;

    -- 2. 인덱스 생성
    CREATE INDEX USER.TABLE_2_N4 ON USER.TABLE_2 (REQUEST_DATE, REQUEST_ID)

    -- 3. 튜닝후
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    ------------------------------------------------
    -- TABLE_2 추가(KJS)
    TABLE_2 XBR_T
    ------------------------------------------------
    WHERE XPRH.REQUEST_ID = XBR_T.REQUEST_ID
    ------------------------------------------------
    -- 조인키 추가(KJS)
    AND XBR.ROWID = XBR_T.ROWID
    ------------------------------------------------
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.020 0 7 0 0
    Execute 1 0.030 0.028 0 12 0 0
    Fetch 12 0.540 0.941 735 3882 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 0.580 0.989 735 3901 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=3882 pr=735 pw=0 time=939267 us)
    108 SORT ORDER BY (cr=3882 pr=735 pw=0 time=939252 us)
    108 HASH UNIQUE (cr=3882 pr=735 pw=0 time=938580 us)
    108 FILTER (cr=1863 pr=728 pw=0 time=592708 us)
    108 NESTED LOOPS (cr=1863 pr=728 pw=0 time=592486 us)
    108 HASH JOIN (cr=666 pr=664 pw=0 time=350058 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53849 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53782 us)
    9219 INDEX RANGE SCAN TABLE_2_N4 (cr=35 pr=35 pw=0 time=100643 us)(Object ID 21787797)
    108 TABLE ACCESS BY USER ROWID TABLE_2 (cr=1197 pr=64 pw=0 time=246521 us)
    ;

    동규님 덕분에 항상 많은 도움을 많이 받고 있어서, 항상 감사하게 생각하고 있습니다. ^^
    시간 허락해 주신다면, 이번에는 제가 술을 살 수 있도록 연락 부탁드립니다. ^^

    그럼 수고하세요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.03.29 17:44 신고  댓글주소  수정/삭제

      44619 블럭 I/O를 3882 로 만드셨네요.
      이에따라 46초 이상 걸리던 것이 1초 이내로 들어왔구요.
      대단하십니다. 이번주 중에 전화주시기 바랍니다.^^
      감사합니다.

  2. 2011.04.01 18:22  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.04.04 01:01 신고  댓글주소  수정/삭제

      오랜만이군요. TSTORY가 접속된다니 다행입니다. 아마 계속 읽으시면 적응이 되어 속도가 빨라질겁니다. 그리고 휴식은 못 하고 있습니다. 밤에 또 출근해서 블로그 관리와 독서 그리고 연구를 해야하죠. 하나라도 안하면 좀 편해질 거 같은데요.^^ 어쩔 수 없는것 같습니다.

  3. 이장미 2011.04.05 16:57  댓글주소  수정/삭제  댓글쓰기

    출근을 하루에 두 번 하시나 보네요~^^

    게다가 답글을 올리신게 새벽 1시!!

  4. 2011.04.05 21:29  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.04.05 22:20 신고  댓글주소  수정/삭제

      놔두셔도 큰문제 없습니다.^^
      블로그를 시작하시는 단계인것 같습니다.
      좋은 글 기대하겠습니다.

    • Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.04.06 13:38 신고  댓글주소  수정/삭제

      감사합니다 ^^;
      블로그는 어제 시작했습니다.
      아무래도 머릿속 정리하는데는 도움이 많이 될 것 같아서요 ㅎ
      지금은 무리지만 언젠간 저도 좋은 글 하나 남겨보고 싶습니다 ㅎ

  5. Favicon of https://aquamir.tistory.com BlogIcon 물빛미르 2019.06.25 17:12 신고  댓글주소  수정/삭제  댓글쓰기

    SQL 속도개선 검색하다가 유익하여 출처 남기고 담아가요.
    좋은 글 감사합니다.