오라클 업그레이드 SQL 튜닝 대상 추출

최근 금융사나 관공서의 차세대 프로젝트를 살펴보면 오라클 데이터베이스의 업그레이드 튜닝이 유독 많다.

오라클 9i나 10g에서 오라클 11g로의 업그레이드가 대부분이다.

데이터베이스 업그레이드가 단순한 앱 업데이트와 다름을 이제 고객도 이식하고 있다. 타사의 프로젝트에서 들려오는 무수한 장애 소식이 데이터베이스에 대한 인식을 바꾼 것이다.

 

오라클 데이터베이스를 업그레이드하면 기존에 잘 수행되던 SQL 실행 계획이 변함을 알고 있을 것이다. 해당 변화가 버전이 높을수록 더 많은 혜택을 주는 것이 대부분이지만, 간혹 발생하는 악성 SQL로 인한 장애는 이러한 이점을 상쇄시킨다. 그래서인지 많은 고객들이 업그레이드 튜닝을 수행에 앞서 모든 SQL에 대한 전수 조사를 원한다.

 

오라클 업그레이드 튜닝 절차

오라클 업그레이드 튜닝의 핵심은 미래 시스템에서 성능에 악영향을 줄 수 있는 SQL을 선별해 튜닝하는 것이다. 그렇다면 수천 또는 수만 개가 넘는 SQL에서 어떻게 이를 선별할 수 있을까? 프로젝트에 제공된 시간과 재원이 한정된 만큼 SQL 선정이 프로젝트의 성패까지 좌우할 수 있다.

 

● SQL 검토 대상 수집

기존 시스템에서 수행시간이 길거나 문제가 된 SQL은 차기 시스템에서도 악성으로 수행될 가능성이 높다. 그러므로 AWR이나 GV$SQL 뷰를 조회해 수행시간이 길거나 빠르지만 수행 횟수가 많은 SQL을 대상으로 튜닝 검토 대상을 선정한다.
오라클 AWR과 GV$SQL 딕셔너리 뷰를 이용해 SQL 전수 조사도 수행하자. 오라클 10g 이상 버전이라면 AWR 기능이 기본으로 제공된다. DBA가 설정한 기간(기본 7일) 동안 DBMS에서 수행된 SQL 정보가 기록돼 있는데, 기본 설정일 경우 1시간마다 수집되기때문에 그 사이에 수행됐던 SQL 정보가 메모리에서 SWAP OUT될 수 있다. 이 경우 AWR에 해당 SQL이 없을 수 있다. 이러한 빈틈을 매우기 위해 GV$SQL 뷰에서도 SQL을 수집해야 한다.
GV$SQL 뷰는 현재 메모리에 파싱된 SQL 정보를 확인할 수 있기 때문이다. 이러한 방법들을 통해 거의 사용되지 않는 SQL을 제외한 대부분의 검토 대상을 추출할 수 있다(<리스트 1> 참조).

AWR을 활용한 수집 방법은 'DBA_HIST_SQL' 키워드로 시작되는 시스템 테이블을 조회하면 된다.

 

<리스트 1> GV$SQL 뷰에서 검토 대상을 추출하는 SQL 예

SELECT PARSING_USER_ID,      /* SQL을 수행한 사용자 ID */
       PARSING_SCHEMA_NAME , /* SQL을 수행한 사용자명 */
       SQL_ID,               /* SQL ID */
       SQL_FULLTEXT          /* SQL 텍스트 전체 내용 */
       PLAN_HASH_VALUE,      /* SQL의 실행계획 해시 값 */
       CASE
            WHEN BIND_DATA IS NULL THEN 'N'
            ELSE 'Y'
       END BIND_DATA         /* 바인드 변수 정보 유무 */
       EXECUTIONS,           /* 수행 횟수 (DBMS 기동 후) */
       CHILD_NUMBER          /* SQL의 버전 번호 */
  FROM GV$SQL
 WHERE PARSING_SCHEMA_NAME NOT IN ('SYS','SYSMAN','SYS TEM','MAXGAUGE') /* 시스템 스키마 제외 */
   AND COMMAND_TYPE IN ('3')
   /* SELECT 유형 SQL만 추출 */
   /* 제외할 모듈 정보 (개발자 SQL 툴 또는 비업무성 계정이 수행한 SQL 등) */
   AND MODULE NOT LIKE '%Orange for ORACLE%'
   AND MODULE NOT LIKE '%sqlservr.exe%'
   AND MODULE NOT LIKE '%SQL Developer%'
   AND MODULE NOT LIKE '%oracle@mid01 (TNS V1-V3)%'
   AND MODULE NOT LIKE '%oracle@hvocidb01ud (TNS V1-V3)%'
   AND MODULE NOT LIKE '%oracle@hscerpdb (TNS V1-V3)%'   /* 제외할 SQL 텍스트 정보 */'
   AND SQL_FULLTEXT NOT LIKE '%DBMS_%' AND SQL_FULLTEXT NOT LIKE 'EXEC % ';

 

● 실행 계획 수집

이제 튜닝 검토 대상인 SQL을 모두 수집했다. 지금부터 할 일은 해당 SQL에 대한 실행 계획을 수립하는 것이다.

오라클 버전 업그레이드 시 SQL 실행 계획이 변경되는 대상을 선별하는 것이 목적이므로 해당 TASK는 필수 과정이 아닐 수 없다.

앞서 추출한 SQL의 (SQL_ID,PLAN_HASH_VALUE) 값을 이용해 실행 계획 정보가 담긴 테이블을 조회하면 현 시스템에서 실제 수행됐던 실행 계획을 간단히 추출할 수 있다.

 

<리스트 2> AWR을 이용한 SQL 실행 계획 추출

SELECT SQL_ID
       ,PLAN_HASH_VALUE,
       ,ID
       ,PARENT_ID
       ,OPERATION
       ,OPTION
       ,OBJECT_NAME
  FROM DBA_HIST_SQL_PLAN
 WHERE (SQL_ID, PLAN_HASH_VALUE) IN (SELECT SQL_ID, PLAN_HASH_VALUE FROM 튜닝검토대상);

 

 

<리스트 3> PL/SQL을 이용한 미래 시스템에서의 실행 계획 수집

SQL>CREATE TABLE PLAN_EXEC_ERR (SQL_ID VARCHAR2(100), ERROR_TEXT VARCHAR2(1000));
     DECLARE v_schema VHARCHAR2(32);
     v_sql_text CLOB;
     v_commit VARCHAR2(32);
     v_sql_err VARCHAR2(100);
     CURSOR c_sql IS SELECT SQL_ID,
                            SQL_TEXT,
                            PARSING_SCHEMA_NAME
                       FROM 튜닝검토대상SQL;
     BEGIN v_commit := 'COMMIT';
     FOR r_sql IN c_sql
     LOOP BEGIN v_schema := 'ALTER SESSION SET CURRENT_SCHEMA = '||r_sql.PARSING_SCHEMA_NAME;
     v_sql_text := 'EXPLAIN PLAN SET STATEMENT_ID= '''|| r_sql.SQL_ID || ''' INTO PLAN_TABLE_TUN FOR ' || r_sql.SQL_TEXT;
     v_sql_err := 'INSERT INTO KHCHOI.ERR_SQL(sql_ id, sql_err) VALUES(''' || r_sql.SQL_ID|| '''';
     EXECUTE IMMEDIATE v_schema;
     EXECUTE IMMEDIATE v_sql_text;
     EXCEPTION
          WHEN OTHERS THEN EXECUTE IMMEDIATE v_sql_ err||','''||SUBSTR(SQLERRM, 11, 200)||''')';
   END;
    EXECUTE IMMEDIATE v_commit;

   END LOOP;
   END;

 

 

<리스트 4> 튜닝 검토 대상 필터링

/* 서로 다른 시스템에서 수집된 실행 계획 비교 */
SELECT A.SQL_ID as SQL_ID
       ,NVL(A.ID, B.ID) as 현행_실행계획_ID
       ,A.OPERATIONS as 현행_실행계획_내용
       ,NVL(B.ID, A.ID) as 미래_실행계획_ID
       ,B.OPERATIONS as 미래_실행계획_내용
  FROM (SELECT SQL_ID
               ,ID
               ,LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
          FROM 현행_수집_실행계획) A
        FULL OUTER JOIN
        (SELECT SQL_ID
                ,ID
                ,LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
           FROM 미래_수집_실행계획) B
        ON (A.SQL_ID = B.SQL_ID AND A.ID = B.ID)
 WHERE TRIM(A.OPERATIONS) <> TRIM(B.OPERATIONS)
 /* 실행계획이 다름을 비교하는 부분 */
    OR A.SQL_ID IS NULL
    OR B.SQL_ID IS NULL ;

 

<리스트 2>에서 만약 메모리 상주 SQL의 실행 계획을 추출하고자 하면 DBA_HIST_SQL_PLAN의 대상인 GV$SQL_PLAN을 이용하면 된다.

SQL 실행 계획을 추출했다고 아직 끝난 것은 아니다. 업그레이드된 시스템의 실행 계획도 추출이 아직 남아 있다.
하지만 해당 시스템에서는 수집된 SQL이 수행된 적이 없기 때문에 이러한 계획을 그냥 얻을 수 없다. 반드시 1회 이상 수행해 업그레이드된 시스템의 오라클 옵티마이저가 선택한 실행 계획을 생성해야 한다.

이를 위한 선행 작업으로 현행 시스템의 모든 오브젝트 및 데이터를 미래 시스템으로 이관해야 한다.

옵티마이저가 올바른 판단을 할 수 있는 강력한 무기가 바로 통계정보이므로 통계 정보도 최신으로 갱신해야 한다.

 

선행 작업까지 마무리됐다면 앞서 수집했던 SQL_FULLTEXT 정보를 이용해 실행 계획을 미래 시스템에서 수집하자. 일일이 수행할 필요없다. PL/SQL을 활용하면 일괄 수행이 가능하다.

 

 

● 실행 계획 비교

이제 모든 재료가 준비됐다. 마지막으로 수행할 일은 현행과 미래 시스템에서 수집된 SQL의 실행 계획 중 달라지는 SQL을 추출하는 것이다.

예외적으로 LITERAL SQL을 많이 이용하는 시스템을 제외하면 보통 10~20% 정도의 SQL 실행 계획이 변경된다. 이중에서도 상당 수는 오라클 버전 업그레이드를 통한 수행속도 향상 혜택을 얻게 되지만 그렇지 않은 경우도 있다.

이를 대비하기 위해 1차적으로 실행 계획이 달라진 모든 SQL을 검토해야 한다.
그러면 수집된 SQL 실행 계획을 서로 비교하는 <리스트 4>를 통해 튜닝 검토 대상을 최종적으로 필터링해 보자.