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

기업 또는 조직에서 생산되는 데이터는 가공되고 정형화되면서 대부분 관계형 데이터베이스의 데이터 저장소에 저장된다. 그리고 데이터 저장소에 보다 쉽고 간편하게 접근할 수 있는 유저 인터페이스(User Interface)를 만들기 위해 자바나 C 언어 같은 다양한 언어로 많은 응용프로그램들을 만들어낸다.

이런 응용프로그램들이 관계형 데이터베이스에 접근하기 위해서는 반드시 SQL이 필요하며, 다양한 언어를 구사하는 모든 개발자는 데이터베이스에 접근하기 위해 공통적으로 SQL을 다룰 수 있어야만 한다. 이런 요구 때문에 대부분의 개발자는 SQL을 능숙하게 다룰 수 있지만, SQL의 수행 원리를 깊이 있게 이해하고 제대로 사용할 수 있는 전문지식을 보유한 개발자는 드물다.

이런 배경으로 인해 구축된 정보시스템은 성능 이슈를 초래하게 되며 이는 결과적으로 사용자의 불편과 불만족으로 이어진다. 이에 이런 불편을 조금이나마 해소하고자 SQL의 최적화 방법을 여기서 소개한다.

데이터베이스 User Call을 최소화하자

개발자 1인이 SQL을 작성한 후 수행하면 데이터베이스는 이를 받아 수행하는데 이를 User Call이라 하며 OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어온 Call을 일컫는다. 또한 개발자가 한번 수행했기 때문에 해당 SQL은 데이터베이스 내에서 한번 호출된다.

하지만 이 SQL이 애플리케이션 적용되어 사용자 및 시스템에 의해 호출된다면 이는 한 번 호출이 아닌 그 이상의 많은 호출이 동시적으로 발생하게 된다. 이런 이유로 개발 단계에서 해당 SQL이 운영으로 전환되면 얼마나 수행될지 짐작할 수도 없다.

때문에 개발 단계에서 SQL을 작성할 때 호출을 최소화할 수 있도록 하는 노력이 필요하다. 다음은 오라클에서 제공하는 AWR(Automatic Workload Repository) 보고서의 Load Profile 항목을 발췌한 내용이다.

       Load Profile        Per Second   Per Transaction
   ----------------   ---------------   ---------------
         Redo size:        302,914.01          4,338.42
     Logical reads:         31,054.38            444.77
     Block changes:          2,093.62             29.99
    Physical reads:            416.09              5.96
   Physical writes:            176.04              2.52
        User calls:          2,276.92             32.61
            Parses:            644.16              9.23
       Hard parses:              0.43              0.01
             Sorts:            198.61              2.84
            Logons:              4.46              0.06
          Executes:          1,273.50             18.24
      Transactions:             69.82

위 여러 항목 중에서 [User calls] 항목과 [Executes] 항목을 살펴보자.

- User Calls : 로그인, 파스, 패치, SQL 실행 등 사용자가 요청한 모든 호출의 합을 뜻한다.
- Executes : 사용자가 수행한 SQL과 데이터베이스 내부에서 실행된 Recursive SQL의 횟수 합을 뜻한다

위 AWR 보고서 Load Profile 항목의 [User Calls] 수치는 2,276.92(Per Second)로서 초당 2,276번 정도의 호출이 발생한 것을 확인할 수 있고, [Executes] 수치는 1,274.50(Per Second)로서 초당 1,274번 정도의 호출이 발생한 것을 확인할 수 있다.

위의 보고서에 기록된 결과로 볼 때 [Executes]보다 [User Calls] 수치가 더 높게 측정된 것을 확인할 수 있다. 이는 일반적으로 정상적이라 할 수 있다.

만약 [Executes]의 수치가 [User Calls]보다 지나치게 높게 측정된다면 SQL의 과다한 Recursive Call을 의심할 수 있다.

[User Call] 수치를 줄이는 방법은 의외로 간단하다. SQL을 작성할 때 절차적이 아닌 집합적 사고를 통해 작성하면 된다.

첫 번째로 프로그래밍 언어 또는 PL/SQL 구문에서의 LOOP 쿼리를 최소화하고 단일 SQL로 소스 코드를 작성한다면 [User Call]을 줄이는 데 크게 도움이 될 것이다.

두 번째로 부분범위 처리의 원리를 활용하는 방안과 세 번째로 사용자정의함수, 프로시저 및 트리거의 적절한 활용을 통해서도 [User Call]을 줄일 수 있다.

[SQL의 User Call을 줄이는 방법]
1. 프로그래밍 언어 또는 PL/SQL 내에서의 SQL 호출 시 LOOP 쿼리를 최소화하자.
2. 부분처리 범위를 원리를 이용해 데이터를 액세스하도록 하자.
3. 사용자정의함수, 프로시저 및 트리거를 상황에 맞게 적절하게 활용하자.

데이터베이스의 Recursive Call을 최소화하자

개발자 1인이 작성한 SQL을 한 번 호출하면 기본적으로 한 번의 User Call이 발생한다. 이때 꼭 짚고 넘어가야 할 항목이 있다. 바로 [Recursive Call]이다.

- Recursive Call : 오라클 내부에서 발생하는 Call을 의미한다. SQL 파싱과 옵티마이징 과정에서 발생하는 Data Dictionary 조회, PL/SQL로 작성된 사용자정의함수, 프로시저 및 트리거 내에서의 SQL 수행이 여기에 해당된다.

단위 SQL의 수행에서 과다한 [Recursive Call]의 발생은 곧 SQL의 성능 저하를 의미한다. 다음의 Auto Trace 정보를 살펴보자.

경과: 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은 기재하지 않았다. SQL*PLUS에서 AUTOTRACE로 단위 SQL 수행 결과 위와 같은 통계 결과가 출력됐다.

이 가운데 주목해야 할 항목은 [recursive calls]이다. 단 한 번의 SQL을 수행했을 뿐이다. 또한 그에 따른 [rows processed]는 10건이다. 하지만 [recursive calls] 횟수는 3만1,582회다. 당연히 원활한 성능을 보장할 수 없다.

그나마 위의 예시는 양호한 편이다. 필자는 모 사이트에서 SQL 튜닝을 하면서 단위 SQL이 한 번 수행 시 [recursive calls] 횟수가 100만 회가 넘는 SQL도 봤다. 문제가 된 SQL은 온라인 트랜잭션 데이터를 처리하는 SQL로 해당 시스템에서 빈번하게 수행되는 SQL이었고, 가장 악성 SQL이었다.

또한 이 SQL을 튜닝함으로써 시스템의 성능은 대폭 향상됐다. 필자는 향후 기고를 통해 SQL의 [recursive calls]을 최소화하는 방법과 그에 따른 예제도 소개할 계획이다.