SQLP 튜닝 연습장

테마

튜닝 연습파싱과 바인드 변수
PARSE-038

모바일 「주문 내역」 한 화면에 DB 호출이 61번 찍힌다

중급SELECT한빛유통583 / 700

상황 ​

한빛유통 모바일 앱의 「주문 내역」 API는 고객의 최근 주문 20건과 주문마다 담긴 상품(이름, 수량, 단가)을 돌려준다. 지난달 API 서버를 클라우드로 옮긴 뒤 응답이 평균 0.04초에서 0.58초가 됐다. DB는 사내 IDC에 남았고 전용선으로 잇는다. APM은 요청 하나에 DB 호출이 61번이라고 보여 준다. DBA는 "61개 문장이 모두 한 번에 0.1ms 안쪽이라 DB는 잘못이 없다"라고 한다.

테이블과 인덱스 ​

  • T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT)(SORT-006에서 만든 인덱스) · 고객 한 명에 주문 약 50건
  • T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3줄(평균 2줄)
  • M_ITM: 100행 · 5블록 · PK_M_ITM (ITM_ID)
  • API 서버와 DB의 왕복은 약 9ms다(옮기기 전에는 같은 IDC 안이라 0.4ms). JDBC fetch size는 기본값 10이다. 통계 최신.

원본 SQL ​

sql
// OrderHistoryApi.java : 주문 20건을 읽고, 주문마다 상품 줄을, 줄마다 상품명을 따로 읽는다 (JdbcTemplate)
List<Order> orders = jdbc.query(
      "SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT FROM ("
    + "  SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD WHERE CUS_ID = ? ORDER BY ORD_DT DESC"
    + ") WHERE ROWNUM <= 20", ORDER_MAPPER, cusId);
for (Order o : orders) {
    o.lines = jdbc.query(
        "SELECT ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC FROM T_ORD_DET WHERE ORD_SEQ = ?", LINE_MAPPER, o.seq);
    for (Line l : o.lines) {
        l.itmNm = jdbc.queryForObject("SELECT ITM_NM FROM M_ITM WHERE ITM_ID = ?", String.class, l.itmId);
    }
}

실행계획 학습용 예시 ​

xplan
[요청 하나 · 고객 CUS_000731 · 세션 통계 증가분]
  execute count                            61
  SQL*Net roundtrips to/from client        63
  session logical reads                   163
  DB time                                 6.1 ms
  SQL*Net message from client             63번 · 0.57 초 (평균 9.0 ms)

[V$SQL · 오늘 하루 (요청 31,200번)]
  SQL                                                              EXECUTIONS    한 번 Buffers
  SELECT ... FROM T_ORD WHERE CUS_ID = :1 ... ROWNUM <= 20             31,200            23
  SELECT ORD_DET_NO, ... FROM T_ORD_DET WHERE ORD_SEQ = :1            624,000             3
  SELECT ITM_NM FROM M_ITM WHERE ITM_ID = :1                        1,248,000             2

[주문 20건 문장의 계획]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |     20 |00:00:00.01 |      23 |
|*  1 |  COUNT STOPKEY                  |                 |      1 |        |     20 |00:00:00.01 |      23 |
|   2 |   VIEW                          |                 |      1 |     50 |     20 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_ORD           |      1 |     50 |     20 |00:00:00.01 |      23 |
|*  4 |     INDEX RANGE SCAN DESCENDING | IX_T_ORD_CUS_DT |      1 |     50 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - access("CUS_ID"=:1)

문제 ​

  1. DB 안의 일은 6ms인데 응답이 0.58초인 이유를 위 숫자로 설명하시오. 옮기기 전에는 왜 드러나지 않았는지도 쓰시오.
  2. 응답 내용이 바뀌지 않게 DB 호출 수를 줄이도록 고치고, 고친 뒤의 왕복 수와 블록 수를 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자