행위

오라클 실행계획 수집

DB CAFE

thumb_up 추천메뉴 바로가기

1 오라클 실행 계획 수집[편집]

1.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 SQL_FULLTEXT NOT LIKE '%DBMS_%' AND SQL_FULLTEXT NOT LIKE 'EXEC % ';

1.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 튜닝검토대상);


1.3 PL/SQL 이용한 TO-BE 시스템에서의 실행 계획 수집[편집]

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;

1.4 시스템간 튜닝 검토 대상 필터링[편집]

  • /* 서로 다른 시스템에서 수집된 실행 계획 비교 */
SELECT A.SQL_ID as SQL_ID
       ,NVL(A.ID, B.ID) as 개발DB_실행계획_ID
       ,A.OPERATIONS as 개발DB_실행계획_내용
       ,NVL(B.ID, A.ID) as 운영DB_실행계획_ID
       ,B.OPERATIONS as 운영DB_실행계획_내용
  FROM (SELECT SQL_ID
               ,ID
               ,LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
          FROM 개발DB_수집_실행계획) A
        FULL OUTER JOIN
        (SELECT SQL_ID
                ,ID
                ,LPAD(' ', 1 + DEPTH * 3,' ') || OPERATION || ' ' || OPTIONS as OPERATIONS
           FROM 운영DB_수집_실행계획) 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 ;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_실행계획_수집&oldid=6390"