소스 수정 없이 SQL 긴급 튜닝

Oracle 12.2 부터 SQL문 수정없이 바로 힌트 적용이 가능합니다.

 

1. 특정 문구로 SQL_ID 문 찾기

SELECT * FROM GV$SQL WHERE SQL_TEXT LIKE '%찾을 문구%';
-- 또는,
SELECT * FROM GV$SQL WHERE SQL_FULLTEXT LIKE '%찾을 문구%';

 

2. 해당 SQL_ID 에 힌트 삽입

- 아래는 patch1 이란 이름으로 gut3k77p5ck3z SQL_ID에 11g 옵티마이저를 사용하겠다는 패치를 생성하는 예시

- 19c 업그레이드 이후 STATISTICS COLLECTOR가 플랜에 나타나며 성능 저하 발생, dynamic sampling (optimizer_dynamic_sampling=2)의 영향으로 추정되나, 긴급 처리가 필요한 경우 사용함

DECLARE
  v_patch_name VARCHAR2(128);
BEGIN
  v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id    => 'gut3k77p5ck3z',
    hint_text => '/*+ optimizer_features_enable(''11.2.0.4'') */',
    name      => 'patch1'
  );
  DBMS_OUTPUT.PUT_LINE('SQL Patch created: ' || v_patch_name);
END;
/

 

3. 패치 확인

SELECT name, description, status
  FROM DBA_SQL_PATCHES;

 

3. 패치 제거

BEGIN
  DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'patch1');
END;
/