모바일 「내 등급」 API를 연 뒤로 정산 목록 커서가 하루에도 몇 번씩 다시 파싱된다

중급SELECT한빛유통553 / 700

상황

한빛유통이 지난달 내놓은 모바일 앱의 「내 등급」 API는 고객 번호로 고객 한 명을 읽는다. 고객 2,000명이 하루 평균 열두 번쯤 앱을 열어 하루 약 24,000번 호출된다. 리뷰에서 바인드 변수를 쓰라고 하자 개발 리더가 답했다. "고객이 2,000명이니 문장도 많아야 2,000개입니다. 한 번씩만 하드 파싱하면 그다음부터는 공유됩니다." 그 뒤로 DBA는 정산 목록(Q4) 같은 이웃 커서가 하루에도 몇 번씩 다시 하드 파싱된다고 알려 왔다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_ID는 'CUS_000123' 모양이다.
회원 가입이 열려 있어서 고객은 계속 는다.
공유 풀에는 화면과 배치의 바인드 문장 약 900개가 늘 올라와 있다. 그중 정산 목록(Q4)과 월 매출(Q1)은 하루 수만 번 실행된다.
CURSOR_SHARING = EXACT

원본 SQL

sql

// GradeApi.java : 고객 번호를 문자열로 이어 붙인다
String sql = "SELECT CUS_NM, CUS_GD, RGN_ID FROM M_CUS WHERE CUS_ID = '" + cusId + "'";
try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) { /* 한 건 */ }

실행계획 학습용 예시

xplan

[V$SQLAREA · 18:00 · FORCE_MATCHING_SIGNATURE로 묶은 이 API 무리]
  PARENTS    EXECS    LOADS     MB    SAMPLE
    1,286   15,410    3,377   20.6    SELECT CUS_NM, CUS_GD, RGN_ID FROM M_CUS WHERE CUS_ID = 'CUS_000123'

[같은 시각 · 이웃 문장 (API를 내놓기 전 주에는 하루 내내 LOADS 1이었다)]
  문장                       LOADS   INVALIDATIONS
  Q4 정산 목록 다음 페이지       6               0
  Q1 월 매출                     4               0

[V$SYSSTAT · 09:00 ~ 18:00 증가분]
  parse count (hard)          4,860    (API를 내놓기 전 주 같은 시간대 412)

[한 번 실행의 계획]
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| M_CUS    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_M_CUS |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"='CUS_000123')

문제

개발 리더의 말(한 번씩만 하드 파싱하면 그다음부터는 공유된다)이 맞는지 LOADS와 이웃 문장의 숫자로 평가하시오.
SQL을 고치고, 이 문장을 바인드 변수로 바꿔도 값에 따라 실행계획이 달라질 걱정이 없는 이유를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// GradeApi.java : 고객 번호는 바인드 변수로
String sql = "SELECT CUS_NM, CUS_GD, RGN_ID FROM M_CUS WHERE CUS_ID = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, cusId);
    try (ResultSet rs = ps.executeQuery()) { /* 한 건 */ }
}

개선 후 실행계획 학습용 예시

xplan

[고친 뒤 하루 (학습용 예시)]
  V$SQLAREA            부모 1 · 자식 1 · 실행 24,100 · LOADS 1 · 16KB
  parse count (hard)   09:00 ~ 18:00  431
  Q4 · Q1 LOADS        1 · 1

모범 답안 서술

개발 리더의 말이 맞으려면 문장 2,000개가 한 번 올라온 뒤 공유 풀에 계속 머물러야 한다. 그러나 V$SQLAREA에 남은 부모는 1,286개이고, 그 LOADS 합계는 3,377이다. 부모 하나가 평균 2.6번 올라왔다는 뜻이다. 오래 안 쓴 커서는 공유 풀에서 밀려나고, 그 고객이 다시 앱을 열면 같은 문장을 다시 하드 파싱한다. 하루에 열두 번쯤 쓰이는 문장 2,000개는 늘 밀려나는 쪽에 선다. 피해는 이 API에서 끝나지 않는다. 문장 2,000개가 20.6MB를 차지하면 그만큼 다른 커서가 밀려난다. API를 내놓기 전에는 하루 내내 LOADS 1이던 이웃 Q4와 Q1이 6과 4가 됐다(INVALIDATIONS 0이므로 무효화가 아니라 밀려남이다). 아무 잘못이 없는 이웃이 다시 하드 파싱을 하는 것이다. 9시부터 18시까지의 하드 파싱도 412 → 4,860으로 늘었다. 게다가 회원 가입이 열려 있어서 고객 수, 곧 문장 수는 계속 는다. 리터럴의 종류는 유한하지 않다. 고객 번호를 바인드 변수로 넘기면 문장은 하나(16KB)이고, 하루 24,100번을 커서 하나가 처리한다. 이 조회는 PK로 정확히 한 건을 찾으므로 어느 고객 번호가 오든 계획은 INDEX UNIQUE SCAN으로 같다. 값에 따라 계획이 달라져야 하는 문장이 아니므로 바인드 변수가 맞다.

해설

LOADS는 다시 파싱된 흔적이다. 부모가 남아 있는 동안 계획(자식의 heap 6)만 밀려났다가 다시 만들어지면 LOADS가 오른다. 부모째 밀려난 문장은 숫자도 함께 사라지므로, 실제 하드 파싱은 남은 LOADS 합계보다 많다. 전체 횟수는 parse count (hard) 증가분으로 센다.
이웃이 밀려났는지는 늘 쓰는 문장의 LOADS와 INVALIDATIONS를 나란히 보면 안다. INVALIDATIONS가 0인데 LOADS가 오르면 무효화가 아니라 밀려남이다(무효화로 오르는 경우는 PARSE-014).
공유 풀을 키우면 밀려나는 횟수는 줄지만, 문장 2,000개(고객이 늘면 그 이상)가 들어갈 자리를 계속 마련해야 한다. 원인은 그대로 두고 그릇만 키우는 일이다.
값이 손에 꼽을 만큼 적고 값마다 계획이 달라야 하는 코드값(상태값 같은)은 리터럴로 두는 편이 낫다(PARSE-002). 고객 번호는 그 반대다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황