테마
SQL 코딩 가이드 v1.0
11화에 제정하고(v0.1) 18화에 개정했다(v1.0). 개발팀이 SQL을 쓸 때 지키는 규칙이다. 튜너가 아니라 SQL을 쓰는 사람이 읽는 문서다. T1 리포트에 "이 SQL을 어떻게 고쳤는가"를 적는다면, 이 문서에는 "다음 SQL을 처음부터 어떻게 쓰는가"를 적는다. v1.0은 사내 튜닝 표준 T6 v1.0과 같은 승격 규칙으로 걸렀다. 리포트에서 2회 이상 나온 원인만 「조문」이 된다. 근거가 한 번뿐인 것은 「대기 조문」으로 남긴다. 지우지 않으며 리뷰 체크리스트에서도 그대로 묻는다. 다만 표준의 조문은 아니며, 두 번째 근거가 나오는 날 조문으로 올린다. 이 가이드는 SQL을 쓰는 사람의 문서이고, T6은 튜너와 리뷰어의 문서다. 같은 원인을 두 문서가 각자의 말로 적는다(조문마다 T6 조 번호를 단다).
왜 이 문서가 있는가
2019-05-20 hd.kang
정산 목록 페이징: ROWNUM 을 안쪽 인라인뷰로 옮김. ...
11화 (5월) 개발팀 반영 소스
"... WHERE ORD_DT <= DATE '" + postitDate + "' AND (... OR ORD_SEQ <= " + postitSeq + ") ..."09화 Q4 rev02의 SQL 파일에는 :p_dt와 :p_seq 바인드가 적혀 있었다. 그런데 개발팀은 돌아가는 실습 스크립트를 베꼈다. 그 스크립트는 SQL*Plus에서 돌리려고 값을 문자열로 박아 둔 것이었다. 리포트(T1 7호)의 「리스크」 칸에는 "포스트잇 값을 문자열로 붙이면 페이지마다 다른 SQL"이라고 적혀 있었지만, 리스크 칸은 개발자가 읽는 문서가 아니었다.
그 결과 (11화 실측 · ora-hanorder) | 값 |
|---|---|
| 정산 목록 3만 번 클릭 · 하드 파싱 | 30,431 (바인드면 1) |
| 걸린 시간 · 그중 파싱 CPU | 36.19초 · 34.66초 (바인드면 0.67초) |
| 공유 풀에서 밀려난 이웃 | 앞서 올라와 있던 Q1 rev03. 다시 하드 파싱해야 한다 |
서버 쪽 처방 CURSOR_SHARING=FORCE의 부작용 | 상태 탭 취소 5,826 → 50,347 · 결과 칸 길이 16 → 160 바이트 |
서버 쪽 처방 바인드 피킹 끄기의 부작용 | 정산 목록 2페이지 14 → 1,005,991 |
서버 스위치는 어느 값이 바인드여야 하고 어느 값이 리터럴이어야 하는지 알지 못한다. 그 구분은 SQL을 쓰는 사람만 할 수 있다.
조문 — 근거 2회 이상 (v1.0)
| # | 조문 | 근거 · 숫자 | T6 | 원천 |
|---|---|---|---|---|
| 8 | 컬럼을 가공하지 않는다. 형변환은 리터럴이나 바인드 쪽에서 한다(ORD_DT >= TO_DATE(:p,'YYYYMMDD') ○ · TO_CHAR(ORD_DT,…) = :p ×) | T1 1호: TO_CHAR(ORD_DT)는 힌트를 줘도 인덱스를 못 탄다(02화) · T1 2호: 숫자 리터럴과 비교한 문자 컬럼 5 → 1,406(03화) 〔근거 2회〕 | 제4조 | II권 Ch1 03 · 04 |
| 11 | 답이 기대는 입력은 모두 인자로 받는다. 시계(SYSDATE · SYSTIMESTAMP · CURRENT_DATE), 세션 시간대, NLS, 세션 상태(SYS_CONTEXT), 패키지 변수는 숨은 입력이다. 인자로 꺼내거나 형식을 명시한다(TO_DATE(:p,'YYYYMMDD')). 결과 캐시(RESULT_CACHE · /*+ RESULT_CACHE */)와 DETERMINISTIC은 답이 테이블과 인자에만 기대는 곳에만 붙인다. DETERMINISTIC은 아무도 검사하지 않는 약속이다 | T1 2호: NLS가 DD-MON-RR이면 27,390건, YYYY-MM-DD면 에러 없이 1,000,000건(03화) · T1 11호: 결과 캐시가 정산일을 6/14로 고정, 연습 DB 재현 100,001 vs 100,000(15b화) 〔근거 2회. 18화 승격. v0.1의 '결과 캐시' 조문을 '숨은 입력'으로 넓혔다. 두 리포트의 원인을 같은 말로 설명할 수 있기 때문이다〕 | 제15조 | I권 「Result 캐시」 p.472 · 「Deterministic 함수 사용 시 주의사항」 p.512 · lnpls PL/SQL Function Result Cache · nlspg Setting Up a Globalization Support Environment |
| 12 | 읽기와 쓰기를 떼어 놓는다. 번호 매기기(채번)는 업무 트랜잭션과 같은 줄에 두지 않는다. 번호는 시퀀스로 받고, 결번 사유는 기록으로 남긴다. 커서로 읽는 동안 그 커서가 읽는 테이블을 루프 안에서 고치며 COMMIT 하지 않는다(fetch across commit). 커밋 주기는 커밋 주기 기준의 경로 번호로 정한다 | T4 1호: 채번 테이블 한 줄에 주문 세션이 줄을 섰다 · 316~321cs(12화) · T4 2호: 읽으면서 고치는 배치에 N건마다 COMMIT → ORA-01555 · 반쯤 반영(13화) · T3 1호 · T3 2호(14·15화) 〔근거 4회 · 18화 신설〕 | 제8조 | I권 02장 p.122 「일련번호 채번 동시성 높이기」 · I권 Ch1 (p.41 · 46) · cncpt Data Concurrency and Consistency |
| 13 | 예외를 삼키지 않는다. WHEN OTHERS THEN NULL을 쓰지 않는다. 배치는 건너뛴 행을 반송함(에러 로그 테이블 · 사유)에 남기고, 끝에서 원장 = 처리 + 반송인지 센다 | T3 2호: 에러 로그는 깨끗했는데 170건이 사라짐(15화) · T4 2호: 오류 시점까지만 반영(13화) · T3 1호: 중간 커밋 동안 화면에 정산이 반만 보임(14화) 〔근거 3회 · 18화 신설〕 | 제14조 | sqlrf INSERT (error_logging_clause) · arpls DBMS_ERRLOG · I권 06장 p.434 |
| 14 | 행마다 SQL을 호출하지 않는다. 루프 안의 SELECT · UPDATE, 행마다 호출하는 함수, 행마다 도는 스칼라 서브쿼리는 한 문장(집합)으로 쓴다. 남겨야 한다면 PR에 횟수를 적는다(recursive calls · 함수 본문 실행 수 · Starts) | T1 5호: 고객명 스칼라 Starts 752,995 · CBC 래치 1,035,706번(07화) · T3 1호: 행마다 인덱스 셋 · redo 916.6MB(14화) · T3 2호: recursive calls 301,187 → 2,457 · 함수 100,020 → 2,000(15화) 〔근거 3회 · 18화 신설〕 | 제6조 | I권 05장 p.346 · 349 · I권 「PL/SQL 함수의 특징과 성능 부하」 p.392 · lnpls PL/SQL Optimization and Tuning |
대기 조문 — 근거 1회 (지키되, 표준의 조문은 아니다)
리뷰 체크리스트 A · B에서는 이 줄들을 그대로 묻는다. "한 번 나온 것은 사건이고 두 번 나온 것은 구조다." 두 번째 근거가 나오는 날 위 표로 옮기고 개정 이력에 적는다.
| # | 대기 조문 | 근거 (1회) | 올라가는 날 |
|---|---|---|---|
| 1 | 화면이 고르는 값은 바인드로 넘긴다. 포스트잇, 검색어, 조회월, 고객번호, 페이지 위치가 그렇다. 문자열로 이어 붙이지 않는다 | T1 9호: 3만 번 클릭 하드 파싱 30,431 → 1 · 네 명 동시 클릭 1,000번당 CPU 135.5 → 3.4cs(11화) · 원천 I권 04장 p.272 · tgsql Cursor Sharing | 리터럴 이어 붙이기가 다른 리포트에서 원인으로 다시 나올 때 |
| 2 | 값이 손에 꼽히고 값마다 길이 달라야 하는 코드값은 리터럴로 쓴다. 단, 상수로 쓴다. 화면 입력을 이어 붙이지 말고 코드에서 문장을 고른다(IF 탭 = '취소' THEN 문장 C) | T1 9호: 리터럴 셋은 완료 1,016 · 취소 5,826. 하나로 묶이면 취소 50,347(11화) · I권 p.277 · p.286 | 〃 |
| 3 | 바인드 문장은 값을 엿보지 못해도 같은 계획이 나오는 모양으로 쓴다(NOT (A AND B) · NVL(:c, col)의 OR-Expansion) | T1 9호: Q4 rev02를 값 없이 파싱하면 2페이지 1,005,991 · rev03은 14(11화) · I권 p.283 | 〃 |
| 4 | IN 리스트는 칸 수를 고정하거나 컬렉션 하나로 넘긴다 | 11화 실습/07: 요청 300개일 때 리터럴 문장 300 · 10칸 고정 1 · 컬렉션 1 · I권 p.315 | 〃 |
| 5 | 화면 프로그램은 커서를 다시 쓴다(문장 캐시). 단, 값에 따라 길이 갈리는 문장을 열어 둔 채 다시 쓰면 ACS가 자식을 바꾸지 못한다. 그런 문장은 2번을 따른다 | 11화 실습/05 · 실습/02 [E] · I권 p.289 · p.297 | 〃 |
| 6 | CURSOR_SHARING은 EXACT로 둔다. 바인드 피킹은 끄지 않는다. 바꾸자는 제안에는 "무엇을 재 보라"로 답한다(체크리스트 C) | T1 9호 「채택하지 않은 대안」 · I권 p.502 · refrn CURSOR_SHARING. 스위치 제안 전반은 이미 T6 제9조(근거 3회)의 조문이다 | 〃 |
| 7 | 날짜 범위는 >= 시작 AND < 다음 구간 시작으로 쓴다. 끝 경계에서 잘릴 성분(시분초)이 있는 컬럼에 해당한다. VARCHAR2(8) 날짜는 BETWEEN이 맞다(03화의 반례) | T1 1호: T_ORD 137건 · 34,159,700원 누락(02화) · II권 Ch1 03 | 시분초가 있는 컬럼의 BETWEEN이 다른 리포트에서 다시 나올 때. 졸업 과제(ECOMMERCE)의 리포트가 그 후보다 |
| 9 | 조인 상대가 그 키로 정말 한 줄인지, 그리고 그 키가 상대 쪽에 정말 있는지 확인한다. 다건이 될 수 있으면 EXISTS나 먼저 묶기를, 없을 수 있으면 외부조인을 쓴다 | T1 6호: Q3 금액 50배(08화) · II권 Ch4 | 1 쪽이 하나가 아니거나 고아 키가 있는 조인이 다시 나올 때. 졸업 과제의 리포트가 그 후보다 |
| 10 | 페이징은 키 기반(포스트잇)으로 하고, 정렬 키는 유일하게 잡는다 | T1 7호: 한 번 클릭 11,652 → 약 15(09화) · II권 Ch5 06 · I권 p.389 | 깊은 페이지나 정렬 키 중복이 다시 나올 때 |
v0.1의 1~6번은 11화 한 화(T1 9호)에서 나온 조문이었다. 그날 숫자는 컸지만 리포트는 하나였다. 7번, 9번, 10번도 앞 화의 리포트 하나씩에서 나왔다. 그래서 v1.0에서 대기 조문으로 내려왔다. 틀려서가 아니라 아직 한 번이어서다. 조문 번호는 바꾸지 않았다(v0.1을 인용한 리포트와 PR이 계속 같은 조문을 가리키도록).
코드 리뷰 체크리스트 — PR마다 (Yes / No / N-A)
A. 공유 풀 — 이 문장은 몇 개가 되는가
| # | 묻는 것 | Y | N | N/A |
|---|---|---|---|---|
| A1 | 화면에서 온 값을 SQL 문자열에 이어 붙이지 않았는가(+ value + · || value || · 템플릿 치환) | |||
| A2 | 리터럴로 남긴 값은 코드값이고, 값마다 길이 달라야 한다는 근거(분포 · 히스토그램 · 실측)가 있는가 | |||
| A3 | IN (…)의 칸 수가 고정이거나 컬렉션인가 | |||
| A4 | 날짜를 DATE '…' 리터럴로 이어 붙이지 않았는가(FORCE에서도 값마다 자식이 생긴다: HASH_MATCH_FAILED) | |||
| A5 | 커서를 재사용하는가(문장 캐시 · PL/SQL이면 정적 SQL 또는 EXECUTE IMMEDIATE … USING) |
B. 계획 — 값이 바뀌어도 괜찮은가
| # | 묻는 것 | Y | N | N/A |
|---|---|---|---|---|
| B1 | 바인드 문장에 OR 갈래가 있다면, 피킹 없이(또는 DBMS_SQL로) 한 번 재 봤는가 | |||
| B2 | 편향된 컬럼(히스토그램이 있는 컬럼)을 바인드로 받는다면, 값마다, 그리고 어느 값이 먼저 오느냐를 바꿔 재 봤는가 | |||
| B3 | PL/SQL이 커서를 열어 둔 채 그 문장을 호출하는가 → 그렇다면 ACS에 기대지 않는다 | |||
| B4 | 실행계획을 첨부했는가 · access와 filter를 구분했는가 |
C. 서버 설정을 바꾸자는 제안이 왔을 때 — "무엇을 재 보라"
| # | 재 볼 것 | 이 교재에서 잰 곳 |
|---|---|---|
| C1 | 그 설정을 내 세션에만 켜고, 리터럴이 일부러 박힌 문장을 찾아 값 순서를 바꿔 가며 잰다 | 11화 실습/01 [C]~[F] |
| C2 | V$SQL_SHARED_CURSOR에서 자식이 왜 생기는지(HASH_MATCH_FAILED 등) | 11화 실습/01 [B] |
| C3 | 결과의 칸 길이(DBMS_SQL.DESCRIBE_COLUMNS). 고정폭 출력이 깨지는가 | 11화 실습/01 [G] |
| C4 | 측정 조건이 운영과 같은가. 특히 statistics_level(ALL이면 ACS 판단이 달라진다) | 11화 실습/02 [D] |
D. 쓰기 · 예외 · 횟수 (조문 11 ~ 14 · 18화 추가)
| # | 묻는 것 | Y | N | N/A |
|---|---|---|---|---|
| D1 | 답이 시계 · 세션 시간대 · NLS · 세션 상태 · 패키지 변수에 기대는 곳이 없는가(있으면 인자로 꺼냈는가 · 형식 마스크를 적었는가) | |||
| D2 | RESULT_CACHE · DETERMINISTIC을 붙였다면 답이 테이블과 인자에만 기대는가 · 결과 캐시에 그 결과가 다 들어가는가(블록 수 · 고유 인자 수) | |||
| D3 | 채번이 업무 트랜잭션과 한 줄에 있지 않은가 · 커서로 읽는 테이블을 루프 안에서 커밋하지 않는가 | |||
| D4 | WHEN OTHERS THEN NULL이 없는가 · 배치라면 반송함과 '원장 = 처리 + 반송' 대조가 있는가 | |||
| D5 | 루프 안의 SQL이나 행마다 호출하는 함수가 없는가(있으면 그 횟수를 PR에 적었는가) | |||
| D6 | 이 PR의 성능 근거에 그 당시의 데이터 규모와 근거 수치가 적혀 있는가(T6 마지막 조항) |
탐지 쿼리 — 운영에서 주기적으로
① 리터럴만 다른 문장 무리 (조문 1 위반 찾기)
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
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(*) >= 50
ORDER BY parents DESC
FETCH FIRST 10 ROWS ONLY;PARENTS가 크고 EXECS가 그와 비슷하면 한 번 쓰고 버리는 문장이다. 공유 풀은 그런 문장을 계속 내보내므로, 한 시점에 본 PARENTS는 지나간 양의 일부일 뿐이다(11화: 3만 개 중 300~400). 지나간 양은 V$SYSSTAT의 parse count (hard) 증가분으로 본다.
② 자식이 많은 문장 (조문 3 · 5 확인)
sql
SELECT sql_id, version_count, executions, loads, SUBSTR(sql_text, 1, 60) AS sample
FROM v$sqlarea WHERE version_count >= 10 ORDER BY version_count DESC FETCH FIRST 10 ROWS ONLY;자식이 왜 생겼는지는 V$SQL_SHARED_CURSOR에서 Y인 칸을 보면 안다(방법은 11화 2_파고들기.md §5).
| 자주 보이는 칸 | 뜻 | 보통의 원인 |
|---|---|---|
BIND_LENGTH_UPGRADEABLE · BIND_MISMATCH | 바인드 그릇 크기 · 타입이 다르다 | 화면 프로그램이 값 길이에 따라 그릇을 다르게 잡는다 |
OPTIMIZER_MODE_MISMATCH · OPTIMIZER_MISMATCH | 세션의 옵티마이저 설정이 다르다 | 접속할 때 ALTER SESSION을 하는 도구 |
HASH_MATCH_FAILED | FORCE가 바꾼 "안전하지 않은 리터럴"의 값이 다르다 | CURSOR_SHARING=FORCE + DATE '…' 등 |
BIND_EQUIV_FAILURE | ACS가 값 범위마다 자식을 나눴다 | 편향 컬럼 + 바인드(정상일 수 있다) |
개정 이력
| 판 | 화 | 무엇이 바뀌었나 |
|---|---|---|
| v0.1 | 11화 | 제정. 조문 1~6(공유 풀 · 바인드 · 리터럴 · 커서) + 앞 화 후보 7~10. 체크리스트 A · B · C. 탐지 쿼리 두 개 |
| v0.1 | 15b화 | 후보 조문 11(결과 캐시는 답이 테이블과 인자에만 기대는 곳에만 · 숨은 입력은 인자로 · DETERMINISTIC은 약속) 추가. T1 11호 〔근거 1회〕 |
| v1.0 | 18화 | T6 v1.0과 같은 승격 규칙으로 거름. 조문 8 · 11(숨은 입력으로 넓혀 승격: T1 2호 + T1 11호) · 12 · 13 · 14(신설: 근거 4 · 3 · 3회). 대기 조문 1~7 · 9 · 10(근거 1회 · 번호 유지). 체크리스트 D 절(D1~D6) 추가 |