Oracle 19c 특정 바인드 값에서만 느릴 때: Adaptive Cursor Sharing 진단

Oracle 19c에서 같은 SQL이 특정 바인드 값에서만 느리다면, 같은 SQL ID의 자식 커서별 계획과 실행 횟수부터 비교해 보세요. Adaptive Cursor Sharing(ACS)이 값을 구분해 계획을 공유하는지, 타입이나 실행 환경이 달라 커서가 나뉜 것인지 먼저 가려야 해요.

IS_BIND_SENSITIVE가 Y여도 값별 계획이 준비되었다고 볼 수는 없어요. IS_BIND_AWARE, 공유되지 않은 이유, 실제 사용된 자식 커서의 계획을 함께 확인해야 다음 조치를 정할 수 있어요.

어떤 성능 편차를 ACS로 살펴볼까

타입이 같은 입력인데도 드문 값과 흔한 값의 조회 비용이 크게 다른 상황이 대상이에요. 예를 들어 소수의 예외 건을 찾는 조회와 대부분의 완료 건을 찾는 조회가 같은 조건식을 사용하더라도, 읽어야 할 행 수는 다를 수 있죠. 먼저 값별 반환 규모가 실제로 다른지 확인합니다.

적용 조건·영향 범위

타입이 다른 입력을 먼저 고쳐야 하는 경우도 있어요. 문자 코드에 숫자를 전달했다면 바인드 타입과 컬럼 변환을 확인하는 절차부터 적용하세요. 이 글은 타입이 맞는 상태에서 값의 분포와 커서 공유가 만드는 차이를 좁힙니다.

bind-sensitive와 bind-aware는 어느 단계일까

바인드 피킹은 최적화할 때 바인드 값을 살펴 계획 선택에 반영하는 동작이에요. 첫 계획이 모든 값에 맞지는 않을 수 있어요. ACS는 실행 특성을 관찰하며 다른 선택도에 맞는 커서 공유를 판단합니다. 선택도는 조건을 만족하는 행이 전체에서 차지하는 비율로 생각하면 돼요. Oracle 19c 커서 공유 설명에서 이 관계와 CURSOR_SHARING 파라미터와의 독립성을 확인할 수 있습니다.

bind-sensitive는 바인드 값이 계획 선택에 영향을 줄 수 있어 관찰하는 상태, bind-aware는 확장된 커서 공유를 사용하도록 표시된 상태예요. V$SQL의 두 상태 열 정의를 보면 구분이 분명해집니다. Y/Y가 보이더라도 현재 업무에서 좋은 계획이 쓰였다는 증거는 별도로 필요해요.

같은 계획이 여러 값에 적합할 수도 있습니다. 반대로 자식 커서가 여러 개여도 바인드 타입·옵티마이저 환경 같은 이유로 공유가 안 된 것일 수 있어요. “자식 두 개가 생겼으니 ACS가 해결했다”는 결론을 먼저 내리면 진단 방향이 틀어집니다.

운영에서는 SQL ID와 관찰 구간부터 고정하기

장애 기록에서 느렸던 실행의 SQL ID, 발생 시각, 접속 서비스와 입력 타입을 확보하세요. 값 자체를 공유해야 한다면 업무 규칙에 맞게 마스킹합니다. 다음 조회는 해당 SQL이 실행된 인스턴스의 같은 컨테이너에서 수행하는 단일 인스턴스 관찰 예제예요. RAC 전체를 조사할 때는 GV$ 계열의 INST_ID까지 보존해야 하며, 이 SQL 결과를 인스턴스 전체 결과로 해석하지 않습니다.

VARIABLE target_sql_id VARCHAR2(13)
EXEC :target_sql_id := '확인한_SQL_ID';

SELECT sql_id, child_number, plan_hash_value,
       is_bind_sensitive, is_bind_aware, is_shareable,
       executions, end_of_fetch_count, buffer_gets,
       elapsed_time, last_active_time
FROM v$sql
WHERE sql_id = :target_sql_id
ORDER BY child_number;

V$SQL의 EXECUTIONS와 BUFFER_GETS는 커서가 캐시에 올라온 뒤의 누적값이에요. 전체 평균에는 서로 다른 값과 실행이 섞입니다. 통제된 테스트에서는 실행 전후 증가분을 비교하고, 운영에서는 같은 시간대의 다른 실행이 섞일 수 있음을 기록하세요. ELAPSED_TIME은 마이크로초 단위의 데이터베이스 시간이며 병렬 실행에서는 작업 프로세스 시간이 합산될 수 있어, 화면에서 잰 응답 시간과 같지 않습니다. 누적 통계와 시간 열의 정의를 기준으로 해석하세요.

결과가 없으면 먼저 SQL ID 오타, 다른 인스턴스·PDB, 캐시에서 사라진 커서를 확인합니다. 자식 번호가 같더라도 재적재된 커서를 이어 붙여 증가분을 계산하면 안 돼요. 업무가 끝나기 전에 일부 행만 가져온 실행과 끝까지 가져온 실행도 구분해야 합니다.

자식 커서가 나뉜 이유를 분리하기

SELECT child_number, bind_mismatch,
       optimizer_mismatch, stats_row_mismatch,
       bind_equiv_failure
FROM v$sql_shared_cursor
WHERE sql_id = :target_sql_id
ORDER BY child_number;

BIND_MISMATCH는 바인드 메타데이터 차이, OPTIMIZER_MISMATCH는 최적화 환경 차이를 가리켜요. BIND_EQUIV_FAILURE는 바인드 값의 선택도가 기존 자식을 최적화할 때 사용한 선택도와 맞지 않았다는 뜻입니다. 이 열 하나로 모든 원인을 확정하지 말고 상태와 계획을 연결하세요. V$SQL_SHARED_CURSOR의 원인별 정의가 판단 근거입니다.

SELECT child_number, predicate, range_id, low, high
FROM v$sql_cs_selectivity
WHERE sql_id = :target_sql_id
ORDER BY child_number, predicate, range_id;

이 뷰는 바인드가 포함된 조건의 선택도 범위를 보여줘요. 예를 들어 숫자 1부터 100까지의 고객 번호를 의미하는 표가 아닙니다. 여러 조건이 있으면 각각의 선택도가 대응하는 범위에 들어가는지를 봅니다. 결과가 비어 있다고 기능 고장으로 단정하지 말고 현재 자식의 상태와 관찰 대상부터 다시 확인하세요. 선택도 범위 뷰의 정의에는 LOW/HIGH가 문자형으로 제공된다는 점도 나와 있습니다.

작은 실습에서 같은 SQL을 값만 바꿔 실행하기

다음은 Oracle 19c 문서를 바탕으로 만든 전용 테스트 스키마용 예제입니다. 이 글을 작성하면서 Oracle 인스턴스에서 실행한 결과는 아니며, 데이터 결과는 예상값이고 자식 생성·계획 선택은 환경에서 확인할 관찰 대상이에요. 정확히 몇 번 실행하면 bind-aware가 된다고 약속하지 않습니다.

CREATE TABLE acs_value_demo (
  item_id NUMBER NOT NULL,
  state_code VARCHAR2(8) NOT NULL,
  amount NUMBER NOT NULL,
  detail_text VARCHAR2(100) NOT NULL
);

INSERT INTO acs_value_demo
  (item_id, state_code, amount, detail_text)
SELECT LEVEL,
       CASE WHEN LEVEL <= 19900 THEN 'COMMON' ELSE 'RARE' END,
       10, RPAD('x', 100, 'x')
FROM dual
CONNECT BY LEVEL <= 20000;
COMMIT;

CREATE INDEX acs_value_demo_i1
  ON acs_value_demo (state_code);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'ACS_VALUE_DEMO',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 2 STATE_CODE',
    cascade => TRUE);
END;
/

통계 수집 구문은 DBMS_STATS의 METHOD_OPT와 GATHER_TABLE_STATS에 맞췄습니다. SIZE 2는 이 예제의 두 상태 값을 관찰하기 위한 지정이에요. 운영의 모든 컬럼에 같은 옵션을 복제할 근거는 아닙니다. 수집 판단 자체가 필요하다면 치우친 분포에서 히스토그램을 판단하는 방법을 먼저 읽어 보세요.

VARIABLE state_value VARCHAR2(8)
EXEC :state_value := 'RARE';
SELECT /*+ gather_plan_statistics */ /* acs_value_demo_v1 */
       SUM(amount) AS total_amount
FROM acs_value_demo
WHERE state_code = :state_value;

EXEC :state_value := 'COMMON';
SELECT /*+ gather_plan_statistics */ /* acs_value_demo_v1 */
       SUM(amount) AS total_amount
FROM acs_value_demo
WHERE state_code = :state_value;

합계 결과 한 행만 보고 두 실행의 처리량이 같다고 생각하면 안 돼요. 실제 차이는 집계 아래의 테이블·인덱스 단계에서 읽는 행에 있습니다. 작은 테스트에서는 두 값 모두 같은 계획이 선택될 수도 있어요. 이는 잘못된 실행 결과가 아니며, 계획을 바꾸려고 운영 공유 풀을 비울 이유도 아닙니다.

위 두 SELECT의 주석·공백·바인드 이름을 같게 유지하세요. RARE 실행 직후 계획을 먼저 저장하고 COMMON을 실행하는 식으로 관찰하면 비교가 쉬워요. 자동으로 실행되는 클라이언트 부가 SQL이 마지막 SQL을 바꿀 수 있으므로 다음 단계에서는 SQL ID와 자식 번호를 직접 지정합니다.

SELECT sql_id, child_number, plan_hash_value, executions
FROM v$sql
WHERE sql_text LIKE
      'SELECT /*+ gather_plan_statistics */ /* acs_value_demo_v1 */%'
  AND parsing_schema_name = USER
ORDER BY sql_id, child_number;

VARIABLE target_sql_id VARCHAR2(13)
VARIABLE target_child NUMBER
-- 위에서 찾은 실제 SQL ID로 바꿉니다.
EXEC :target_sql_id := '확인한_SQL_ID';
-- 실행 전후 executions가 증가한 자식 번호를 넣습니다.
EXEC :target_child := 0;

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  :target_sql_id, :target_child,
  'ALLSTATS LAST +PEEKED_BINDS +PREDICATE'));

DISPLAY_CURSOR의 권한과 LAST 형식은 DBMS_XPLAN 공식 설명을 따릅니다. A-Rows가 없다면 해당 실행에 통계를 수집했는지와 자식 번호를 다시 확인하세요. PEEKED_BINDS는 계획 생성 때 관찰한 값이며 마지막 실행의 입력값이라는 뜻은 아닙니다. 같은 자식을 여러 세션이 사용하면 LAST 결과도 자신이 관찰하려던 실행과 다를 수 있어요.

V$SQL_BIND_CAPTURE 역시 모든 실행의 입력 일지가 아니에요. 바인드 캡처의 제한에 따라 값이 없거나 과거 값일 수 있으므로, 현재 느린 요청의 입력으로 단정하지 않습니다. 계획 읽기가 낯설다면 집계 아래 E-Rows와 A-Rows를 비교하는 순서를 함께 확인하세요.

무엇을 확인하면 진단을 마칠 수 있을까

관찰 결과와 다음 행동

변경 전후 비교는 같은 데이터와 입력 타입, 반환 범위에서 해야 해요. 한 값이 빨라졌어도 흔히 쓰는 다른 값의 읽기량이 크게 늘었다면 개선으로 결론 내리지 않습니다. 계획 해시가 달라졌다는 사실과 성능이 좋아졌다는 판단을 나누어 기록하세요.

잠금·부하와 되돌릴 수 있는 경계

운영에서 위의 진단 뷰를 SELECT하는 단계는 업무 행을 수정하지 않습니다. 다만 고정 뷰를 넓게 반복 조회하면 진단 자체의 비용이 생기므로 SQL ID를 한정하세요. 테스트 SELECT도 실제로 실행되며, 통계 수집과 인덱스 생성은 CPU·I/O·공간을 사용합니다. 일반 조회라고 운영 부하가 없다고 생각하면 안 됩니다.

공유 풀 초기화는 과거 관찰 증거를 없애고 다른 SQL의 재파스를 유발할 수 있어요. CURSOR_SHARING을 바꾸거나 힌트를 고정하는 것도 상태 표시를 Y로 만들기 위한 조치가 아닙니다. 원인이 확인된 뒤 정상·지연 값의 검증과 복구 계획을 갖춘 별도 변경으로 다루세요.

마지막으로 남길 기록

조사 결과에는 “ACS 사용 여부” 한 줄보다 SQL ID, 환경, 자식 번호, 바인드 타입과 대표 값 범주, 실행 구간, 계획 및 읽기량을 함께 남겨 두세요. 지금 가진 증거로 값별 계획이 적절한지 판단할 수 없다면, 부족한 관찰이 무엇인지 적는 편이 안전해요. 다음 담당자가 기록만 보고 어느 값과 자식을 더 확인해야 할지 알 수 있으면 충분합니다.

공식 출처

• Oracle Database 19c — Improving Real-World Performance Through Cursor Sharing
• Oracle Database 19c — V$SQL
• Oracle Database 19c — V$SQL_SHARED_CURSOR
• Oracle Database 19c — V$SQL_CS_SELECTIVITY
• Oracle Database 19c — DBMS_XPLAN
• Oracle Database 19c — V$SQL_BIND_CAPTURE
• Oracle Database 19c — DBMS_STATS
• Oracle Database 19c — COMMIT
• Ask TOM — SELECT with bind variables: plan and captured values