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

일반적으로 웹 같은 OLPT 애플리케이션에서 데이터를 보기위해 테이블에 저장된 그대로를 보는 경우는 거의 없을 것이다. 사용자가 관심을 갖는 데이터를 기준으로 정렬해서 보고 특히 관심이 있는 상위 몇 개의 데이터부터 보는 것이 보통의 구성방식이다.

오라클에서는 n-Row 처리 방식의 게시판 쿼리를 구현하기 위해 ROWNUM이라는 Pseudo Colum을 쓰게 된다. 다소 복잡한 쿼리에서 ROWNUM을 사용하는 경우 의도하지 않게 실행계획이 고정되어 성능상의 불이익을 주는 경우가 발생한다.

하나의 예제를 확인해 보자.

MY_T 테이블에서 10건을 추출하기 위해 다음과 같은 쿼리를 수행하면 실행 계획에는 인라인 뷰를 사용했음에도 불구하고 아래의 실행 계획처럼 인라인 뷰의 항목이표현되지 않는 것을 알 수 있다. (MY_T 테이블은 10만 건의 ROW가 존재하며 인덱스는 COL1 단일 컬럼으로 이뤄져 있다).

SELECT /*+ qb_name(main)*/ 
       COL1, RND
  FROM 
     ( SELECT /*+ qb_name(sub)*/ 
              COL1, RND
         FROM MY_T
     )
 WHERE COL1 <= 10;

위의 SQL에 대한 실행 계획은 아래와 같다.

 
-----------------------------------------------------------------------------------------------
| Id |  Operation                  | Name    | Starts | E-Rows|E-Bytes| A-Rows | A-Time      |
-----------------------------------------------------------------------------------------------
| 1  | TABLE ACCESS BY INDEX ROWID | MY_T    | 1      | 1     | 13    | 10     | 00:00:00.01 |
|* 2 | INDEX RANGE SCAN            | PK_MY_T | 1      | 1     |       | 10     | 00:00:00.01 |
-----------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-----------------------------------------------------------
1 - SEL$354B60BE / MY_T@SUB
2 - SEL$354B60BE / MY_T@SUB


Predicate Information (identified by operation id):
-----------------------------------------------------------
2 - access("COL1"<=10)

이 쿼리는 Optimizer의 Transformation 기능에 의해 변경된다. 실행계획에서 Query Block Name 절에 보면 하나의 Query Block으로 합쳐져 SQL에 설정한 다른 Query Block Name이 보이지 않는다.

아래 쿼리를 실행시켜도 동일한 실행계획을 얻을수 있다. 단지, 차이점이라면 설정한 Query Block Name이 보인다는 것이다.

SELECT /*+ qb_name(main)*/ 
       COL1, RND
  FROM MY_T
 WHERE COL1 <= 10;

위 경우는 인라인 뷰가 주 쿼리와 병합되는 Query Transformation이 발생한 경우이다. 하지만 다음처럼 인라인 뷰가 병합되지 않는 상황에서 주 쿼리의 조건 절만 인라인 뷰 안으로 침투할 수 있는 상황이 발생할 수 있다.

SELECT /*+ qb_name(main) */ 
       COL1, RND
  FROM 
     ( SELECT /*+ qb_name(sub) NO_MERGE */ 
              COL1, RND
         FROM MY_T
     )
 WHERE COL1 <= 10;

위의 SQL에서 주 쿼리의 조건이 인라인 뷰로 침투한다면 실행 계획은 다음과 같이 VIEW라는 실행 계획이 생성될 것이다.

 
---------------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Starts | ERows|E-Bytes| A-Rows |A-Time      |
---------------------------------------------------------------------------------------------
| 1  | VIEW                        |         | 1      |1     | 26    | 10     |00:00:00.01 |
| 2  | TABLE ACCESS BY INDEX ROWID | MY_T    | 1      |1     | 13    | 10     |00:00:00.01 |
|* 3 | INDEX RANGE SCAN            | PK_MY_T | 1      |1     |       | 10     |00:00:00.01 |
---------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
---------------------------------------------------------------------------------------------
1 - SUB / from$_subquery$_001@MAIN
2 - SUB / MY_T@SUB
3 - SUB / MY_T@SUB


Predicate Information (identified by operation id):
-----------------------------------------------------------
3 - access("COL1"<=10)

실행계획을 보면 인라인 뷰는 그대로 유지되었지만 Predicate Information 절에 정보를 참조해보면 COL1<=10이라는 조건절이 인라인 뷰에 대한 액세스가 아닌 테이블에 대해 액세스되었음을 확인할 수 있다.

하지만 이 쿼리에서 일련번호를 붙이려는 의도에서 ROWNUM이 포함되는 순간 상황은 달라진다.

SELECT /*+ qb_name(main) */ COL1, RND
  FROM 
     ( SELECT /*+ qb_name(sub) NO_MERGE */ 
              ROWNUM, COL1, RND
       FROM MY_T
     )
 WHERE COL1 <= 10;

위와 같이 인라인 뷰 안에 ROWNUM 연산자를 사용한다면 다음과 같이 실행 계획이 생성된다.

 
--------------------------------------------------------------------------------
| Id |  Operation        | Name | Starts | E-Rows|E-Bytes| A-Rows | A-Time     |
--------------------------------------------------------------------------------
|* 1 | VIEW              |      | 1      | 100K  |2539K  | 10     |00:00:11.00 |
| 2  | COUNT             |      | 1      |       |       | 100K   |00:00:10.68 |
| 3  | TABLE ACCESS FULL | MY_T | 1      | 100K  |1269K  | 100K   |00:00:09.98 |
--------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SUB / from$_subquery$_001@MAIN
2 - SUB
3 - SUB / MY_T@SUB


Predicate Information (identified by operation id):
-----------------------------------------------------------
1 - filter("COL1"<=10)

실행계획을 확인해 보면 ROWNUM이 없는 쿼리는 10건만 읽고 처리했지만 ROWNUM이 있는 쿼리는 대상 테이블을 전체 10만 건을 읽고 난 후에야 Inline-View에 대한 필터로 10건을 처리한 것을 확인할 수 있다.

집합 내에 ROWNUM 연산자가 존재하는 경우 VIEW-MERGING이나 PUSH-PREDICATE가 발생하지 않고 인라인 뷰가 먼저 처리되는 특성이 있다.

Outer 집합에 ROWNUM 값을 제공하기 전에 Inner 집합에서 이미 ROWNUM에 대한 값을 할당해야 하기 때문이다.

이렇게 단지 하나의 단어를 추가했을 뿐이지만 시간상으로는 0.01초에서 11초라는 어마어마한 차이가 존재하게 된다.

일반적으로 많이 사용되는 ROWNUM 연산자에 이러한 특징이 있다는 점을 인지하지 못하고 사용되는 경우가 많고 이로 인해 성능 문제가 발생된다면 의외로 문제를 찾지 못해 힘든 경우가 많다.

ROWNUM의 기본적인 특징을 이용해 테이블 간의 조인 순서를 제어하는 등 효과적으로 튜닝하는 사례가 있기 때문에 관심을 가질 필요가 있다.