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

오라클 데이터베이스를 운영하거나 DBA라면 누구나 한 번쯤은 성능에 관해 고민해 봤을 것이다. 오라클 데이터베이스의 성능에 영향을 미치는 요소는 매우 많지만, 그 중에서도 데이터베이스의 SQL에 대한 호출을 소홀히 하는 경향이 있다.

단순히 한 번 수행하는 SQL이라 해도 경우에 따라서는 내부 호출이 빈번히 발생할 수 있다는 사실을 인지해야 한다. SQL을 최적화하려면 먼저 데이터 블록 I/O를 최소화해야 하지만, 그 외에도 Recursive Calls 발생 횟수를 줄이는 방안도 고려할 필요가 있다.

오라클과 같은 관계형 데이터베이스의 데이터 처리 작업이 SQL에 의해 이뤄진다는 것은 익히 잘 알려져 있다. 작성된 SQL은 개발자에 의해 프로그램 소스에 삽입되어 응용프로그램에 의해 호출된다. 작성된 SQL이 프로그램 소스에 삽입되기 전에 개발자는 해당 SQL이 응용프로그램이나 시스템 사용자에 의해 얼마나 호출될지 파악하기가 쉽지 않다.

가끔 한 번씩 수행되는 SQL이라면 내부호출이 많다 해도 문제될 것이 없다. 하지만 초당 100회에서 1,000회 정도 빈번하게 호출되는 온라인 트랜잭션용 SQL일 경우 Recursive Calls가 많다면 해당 업무처리 성능에 영향을 받게 된다.

이번 주제에서는 Recursive Calls가 빈번한 SQL 유형과 그에 대한 해결책을 살펴본다.

Recursive Calls는 오라클 데이터베이스 내부에서 발생하는 Call을 의미한다. 다음과 같은 경우에 SQL 수행 과정에서 Recursive Calls가 발생하게 된다.

- SQL 파싱 및 옵티마이징 과정에서 Data Dictionary 조회 시 발생
- PL/SQL로 작성된 사용자 정의 함수를 수행하는 과정에서 발생
- 프로시저 및 트리거 내에서 SQL이 수행될 때 발생

단위 SQL의 처리 과정에서 과다한 Recursive Calls가 발생하면 해당 SQL은 원활한 성능을 보장받기 어렵다. [리스트 1]은 Recursive Calls가 빈번하게 발생하는 잘못된 SQL의 예제를 보여주고 있다.

[리스트 1] Recursive Calls가 많은 비효율 SQL 예제

SELECT A.HNO, A.ACA_NM, A.REGDATE, A.FLAG
  FROM 
     ( SELECT A.HNO, A.ACA_NM, A.REGDATE, B.FLAG
         FROM TB_HEAD A, TB_TAIL B
        WHERE A.HNO = B.HNO
          AND B.FLAG = 'H'
          AND FN_EDULCD_BYZNO_NEW (A.ZNO) = 'N100000075'
        ORDER BY REGDATE DESC
     ) A
 WHERE ROWNUM <= 10;
 
 
 경   과: 00:00:01.54
-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |    10 |   760 |    70  (22)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |         |       |       |            |          |
|   2 |   VIEW                  |         |   326 | 24776 |    70  (22)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|         |   326 | 33252 |    70  (22)| 00:00:01 |
|   4 |     NESTED LOOPS        |         |   326 | 33252 |    69  (21)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | TB_HEAD |   234 | 20358 |    69  (21)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN  | PK_TAIL |     1 |    15 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
      31582  recursive calls
          0  db block gets
      63387  consistent gets
          0  physical reads
          0  redo size
        909  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

이 SQL과 Trace 예제는 Recursive Calls 횟수가 많은 전형적인 비효율 악성 SQL 예제다. FN_EDULCD_BYZNO _NEW(A.ZNO) 사용자 함수를 WHERE 조건 절에 사용함으로 인해 조건 필터 대상이 되는 모든 데이터 행에 의해 해당 함수가 호출되어 Recursive Calls 횟수가 증가하는 원인이 된다.

실제로 Trace 결과를 보면 SQL 한 번 호출 시 FETCH되는 데이터 행은 10건인데 반해, Recursive Calls는 31,582회 수행됐음을 알 수 있다

대상 테이블 TB_HEAD의 데이터량이 대용량이라면 Recursive Calls 횟수는 더욱 증가할 것이며, 위의 악성 SQL이 빈번히 수행되는 온라인 트랜잭션 SQL이라면 시스템은 심각한 서비스 장애 상황을 초래할 수 있다.

[리스트 2]는 악성 SQL을 튜닝해 Recursive Calls 발생 횟수를 최소화시킨 최적화된 SQL 상태를 보여준다

[리스트 2] Recursive Calls 발생이 없는 최적화된 SQL 예제

SELECT A.HNO, A.ACA_NM, A.REGDATE, A.FLAG
  FROM 
     ( SELECT A.HNO, A.ACA_NM, A.REGDATE, B.FLAG
        FROM TB_HEAD A, TB_TAIL B
       WHERE A.HNO = B.HNO
         AND B.FLAG = 'H'
         AND EXISTS (SELECT 1
                       FROM TB_ZONE C
                      WHERE A.ZNO = C.ZNO 
                        AND EDU_LCD = 'N100000075')
       ORDER BY REGDATE DESC
     ) A
 WHERE ROWNUM <= 10;
 
 
 경   과: 00:00:00.03
------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |    10 |   760 |    61   (5)| 00:00:01 |
|*  1 |  COUNT STOPKEY           |         |       |       |            |          |
|   2 |   VIEW                   |         |   115 |  8740 |    61   (5)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY |         |   115 | 14030 |    61   (5)| 00:00:01 |
|   4 |     NESTED LOOPS         |         |   115 | 14030 |    60   (4)| 00:00:01 |
|*  5 |      HASH JOIN RIGHT SEMI|         |    94 | 10058 |    60   (4)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL  | TB_ZONE |     1 |    20 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL  | TB_HEAD | 23350 |  1983K|    56   (2)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN   | PK_TAIL |     1 |    15 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        230  consistent gets
          0  physical reads
          0  redo size
        909  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

이 SQL은 WHERE 조건 절에 사용한 사용자 함수를 배제하고 함수 내부에 존재하던 SQL의 조건을 분석해 함수 내부조건을 SQL의 조건 절에 적용했다.

그 결과 Recursive Calls 횟수는 0이 되었으며 수행시간도 1.54초에서 0.03초로 향상됐음을 확인할 수 있었다. 물론 요청에 대한 응답 데이터는 동일했다.

개발자 또는 DBA가 SQL을 신규로 작성하거나 최적화함에 있어서 Recursive Calls는 놓치기 쉬운 요소다. 하지만 이런 요소들에 관심을 갖고 SQL을 작성한다면 데이터베이스는 건강하게 운영될 수 있음을 명심하자!