꿈꾸는 개발자, DBA 커뮤니티 구루비

05 인덱스를 이용한 소트 연산 대체

PK 컬럼에 아래와 같은 distinct 쿼리를 수행할 일은 없겠지만 혹시 수행한다면 이때도 sort unique 오퍼레이션이 생략된다. sort unique nosort가 그것을 표현하고 있는데, 소트를 수행하지 않고도 인덱스를 이용해 unique한 집합을 출력할 수 있다.



SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT DISTINCT empno
  4  FROM   emp ;

해석되었습니다.

SQL>
SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 4053311859

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    14 |    56 |     2  (50)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|        |    14 |    56 |     2  (50)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

9 개의 행이 선택되었습니다.

이들보다 활용도가 높은 것은 인덱스를 이용해 sort order by, sort group by를 대체하는 경우다. 이에 대해 좀 더 자세히 살펴보기로 하자.

(1) Sort Order By 대체

아래 쿼리를 수행할 때 region + custid 순으로 구성된 인덱스를 사용한다면 sort order by 연산을 대체할 수 있다.

인덱스가 region 단일 컬럼으로 구성됐거나, 결합 인덱스라도 region 바로 뒤에 custid가 오지 않는다면 region = 'A' 조건을 만족하는 모든 레코드를 인덱스를 경유해 읽어야 한다. 그 과정에서 다량의 랜덤 액세스가 발생할 테고 읽은 데이터를 custid순으로 정렬하고 나서야 결과집합 출력을 시작하므로 OLTP 환경에서 요구되는 빠른 응답 속도를 만족하기 어렵게 된다.
아래는 region 단일 컬럼 인덱스를 사용할 때의 실행계획이다.



EXPLAIN PLAN
FOR
SELECT  /* index(a customer_x01) */ 
        custid ,
        name ,
        resno ,
        status ,
        tell
FROM    customer a
WHERE   region = 'A'
ORDER   BY custid

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 40000 | 3515K |  2041   (1)|
|   1 |  SORT ORDER BY               |              | 40000 | 3515K |  2041   (1)|
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER     | 40000 | 3515K |  1210   (1)|
|*  3 |    INDEX RANGE SCAN          | CUSTOMER_X01 | 40000 |       |   96    (2)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("REGION"='A')

Full Table Scan 방식으로 처리하면 테이블 랜덤 엑세스 부하를 줄일 수는 있지만 필요없는 레코드까지 모두 읽는 비효율이 따르고, 정렬 작업 때문에 전체범위처리가 불가피하다.
아래는 region + custid 순으로 구성된 인덱스를 사용할 때의 실행계획이며, order by 절을 그대로 둔 상태에서 자동으로 sort order by 오퍼레이션이 제거된 것을 볼 수 있다. 이 방식으로 수행한다면 region = 'A' 조건을 만족하는 전체 로우를 읽지 않고도 결과 집합 출력을 시작할 수 있어 OLTP 환경에서 극적인 성능 개선 효과를 가져다 준다.



EXPLAIN PLAN
FOR
SELECT  /* index(a customer_x02) */ 
        custid ,
        name ,
        resno ,
        status ,
        tell
FROM    customer a
WHERE   region = 'A'
ORDER   BY custid

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 40000 | 3515K |  1372   (1)|
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER     | 40000 | 3515K |  1372   (1)|
|*  2 |    INDEX RANGE SCAN          | CUSTOMER_X02 | 40000 |       |   258   (2)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("REGION"='A')

물론, 소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있는 업무에서만 이 방식이 유리하다. 인덱스를 스캔하면서 결과집합을 끝까지 Fetch 한다면 오히려 I/O 및 리소스 사용 측면에서 손해다. 대상 레코드가 소량일 때는 소트가 발생하더라도 부하가 크지 않아 개선효과도 미미하다.

(2) Sort Group By 대체

region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용한다면 아래 쿼리에 필요한 sort group by 연산을 대체할 수 있다. 실행계획에 'sort group by nosort'라고 표시되는 부분을 확인하기 바란다.



EXPLAIN PLAN
FOR
SELECT region ,
       AVG( age ) ,
       COUNT( * )
FROM   customer
GROUP  BY region

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    25 |   725 | 30142   (1)|
|   1 |  SORT GROUP BY NOSORT        |              |    25 |   725 | 30142   (1)|
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER     | 1000K |   27M | 30142   (1)|
|   3 |    INDEX FULL SCAN           | CUSTOMER_X01 | 1000K |       |  2337   (2)|
----------------------------------------------------------------------------------

그림 5-9를 보면 위 실행계획이 어떻게 수행되는지 쉽게 이해할 수 있다.

간단히 설명하면, 인덱스를 스캔하면서 테이블을 액세스하다가 'A'가 아닌 레코드를 만다는 순간 그때까지 집계한 값을 Oracle Net으로 내려 보낸다. 이 값은 운반단위에 해당하는 SDU(Session Data Unit)에 버퍼링될 것이다.

(3) 인덱스가 소트 연산을 대체하지 못하는 경우

아래는 sal 컬럼을 선두로 갖는 인덱스가 있는데도 정렬을 수행하는 경우다.



SQL> CREATE TABLE emp2 AS
  2  SELECT *
  3  FROM   emp ;

테이블이 생성되었습니다.

SQL> begin
  2      dbms_stats.gather_table_stats(user, 'emp2', method_opt => 'for all colu
mns size 1');
  3  end ;
  4  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> CREATE INDEX emp2_sal_idx ON emp2( sal ) ;

인덱스가 생성되었습니다.

SQL> ALTER SESSION SET optimizer_mode = all_rows ;

세션이 변경되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    emp2
  5  ORDER   BY sal ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2441141433

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP2 |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 개의 행이 선택되었습니다.

옵티마이저가 이런 결정을 하는 가장 흔한 원인은 인덱스를 이용하지 않는 편이 더 낫다고 파단하는 경우다. 위에서 옵티마이저 모드가 all_rows인 것을 볼 수 있고, 이때 옵티마이저는 전체 로우를 Fetch 하는 것을 기준으로 쿼리 수행 비용을 산정한다. 따라서 데이터량이 많을수록 인덱스를 이용한 테이블 랜덤 액세스 비용이 높아져 옵티마이저는 차라리 Full Table Scan하는 쪽을 택할 가능성이 높아진다.
옵티마이저 모드를 first_rows로 바꾸면 사용자가 일부만 Fetch 하고 멈출 것임을 시사하므로 옵티마이저는 인덱스를 이용해 정렬 작업을 대체한다. 아래는 first_rows 힌트를 사용했을 때의 실행계획인다.



SQL> ALTER SESSION SET optimizer_mode = first_rows ;

세션이 변경되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    emp2
  5  ORDER   BY sal ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2441141433

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP2 |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 개의 행이 선택되었습니다.

옵티마이저 모드를 바꿨는데도 옵티마이저가 계속해서 소트 오퍼레이션을 고집한다면 그럴 만한 이유가 있다. 십중팔구 sal 컬럼에 not null 제약이 정의돼 있지 않을 것이다. 단일 컬럼 인덱스일 때 값이 null이면 인덱스 레코드에 포함되지 않는다고 했다. 따라서 인덱스를 이용해 정렬 작업을 대체한다면 결과에 오류가 생긱 수 있어 옵티마이저는 사용자의 뜻을 따를 수 없는 것이다.
group by도 마찬가지다. group by nosort를 위해 사용하려는 인덱스가 단일 컬럼 인덱스일 때는 해당 컬럼에 not null 제약이 설정되어 있어야 제대로 동작한다.



SQL> ALTER TABLE emp2 MODIFY sal NOT NULL ;

테이블이 변경되었습니다.

SQL> ALTER SESSION SET optimizer_mode = first_rows ;

세션이 변경되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    emp2
  5  ORDER   BY sal ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 422249803

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    14 |   518 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2         |    14 |   518 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | EMP2_SAL_IDX |    14 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

9 개의 행이 선택되었습니다.

인덱스가 있는데도 소트를 대체하지 못하는 사례가 또 한가지 있다. 아래 스크립트와 실행계획을 보자.



SQL> CREATE INDEX emp_dept_ename_idx ON emp( deptno , ename ) ;

인덱스가 생성되었습니다.

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT /*+ index(e emp_dept_ename_idx) */
  2         *
  3  FROM   emp e
  4  WHERE  deptno = 30
  5  ORDER  BY ename ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3593519358

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     6 |   222 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP                |     6 |   222 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     6 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=30)
       filter("DEPTNO"=30)

정상적으로 인덱스를 사용하는 것을 확인할 수 있다. 그런데 null 값이 먼저 출력되도록 하려고 아래처럼 nulls first 구문을 사용하는 순간 실행계획에 sort order by가 다시 나타난다.



SQL> set autotrace traceonly exp
SQL>
SQL> SELECT /*+ index( e emp_dept_ename_idx) */
  2         *
  3  FROM   emp e
  4  WHERE  deptno = 30
  5  ORDER  BY ename nulls first ;

Execution Plan
----------------------------------------------------------
Plan hash value: 669367854

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     6 |   222 |3       (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |                    |     6 |   222 |3       (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                |     6 |   222 |2        (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     6 |       |1        (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=30)

단일 컬럼 인덱스일 때는 null 값을 저장하지 않지만 결합 인덱스일 때는 null 값을 가진 레코드를 맨 뒤쪽에 저장한다. 따라서 null 값부터 출력하려고 할 때는 인덱스를 이용하더라도 소트가 불가피하다.

SDU, TDU



 흔히 Array Fetch를 얘기할 때, Array 버퍼가 서버 측(Server Side)에 할당된다고 생각한다. 서버 측 Array 버퍼는 클라이언트 측(Client Side)에 위치하며, 서버 측에서는 SDU에 버퍼링이 이루어진다. Array Fetch를 수행하는 내부 메커니즘에 대해 자세히 알아보자.
 오라클에서 데이터를 전송하는 단위는 ArrarySize에 의해 결정된다. 하지만 내부적으로 데이터는 네트워크 패킷 단위로 단편화되어 여러 번에 걸쳐 나누어 전송된다. 하지만 내부적으로 데이터는 네트워크 패킷 단위로 단편화되어 여러 번에 걸쳐 나누어 전송된다. 부분범위처리 내에 또 다른 부분범위처리가 작동하는 것이다. 이것은 네트워크 프로그램을 해 보았다면 너무 상식적인 애기다. 예를 들어, ArraySize가 100이고 한 레코드당 1MB를 차지한다면 한번 Fetch 할 때마다 100MB를 전송해야 하는데, 이를 하나의 패킷으로 묶어 한 번에 전송하지는 않는다. 네트워크를 통해 큰 데이터를 전송할 때는 작은 패킷들로 단편화해야 하며, 그래야 유실이나 에러가 발생했을 때 부분 재전송을 통해 복구할 수 있다.
 IT에 종사하면서 OSI 7 레이어를 모르는 독자는 아마 없을 것이다. 이것은 Application, Presentation, Session, Transport, Network, Data Link, Physical 이렇게 7개 레이어로 이루어진다. 오라클 서버와 클라이언트는 Application 레이어에 위치하며, 그 아래에 있는 레이어를 통해 서로 데이터를 주고받는다.
 SDU(Session Data Unit)는 Session 레이어 데이터 버퍼에 대한 규격으로서, 네트워크를 통해 전송하기 전에 Oracle Net이 데이터를 담아 두려고 사용하는 버퍼다. 예컨대, ArraySize를 5로 설정하면 클라이언트 측에는 서버로부터 전송받은 5개 레코드를 담을 Arrary 버퍼를 할당한다. 서버 측에서 Oracle Net으로 데이터를 내려보내다가 5건당 한 번씩 전송 명령을 날리고는 매번 클라이언트로부터 다음 Fetch Call을 기다리는데, Oracle Net이 서버 프로세스로부터 전송요청을 받기 전에라도 SDU가 다 차면 버퍼에 쌓인 데이터를 전송하는데, 이 때는 클라이언트로부터 Fetch Call을 기다리지 않고 곧이어 데이터를 받아 SDU를 계속 채워 나간다.
 TDU(Transport Data Unit)는 Transport 레이어 데이터 버퍼에 대한 규격이다. 물리적인 하부 레이어로 내려보내기 전에 데이터를 잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어하는 역할을 한다.
 SDU와 TDU 사이즈는 TNSNAMES.ORA, LISTENER.ORA 파일에서 아래와 같이 설정가능하며, 이들의 기본 설정 값은 2KB다.

    (SDU=2048)(TDU=2048)

 예를 들어, 결과 집합이 18건이고 각 로우당 900바이트를 차지한다고 가정하자. 그러면 총 16,200바이트를 전송해야 하는데, 만약 ArraySize를 5로 설정하면 한번 Fetch 할 때마다 4,500(=900X5)바이트씩 3번을 전송하고 4번째는 2,700(900X3) 바이트를 전송하게 된다.

    fetch1 : 900 X 5 = 4,500
    fetch2 : 900 X 5 = 4,500
    fetch3 : 900 X 5 = 4,500
    fetch4 : 900 X 3 = 2,700

 이때, SDU를 2,048로 설정하면 총 11개 패킷으로 단편화되어서 Transport 레이어로 전달된다.

    fetch1 : 2,048 + 2,048 + 404 = 4,500(-> 3개 패킷)
    fetch2 : 2,048 + 2,048 + 404 = 4,500(-> 3개 패킷)
    fetch3 : 2,048 + 2,048 + 404 = 4,500(-> 3개 패킷)
    fetch4 : 2,048 + 652         = 2,700(-> 2개 패킷)

 TDU는 1,048로 설정했다고 가정하자. 그러면 총 17개 패킷으로 단편화되어 클라이언트에게 전송이 이루어진다.

    fetch1 : (1,460 + 588) + (1,460 + 588) + 404 = 4,500(-> 5개 패킷)
    fetch2 : (1,460 + 588) + (1,460 + 588) + 404 = 4,500(-> 5개 패킷)
    fetch3 : (1,460 + 588) + (1,460 + 588) + 404 = 4,500(-> 5개 패킷)
    fetch4 : (1,460 + 1240)                      = 2,700(-> 2개 패킷)

 참고로, 각 패킷은 헤더 정보를 포함하므로 패킷 단편화를 줄이면 네트웍 트래픽도 줄어들게 된다.

문서에 대하여

최초작성자 : 황종필
최초작성일 : 2010년 06월 05일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*