글자 하나 다르지 않은 고객 조회 SQL에 자식 커서가 셋씩 붙는다

기초PLSQL한빛유통564 / 700

상황

박팀장이 AWR 보고서의 「SQL ordered by Version Count」를 보냈다. 맨 위는 고객 한 명의 이름과 등급을 읽는 문장이다. 정산(PKG_STLM), 고객센터(PKG_CS), 포인트(PKG_PNT) 패키지가 같은 정적 SQL을 쓰고, 셋이 합쳐 하루 40만 번 호출한다. 통계를 모으는 밤이 지나면 아침마다 이 문장이 세 번씩 하드 파싱된다. 정산 스키마에는 모양이 같은 문장이 30개쯤 더 있다. DBA가 물었다. "바인드 변수도 쓰고 텍스트도 하나인데 왜 자식이 셋이죠?"

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_ID VARCHAR2(40), 지금 값은 'CUS_000123' 모양(10자)
세 패키지의 정적 SQL은 PL/SQL이 대문자와 바인드 이름을 정리해 똑같은 텍스트(... WHERE CUS_ID = :B1)로 공유 풀에 들어간다.
DB 문자셋 AL32UTF8 · NLS_LENGTH_SEMANTICS = BYTE · CURSOR_SHARING = EXACT

원본 SQL

sql

-- PKG_STLM (2019) : 정산
PROCEDURE get_cus(p_cus IN VARCHAR2, o_nm OUT VARCHAR2, o_gd OUT VARCHAR2) IS
  v_cus_id  VARCHAR2(10) := p_cus;
BEGIN
  SELECT CUS_NM, CUS_GD INTO o_nm, o_gd FROM M_CUS WHERE CUS_ID = v_cus_id;
END;

-- PKG_CS (2021) : 고객센터
PROCEDURE load_customer(p_cus IN VARCHAR2) IS
  v_cus_id  VARCHAR2(40) := p_cus;
  v_nm      VARCHAR2(40);
  v_gd      VARCHAR2(1);
BEGIN
  SELECT CUS_NM, CUS_GD INTO v_nm, v_gd FROM M_CUS WHERE CUS_ID = v_cus_id;
  g_screen.cus_nm := v_nm;  g_screen.cus_gd := v_gd;
END;

-- PKG_PNT (2023) : 포인트 — "넉넉하게"
FUNCTION grade_of(p_cus IN VARCHAR2) RETURN VARCHAR2 IS
  v_cus_id  VARCHAR2(4000) := p_cus;
  v_nm      VARCHAR2(4000);
  v_gd      VARCHAR2(4000);
BEGIN
  select cus_nm, cus_gd into v_nm, v_gd from m_cus where cus_id = v_cus_id;
  RETURN v_gd;
END;

실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위

xplan

[V$SQLAREA — 10:00]
 SQL_ID          VERSION_COUNT  EXECUTIONS  PARSE_CALLS  LOADS  SHARABLE_MEM
 7bq2mfx0d3k1a               3     141,207           38      3        61,512
 SQL_TEXT : SELECT CUS_NM, CUS_GD FROM M_CUS WHERE CUS_ID = :B1

[V$SQL — 자식 셋]
 CHILD  IS_SHAREABLE  EXECUTIONS  FIRST_LOAD_TIME          처음 부른 곳   PLAN_HASH
     0  N                  9,820  2024-06-17/06:10:02      PKG_STLM       2518412337
     1  N                 31,455  2024-06-17/07:58:41      PKG_CS         2518412337
     2  Y                 99,932  2024-06-17/09:03:17      PKG_PNT        2518412337

[V$SQL_BIND_METADATA — 자식마다 :B1의 그릇]
 CHILD  BIND_NAME  DATATYPE  MAX_LENGTH
     0  B1                1          32
     1  B1                1         128
     2  B1                1        4000

[V$SQL_SHARED_CURSOR — Y인 칸만]
   child 1 : BIND_LENGTH_UPGRADEABLE = Y
   child 2 : BIND_LENGTH_UPGRADEABLE = Y

[한 번 실행 — 어느 자식이든 같은 계획]
------------------------------------------------------------------------------------------------
| 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"=:B1)

문제

텍스트가 하나인데 자식 커서가 셋인 이유를 위 표로 설명하시오. 자식이 하나 늘 때마다 무엇이 되풀이되는지도 쓰시오.
세 패키지의 선언을 고쳐 자식이 하나로 모이게 하시오. 그 방법이 크기를 VARCHAR2(4000)으로 통일하는 것보다 나은 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 세 패키지 모두 변수를 컬럼에 묶는다 → 그릇이 하나(VARCHAR2(40) → 128바이트 구간) → 자식 하나
-- PKG_STLM
PROCEDURE get_cus(p_cus IN M_CUS.CUS_ID%TYPE, o_nm OUT M_CUS.CUS_NM%TYPE, o_gd OUT M_CUS.CUS_GD%TYPE) IS
  v_cus_id  M_CUS.CUS_ID%TYPE := p_cus;
BEGIN
  SELECT CUS_NM, CUS_GD INTO o_nm, o_gd FROM M_CUS WHERE CUS_ID = v_cus_id;
END;

-- PKG_CS
PROCEDURE load_customer(p_cus IN M_CUS.CUS_ID%TYPE) IS
  v_cus_id  M_CUS.CUS_ID%TYPE := p_cus;
  v_nm      M_CUS.CUS_NM%TYPE;
  v_gd      M_CUS.CUS_GD%TYPE;
BEGIN
  SELECT CUS_NM, CUS_GD INTO v_nm, v_gd FROM M_CUS WHERE CUS_ID = v_cus_id;
  g_screen.cus_nm := v_nm;  g_screen.cus_gd := v_gd;
END;

-- PKG_PNT
FUNCTION grade_of(p_cus IN M_CUS.CUS_ID%TYPE) RETURN M_CUS.CUS_GD%TYPE IS
  v_cus_id  M_CUS.CUS_ID%TYPE := p_cus;
  v_nm      M_CUS.CUS_NM%TYPE;
  v_gd      M_CUS.CUS_GD%TYPE;
BEGIN
  SELECT CUS_NM, CUS_GD INTO v_nm, v_gd FROM M_CUS WHERE CUS_ID = v_cus_id;
  RETURN v_gd;
END;

개선 후 실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위

xplan

[다음 날 10:00 — 세 패키지 반영 뒤]
 VERSION_COUNT  1 · LOADS 1 · 자식 0 · V$SQL_BIND_METADATA.MAX_LENGTH 128 · V$SQL_SHARED_CURSOR Y 칸 없음
 하드 파싱(이 문장) 아침마다 3 → 1 · SHARABLE_MEM 61,512 → 20,504

모범 답안 서술

세 자식의 계획 해시(2518412337)는 같다. 다른 것은 V$SQL_BIND_METADATA.MAX_LENGTH, 즉 바인드 변수를 담는 그릇의 크기다. PL/SQL의 정적 SQL은 변수를 선언한 크기의 그릇으로 넘긴다. 오라클은 그 크기를 32, 128, 2000, 4000바이트 구간으로 올려 잡는데, VARCHAR2(10)은 32, VARCHAR2(40)은 128, VARCHAR2(4000)은 4000 구간이다. 그래서 아침에 정산 배치가 처음 부르면 32바이트 그릇의 자식 0이 생긴다. 07:58에 고객센터가 128바이트 그릇으로 오면 자식 0의 그릇에 들어가지 않으므로 자식 1을 새로 만든다(BIND_LENGTH_UPGRADEABLE = Y). 09:03에 포인트 패키지가 4000바이트로 오면 또 자식 2를 만든다. 작은 그릇의 손님은 큰 그릇의 자식을 같이 쓸 수 있어서, 앞의 자식들은 IS_SHAREABLE = N이 되고 지금은 자식 2 하나만 쓰인다. 자식이 하나 늘 때마다 하드 파싱(LOADS 3)을 한 번 더 하고 공유 메모리를 그만큼 더 쓴다. 통계를 모아 커서가 무효화되는 밤이 지나면 이 일이 매일 되풀이된다. 이 문장 하나로는 작아 보여도 같은 모양의 문장이 30개면 아침마다 하드 파싱이 90번이고, 자식 목록이 길수록 커서를 찾는 동안 mutex를 오래 잡는다. 세 패키지의 변수를 모두 M_CUS.CUS_ID%TYPE으로 선언하면 그릇이 128바이트 하나로 맞춰져 자식이 하나가 된다(VERSION_COUNT 3 → 1). VARCHAR2(4000)으로 통일해도 자식은 하나가 되지만, 컬럼과 상관없는 크기로 굳는다. %TYPE은 컬럼이 바뀌면 따라간다. 그리고 VARCHAR2(10)은 고객 번호가 11자(CUS_1000000)가 되는 날 ORA-06502로 실패하는 숨은 버그이기도 하다.

해설

교재 11화 실습/06에서는 같은 문장을 VAR p_ym VARCHAR2(6)과 VARCHAR2(200)으로 불렀더니 그릇이 32 → 2000바이트로 바뀌며 자식이 하나 더 생겼다. PL/SQL 변수, SQL*Plus의 VAR, 드라이버의 바인드 크기가 모두 같은 규칙을 따른다.
화면 프로그램에서도 같은 일이 생긴다. 드라이버가 문자열 길이에 맞춰 바인드 크기를 정하거나 개발자가 크기를 제멋대로 주면, 짧은 값과 긴 값이 섞일 때마다 그릇이 커지며 자식이 는다(PARSE-030).
V$SQL_SHARED_CURSOR의 Y 칸은 "그 줄의 자식이 새로 생긴 이유"다. 그래서 자식 0 줄에는 Y가 없고, 자식 1과 2 줄에 BIND_LENGTH_UPGRADEABLE이 찍힌다.
PARSE_CALLS가 38뿐인 것은 PL/SQL이 커서를 세션에 캐시해 두고 다시 실행하기 때문이다. 자식 수는 파스 호출 수가 아니라 그릇 크기가 바뀐 횟수를 따라간다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황