[Oracle] 튜닝 최후의 수단 PROFILE

튜닝의 끝은 순정이다.

 

가장 좋은 SQL 튜닝은 튜닝을 하지 않는 것이라고 생각한다.

데이터 모델링이 잘 되어있고 통계정보도 잘 수집되어 

별도의 힌트없이 설계자의 의도대로 플랜이 수행되어 일정한 성능이 확보되는 SQL일 것이다.

 

그러나, 현실은 그렇지 못하다.

계속해서 변하는 비즈니스 로직과 통계정보 수집의 제약, 

그리고 힌트 없이는 도무지 내 의도대로 풀리지 않는 Oracle의 Optimizer 등으로 인해 

힌트를 통해 플랜을 고정하는 것이 현실적인 튜닝 방법이다.

 

그러나, 현실은 때때로 그렇지 못하다.

모든 SQL을 DBA나 튜너에게 SQL 검토를 받기에는 시간의 제약이 생기는 상황도 있고, 

혹시나 튜닝을 받았던 SQL이더라도 갑자기 비즈니스 상황의 변화로 

데이터량이나, SQL 수행횟수가 급격하게 증가한다면 힌트 역시 무의미해질 수도 있다. 

 

이럴때 운영DB에서 실시간으로 불기둥처럼 치솟는 트랜잭션을 보면 

마음 한 켠이 싸늘해지고 가슴에 비수가 날아와 꽂힌다.

 

프로그램 이관 직후에 이런 상황이 생긴다면 2가지를 고려해볼 수 있다.

 

1. 프로그램 원상복구 

  일반적인 상황에서는 프로그램 원복이 가장 현실적이고 확실한 방법이며,

서비스 영향도도 최소화할 수 있다. 

 

2. 운영DB에서 즉시 튜닝

   정말 피하고 싶지만 비즈니스 로직상 프로그램 원복이 불가능한 상황이 있을 수 있다.

DB CPU는 100% 이고 트랜잭션 에러로 빨갛게 도배된 APM, DPM을 해결할 수 있는 

유일한 방법이 SQL 튜닝일때, 튜닝 최후의 수단 PROFILE을 통해 문제를 해결할 수 있다.

 

새로운 DB Object(인덱스 등)를 생성했거나, 통계정보 변화 등으로 인해 SQL의 플랜이 변경된 경우

또는 신규 SQL의 튜닝이 되지 않은 경우 

 

아래와 같은 오라클 Procedure로 특정 SQL_ID의 PLAN_HASH_VALUE를 강제로 적용하게 할 수 있다.

 

이때, 문제가 발생한 SQL의

튜닝된 SQL을 DBMS에서 실행해 V$SQL_PLAN에 새로운 SQL_ID와 PLAN_HASH_VALUE을 

아래와 같은 PROCEDURE로 강제 적용하는 것이다.

DECLARE
 CL_SQL_TEXT CLOB;
 HINT_SPEC SYS.SQLPROF_ATTR;
BEGIN
 SELECT SQL_FULLTEXT INTO CL_SQL_TEXT
 FROM GV$SQL
 WHERE SQL_ID = 'SQL 아이디' -- Profile 적용할 SQL ID
       AND ROWNUM = 1

 SELECT EXTRACTVALUE(VALUE(d), '/hint') AS OUTLINE_HINTS
        BULK COLLECT
   INTO HINT_SPEC
 FRON XMLTABLE('/*/outline_data/hint'
      PASSING (
                SELECT XMLTYPE(OTHER_XML) AS xmlval
                FROM V$SQL_PLAN
                WHERE SQL_ID = 'SQL 아이디' -- 튜닝 후 SQL의 SQL_ID
                      AND PLAN_HASH_VALUE = '플랜해시밸류' -- 튜닝한 SQL의 플랜해시밸류
                      AND OTHER_XML IS NOT NULL
                      AND ROWNUM = 1
               ) 
       ) D;
 
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
      SQL_TEXT => CL_SQL_TEXT,
      PROFILE => HINT_SPEC
      NAME => '식별할 프로파일 이름' -- 식별할 프로파일 이름
      FORCE_MATCH => TRUE      -- 컬럼의 데이터가 skew되어있고 히스토그램 정보가 있으면 FALSE로 하기
  );

END;
/

 

혹시 모르니 Profile 적용에 대한 원복 스크립트 준비도 필요하다.

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'식별할 프로파일 이름');

 

SQL Profile을 사용하는 일이 없기를 바라며, SQL Profile을 준비한다.

 

 

 

* 참고 - 오라클 ORACLE 12c DOCS : 

https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310

CREATE PROFILE