오라클 실행계획 수집
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
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 ;