월 마감 업로드가 도는 3분 동안 매장 POS의 메뉴 화면까지 굼떠진다

기초INSERT버거549 / 700

상황

버거 본사는 매달 1일 새벽에 매장 PC에 쌓인 지난달 주문상품을 받아 ORD_ITEM에 넣는다. 수신 프로그램(2013년, C#)은 한 줄을 읽을 때마다 INSERT 문자열을 새로 만들어 보낸다. 2013년 12월분 103,030행을 넣는 데 3분 34초가 걸렸다. 같은 시각, 밤새 문을 여는 매장들에서 "메뉴 화면이 한 박자씩 늦게 뜬다"는 연락이 왔다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 한 달 약 103,000행
ORD_NO와 ITEM_ID는 NUMBER이고, ORD_DT('20131201')와 ORD_HMS('113005')는 VARCHAR2다.
매장 POS의 조회 문장(메뉴 가격, 매장 정보)은 바인드 변수로 쓰여 있고, 평소에는 공유 풀에 늘 올라와 있다.
CURSOR_SHARING = EXACT · session_cached_cursors = 50

원본 SQL

sql

// MonthUpload.cs (2013) : 한 줄마다 값을 문자열에 이어 붙여 INSERT 문장을 새로 만든다
using (var tx = conn.BeginTransaction())
{
    var cmd = conn.CreateCommand();
    foreach (var r in rows)                        // 2013년 12월분 103,030행
    {
        cmd.CommandText = "INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) VALUES ("
                        + r.OrdNo + ", " + r.ItemId + ", " + r.Qty + ", '" + r.OrdDt + "', '" + r.OrdHms + "')";
        cmd.ExecuteNonQuery();
    }
    tx.Commit();
}

-- DB에 도착하는 문장 (행마다 하나)
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) VALUES (998201, 17, 2, '20131201', '113005')

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[세션 통계 — 2013년 12월분 업로드 한 번 (V$MYSTAT 증가분)]
  경과 시간                          214 초
  execute count                      103,046
  parse count (total)                103,051
  parse count (hard)                 103,012
  parse time cpu                      12,360 cs
  CPU used by this session            15,840 cs
  session logical reads              721,400   (행당 약 7 : INSERT 5 + 하드 파싱의 딕셔너리 조회 2)
  SQL*Net roundtrips to/from client  103,070

[10046 → TKPROF — 표가 103,012개다. 그중 둘]
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) VALUES (998201, 17, 2, '20131201', '113005')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          4           1
Misses in library cache during parse: 1

INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) VALUES (998201, 42, 1, '20131201', '113005')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          4           1
Misses in library cache during parse: 1

[업로드 전후 · POS 문장의 V$SQLAREA]
  문장                         LOADS (업로드 전 → 후)   INVALIDATIONS
  메뉴 가격 조회 (바인드)         1 → 6                    0
  매장 정보 조회 (바인드)         1 → 4                    0

문제

행당 7블록을 읽는 INSERT가 3분 넘게 걸리는 이유를 세션 통계와 TKPROF로 설명하시오. 업로드하는 동안 POS 화면까지 느려진 이유도 쓰시오.
같은 결과를 내도록 업로드 프로그램을 고치시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

// MonthUpload.cs : 문장은 하나 · 값은 바인드 변수 · 1,000행씩 배열로 보낸다 (ODP.NET 배열 바인드)
using (var tx = conn.BeginTransaction())
{
    var cmd = conn.CreateCommand();
    cmd.BindByName  = true;
    cmd.CommandText = "INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) "
                    + "VALUES (:ord_no, :item_id, :qty, :ord_dt, :ord_hms)";
    foreach (var chunk in rows.Chunk(1000))
    {
        cmd.Parameters.Clear();
        cmd.ArrayBindCount = chunk.Length;
        cmd.Parameters.Add("ord_no",  OracleDbType.Int64,    chunk.Select(r => r.OrdNo).ToArray(),  ParameterDirection.Input);
        cmd.Parameters.Add("item_id", OracleDbType.Int32,    chunk.Select(r => r.ItemId).ToArray(), ParameterDirection.Input);
        cmd.Parameters.Add("qty",     OracleDbType.Int32,    chunk.Select(r => r.Qty).ToArray(),    ParameterDirection.Input);
        cmd.Parameters.Add("ord_dt",  OracleDbType.Varchar2, chunk.Select(r => r.OrdDt).ToArray(),  ParameterDirection.Input);
        cmd.Parameters.Add("ord_hms", OracleDbType.Varchar2, chunk.Select(r => r.OrdHms).ToArray(), ParameterDirection.Input);
        cmd.ExecuteNonQuery();                     // 1,000행이 한 번의 실행 호출로 간다
    }
    tx.Commit();                                   // 커밋은 전처럼 마지막에 한 번
}

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[같은 103,030행 — 바인드 변수 + 배열 바인드 1,000행씩 (학습용 예시)]
  경과 시간                          3.1 초
  execute count                      104
  parse count (hard)                 1
  parse time cpu                     0 cs
  CPU used by this session           188 cs
  session logical reads              212,600
  SQL*Net roundtrips to/from client  112
  POS 문장 LOADS                     1 → 1

[참고 — 바인드 변수만 쓰고 한 행씩 실행하면]
  경과 시간                          58 초
  parse count (hard)                 1
  SQL*Net roundtrips to/from client  103,070

모범 답안 서술

TKPROF에는 표가 103,012개 있고, 표마다 Parse 1 · Execute 1 아래에 Misses in library cache during parse: 1이 찍혀 있다. 값이 SQL 텍스트 안에 들어 있어서 오라클에게는 행마다 처음 보는 문장이므로, 10만 번 모두 하드 파싱을 했다. parse time cpu 12,360cs가 CPU 15,840cs의 78%다. 행 하나를 넣는 일(약 5블록)보다 그 문장을 어떻게 실행할지 정하는 일에 CPU를 더 썼다. 하드 파싱할 때마다 공유 풀에 새 커서 자리를 만들어야 해서 latch: shared pool을 두고 다투고, 자리가 모자라면 오래 안 쓴 커서를 내보낸다. 밤새 문을 여는 매장들의 메뉴 가격 조회 커서가 그렇게 다섯 번 밀려났다가 다시 하드 파싱됐다(LOADS 1 → 6, INVALIDATIONS 0). 업로드가 POS 화면까지 느리게 한 까닭이다. 문장은 하나로 고정하고 값은 바인드 변수(:ord_no 등)로 넘긴다. 그러면 하드 파싱은 1번이다. 여기에 ODP.NET 배열 바인드(ArrayBindCount)로 1,000행씩 한 번에 보내면 실행 호출과 왕복도 103,070 → 약 110번으로 준다. 경과 시간은 214초 → 3.1초, 하드 파싱은 103,012 → 1이다. 트랜잭션은 전처럼 마지막에 한 번 커밋하므로 결과도 같다.

해설

바인드 변수만 바꿔도 하드 파싱은 1번이 된다. 하지만 한 행씩 실행하면 왕복 10만 번이 남아서 약 58초가 걸린다. 하드 파싱을 없애는 일과 호출 횟수를 줄이는 일은 서로 다른 일이다(호출 줄이기는 교재 15화).
INSERT ... VALUES에는 값에 따라 고를 실행계획이 없다. 그래서 바인드 변수로 바꿔서 잃는 것이 없다.
넣을 행이 DB 안의 다른 테이블에 이미 있다면 INSERT ... SELECT 한 문장이 가장 낫다(DML 단원). 이 문제는 원천이 매장 PC의 파일이라 배열 바인드가 현실적인 답이다.
같은 실수를 서버 쪽에서 CURSOR_SHARING = FORCE로 덮을 수도 있지만, 다른 문장까지 바꾸는 스위치다(PARSE-003). 프로그램을 고칠 수 있으면 프로그램을 고친다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황