SQLP 튜닝 연습장

테마

11화 · 파고들기 ​

필요할 때 찾아보는 문서다. 처음 읽을 때는 1_사건.md만으로 충분하다. 이 문서의 숫자는 모두 ora-hanorder(10화를 마친 상태: T_ORD_BIG.ORD_ST FREQUENCY 히스토그램, 인덱스 3개)에서 잰 값이다. 실습 스크립트 번호도 함께 적는다.

찾는 것바로 가기
라이브러리 캐시 · KGL 핸들 · heap 0 / heap 6 · 부모와 자식§1 구조
파싱 네 가지가 각각 무엇을 건너뛰나 · TKPROF의 Parse 줄§2 파싱
하드 파싱의 값: 3만 번 클릭 · 밀려나는 이웃 · 네 명 동시§3 하드 파싱
"같은 SQL"의 기준 · FORCE_MATCHING_SIGNATURE · 탐지 쿼리§4 같은 SQL
자식이 갈라지는 이유 · V$SQL_SHARED_CURSOR 읽는 법§5 자식
바인드 피킹 · 편향 컬럼 + 히스토그램§6 피킹
ACS: IS_BIND_SENSITIVE / IS_BIND_AWARE · 움직일 때와 못 움직일 때§7 ACS
CURSOR_SHARING=FORCE의 이득과 함정 · 조각별 리터럴 지도§8 FORCE
바인드 피킹을 끄면 · DBMS_SQL의 "값 없는 파싱"§9 피킹 끄기
OR-Expansion의 두 얼굴(08화 :C IS NULL과 rev02)§10 OR
Q4 rev03 · 정합성 회귀 체크리스트§11 rev03
IN 리스트 길이가 매번 다를 때§12 IN
ORA-04031: Free에서 본 것과 못 본 것§13 04031
측정값 전부§14 측정
원천 좌표§15 좌표

1. 라이브러리 캐시 — 핸들과 힙 ​

공유 풀(shared pool) 안의 라이브러리 캐시는 SQL, PL/SQL, 테이블 정의처럼 "실행에 필요한 물건"을 이름으로 찾아 쓰는 캐비닛이다. 물건 하나가 곧 KGL 객체 하나이고, 핸들 하나다.

 SQL 텍스트 ──해시──▶ 해시 버킷 ──▶ [부모 핸들] ── heap 0 : 텍스트 · 자식 목록 · 의존 객체
                                       │
                                       ├── [자식 핸들 0] ── heap 0 : 환경 · 바인드 정보
                                       │                  └ heap 6 : 실행계획 (가장 큰 덩어리)
                                       └── [자식 핸들 1] ── heap 0 · heap 6

실측 — Q1 rev03을 ALL_ROWS · FIRST_ROWS_10으로 한 번씩 (실습/91 · SYSDBA · X$KGLOB) ​

객체핸들의 부모 (KGLHDPAR)자식 번호heap 0heap 6
부모자기 자신655354,064없음
자식 0부모08,08036,416
자식 1부모18,08036,416
  • 실행계획(heap 6)은 자식에만 있다. 하드 파싱으로 만드는 것 중 가장 큰 것이 이것이다.
  • 같은 것을 V$ 뷰로 보면 V$SQL.SHARABLE_MEM은 자식마다 48,928, V$SQLAREA.SHARABLE_MEM(부모 합계)은 93,424다.

V$SQLAREA와 V$SQL — 부모와 자식 ​

뷰한 줄 =쓰는 곳
V$SQLAREA부모 커서 (SQL 텍스트 하나)VERSION_COUNT · 부모 단위 메모리 · "같은 문장이 몇 개인가"
V$SQL자식 커서계획 · 실행 통계 · IS_BIND_* · CHILD_NUMBER
V$SQL_SHARED_CURSOR자식 커서이 자식이 왜 따로 생겼나(§5)

⚠️ V$SQL.SHARABLE_MEM은 더하면 안 된다. 자식 줄마다 부모 몫이 섞여 있다. FORCE로 자식이 329개 생긴 Q4는 V$SQL 합계가 113.1MB, V$SQLAREA는 10.5MB였다(실습/01 [H]). 자식 하나의 SHARABLE_MEM이 360,389바이트로 찍히는 것은, 자식 목록만큼 커진 부모의 heap 0이 줄마다 함께 들어가기 때문이다. 부모 단위 메모리는 V$SQLAREA로 본다.

2. 파싱 네 가지 ​

애플리케이션이 SQL을 보내면 서버는 파스 호출(parse call)을 받는다. 그 뒤에 무슨 일이 일어나느냐에 따라 네 가지로 나뉜다.

이름파스 호출라이브러리 캐시에서 찾기최적화 · 실행계획 생성서식함 비유
①하드 파싱있다찾았는데 없다한다총무팀에 새 양식을 주문
②소프트 파싱있다해시 체인을 따라가 찾는다건너뛴다서식함까지 가서 칸을 찾아 꺼냄
③세션 커서 캐시 적중있다내 세션이 기억해 둔 자리로 바로 간다건너뛴다칸 위치를 적어 둔 수첩
④커서를 열어 둔 채 재실행없다안 간다건너뛴다책상 위에 펼쳐 둔 양식
  • ②와 ③을 가르는 것은 SESSION_CACHED_CURSORS다(이 컨테이너 기본값 50). 같은 문장을 몇 번 닫았다 다시 열면 세션이 그 커서의 위치를 기억하고, session cursor cache hits 통계가 오른다.
  • ④는 애플리케이션이 커서를 닫지 않고 바인드 값만 바꿔 다시 실행하는 것이다. PL/SQL은 같은 동적 SQL을 연달아 부르면 알아서 이렇게 한다. EXECUTE IMMEDIATE ... USING을 3만 번 불러도 파스 호출은 1번이다(실습/00 [B]). 화면 프로그램 쪽에서는 문장 캐시(statement cache)가 같은 역할을 한다.

실측 — 같은 2,000번 클릭 (실습/05 · DBMS_SQL로 파스 호출을 직접 쥔다) ​

걸린 시간 (cs)parse count (total)parse count (hard)session cursor cache hitsparse time cpu (cs)CPU (cs)logical reads
① 하드 (리터럴)167 · 1702,059 · 2,0622,034 · 2,000—155 · 162163 · 17032,204 · 31,580
② 소프트 (캐시 0)8 · 72,008401 · 0730,964
③ 세션 캐시 (캐시 50)6 · 92,00142,00416 · 830,964
④ 열어 둔 채4 · 52370530,964

(두 값은 1회차 · 2회차. 문장은 Q4 rev03. logical reads 30,964는 클릭당 15.5블록)

  • 세션 하나, 이 정도 크기에서는 ②③④가 구별되지 않는다. 차이는 여럿이 동시에 같은 문장을 찾을 때 난다(§3).
  • 하드 파싱이 4, 3으로 찍힌 것은 첫 파싱과 재귀 SQL 몫이다.

TKPROF의 Parse 줄 (실습/90 · 10046 level 12 · 20번씩) ​

[L] 리터럴 — 문장 20개. 표마다:
call     count    ...   query    rows
Parse        1              0       0
Execute      1              0       0
Fetch        1             14      11
Misses in library cache during parse: 1          ← 하드 파싱

[S] rev03 · 클릭마다 파스 호출 — 문장 1개:
Parse       20              0       0
Execute     20              0       0
Fetch       20            280     220
Misses in library cache during parse: 1          ← 첫 한 번만
Misses in library cache during execute: 1

[O] rev03 · 열어 둔 채 — 문장 1개:
Parse        1              0       0
Execute     20              0       0
Fetch       20            280     220

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
Misses in library cache during parse: 33         ← L 20 + S 1 + O 1 + 딕셔너리 재귀 SQL
TKPROF에서 읽는 것뜻
Parse 줄의 count파스 호출 수. 실행 수와 같으면 애플리케이션이 매번 파싱하는 것이고(②③), 1이면 열어 둔 채 쓰는 것이다(④)
Misses in library cache during parse하드 파싱 수
Misses in library cache during execute실행 때 계획을 다시 세운 수. DBMS_SQL처럼 값을 묶기 전에 파싱했거나 계획이 무효화된 경우다
Row Source의 card=옵티마이저가 본 행 수. [S]는 card=49957(값을 못 엿봤을 때의 추정으로 100만의 5% 언저리), [O]는 card=999990(엿본 값)이다. 같은 문장이라도 값을 엿봤는지가 여기서 드러난다(§9)

3. 하드 파싱의 값 ​

정산 목록 3만 번 클릭 (실습/00 · 단일 세션) ​

[A] 개발팀: 포스트잇을 문자열로[B] rev02 파일: 바인드
걸린 시간35.3~36.4초 (4회)0.67초 (세 번 다)
parse count (hard)30,000~30,431 (4회)1
parse time cpu3,466~3,493cs0
CPU used by this session3,617cs (두 번 다)67 · 68cs
session logical reads522,931 · 517,487 (클릭당 17.4 · 17.2)466,584 (클릭당 15.6)
recursive calls154,449 · 145,98430,009
공유 풀에 남은 문장V$SQL 308~481 (3만 개 중. 공유 풀 사정에 따라 실행마다 다르다)1 (실행 30,000)
  • CPU의 96%가 파싱이다(3,466 / 3,617). 데이터를 읽는 데가 아니라 "어떻게 읽을지 정하는 데" 썼다.
  • 하드 파싱도 블록을 읽는다. 클릭당 약 2블록으로, 딕셔너리를 찾는 재귀 SQL 몫이다.
  • 3만 개 중 몇백 개만 남았다. 공유 풀은 커서를 다 쥐고 있지 않고 오래 안 쓴 것부터 내보낸다.

밀려나는 이웃 ​

[A0]에서 Q1 rev03을 한 번 실행해 V$SQL에 올린 뒤 [A]를 돌렸다. [A] 직후 Q1은 V$SQL에 없었다(두 번 다). Q1은 아무 잘못도 없이 다음 실행 때 다시 하드 파싱된다.

"공유 풀에 같은 SQL이 수만 개"라는 말은 반만 맞다. 수만 개가 쌓여 있는 것이 아니라 지나간다. 들어올 때마다 하드 파싱을 하고, 나갈 때는 이웃을 데리고 나간다.

네 명 동시 (실습/08 · 07화 03 시리즈 방식) ​

클릭 1,000번당 · 네 명 평균1회차 리터럴2회차 리터럴1회차 바인드2회차 바인드1회차 소프트
걸린 시간 (cs)204.6171.45.15.45.5
CPU (cs)135.5136.53.43.63.6
Concurrency 대기 (cs)9.5811.250.110.150.28
resmgr:cpu quantum (cs)60.324.31.61.71.7
하드 파싱 (네 명 합)12,00712,007444

(리터럴 일꾼은 각자 3,000번, 바인드와 소프트 일꾼은 각자 20,000번. 바인드 일꾼은 클릭마다 커서를 열고, 파싱하고, 닫는다. 문장 캐시가 없는 화면 프로그램과 같다. 바인드 문장은 rev03)

  • 리터럴 일꾼 한 명의 대기 맨 위에 있는 것은 latch: shared pool(3,000번 동안 23~25cs)로, 공유 풀에 새 자리를 만들 때 잡는 래치다. 그다음은 row cache mutex(2~5cs, 딕셔너리 캐시)다.
  • 소프트(캐시 0) 일꾼의 대기 맨 위는 library cache: mutex X(2~5cs)다. 세션 캐시를 켠 바인드 일꾼은 1~3cs였다. ②와 ③의 차이는 여럿이 함께 쓸 때 이렇게 드러나지만, 크지는 않다.
  • resmgr:cpu quantum은 Free가 CPU를 2개로 묶어 두어서 생기는 줄서기다(07화에서 본 것). 실행마다 크게 흔들린다(60.3, 24.3, 3회차 7). 흔들리지 않는 숫자는 CPU 열이다(3회차 리터럴 132.3, 바인드 3.5).

Free 한 대에서 만들 수 있는 동시성은 여기까지다. 수백 세션이 하드 파싱을 두고 다투는 규모의 래치와 뮤텍스 경합은 이 교재의 범위 밖이다(별권).

4. 같은 SQL인가 — 텍스트와 지문 ​

오라클이 "같은 문장"으로 보는 기준은 텍스트다. 대소문자, 공백, 주석, 리터럴 중 하나만 달라도 다른 부모 커서가 된다.

컬럼무엇이 같으면 같은 값인가쓰는 곳
SQL_ID / HASH_VALUE텍스트가 바이트까지 같다부모 커서를 가리킬 때
EXACT_MATCHING_SIGNATURE텍스트가 같다 (공백 · 대소문자를 정규화한 뒤)SQL Profile 등
FORCE_MATCHING_SIGNATURE리터럴을 바인드로 바꿔 놓고 보면 같다리터럴만 다른 무리 찾기

이 화의 실습은 측정 문장마다 /* CH11 [태그] R+시각 */ 주석을 붙인다. 주석도 텍스트다. 표식이 다르면 다른 문장이 된다. 이 성질을 이용해 실행마다 새 문장 무리를 만들어서 앞 실행의 문장과 섞이지 않게 했다. 반대로 공유돼야 할 문장끼리는 표식이 같아야 한다(같은 구간 안에서는 표식을 바꾸지 않는다).

탐지 쿼리 — 공유 풀에서 "리터럴만 다른 문장" 무리 (실습/00 [C]) ​

sql
SELECT force_matching_signature AS fms,
       COUNT(*)                 AS parents,        -- 리터럴만 다른 부모 커서 수
       SUM(version_count)       AS children,
       SUM(executions)          AS execs,
       ROUND(SUM(sharable_mem)/1024/1024, 1) AS mb,
       SUBSTR(MIN(sql_text), 1, 60) AS sample
  FROM v$sqlarea                                    -- 부모 단위 (§1 의 경고)
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) >= 50
 ORDER BY parents DESC
 FETCH FIRST 5 ROWS ONLY;

실측해 보니 [A] 직후 맨 위가 [A] 무리였다(부모 301개와 397개, 각각 1회차와 2회차). PARENTS가 크고 EXECS가 그와 비슷하면 한 번 쓰고 버리는 문장이다.

5. 자식 커서가 갈라지는 이유 ​

부모(텍스트)가 같아도 같이 쓸 수 없는 사정이 있으면 자식이 새로 생긴다. 그 사정은 V$SQL_SHARED_CURSOR에 칸(Y/N)으로 적힌다.

Y인 칸만 뽑는 법 (실습/01 · 02 · 06) ​

칸 이름은 버전마다 조금씩 다르다. 그래서 길이 1짜리 VARCHAR2 칸을 전부 모아 UNPIVOT한다.

sql
DECLARE
  cols VARCHAR2(32767); TYPE rc IS REF CURSOR; c rc; ch NUMBER; col VARCHAR2(128);
BEGIN
  SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id) INTO cols
    FROM all_tab_columns
   WHERE owner = 'SYS' AND table_name = 'V_$SQL_SHARED_CURSOR' AND data_type = 'VARCHAR2' AND data_length = 1;
  OPEN c FOR 'SELECT child_number, col FROM (SELECT child_number, ' || cols
          || ' FROM v$sql_shared_cursor WHERE sql_id = ''&sql_id'')'
          || ' UNPIVOT (v FOR col IN (' || cols || ')) WHERE v = ''Y'' ORDER BY 1';
  LOOP FETCH c INTO ch, col; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE('child ' || ch || ' : ' || col); END LOOP;
  CLOSE c;
END;
/

이 화에서 만난 사유 ​

칸무엇 때문에이 화의 실측
BIND_LENGTH_UPGRADEABLE바인드 변수의 그릇이 기존 자식보다 크다Q1 rev03 · VAR p_ym VARCHAR2(6) → VARCHAR2(200). 그릇 32 → 2000바이트(V$SQL_BIND_METADATA.MAX_LENGTH, 실습/06)
OPTIMIZER_MODE_MISMATCH세션의 optimizer_mode가 다르다Q1 rev03 · FIRST_ROWS_10 세션 (실습/06)
HASH_MATCH_FAILEDFORCE가 "안전하지 않은 리터럴"로 본 값이 다르다Q4 · DATE '2024-12-30'과 DATE '2024-12-29' → 자식 0 · 1 (실습/01 [B])
BIND_EQUIV_FAILUREACS가 이 값 범위에는 다른 자식이 필요하다고 판단상태 탭 100건 · 취소 값 (실습/02 [C])
LOAD_OPTIMIZER_STATSACS를 시작하려고 하드 파싱을 강제같은 실험의 자식 0 (실습/02 [C])

실습/06의 결과는 부모 하나, VERSION_COUNT 3, 실행 5, LOADS 3이다. 자식 0은 IS_SHAREABLE = N(그릇이 작아서 더는 안 쓴다)이고, 자식 1은 보통 세션 몫, 자식 2는 FIRST_ROWS 몫이다.

📌 Y 칸과 REASON 칸은 방향이 다르다 ​

Y 칸은 "그 줄의 자식이 새로 생긴 이유"다(자식 1 줄에 BIND_LENGTH_UPGRADEABLE). REASON(XML)은 "그 줄의 자식을 다음 파스가 쓰지 못한 이유"에 가깝다. 같은 실험에서 자식 0 줄의 REASON은 Bind mismatch(22), 자식 1 줄은 Optimizer mismatch(10)이었다. 둘을 섞어 읽으면 한 칸씩 어긋난다. "왜 자식이 생겼나"는 Y 칸으로 답한다.

6. 바인드 피킹 ​

바인드 변수를 쓰면 SQL 텍스트에는 값이 없다. 그래도 오라클은 하드 파싱하는 순간 그 바인드에 들어 있는 값을 엿보고, 그 값이면 몇 건이 나올지 셈해서 계획을 세운다. 그 뒤에는 어떤 값이 오든 그 계획을 같이 쓴다.

피킹은 다음 두 조건이 겹칠 때 문제가 된다.

  1. 값마다 건수가 크게 다르고(편향, skew), 오라클이 그 사실을 안다(히스토그램).
  2. 건수에 따라 고를 길이 둘 이상 있다.

T_ORD_BIG.ORD_ST — 10화 종료 상태 ​

값행수비율히스토그램 (ENDPOINT_NUMBER 누적)
CANCEL20,0002%20,000
COMPLETE900,00090%920,000
PENDING80,0008%1,000,000

T_ORD_BIG에는 ORD_ST 인덱스가 없다. 그래서 "상태 = X"만 묻는 문장이면 고를 길이 하나(FULL)뿐이고, 피킹이 해를 끼칠 자리도 없다. 길이 둘이 되는 것은 상태 조건과 정렬이 함께 올 때다. 최근 순 인덱스 IX_ORD_BIG_DT_SEQ를 거꾸로 내려가며 상태를 거를지, 테이블을 다 읽고 정렬할지 골라야 한다.

정산 상태 확인 화면 — 최근 1,000건 (실습/01 [C]) ​

sql
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
         WHERE ORD_ST = 'CANCEL' ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000;
리터럴 (EXACT)계획계획 해시Buffers
'COMPLETE'INDEX FULL SCAN DESCENDING + TABLE ACCESS BY INDEX ROWID3697846331,016
'PENDING'TABLE ACCESS FULL + SORT ORDER BY STOPKEY6547982475,826
'CANCEL'〃6547982475,826

억지로 인덱스 계획을 쓰면 취소 50,347, 미결 13,510이다(§8 [D]). 취소는 50건에 한 건꼴이라, 1,000건을 채우려면 인덱스를 따라 약 5만 행을 테이블까지 가서 확인해야 한다.

이 데이터에는 버릇이 하나 있다. 미결과 취소는 생성 순번의 나머지(MOD)로 정해져서 1,095일 중 219일에만 있고(닷새에 하루), 마지막 날은 12월 26일이다. 12월 27~30일에는 하나도 없다. 그래서 "최근 11건"처럼 아주 적게 뽑아도 인덱스 쪽은 취소 3,772블록, 미결 3,694블록을 지나야 했다(탐색 측정). 상관 컬럼 이야기는 10화에서 다룬 영역이다.

7. 적응적 커서 공유 (ACS) ​

표시뜻
IS_BIND_SENSITIVE = Y"값에 따라 계획이 갈릴 수 있다"는 표시. 히스토그램이 있는 컬럼의 = 바인드, 범위 바인드 등에 붙는다. 피킹을 했을 때만 붙는다(§9)
IS_BIND_AWARE = Y"값의 범위마다 자식을 따로 쓴다"로 바뀐 자식
V$SQL_CS_HISTOGRAM실행할 때마다 일량을 세 칸(BUCKET_ID 0 · 1 · 2) 중 하나에 센다
V$SQL_CS_SELECTIVITYbind aware 자식이 맡은 선택도 범위

순서: bind sensitive 자식이 실행되면서 칸이 쌓인다 → 서로 다른 칸의 실행이 섞이면 → 다음 파스 호출 때 bind aware 자식을 새로 만들고 값을 다시 엿본다(BIND_EQUIV_FAILURE). 칸을 가르는 기준은 공식문서에 나와 있지 않다. 아래는 이 버전에서 잰 결과다.

언제 움직이고 언제 못 움직이나 (실습/02 · statistics_level = TYPICAL) ​

순서한 번 실행 BuffersCS_HISTOGRAM결과
[A] 1,000건완료 → 취소 ×41,0xx → 50,347 ×41:5못 고침: 다 1번 칸
[B] 1,000건취소 → 완료 ×45,826 → 5,826 ×41:5못 고침
[C] 100건완료 → 취소 ×3104 → 8,334 → 5,826 → 5,826자식0 0:1 1:1 · 자식1 1:2고침: 나쁜 한 번 뒤 자식 1 (선택도 0.018~0.022)
[E] 100건 · PL/SQL완료 → 취소 ×5103 → 8,333 ×5—못 고침: 파스 호출 1번
  • 1,034와 50,347은 50배 차이인데 같은 칸이다. 칸은 셋뿐이다.
  • [C]처럼 고쳐져도 나쁜 실행이 최소 한 번 있다.
  • [E]: PL/SQL이 커서를 열어 둔 채 다시 실행하면 파스 호출이 없어서 자식을 다시 고를 기회가 없다. 같은 문장, 같은 순서인데 SQL*Plus에서는 고쳐지고 PL/SQL에서는 안 고쳐졌다.

☠️ 재는 도구가 결과를 바꿨다 — statistics_level ​

[B]와 같은 순서를 statistics_level = ALL로 다시 재면(실습/02 [D]) 완료 탭이 두 번째부터 1,016으로 고쳐진다. CS_HISTOGRAM은 1:1 2:1로, 완료(FULL) 실행이 2번 칸에 들어갔다. TYPICAL에서는 1번 칸이었다. ALL(그리고 GATHER_PLAN_STATISTICS 힌트)은 실행계획 줄마다 행 수와 시간을 모으는 장치다. DISPLAY_CURSOR의 ALLSTATS가 이 값을 쓴다. 이 장치를 켜자 ACS가 세는 일량이 달라졌다. 운영은 TYPICAL이다. 그래서 이 화의 ACS와 FORCE 측정은 TYPICAL에서 했고, 한 번 실행한 Buffers는 V$SQL.BUFFER_GETS가 늘어난 양으로 쟀다. 새 문장의 첫 실행에는 하드 파싱 중에 읽은 블록 수십 개가 더해진다(예: 완료 탭 첫 실행 1,016~1,065).

8. CURSOR_SHARING=FORCE — 이득과 함정 ​

CURSOR_SHARING은 서버가 리터럴을 바인드로 바꿔 끼울지 정하는 파라미터로, 값은 EXACT(기본)와 FORCE가 있다. SIMILAR도 이 버전에서 설정은 받아들여지지만(측정으로 확인) 폐기 예정 값이라 다루지 않는다. 이 화의 실험은 모두 ALTER SESSION으로, 내 세션에만 켜고 끝에서 EXACT로 되돌렸다.

조각마다 박혀 있던 리터럴 (실습/01 [A][B]) ​

조각화면이 고르는 값문장 안의 상수FORCE가 바꾼 텍스트EXACT → FORCE Buffers계획
Q1 rev03조회월: 이미 바인드 :p_ym(03화)'01' · 'YYYYMMDD' · 1:p_ym||:"SYS_B_0" · :"SYS_B_1" … :"SYS_B_4"91 → 91같음 (92368507)
Q2 rev01없음없음그대로5,869 → 5,868같음 (2292855265)
Q3 rev01없음SELECT 1 · FETCH FIRST 5SELECT :"SYS_B_0" · FETCH FIRST :"SYS_B_1"840 → 839같음 (2231864090)
Q4 (개발팀 구현)포스트잇: 문자열로 이어 붙임날짜 · 번호 · ROWNUM <= 11DATE :"SYS_B_0" … ROWNUM <= :"SYS_B_3"15 → 15같음 (3610158121)
상태 탭 (강도현)탭: 상수'COMPLETE' 등 · 1000ORD_ST = :"SYS_B_0" · ROWNUM <= :"SYS_B_1"아래 표첫 값이 정함

이득 — Q4 3만 번 클릭 (실습/01 [H]) ​

하드 파싱걸린 시간공유 풀
EXACT (개발팀 구현)30,000~30,43136.19초부모 3만 개가 지나감
FORCE329~3341.45~1.55초부모 1 · VERSION_COUNT 329 · 부모 10.5MB
바인드 (rev02 · rev03)10.67초부모 1 · 자식 1

FORCE는 효과가 있다. 다만 1이 아니라 329다. 3만 클릭이 지나간 서로 다른 날짜 수와 같다(하드 파싱 329~334에서 나머지는 재귀 SQL 몫).

함정 1 — DATE '...'는 값마다 자식 (HASH_MATCH_FAILED) ​

FORCE는 DATE '2024-12-30'을 DATE :"SYS_B_0"으로 바꿔 적지만, 이 자리는 안전하지 않은 리터럴로 다룬다. 값이 다르면 기존 자식을 못 쓰고 새로 만든다. 같은 날짜의 두 페이지는 자식 0을 같이 쓰고, 다른 날짜는 자식 1을 만들었다(실습/01 [B]). 탐색 측정에서 TO_DATE('20241230','YYYYMMDD') 형태는 자식 하나를 같이 썼다.

함정 2 — 일부러 박은 리터럴이 하나로 묶인다 (실습/01 [C]~[F]) ​

상태 탭 · 최근 1,000건완료미결취소
EXACT (탭마다 문장)1,0165,8265,826
FORCE · 첫 손님 완료1,01613,510 (2.3배)50,347 ×3 (8.6배)
FORCE · 첫 손님 취소5,826 ×3 (5.7배)—5,826
FORCE + /*+ CURSOR_SHARING_EXACT */1,016—5,826
  • 어느 쪽이든 누군가는 손해를 본다. 그리고 1,000건 화면에서는 ACS도 고치지 못했다(§7).
  • CURSOR_SHARING_EXACT 힌트를 쓰면 FORCE를 켠 채로도 그 문장만은 리터럴을 지킨다. FORCE를 꼭 켜야 하는 날의 탈출구지만, 거꾸로 말하면 리터럴이 맞는 문장을 전부 찾아 힌트를 붙여야 한다.

함정 3 — 결과의 칸 길이가 바뀐다 (실습/01 [G]) ​

SELECT SUBSTR(CUS_ID, 1, 4), 'A' FROM M_CUS의 결과 열 최대 길이(DBMS_SQL.DESCRIBE_COLUMNS):

SUBSTR(CUS_ID,1,4)'A'
EXACT16바이트1
FORCE16032

SUBSTR의 1과 4가 바인드로 바뀌면 오라클은 결과 길이를 컬럼 최대(40자 × 4바이트)로 잡는다. 결과 칸 길이에 기대는 고정폭 화면, 파일, 보고서가 깨진다. 실행계획과 상관없는 함정이라 Buffers로는 보이지 않는다.

9. 바인드 피킹을 끄면 ​

_optim_peek_user_binds = FALSE로 끈다(밑줄로 시작하면 숨은 파라미터다. 운영에서는 오라클 지원 없이 바꾸지 않는다). 끄면 오라클은 값을 모르는 채로 계획을 세운다. 이 화에서는 세션에서만 끄고 바로 되돌렸다(실습/03).

문장피킹 켬 (기본)피킹 끔
상태 탭 1,000건 (바인드)첫 값이 정함 (§7)셋 다 인덱스 계획: 완료 1,017 · 미결 13,510 · 취소 50,347. IS_BIND_SENSITIVE = N(ACS도 손을 놓는다)
Q4 rev02 (포스트잇 바인드)어디서 태어나든 14~172페이지 1,005,991 · 100페이지 1,005,006 · 1,000페이지 995,954 · 10,000페이지 905,413 · 마지막 18
Q4 rev0314~1714~17 (14 · 14 · 17 · 16 · 16)

값을 모른 채 파싱되는 다른 길 — DBMS_SQL ​

DBMS_SQL.PARSE는 BIND_VARIABLE보다 먼저 호출된다. 그래서 파싱하는 순간에는 엿볼 값이 없다. 계획의 Outline에도 피킹을 끈 것과 똑같이 찍힌다.

      OPT_PARAM('_optim_peek_user_binds' 'false')

실습/05 [5]에서 DBMS_SQL로 2페이지를 한 번 부르면 rev02는 1,005,990, rev03은 13~19였다. TKPROF의 Row Source에서도 차이가 보인다. DBMS_SQL로 부른 rev03은 card=49957(값을 못 엿봤을 때의 추정), EXECUTE IMMEDIATE ... USING으로 부른 rev03은 card=999990(엿본 값)이다. 바인드 수가 매번 다른 동적 SQL은 PL/SQL에서 DBMS_SQL로 짜게 된다(§12의 ②가 그 예다).

결론: 바인드 문장은 "엿본 값이 무엇이든, 엿보지 못해도 같은 계획이 나오는 모양"으로 쓴다. 이것이 SQL 코딩 가이드 v0.1의 3번 조문이다.

10. OR-Expansion의 두 얼굴 ​

OR-Expansion은 OR로 이어진 조건을 갈래로 나눠 UNION-ALL로 합치는 변환이다. 계획에 VW_ORE_…가 보이면 이 변환이 일어난 것이다(08화 변환 도감 참고).

좋은 얼굴 — 08화 cust_id = NVL(:c, cust_id) (구 시스템 ORDERS · 실습/03 [E]) ​

xplan
|   2 |   VIEW                                 | VW_ORE_9F22E1CA | Starts
|   3 |    UNION-ALL                           |                 |
|*  4 |     FILTER                             |                 |   1     ← :C IS NOT NULL
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS         |   1 / 0
|*  6 |       INDEX RANGE SCAN                 | ORDERS_X_CUST   |   1 / 0
|*  7 |     FILTER                             |                 |   1     ← :C IS NULL
|*  8 |      TABLE ACCESS FULL                 | ORDERS          |   0 / 1
:c인덱스 갈래 StartsFULL 갈래 StartsBuffers계획 해시
1231013593186481
NULL011,205593186481

계획은 하나, 길은 둘이다. 어느 길을 탈지는 실행할 때 :C IS NULL 필터가 고른다. 08화가 넘긴 "바인드 값에 따라 갈리는 계획"의 답이 이것이다. 값이 바뀌어도 커서는 하나이고, 피킹이나 ACS에 기대지 않는다.

나쁜 얼굴 — Q4 rev02를 값 없이 파싱했을 때 (실습/03 [B]) ​

xplan
|*  3 |    SORT ORDER BY STOPKEY                 |                   |
|   4 |     VIEW                                 | VW_ORE_23B51052   |
|   5 |      UNION-ALL                           |                   |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG         |
|*  7 |        INDEX RANGE SCAN                  | IX_ORD_BIG_DT     |   ← ORD_DT < :p_dt  (2페이지면 거의 전부)
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG         |
|*  9 |        INDEX RANGE SCAN                  | IX_ORD_BIG_DT_SEQ |   ← ORD_DT = :p_dt AND ORD_SEQ <= :p_seq

두 갈래를 둘 다 끝까지 읽고 합친 뒤 정렬해 11건을 자른다. 값을 알았다면(피킹) 첫 갈래가 거의 전부라는 걸 보고 펼치지 않았을 것이다. 실제로 피킹을 켜면 INDEX RANGE SCAN DESCENDING + STOPKEY 하나로 14블록이다.

펼칠 거리를 없앤다 — rev03 ​

sql
-- rev02 : WHERE ORD_DT <= :p_dt AND (ORD_DT < :p_dt OR ORD_SEQ <= :p_seq)
-- rev03 : WHERE ORD_DT <= :p_dt AND NOT (ORD_DT = :p_dt AND ORD_SEQ > :p_seq)

옵티마이저는 rev03의 NOT을 다시 ("ORD_DT"<>:P_DT OR "ORD_SEQ"<=:P_SEQ)로 풀어 쓰지만, 이번에는 펼칠 갈래가 아니라 인덱스 안에서 거르는 filter로 쓴다(피킹 켬, 피킹 끔, DBMS_SQL 모두 계획 해시 3610158121).

11. Q4 rev03 ​

리비전/Q4_rev03.sql

측정 (실습/04 · 진짜 DATE 바인드 · 피킹 켬) ​

페이지121001,00010,000마지막 (100,000)
Buffers141415171616 (10건 · 다음 없음)
문장첫 페이지다음 페이지〃〃〃〃

문장은 첫 페이지용과 다음 페이지용 둘이고, 하드 파싱도 두 번이다. 10만 페이지를 다 넘기는 동안 다음 페이지 문장은 LOADS 1, 실행 99,999, 페이지당 15.6블록이었다.

정합성 회귀 체크리스트 (08화 양식 · 실습/04) ​

#항목대상 / 방법결과판정
A기준rev00 (OFFSET + 전체 정렬 · FULL 고정). 원장은 T_ORD_BIG 전체를 (ORD_DT DESC, ORD_SEQ DESC)로 정렬한 것—정함
B건수 · 합계 · MINUS 양방향 · 중복 키rev03 (DATE 바인드) vs rev00, 100페이지10 = 10 · 2,627,200 = 2,627,200 · 0 / 0 · 순서 같음 · 정렬 키 중복 0통과
C잘라 내기 전 전체rev03으로 100,000페이지를 끝까지 넘기며 전체 정렬 커서와 한 행씩 대조페이지 100,000 · 행 1,000,000 · 합계 255,398,623,700 · 어긋난 행 0통과
D조인마다 1 쪽Q4는 조인이 없다—해당 없음 (조인 없음)
E경계 데이터날짜가 바뀌는 92페이지 (12월 30일 3건 + 29일 7건) · 마지막 페이지92페이지 10 = 10 · 2,848,500 · 0 / 0 · 날짜 2종류 / 마지막 10 = 10 · 3,095,700 · 0 / 0통과
F계획 해시와 무관하게 대조rev02와 rev03은 피킹을 켜면 계획 해시가 같다(3610158121). 그래서 결과로 대조한다(B · C)통과
G변환을 근거로 쓰지 않음rev02의 OR-Expansion은 성능 설명에만 썼다. 뜻이 같다는 것은 C로 증명—통과
H영향 분석변경은 Q4 다음 페이지 문장 하나. 인덱스 · 통계 · 파라미터 변경 없음. FORCE와 피킹 끄기를 채택하지 않았으므로 다른 문장에 영향 없음—통과

12. IN 리스트의 길이 ​

"고객을 여러 명 골라 이름을 붙이는" 화면이다. 고르는 수는 1~10명, 요청은 300개다(난수 시드 고정, 길이 분포 1:28 2:31 3:31 4:30 5:38 6:26 7:27 8:37 9:25 10:27). 대상은 M_CUS와 PK_M_CUS다(실습/07).

방식문장 (부모)하드 파싱요청 한 번 logical reads
① 리터럴 IN ('CUS_000123', …)300300 · 30312.3 · 12.5
② 길이만큼 바인드 IN (:b1 … :bk)104012.3 · 12.7
③ 10칸 고정 · 남는 칸은 마지막 값 되풀이1412.3
④ 컬렉션 IN (SELECT column_value FROM TABLE(:arr))1112.8 · 12.9
  • 바인드로 바꿔도 칸 수가 문장의 일부다. 길이가 열 가지면 문장도 열 개다.
  • ②③의 하드 파싱이 문장 수보다 많은 것은 DBMS_SQL이 값을 묶기 전에 파싱하고 실행 때 한 번 더 맞추기 때문이다(§2 TKPROF의 Misses during execute).
  • ③의 계획은 INLIST ITERATOR + INDEX UNIQUE SCAN PK_M_CUS(E-Rows 10)다. 되풀이한 값 때문에 일량이 늘지는 않았다.
  • ④의 계획은 COLLECTION ITERATOR PICKLER FETCH(E-Rows 5) → SORT UNIQUE → NESTED LOOPS + PK_M_CUS다. 컬렉션의 건수 추정이 계획을 정하므로, 수천 개를 넘기는 화면이라면 다시 재야 한다.

13. ORA-04031 ​

메시지는 "unable to allocate N bytes of shared memory"이다. 공유 풀(또는 large pool 등)에서 필요한 크기의 연속된 메모리를 얻지 못했다는 뜻이다. 오라클은 먼저 붙잡히지 않은(unpinned) 조각을 내보내 자리를 만들고, 그래도 자리를 못 만들 때 이 에러를 낸다.

Free에서 본 것 ​

관찰값
공유 풀 크기 (V$SGA_DYNAMIC_COMPONENTS · CDB)416MB (sga_target 0 · 고정)
3만 개 하드 파싱 뒤 V$SQL에 남은 [A] 문장308~481
[A] 앞에 올려 둔 이웃 Q1[A] 직후 V$SQL에 없음 (두 번 다)
ORA-04031나지 않았다

재현하지 못했다. 3만 개를 넣어도 공유 풀은 에러 대신 밀어내기로 버텼다. 04031이 나려면 내보낼 수 없는 것들(실행 중이거나 열려 있어 붙잡힌 커서, 조각난 큰 요청)로 공유 풀이 막혀야 하는데, 그런 상태를 이 컨테이너 한 대로 만들어 보지는 않았다. 대신 밀려나는 것을 쟀다. 그 대가는 이웃이 다시 하드 파싱을 해야 한다는 점이다. 대규모 EE 환경에서 04031이 터지는 모습은 별권에서 다룬다.

14. 이 화의 측정값 전부 ​

ora-hanorder · Oracle AI Database 26ai Free 23.26.2.0.0 · cpu_count 2 · 공유 풀 416MB · session_cached_cursors 50 · statistics_level TYPICAL (따로 적은 곳 제외). 값이 두 개면 1회차 · 2회차다.

3만 번 클릭 (실습/00) ​

[A] 리터럴[B] 바인드FORCE ([H] 실습/01)
걸린 시간36.19 · 36.19초0.67 · 0.67초1.45~1.55초
하드 파싱30,000~30,4311 · 1329~334
parse time cpu3,466~3,493cs0—
CPU3,617 · 3,617cs67 · 68cs145~155cs
logical reads522,931 · 517,487466,584 · 466,584—
V$SQL에 남은 것308~4811부모 1 · VERSION_COUNT 329 · 10.5MB
이웃 Q1밀려남 · 밀려남——

여섯 조각 · FORCE (실습/01 [A][B] · GATHER_PLAN_STATISTICS) ​

EXACTFORCE
Q1 rev039191
Q2 rev015,8695,868
Q3 rev01840839
Q4 2 · 3 · 100페이지1515 · 15 · 15 (자식 0 · 0 · 1)

상태 탭 · 최근 1,000건 (실습/01 · 02 · 03) ​

경우완료미결취소
EXACT1,016 (첫 실행 1,016~1,065)5,826 · 5,8305,826
인덱스 계획 (FORCE 완료 먼저 · 바인드 완료 먼저 · 피킹 끔)1,016~1,03413,51050,347
FULL 계획 (FORCE 취소 먼저 · 바인드 취소 먼저)5,826—5,826~5,830
CURSOR_SHARING_EXACT1,016—5,826

ACS (실습/02) ​

§7의 표를 본다. 100건 화면: 완료 104 · 취소(나쁜 한 번) 8,334 · 이후 5,826 · 선택도 0.018~0.022 · PL/SQL 열어 둔 채 완료 103 · 취소 8,333 ×5 · 파스 호출 1.

피킹 끔 · rev02 / rev03 (실습/03 · 04 · 05) ​

페이지21001,00010,000100,000
rev02 · 피킹 끔1,005,9911,005,006995,954905,41318
rev03 · 피킹 끔1414171616
rev02 · 피킹 켬 (2페이지에서 태어남 · 마지막에서 태어남)14 · 1414 · 1417 · 1716 · 1616 · 16
rev03 · 피킹 켬 (실습/04)1415171616
DBMS_SQL (실습/05 [5]) · rev02 / rev031,005,990 / 13 · 19

파싱 네 가지 · 네 명 동시 · 부모와 자식 · IN 리스트 · KGL ​

→ §2 · §3 · §5 · §12 · §1

rev03 정합성 ​

→ §11. 10만 페이지 순회 3.4 · 3.7초.

15. 원천 좌표 ​

전체 지도 → 00_자료준비/06_원서와_공식문서_지도.md PDF 페이지 = 책 페이지 − 2 · 성능고도화I_목차색인.md (공개 사이트에는 없음)

성능고도화 I권 04장 — 라이브러리 캐시 최적화와 SQL 커서 (주 원천) ​

주제책 페이지
SQL 파싱 · SQL 최적화 · Row-Source Generation248 · 249 · 251
라이브러리 캐시 구조252
커서란? · 커서 공유257 · 260
Child 커서를 공유하지 못하는 경우 · Parent 커서를 공유하지 못하는 경우264 · 270
바인드 변수의 중요성272
바인드 변수의 부작용과 해법 · 적응적 커서 공유 · 입력 값에 따라 SQL 분리 · Literal 상수 값 사용277 · 283 · 286 · 288
세션 커서 캐싱 · 애플리케이션 커서 캐싱289 · 297
Static SQL vs Dynamic SQL · 문제의 본질은 바인드 변수 사용 여부307 ~ 314
Dynamic SQL 사용 기준 · 선택적 검색 조건315 · 317 ~ 330

성능고도화 I권 Ch1 · 그 밖 ​

주제권-장책 페이지
Shared Pool: 딕셔너리 캐시 · 라이브러리 캐시I권 Ch194 · 96
Library Cache Lock & Pin · 두 직렬화 장치를 따로 두는 이유I권493 ~ 498
Cursor_SharingI권502

오라클 공식문서 ​

주제매뉴얼장 제목
커서 공유 · 바인드 피킹 · ACS · CURSOR_SHARINGtgsqlImproving Real-World Performance Through Cursor Sharing
SQL 처리 단계 · 하드/소프트 파싱 · 공유 SQL 영역tgsqlSQL Processing
공유 풀 · 라이브러리 캐시 구조cncptMemory Architecture (Shared Pool)
공유 풀 크기 · ORA-04031tgdbaTuning the Shared Pool and the Large Pool
CURSOR_SHARING · SESSION_CACHED_CURSORS · OPEN_CURSORSrefrnInitialization Parameters
V$SQL · V$SQLAREA · V$SQL_SHARED_CURSOR · V$SQL_CS_HISTOGRAM · V$SQL_CS_SELECTIVITY · V$SQL_BIND_METADATA · V$LIBRARYCACHErefrnDynamic Performance Views
CURSOR_SHARING_EXACT 힌트sqlrfComments — Hints
10046 · TKPROFtgsqlPerforming Application Tracing

이 화에서 꼭 확인할 뷰 ​

sql
V$SQLAREA            -- 부모: version_count · executions · loads · force_matching_signature · sharable_mem
V$SQL                -- 자식: child_number · is_bind_sensitive · is_bind_aware · is_shareable · plan_hash_value
V$SQL_SHARED_CURSOR  -- 자식이 따로 생긴 이유 (Y 칸) · reason (XML)
V$SQL_CS_HISTOGRAM   -- ACS 의 세 칸 · V$SQL_CS_SELECTIVITY -- bind aware 자식의 선택도 범위
V$MYSTAT             -- parse count (total) · parse count (hard) · session cursor cache hits · parse time cpu
V$SES_OPTIMIZER_ENV  -- 내 세션의 cursor_sharing · optimizer_mode · (바꿨다면) _optim_peek_user_binds

다음 화가 여기서 가져가는 것 ​

화가져가는 것
12하드 파싱이 3만에서 1이 되면 같은 시간에 더 많은 요청이 처리된다. 그러면 그동안 파싱 뒤에 숨어 있던 줄서기가 데이터 쪽에서 드러난다
14T_ORD_BIG 인덱스 3개(변화 없음)의 적재 비용 청구서
15PL/SQL이 커서를 열어 둔 채 다시 쓸 때: 파스 호출 0의 이득과 ACS를 잃는 대가(§7 [E]) · DBMS_SQL의 값 없는 파싱(§9)
18「SQL 코딩 가이드」 v0.1 → v1.0: 리포트에서 2회 이상 되풀이된 원인만 조문으로