5장. 실행 계획의 해석
학습 목표
- 실행 계획의 수집
- 실행 계획의 표시
- 실행 계획의 해석
실행 계획은 무엇인가?
실행 계획은 옵티마이저의 출력물이며, 실행 엔진에게 제공된다. 실행 계획은 쿼리가 요청하는 데이터를
가장 효율적으로 읽기 위해 반드시 수행해야 할 작업을 실행 엔진에게 지시한다.
EXPLAIN PLAN 문장은 SELECT, UPDATE, INSERT, DELETE 문장에 대하여 오라클 옵티마이저가 선택한
실행 계획을 수집한다.
실행 계획의 단계는 부여된 번호 순으로 수행되지 않는다. 각 단계들 사이에는 부모-자식 관계가 존재한다.
행 원본(Row Source) 트리는 실행 계획의 핵심이며, 다음 정보를 표시한다.
① 해당 문장에 의해 참조되는 테이블의 순서
② 해당 문장에서 참조 된 각 테이블을 액세스하는 방법
③ 해당 문장에서 조인 연산에 의해 영향을 받은 테이블의 조인 방법
④ 필터, 정렬, 집계와 같은 데이터 연산
행 원본 트리(또는 병렬 작업을 위한 데이터 플로우 트리)에 추가하여, 실행 계획 테이블은 다음과 같은 정보를 포함한다.
① 최적화. 예를 들어, 각 작업의 비용과 카디널리티
② 파티셔닝. 예를 들어, 액세스 된 파티션들의 집합
③ 병렬 실행. 예를 들어, 조인 입력들의 분산 방식
EXPLAIN PLAN 결과는 사용자가 중첩 루프와 같은 특정 실행 계획을 옵티마이저가 선택 할 수 있도록 결정하는데 도움을 준다.
실행 계획은 어디서 검색해야 하는가?
데이터베이스 내부에서 실행 계획을 읽어 오는 방법은 매우 다양하다. 다음은 가장 많이 알려진 방법들이다.
① EXPLAIN PLAN 명령은 옵티마이저가 SQL 문장을 실행하는데 사용 할 수도 있는 실행 계획을 볼 수 있도록 해준다.
이 명령은 매우 유용한데, 그 이유는 이 명령이 SQL 문장을 실행하지 않고, 옵티마이저가 사용 할 수도 있는 계획에 대한
전체적인 윤곽을 보여주며, 그 결과를 PLAN_TABLE이라고 부르는 테이블에 입력하기 때문이다.
② V$SQL_PLAN은 가장 최근에 실행된 커서에 대하여 실행 계획을 조사하는 방법을 제공한다. V$SQL_PLAN의 정보는
EXPLAIN PLAN 문장의 출력과 매우 유사하다. 그러나, EXPLAIN PLAN이 해당 문장에 실행 되었을 때, 사용 할 수도 있는
이론적인 실행 계획을 보여주는 반면에 V$SQL_PLAN은 사용된 실제 계획을 포함한다.
③ V$SQL_PLAN_MONITOR는 V$SQL_MONITOR에서 발견된 각 SQL 문장에 대한 실행 계획 수준의 모니터링 통계 자료를
표시한다. V$SQL_PLAN_MONITOR 내의 각 행은 모니터링 된 실행 계획의 작업 한 개와 일치한다.
④ AWR(Automatic Workload Repository) 인프라와 statspack 리포트는 최상위 SQL로부터 읽어온 실행 계획을 저장한다.
실행 계획은 DBA_HIST_SQL_PLAN과 STATS$SQL_PLAN에 기록된다.
⑤ 실행 계획과 행 원본 작업은 DBMS_MONITOR에 의해서 작성된 트레이스 파일들에 덤프 된다.
⑥ SMB(SQL Management Base)는 SYSAUX 테이블스페이스에 저장되는 데이터 딕셔너리의 일부이다.
여기에는 문장 로그, 계획 이력, SQL 계획 기준선, SQL 프로필이 저장된다.
⑦ 10053 이벤트는 CBO의 계산 결과를 덤프하는데 사용되며 실행 계획이 포함 될 수 있다.
⑧ Oracle Database 10g Release 2부터 프로세스 상태(또는 프로세스로부터 읽어온 에러 스택)를 덤프하는 경우,
생성된 트레이스 파일에 실행 계획이 포함된다.
실행 계획 확인
DBMS_XPLAN 패키지는 5개의 테이블 함수를 제공한다.
① DISPLAY : 실행 계획 테이블의 내용을 포맷팅하고 표시한다.
② DISPLAY_AWR : AWR 내 저장된 SQL 문장의 실행 계획 내용을 포맷팅하고 표시한다.
③ DISPLAY_CURSOR : 모든 로드 된 커서의 실행 계획 내용을 포맷팅하고 표시한다.
④ DISPLAY_SQL_PLAN_BASELINE : SQL 핸들에 의해 식별된 SQL 문장에 대하여 하나 이상 의 실행 계획을 표시한다.
⑤ DISPLAY_SQLSET : SQL 튜닝 세트에 저장된 문장의 실행 계획 내용을 포맷팅하고 표시한 다.
EXPLAIN PLAN 명령
EXPLAIN PLAN 명령은 옵티마이저가 SQL 문장을 실행하는데 사용 할 실행 계획을 생성하는데 사용된다.
이 명령은 문장을 실행하지 않지만, 사용 될 지 모르는 실행 계획을 간단하게 생성한다. 만약, 실행 계획을 조사한다면,
오라클 서버가 해당 문장을 어떻게 실행하는지 확인 할 수 있다.
EXPLAIN PLAN 명령의 사용
① SQL 문장의 실행 계획을 생성하기 위해 EXPLAIN PLAN 명령을 먼저 사용한다.
② PLAN_TABLE을 쿼리하여 실행 계획의 각 단계들을 읽어 온다.
PLAN_TABLE은 모든 사용자들이 EXPLAIN PLAN 문장의 출력을 저장 할 수 있도록 전역 임시 테이블로 자동 생성된다.
PLAN_TABLE은 디폴트 샘플 출력 테이블이며, EXPLAN PLAN 문장은 실행 계획을 설명하는 행들을 해당 테이블에 저장한다.
참고 : 만약, 실행 계획 정보를 장기간 유지하고자 하는 경우에는 $ORACLE_HOME/rdbms/
admin/utlxplan.sql 스크립트를 사용하여 자신만의 PLAN_TABLE을 생성 할 수 있다.
그림 5.1
이 명령은 실행 계획의 각 단계 별 행을 실행 계획 테이블에 저장한다. 위 그림의 구문 다이어그램에서
이탤릭체로 표시된 부분이 의미하는 내용은 다음과 같다.
|
항목 |
의미 |
|
text |
문장에 지정한 식별자이다. 각 문장을 구분하려면 이 항목에 값을 입력하여야 하며, 그래야만 나중에 분석해야 할 문장을 지정 할 수 있다. 다른 사용자들과 실행 계획 테이블을 공유하거나 동일한 실행 계획 테이블에 여러 개의 실행 계획을 저장하는 경우, 매우 중요하다. |
|
schema.table |
출력 테이블에 대한 이름이며, 디폴트는 PLAN_TABLE이다. |
|
statement |
SQL 문장 |
EXPLAIN PLAN 명령 : 예
그림 5.2
이 명령은 실행 계획 테이블에 SQL 문장의 실행 계획을 저장하고, 나중에 참조하기 위해 demo01이라는
이름의 태그를 추가하였다. 또는 다음과 같은 구문을 사용 할 수도 있다.
EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id;
PLAN_TABLE
실행 계획을 수집하기 위해 사용 할 수 있는 방법은 다양하다. 지금은 EXPLAIN PLAN 명령에 대해서만
소개한다. 이 SQL 문장은 문장을 실행하지 않고 SQL 문장의 실행 계획을 수집하며, 그 결과를
PLAN_TABLE 테이블에 출력한다. 실행 계획을 수집하고 표시하는 방법이 무엇이 되었든지 간에 기본
형식과 목적은 동일하다. 그러나, PLAN_TABLE은 옵티마이저에 의해 선택되지 않을 수도 있는 계획을
보여준다. PLAN_TABLE은 전역 임시 테이블로 자동 생성되고 모든 사용자가 볼 수 있다. PLAN_TABLE은
디폴트 샘플 출력 테이블이며, EXPLAIN PLAN 문장은 실행 계획을 설명하는 행들을 해당 테이블에
입력한다. PLAN_TABLE은 트리 구조와 유사하게 구성되고, SELECT 문장의 CONNECT BY 구문에 ID와
PARENT_ID 컬럼을 사용하여 해당 구조를 읽어 올 수 있다. PLAN_TABLE 테이블은 각 사용자들을 위해
자동으로 생성되지만, utlxplan.sql 스크립트를 사용하여 자신의 스키마에 로컬 PLAN_TABLE을 직접
생성하고 EXPLAN PLAN의 결과를 저장하는데 사용 할 수 있다. 이 스크립트의 정확한 이름과 위치는
운영 체제에 따라 달라진다. UNIX에서는 $ORACLE_HOME/rdbms/admin 디렉터리에 저장된다.
데이터베이스 버전이 업그레이드 된 후에는 로컬 PLAN_TABLE 테이블을 삭제하고 재 생성하여야 하는데,
그 이유는 PLAN_TABLE 테이블의 컬럼들이 변경 될 수 있기 때문이다. 그렇지 않으면 스크립트가
실패하거나 TKPROF가 실패 할 수 있다.
참고 : 만약, 다른 이름을 가진 출력 테이블을 생성하려면, utlxplan.sql 스크립트로 PLAN_TABLE을 직접
생성하고 RENAME 문장으로 테이블의 이름을 변경한다.
PLAN_TABLE의 표시 :TYPICAL
그림 5.3
그림의 예제에서 EXPLAIN PLAN 명령은 PLAN_TABLE에 SQL 문장의 실행 계획을 입력하고, 추후 참조를
위해 demo01이라는 이름의 태그를 추가하였다. 여기서, SELECT 문장 자체는 실행되지 않는다는 사실을
인식하는 것이 중요하며, 해당 환경에서 가장 우수한 실행 계획을 결정하기 위해 SELECT 문장이 최적화
되었다. DBMS_XPLAN 패키지의 DISPLAY 함수는 PLAN_TABLE에 저장된 가장 마지막 문장을 포맷팅하고
표시하는데 사용된다. 또한, 동일한 결과를 얻어 오기 위해 다음과 같은 구문을 사용 할 수 있다.
SELECT * FROM TABLE(dbms_xplan.display(‘plan_table’,’demo01’,’typical’,null));
출력은 위 그림에서 보여지는 것과 동일하다. 이 예제에서 PLAN_TABLE 대신에 다른 실행 계획 테이블,
문장의 ID를 나타내는 demo01 대신에 다른 값을 지정 할 수 있다. TYPICAL은 실행 계획에서 가장
의미가 있는 정보들, 즉, 작업 ID, 이름과 옵션, 행 번호, 바이트, 최적화 비용을 표시한다. DISPLAY함수의
마지막 파라메터는 filter_preds에 대한 것이다. 이 파라메터는 실행 계획이 저장된 해당 테이블로부터
선택된 행들의 집합을 제한하기 위한 필터 조건 또는 조건들을 지정한다. 이 값이 Null(디폴트)이면,
표시된 실행 계획은 가장 마지막에 실행된 실행 계획이다. 이 파라메터는 실행 계획이 저장된 해당
테이블의 어떠한 컬럼이라도 참조 할 수 있고, 어떠한 SQL 구조물, 예를 들어, 서브쿼리 또는 함수
요청이라도 포함 될 수 있다.
참고:이 방법 대신에 PLAN_TABLE에 저장된 실행 계획 중, 가장 마지막 문장을 표시하기 위해 utlxpls.sql
(또는 병렬 쿼리의 경우 utlxplp.sql) 스크립트(ORACLE_HOME/rdbms/admin/ 디렉터리에
위치)를 실행 할 수 있다. 이 스크립트는 DBMS_XPLAN 패키지의 DISPLAY 테이블 함수를 사용한다.
PLAN_TABLE의 표시 : ALL
그림 5.4
여기서는 앞 예제와 같이 동일한 EXPLAIN PLAN 명령을 사용하였다. DISPLAY 함수에 사용된 ALL 옵션은
사용자 수준의 정보를 최대화하여 출력 할 수 있도록 해준다. 이 옵션은 TYPICAL 수준에 서 표시되는
정보와 PROJECTION, ALIAS와 해당 작업이 분산되었을 때, REMOTE SQL과 같은 추가 정보를 포함한다.
표시되는 출력을 정밀하게 제어하려면, 다음 키워드를 포맷 파라메터에 추가하여 디폴트 동작을
커스터마이징 할 수 있다. 각 키워드는 실행 계획 테이블 컬럼들의 논리적 그룹(파티션과 같은) 또는
기본 실행 계획 테이블 출력에 대한 논리적 추가 사항(PREDICATE와 같은)를 표시한다. 포맷 키워드는
콤마 또는 공백으로 구분되어야 한다.
① ROWS : 옵티마이저에 의해 예측된 행의 개수를 표시한다.
② BYTES : 옵티마이저에 의해 예측된 바이트 수를 표시한다.
③ COST : 옵티마이저 비용 정보를 표시한다.
④ PARTITION : 파티션 프루닝 정보를 표시한다.
⑤ PARALLEL : PX 정보(분산 방식 및 테이블 큐 정보)를 표시한다.
⑥ PREDICATE : 조건젃 영역을 표시한다.
⑦ PROJECTION : 프로젝션 영역을 표시한다.
⑧ ALIAS : Query Block Name/Object Alias 영역을 표시한다.
⑨ REMOTE : 분산 쿼리(예를 들어, 직렬 분산에 의한 원격 및 원격 sql)를 표시한다.
⑩ NOTE : 실행 계획의 노트 영역을 표시한다.
만약, 대상 실행 계획 테이블이 실행계획의 통계 컬럼(예를 들어, 고정 뷰인
V$SQL_PLAN_STATISTICS_ALL의 내용을 캡처하는데 사용되는 테이블인 경우)을 저장한다면, DISPLAY
함수를 사용 할 때, 표시해야 할 통계의 범주를 지정하기 위해 추가적인 포맷 키워드가 사용 될 수도
있다. 이 추가적인 포맷 키워드는 IOSTATS, MEMSTATS, ALLSTATS, LAST이다.
참고 : 포맷 키워드에는 지정된 정보를 배제하기 위해 ‘-‘ 기호를 접두사로 추가 할 수 있다. 예를 들어,
‘-PROJECTION’은 프로젝션 정보를 제외한다.
PLAN_TABLE의 표시 : ADVANCED
그림 5.5
ADVANCED 포맷은 Oracle Database 10g Release 2와 그 이후 버전에서만 사용 가능하다. 이 출력 포맷은 ALL
포맷으로부터 나온 모든 영역과 특정 실행 계획을 재 생성하기 위한 힌트의 집합을 표시하는 아웃라인 데이터를 포함한다.
만약, 서로 다른 환경에서 특정 실행 계획을 재 생성하고자 하는 경우에 이 영역은 매우 유용 할 수 있다.
이 영역은 10053 이벤트의 트레이스 파일에서 표시되는 영역과 동일하다.
참고 : ADVANCED 포맷이 V$SQL_PLAN과 함께 사용되면 Peeked Binds라고 부르는 하나 이상의 영역이 존재하게 된다.
AUTOTRACE
SQL*Plus에서 SQL 문장을 실행 할 때, 실행 계획 및 실행 통계에 대한 리포트를 자동으로 얻을 수 있다. 이 리포트는 SQL
DML(즉, SELECT, DELETE, UPDATE, INSERT)이 성공한 후에 작성된다. 이 방법은 이러한 문장들의 성능을 모니터링 및
튜닝하는데 매우 유용하다.
이 기능을 사용하려면, 자신의 스키마에 사용 가능한 PLAN_TABLE을 반드시 가지고 있어야 하며, 사용자에게 PLUSTRACE
롤을 부여하여야 한다. $ORACLE_HOME/sqlplus/admin/plustrce.sql 스크립트를 실행하면 PLUSTRACE 롤이 생성되고
DBA 롤에 부여된다.
일부 버전 및 플랫폼의 경우, 데이터베이스 생성 스크립트에 의해 이 스크립트가 실행된다. 만약, 자신의 플랫폼이 이러한
경우가 아니라면, SYSDBA로 접속하여 plustrce.sql 스크립트를 실행하면 된다.
PLUSTRACE 롤은 3개의 V$ 뷰에 대해서 SELECT 권한을 포함하고 있으며, 이 권한들은 AUTOTRACE 통계를 생성하는데
필수적이다. AUTOTRACE는 SQL 문장 튜닝을 위한 우수한 진단 툴이다. 이 방법은 순수한 선언적 방법이므로 사용 방법이
EXPLAIN PLAN을 사용하는 것보다 쉽다.
참고 : 시스템은 날짜 바인드 변수의 암묵적 형 변환을 수행하는 문장에 대해 EXPLAIN PLAN을 지원하지 않는다.
바인드 변수를 사용하는 경우, EXPLAIN PLAN 출력은 실제 실행 계획을 표현하지 못 할 수 있다.
AUTOTRACE 구문
그림 5.6
위 그림에서 보여지는 구문을 이용하여 다양한 방식으로 AUTOTRACE를 활성화 할 수 있다. 이 명령의 옵션은 다음과 같다.
① OFF : SQL 문장의 자동 추적을 비활성화한다.
② ON : SQL 문장의 자동 추적을 활성화한다.
③ TRACE 또는 TRACE[ONLY] : SQL 문장의 자동 추적을 활성화하고 문장의 출력 결과를 억제한다.
④ EXPLAIN : 실행 계획을 표시하지만 통계를 표시하지 않는다.
⑤ STATISTICS : 통계 자료를 표시하지만 실행 계획은 표시하지 않는다.
참고 : 만약, EXPLAIN과 STATISTICS 명령 옵션을 생략하면 실행 계획과 통계가 디폴트로 표시된다.
AUTOTRACE : 예제
그림 5.7
AUTOTRACE 시스템 변수를 설정하면 리포트를 제어 할 수 있다. 다음은 사용 예이다.
① SET AUTOTRACE ON : AUTOTRACE 리포트는 옵티마이저 실행 계획과 SQL 문장 실행 통계를 포함한다.
② SET AUTOTRACE TRACEONLY EXPLAIN : AUTOTRACE 리포트는 문장 실행 없이 옵티마이저 실행 계획만 표시한다.
③ SET AUTOTRACE ON STATISTICS : AUTOTRACE 리포트는 SQL 문장 실행 통계와 행들을 표시한다.
④ SET AUTOTRACE TRACEONLY : SET AUTOTRACE ON과 유사하지만, 사용자 쿼리의 출력을 억제한다.
만약, STATISTICS가 활성화 되면, 쿼리 데이터는 여전히 인출되지만 출력되지 않는다.
⑤ SET AUTOTRACE OFF : AUTOTRACE 리포트가 생성되지 않는다. 디폴트이다.
AUTOTRACE : 통계
그림 5.8
문장이 실행 될 때, 통계 자료는 서버에 의해 기록되고, 해당 문장을 실행하는데 필요한 시스템 자원을 표시한다.
이 결과에는 다음과 같은 통계 자료가 포함된다.
① recursive calls는 사용자 및 시스템 수준에서 발생한 재귀 호출 횟수이다. 오라클 데이터베이스는 내부 작업을
위해 사용되는 테이블을 유지 관리한다. 오라클 데이터베이스는 이러한 테이블에 변경 작업을 수행해야 할 때,
내부에 SQL 문장을 생성하고, 순서대로 재귀 호출을 발생시킨다.
② db block gets는 CURRENT 블록의 요청 횟수이다.
③ consistent gets는 블록에 대한 일관성 읽기 횟수이다.
④ physical reads는 디스크에서 읽어온 데이터 블록의 개수다. 이 수치는 physical reads direct의 값에 버퍼 캐시로 읽어온 횟수를 더한 값이다.
⑤ redo size는 발생한 리두의 전체 양을 바이트로 표시한다.
⑥ bytes sent via SQL*Net to client는 포그라운드 프로세스로부터 클라이언트에게 전송한 전체 바이트 값이다.
⑦ bytes received via SQL*Net from client는 Oracle Net을 통해 클라이언트로부터 수신한 전체 바이트 값이다.
⑧ SQL*Net roundtrips to/from client는 클라이언트로 전송 및 수신한 Oracle Net 메시지의 전체 횟수이다.
참고 : AUTOTRACE에 의해 출력된 통계 자료는 V$SESSTAT에서 읽어온 값이다.
① sorts (memory)는 메모리 내에서만 수행되고 디스크에 기록하지 않은 정렬 작업의 횟수 이다.
② sort (disk)는 최소한 한번 이상의 디스크 쓰기가 필요한 정렬 작업의 횟수이다.
③ row processed는 작업이 진행되는 동안 처리된 행의 개수다.
통계 자료에서 참조된 클라이언트는 SQL*Plus이다. Oracle Net는 Oracle Net의 설치 여부와 상관 없이 SQL*Plus와
서버간의 일반적인 프로세스 통신을 참조한다. 통계 리포트의 디폴트 포맷을 변경 할 수는 없다.
참고 : db block gets는 데이터베이스의 현재 블록 읽기를 나타낸다. consistent gets는 특정 SCN(System Change Number)를
반드시 만족하는 블록의 읽기이다. physical reads는 디스크로부터 읽어온 블록 읽기 횟수를 나타낸다. db block gets와
consistent gets는 상시에 모니터링 되는 두 통계이다. 이 값들은 읽어온 행의 개수와 비교 할 때, 매우 낮은 값이다.
정렬은 디스크가 아닌 메모리 내에서 수행되어야만 한다.
V$SQL_PLAN 뷰의 사용
이 뷰는 라이브러리 캐시 내에 존재하는 커서들의 실행 계획을 조사 할 수 있는 방법을 제공한다. 이 뷰 내의 정보는
PLAN_TABLE 내의 정보와 매우 흡사하다. 그러나, EXPLAIN PLAN은 이 문장이 실행 될 때, 사용 할 수도 있는 이론적인
실행 계획을 보여주지만, V$SQL_PLAN은 실제 사용 된 실행 계획을 포함한다. EXPLAIN PLAN 문장에 의해 얻어진 실행
계획은 커서를 실행하는데 사용 될 실제 실행 계획과 다를 수 있다. 그 이유는 커서가 서로 다른 값을 갖는 세션
파라메터로 컴파일 될 수 있기 때문이다.
V$SQL_PLAN은 하나의 SQL 문장과 연관된 모든 커서가 아닌 하나의 커서에 대한 실행 계획을 표시한다. 하나의 SQL
문장이 하나 이상의 커서를 가질 수 있고, 각 커서는 CHILD_NUMBER로 구분된다. 예를 들어, 서로 다른 여러 사용자에
의해 실행된 동일한 문장은 참조된 객체가 서로 다른 스키마에 존재하는 경우, 해당 문장과 관련된 서로 다른 커서들을
갖게 된다. 이와 유사하게 서로 다른 힌트는 서로 다른 커서를 발생시킨다. V$SQL_PLAN 테이블은 동일한 문장의 서로
다른 자식 커서에 대한 실행 계획을 확인하는데 사용 될 수 있다.
참고 : 그 외에 유용한 뷰는 V$SQL_PLAN_STATISTICS이며, 각 각의 캐시된 커서에 대하여 실행 계획 내의 각 작업에 대한
실행 통계를 제공한다. 또한, V$SQL_PLAN_STATISTICS_ALL 뷰는 V$SQL_PLAN으로부터 읽어온 정보와
V$SQL_PLAN_STATISTICS와 V$SQL_WORKAREA로부터 읽어 온 정보를 결합한다.
V$SQL_PLAN 컬럼
|
컬럼 |
설명 |
|
HASH_VALUE |
라이브러리 캐시 내의 부모 문장의 해시 값 |
|
ADDRESS |
이 커서의 부모에 대한 핸들 주소 |
|
CHILD_NUMBER |
이 실행 계획을 이용한 자식 커서 번호 |
|
POSITION |
동일한 PARENT_ID를 갖는 모든 작업의 처리 순서 |
|
PARENT_ID |
현 단계의 출력에서 처리할 다음 실행 단계의 ID |
|
ID |
실행 계획 내에서 각 단계에 부여된 번호 |
|
PLAN_HASH_VALUE |
해당 커서에 대한 SQL 실행 계획의 수치 표현 |
표 5.1
이 뷰는 PLAN_TABLE 컬럼 대부분과 그 외의 컬럼들을 포함하고 있다. PLAN_TABLE에도 존재하던 컬럼들은 해당 컬럼과
동일한 값을 갖는다.
① ADDRESS
② HASH_VALUE
ADDRESS와 HASH_VALUE 컬럼을 V$SQLAREA와 조인하면 커서 고유 정보를 추가 할 수 있다.
ADDRESS, HASH_VALUE, CHILD_NUMBER 컬럼을 V$SQL과 조인하면 자식 커서 고유 정보를 추가 할 수 있다.
PLAN_HASH_VALUE 컬럼은 해당 커서에 대한 SQL 실행 계획의 수치 표현이다. 하나의 PLAN_HASH_VALUE를 다른 값과
비교하면 두 개의 실행 계획이 동일한지 여부를 쉽게 확인 할 수 있다(두 실행 계획을 라인 별로 비교 할 필요가 없다).
참고 : Oracle Database 10g 부터 SQL_HASH_VALUE가 SQL_ID와 함께 V$SESSION에 추가되었으며, 이 컬럼은 여러
V$ 뷰에서 읽어 올 수 있다. SQL_HASH_VALUE는 32비트 값이며 AWR 데이터의 대량 리포지터리 내에서 충분히 고유한
값이라고 볼 수 있다. SQL_ID는 64비트 해시 값으로, 좀 더 고유하며 하위 32비트는 SQL_HASH_VALUE이다. 이 값은 좀
더 관리가 수월하도록 일반 문자열로 표현된다.
V$SQL_PLAN_STATISTICS 뷰
V$SQL_PLAN_STATISTICS 뷰는 해당 실행 계획의 모든 작업에 대한 실제 실행 통계, 즉, 출력 행 의 개수, 전체 수행
시간을 제공한다. 출력 행의 개수를 제외하고 모든 통계 자료는 누적 값이다. 예를 들어, 조인 작업에 대한 통계는 두 입력
데이터에 대한 통계를 포함한다. V$SQL_PLAN_STATISTICS 내의 통계 자료는 STATISTICS_LEVEL 초기화 파라메터를 ALL로
설정하거나 GATHER_PLAN_STATISTICS 힌트를 사용하여 해당 커서를 컴파일 하였을 때, 사용 가능하다.
V$SQL_PLAN_STATISTICS_ALL 뷰는 SQL 메모리(정렬 또는 해시 조인)를 사용하는 행 원본에 대한 메모리 사용량 통계를
포함한다. 이 뷰는 V$SQL_PLAN 내의 정보와 V$SQL_PLAN_STATISTICS와 V$SQL_WORKAREA로부터 읽어온 실행 통계를
결합한 것이다.
중요 동적 성능 뷰들 간의 연결
그림 5.9
V$SQLAREA는 공유 SQL 영역 내의 통계를 표시하며 SQL 문자열 당 하나의 행을 포함한다. 이 뷰는 메모리 내에 있으면서
파싱 되었고 실행 할 준비가 된 SQL 문장에 대한 통계 자료를 제공 한다.
① SQL_ID는 라이브러리 캐시 내 부모 커서의 SQL 식별자이다.
② VERSION_COUNT는 캐시에 현재 존재하는 부모 커서 아래 자식 커서의 개수이다. V$SQL은 공유 SQL 영역의 통계를
나열하고, 입력된 원본 SQL의 각 자식 커서에 대하여 커서 당 하나의 행을 포함하고 있다.
③ ADDRESS는 이 커서에 대한 부모 커서의 핸들 주소이다.
④ HASH_VALUE는 라이브러리 캐시 내의 부모 문장의 값이다.
⑤ SQL_ID는 라이브러리 캐시에 저장된 부모 커서의 SQL 식별자이다.
⑥ PLAN_HASH_VALUE는 이 커서에 대한 SQL 실행 계획의 수치 표현이다. 하나의 PLAN_HASH_VALUE를 다른 값과
비교하면, 두 개의 실행 계획이 동일한지 확인 할 수 있다(두 실행 계획을 라인 별로 비교 할 필요가 없다).
⑦ CHILD_NUMBER는 자식 커서의 번호이다.
V$SQL 내에 표시되는 통계 자료는 쿼리가 실행 된 후, 정상적으로 업데이트 된다. 그러나, 수행 시간이 긴 쿼리는 매
5초마다 업데이트된다. 그러므로, 현재 진행 중인 쿼리의 영향을 쉽게 확인 할 수 있다. V$SQL_PLAN은 라이브러리
캐시 내에 로드 된 각 자식 커서에 대한 실행 계획 정보를 포함한다. ADDRESS, HASH_VALUE, CHILD_NUMBER 컬럼을
V$SQL과 조인하면, 자식 커서 고유의 정보를 추가 할 수 있다.
V$SQL_PLAN_STATISTICS는 각 자식 커서에 대한 행 원본 수준 별 실행 통계를 제공한다. ADDRESS와 HASH_VALUE
컬럼을 V$SQLAREA와 조인하여 부모 커서를 찾을 수 있다. ADDRESS, HASH_VALUE, CHILD_NUMBER 컬럼을 V$SQL과
조인하여 자식 커서를 찾을 수 있다.
V$SQL_PLAN_STATISTICS_ALL은 SQL 메모리(정렬 또는 해시 조인)를 사용하는 행 원본에 대한 메모리 사용량 통계를
포함한다. 이 뷰는 V$SQL_PLAN 내의 정보와 V$SQL_PLAN_STATISTICS 및 V$SQL_WORKAREA의 실행 통계를 결합한 것이다.
V$SQL_WORKAREA는 SQL 커서에 의해 사용되는 작업 영역에 대한 정보를 표시한다. Shared Pool 내에 저장된 각 SQL
문장은 V$SQL 뷰에 나열된 하나 이상의 자식 커서를 포함한다. V$SQL_WORKAREA는 이러한 자식 커서가 필요로 하는
모든 작업 영역을 나열한다. V$SQL_WORKAREA는 V$SQLAREA(ADDRESS, HASH_VALUE) 및 V$SQL(ADDRESS,
HASH_VALUE, CHILD_NUMBER)과 조인 될 수 있다.
이 뷰를 사용하면 다음과 같은 질문에 대한 답변이 가능하다.
① 캐시 영역을 제일 많이 필요로 하는 상위 10개의 작업 영역은 무엇인가?
② AUTO 모드에서 할당 된 작업 영역들 중에서 최대 메모리를 사용하는 작업 영역들의 비율은 몇 퍼센트인가?
V$SQLSTATS는 SQL 커서에 대한 기본적인 성능 통계를 SQL 문장과 옵티마이저 실행 계획의 고유한 조합(즉, SQL_ID와
PLAN_HASH_VALUE의 고유한 조합)에 따른 데이터를 표현하는 각 행들과 함께 표시한다. V$SQLSTATS 내의 컬럼들에 대한
정의는 V$SQL과 V$SQLAREA 뷰의 컬럼들과 일치한다. 그러나, V$SQLSTATS 뷰는 빠르고 확장성이 좋으며 데이터 보존
기간(해당 커서가 Shared Pool에서 커서가 탈락되더라도 해당 통계가 뷰에 여전히 남아 있을 수 있다)이 길다는 점에서
V$SQL 및 V$SQLAREA과 다르다. V$SQLSTATS는 V$SQL과 V$SQLAREA에서 나타나는 컬럼의 일부를 포함한다.
V$SQL_PLAN의 검색
그림 5.10
예제에서 보는 것처럼 현재 또는 가장 마지막에 실행된 문장을 표시하려면 DBMS_XPLAN.DISPLAY_CURSOR() 함수를
사용하여 V$SQL_PLAN을 검색 할 수 있다.
주어진 문장에 대한 실행 계획을 얻으려면, 파라메터로 해당 문장의 SQL_ID 값을 전달해야 한다. SQL_ID는 커서 캐시
내 SQL 문장의 SQL_ID이다. V$SQL 또는 V$SQLAREA 내의 SQL_ID를 검색하여 적젃한 값을 읽어 올 수 있다. 또한,
V$SESSION의 특정 세션에 대한 PREV_SQL_ID를 읽어 올 수도 있다. 이 파라메터의 디폴트 값은 Null이며, 가장 마지막에
실행된 커서의 실행 계획을 표시한다. SQL_ID를 얻기 위해 다음 쿼리를 실행한다.
SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id;
SELECT SQL_ID, SQL_TEXT FROM V$SQL
WHERE SQL_TEXT LIKE ‘%SELECT e.last_name, %’;
select SQL_id, sql_text from v$SQL …
select e.last_name, d.department_name …
CHILD_NUMBER는 표시할 커서의 자식 커서 번호이다. 만약, 이 값을 제공하지 않으면, 주어진 SQL_ID 파라메터와
일치되는 모든 커서의 실행 계획들이 표시된다. SQL_ID가 지정된 경우에만 CHILD_NUMBER가 지정 될 수 있다.
FORMAT 파라메터는 실행 계획의 구체적인 수준을 제어한다. 표준 값(BASIC, TYPICAL, SERIAL, ALL, ADVANCED)에
추가하여, 해당 커서에 대한 실행 통계를 표시하기 위해 추가적인 지원 값들이 있다.
① IOSTATS: SQL 문장이 실행 되었을 때, 기본 실행 통계가 수집 (GATHER_PLAN_STATISTICS 힌트 또는 statistics_level
파라메터가 ALL로 설정 된 경우) 되었다고 가정하고, 이 포맷에서는 해당 커서의 ALL(또는 LAST) 실행에 대한 I/O 통계를 표시한다.
② MEMSTATS : PGA 메모리 관리가 활성화(pga_aggregate_target 파라메터를 0이 아닌 값으 로 지정)되었다고 가정하고,
이 포맷은 메모리 관리 통계(예를 들어, 연산자의 실행 모드, 얼마나 많은 메모리를 사용했는지, 디스크를 몇 바이트나 사용했는지 등)를
표시할 수 있도록 해준다. 이러한 통계 자료는 해시 조인, 정렬 또는 일부 비트맵 연산과 같이 메모리를 많이 사용하는 연산자에만 적용된다.
③ ALLSTATS : IOSTATS MEMSTATS를 함께 사용한 것이다.
④ LAST : 디폴트 값이며, 해당 커서의 모든 실행 결과에 대하여 실행 통계를 표시한다.
LAST 키워드는 마지막 실행에 대한 실행 통계만 보여준다.
AWR(Automatic Workload Repository)
AWR은 Oracle Database 10g에서 소개된 지능형 인프라의 일부이다. 이 인프라는 ADDM(Automatic Database Diagnostic Monitor)와
같은 많은 구성요소들에 의해서 사용된다. AWR 은 문제점 탐지, 자가 튜닝을 위해 시스템 성능 통계를 수집, 처리, 유지하고, 이러한 통계를
데이터베이스에 영구히 저장한다.
AWR에 의해 수집되고 처리되는 통계 자료는 다음과 같은 것들이 있다.
① 데이터베이스 세그먼트의 액세스 및 이용률 통계를 결정하는 객체 통계 자료
② 데이터베이스 활동에 관한 시간 사용량 기반의 시간 모델 통계. V$SYS_TIME_MODEL과 V$SESS_TIME_MODEL 뷰에서 표시
③ V$SYSSTAT와 V$SESSTAT 뷰에 수집된 시스템 및 세션 통계 일부
④ 총 수행 시간, CPU 시간, buffer get 등과 같은 기준에 의해 시스템에 가장 큰 부하를 발생시키는 SQL 문장
⑤ 최근 세션들의 이력을 표시하는 ASH 통계
데이터베이스는 매 시간마다 성능 데이터의 스냅샷을 생성하고 작업 부하 리포지터리 내의 통계 를 수집한다. 스냅샷 내의
데이터는 ADDM에 의해 분석된다. ADDM은 시스템 부하에 미치는 영향을 기반으로 하여 수집 할 문장을 결정하기 위해
스냅샷 간의 차이를 비교한다. 이것은 오랫동안 수집해야 할 SQL 문장의 개수를 감소시켜 준다.
참고 : DBMS_WORKLOAD_REPOSITORY 또는 EM과 같은 PL/SQL 패키지를 사용하여 SQL을 얼마나 자주 AWR에 저장 할
것 인지와 보존 기간을 관리 할 수 있다.
PL/SQL을 사용하여 AWR 관리
그림 5.11
AWR 관리를 위한 기본 인터페이스는 EM이지만, 모니터링 기능은 DBMS_WORKLOAD_REPOSITORY 패키지내의
프로시저들을 이용하여 관리 할 수 있다.
오라클 데이터베이스와 관련된 스냅샷은 자동으로 생성되지만 DBMS_WORKLOAD_REPOSITORY 프로시저를 사용하여
ADDM이 사용 할 스냅샷과 기준선을 생성, 삭제, 수정 할 수 있다. 스냅샷과 기준선은 성능 비교를 위해 사용되는 특정
기간 동안의 이력 데이터 집합이다. 이 프로시저를 실행하려면 DBA 롤을 부여 받아야 한다.
스냅샷 생성
만약, 자동으로 생성되는 스냅샷 이외에 다른 시간대에서 통계자료를 수집하고자 한다면 CREATE_SNAPSHOT 프로시저를
이용하여 직접 스냅샷을 생성 할 수 있다. 예를 들면 다음과 같다.
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(‘ALL’);
이 예제에서는 인스턴스에 대한 스냅샷이 디폴트 플러시 수준(TYPICAL)에서 즉시 생성된다. 사용자는
DBA_HIST_SNAPSHOT 뷰에서 이 스냅샷을 확인 할 수 있다.
스냅샷 삭제
DROP_SNAPSHOT_RANGE 프로시저를 이용하여 저장된 범위의 스냅샷들을 삭제 할 수 있다. 데이터베이스 ID와 함께
스냅샷 ID들의 목록을 확인하려면, DBA_HIST_SNAPSHOT 뷰를 확인하면 된다. 예를 들어, 다음과 같은 범위의 스냅샷을
삭제 할 수 있다.
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);
이 예제에서는 삭제 할 스냅샷 ID의 범위는 22에서 32이다. 데이터베이스 식별자는 3310949047이다. 만약, dbid에 값을
지정하지 않으면, 로컬 데이터베이스 식별자가 디폴트 값으로 사용된다.
DROP_SNAPSHOT_RANGE 프로시저가 호출되면, 스냅샷 범위에 지정된 시간 동안에 해당되는 ASH 데이터도 삭제된다.
스냅샷 설정 수정
지정된 데이터베이스 ID와 관련된 스냅샷의 생성 주기와 보존 기간을 조정 할 수 있다. 그러나, 이러한 설정은 오라클 진단
툴의 정밀도에 영향을 줄 수도 있다. INTERVAL 설정은 얼마나 자주 스냅샷이 자동으로 생성될 것인지를 분으로 지정한다.
RETENTION 설정은 스냅샷이 작업 부하 리포지터리에 얼마나 오랫동안 보존 될 것인지를 분으로 지정한다. 이 설정을
조정하려면 다음 예제와 같이 MODIFY_SNAPSHOT_SETTINGS 프로시저를 사용하면 된다.
Exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200, interval => 30, dbid => 3310949047);
이 예제에서 보존 기간은 43,200분(30일)로 지정되었으며, 각 스냅샷 간의 주기는 30분으로 지정되었다. 만약, NULL이
지정되었다면, 기존 값은 보존된다. 데이터베이스 식별자는 3310949047이다. 만약, dbid를 지정하지 않으면, 로컬
데이터베이스 식별자가 디폴트 값으로 사용된다. DBA_HIST_WR_CONTROL 뷰에서 데이터베이스 인스턴스에 대한 현재
설정값을 확인 할 수 있다.
중요 AWR 뷰
AWR 데이터는 EM 스크린 또는 AWR 리포트에서 확인 할 수 있다. 그러나, 다음 뷰들로부터 직접 통계 자료를 확인 할 수도 있다.
V$ACTIVE_SESSION_HISTORY는 1초에 한번씩 활성화 된 데이터베이스 세션의 활동성을 표시한다. V$ 메트릭 뷰는 시스템의
성능을 추적하기 위해 메트릭 데이터를 제공한다. 메트릭 뷰는 이벤트, 이벤트 클래스, 시스템, 세션, 서비스, 파일, 테이블스페이스
메트릭과 같은 여러 가지 그룹으로 분류된다. 이 그룹은 V$METRICGROUP 뷰에서 확인 할 수 있다.
DBA_HIST 뷰는 데이터베이스 내에 저장된 이력 데이터를 포함한다.
① DBA_HIST_ACTIVE_SESS_HISTORY는 최근의 시스템 활동과 관련하여 샘플링된 인메모리 활성 세션 이력의 내용에 대한 이력을 표시한다.
② DBA_HIST_BASELINE은 시스템에서 수집 된 기준선에 대한 정보를 표시한다.
③ DBA_HIST_DATABASE_INSTANCE는 데이터베이스 환경에 대한 정보를 표시한다.
④ DBA_HIST_SNAPSHOT은 시스템 내의 스냅샷과 관련된 정보를 표시한다.
⑤ DBA_HIST_SQL_PLAN은 SQL 실행 계획을 표시한다.
⑥ DBA_HIST_WR_CONTROL은 AWR 제어를 위한 설정을 표시한다.
AWR 조회
그림 5.12
AWR 내의 모든 저장된 실행 계획을 표시하려면 DBMS_XPLAN.DISPLAY_AWR() 함수를 사용하면 된다.
위 그림의 예제에서 SQL_ID를 인자로 전달하며, SQL_ID는 커서 캐시 내의 SQL 문장의 SQL_ID이다.
또한, DISPLAY_AWR() 함수는 PLAN_HASH_VALUE, DB_ID, FORMAT 파라메터를 전달 받는다.
이 예제를 완료하기 위한 절차는 다음과 같다.
1. SQL 문장을 실행한다.
SQL> select /* example */ * from hr.employees natural join hr.departments;
2. SQL_ID를 검색하기 위해 V$SQL_TEXT를 조회한다.
SQL> select sql_id, sql_text from v$SQL
where sql_text
like ‘%example%’;
SQL_ID SQL_TEXT
--------------------------------------- --------------------------------
select sql_id, sql_text from v$SQL … select /* example */ * from …
3. SQL_ID를 이용하여 이 문장이 DBA_HIST_SQLTEXT 딕셔너리 뷰 내에 캡처되었는지 검증한다. 만약, 쿼리가 아무런
행을 리턴하지 않는다면 해당 문장이 아직 AWR에 로드되지 않았음을 의미한다.
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID=’454rug2yval8w’;
no rows selected
다음 번 스냅샷(스냅샷은 매 시간마다 생성)이 생성될 때까지 기다리지 말고 직접 AWR
스냅샷을 생성한다. 해당 문장이 DBA_HIST_SQLTEXT내에 캡처되었는지 확인한다.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID=’454rug2yval8w’;
SQL_ID SQL_TEXT
------------------- --------------------------------------------------------------
454rug2yval8w select /* example */ * from …
4. 실행 계획을 읽기 위해 DBMS_XPLAN.DISPLAY_AWR() 함수를 사용한다.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR(‘454rug2yval8w));
AWR 데이터에서 SQL 리포트 생성
그림 5.13
Oracle Database 10g Release 2부터 AWR 데이터로부터 SQL 리포트를 생성하는 것이 가능해졌으며, 이것은
Statspack에서 sqrepsql.sql을 실행시킨 것과 동일하다. 10.1.0.4.0에서 sprepsql.sql과 동 일한 작업을 수행하는 스크립트는 AWR에
존재하지 않는다. 그러나, 10gR2에서는 sprepsql.sql과 동일한 작업을 실행 할 수 있는 스크립트가 있으며,
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql 파일을 호출하여 AWR SQL 리포트를 생성 할 수 있다.
dbms_xplan PL/SQL 패키지의 display_awr 테이블 함수를 이용하여 AWR 내의 실행 계획 정보를 표시 할 수 있다.
예를 들어, 다음 문장은 AWR 내에 SQL_ID에 대한 실행 계획을 표시한다.
select * from table(dbms_xplan.display_awr(‘6g1p4s9ra6ag8’));
DBA_HIST_SQLTEXT 컬럼에서 SQL_ID를 조회하여 관심 있는 SQL 문장의 적절한 값을 읽어 올 수 있다.
SQL 모니터링 : 개요
그림 5.14
STATISTICS_LEVEL 초기화 파라메터를 ALL 또는 TYPICAL(디폴트) 중의 어느 하나로 설정하면 SQL 모니터링 기능이
활성화 된다. 또한, SQL 모니터링은 Oracle Database Tuning Pack의 기능이므로
CONTROL_MANAGEMENT_PACK_ACCESS 파라메터를 DIAGNOSTIC+TUNING(디폴트)로 설정하여야 한다.
기본적으로 SQL 모니터링은 SQL 문장이 병렬로 실행되거나, 한번 실행 될 때 최소한 5초 이상의 CPU 또는 I/O 시간을
소비하는 경우에만 자동으로 시작된다. 앞에서 언급했던 것처럼 SQL 모니터링은 기본적으로 활성화된다. 그러나, SQL
문장의 모니터링을 강제로 수행하거나 중지시키는 2개의 문장 수준 힌트가 존재한다. SQL 모니터링을 강제로 실행하려면
MONITOR 힌트를 사용하고, SQL 문장이 모니터링 되지 않도록 하려면 NO_MONITOR 힌트를 사용한다.
V$SQL_MONITOR와 V$SQL_PLAN_MONITOR 뷰를 이용하여 SQL 문장에 대한 통계 자료를 모니터링 할 수 있다.
모니터링이 시작되면 V$SQL_MONITOR 동적 성능 뷰에 엔트리가 추가된다. 이 엔트리는 문장 실행과 관련되어 수집되는
주요 성능 메트릭, 여기에는 CPU 시간, 읽기 및 쓰기 횟수, I/O 대기 시간, 그 외 다양한 대기 시간이 추적된다. 이 통계
자료는 1초에 한번씩 문장이 실행 될 때마다 거의 실시간으로 갱신된다.
문장의 실행이 완료되면, 모니터링 정보는 즉시 삭제되지 않고, 최소 1분 동안 V$SQL_MONITOR 뷰에 유지된다. 해당
엔트리는 새로운 문장이 모니터링되어 해당 저장 공간이 재 할당되면 삭제 된다.
V$SQL_MONITOR와 V$SQL_PLAN_MONITOR 뷰는 모니터링 되는 문장의 실행과 관련하여 추가 정보를 얻기 위해
다음과 같은 뷰들과 결합되어 사용된다.
V$SQL, V$SQL_PLAN, V$ACTIV_SESSION_HISTORY, V$SESSION_LONGOPS, V$SESSION
SQL 모니터링 데이터를 확인하기 위해 SQL 모니터링 리포트를 사용 할 수도 있다. SQL 모니터링 리포트는 EM의 GUI
버전에서도 사용 할 수 있다.
SQL 모니터링 리포트 : 예제
그림 5.15
이 예제에서 하나의 세션은 SQL 모니터링 리포트를 출력하고, 다른 하나의 세션은 SALES 테이블을 SELECT 한다고 가정한다.
DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수에는 실행된 문장, 리포트가 얼마나 구체적인지, 리 포트 유형(TEXT, HTML,
XML)을 지정하기 위해 몇 개의 입력 파라메터를 설정한다. 위 그림의 예처럼 파라메터를 지정하지 않으면 모니터링된 가장
마지막 문장에 대하여 텍스트 리포트를 생성한다.
SELECT 문장이 시작되고, 실행되는 동안, 두 번째 세션에서 SQL 모니터링 리포트를 출력한다.
해당 리포트로부터 현재 실행 중인 SELECT 문장을 확인 할 수 있다. Global Information 영역에서 몇 가지 중요한 정보를 확인 할 수 있다.
- 동일한 SQL 문장의 두 실행 계획을 고유하게 구분하기 위해, 실행 키라고 부르는 복합 키가 생성된다.
이 실행 키는 V$SQL_MONITOR의 각 컬럼에 해당하는 3가지 속성으로 구성된다.
① SQL 문장을 구분하기 위한 SQL 식별자(SQL_ID)
② 이 기본키가 고유하다는 것을 보장하는 내부 생성 식별자(SQL_EXEC_ID)
③ 문장이 시작된 시간을 나타내는 타임스탬프(SQL_EXEC_START)
또한, 이 리포트는 지금까지 계산된 몇몇 중요 통계 자료를 보여준다.
그림 5.16
이 리포트는 해당 문장에 의해 현재 사용 중인 실행 경로를 표시한다. SQL 모니터링은 실행 계획 에서 현재 진행 중인
작업을 표시해준다. 이것은 사용자가 실행 계획의 어느 부분에서 가장 많은 시간이 소비되는지 탐지 할 수 있도록
해주기 때문에 어떤 부분을 집중 분석해야 하는지 알 수 있다. 실행 중인 작업은 리포트의 Id 컬럼에 화살표로 표시된다.
Time Active(s) 컬럼은 얼마나 오랫동안 해당 작업이 활성화되었는지를 표시한다(델타는 최초로 활성화 된 시간부터 가장
마지막 활성화 시간의 차이를 초로 표시).
Start Active 컬럼은 초로 표시되며 해당 SQL 문장이 실행된 후, 실행 계획 내의 해당 작업이 언제 시작되었는지를
보여준다. 이 리포트에서 Id 2의 테이블 전체 액세스 작업은 1초 후에 시작되어 현재까지 23초간 진행 중이다.
Start 컬럼은 실행 계획에서 해당 작업이 실행 된 횟수를 표시한다.
Rows (Actual) 컬럼은 생성된 행의 개수를 나타내며, Rows (Estim) 컬럼은 Optimizer에 의해 예측된 카디널리티를 보여준다.
Activity (percent)와 Active Detail (sample#) 컬럼은 V$SQL_PLAN_MONITOR와 V$ACTVE_SESSON_HISTORY 뷰를
조인하여 읽어 올 수 있다. Activity (percent)는 실행 계획의 각 작업에 의해 소비된 데이터베이스 시간의 비율을 보여준다.
Activity Detail (sample#)은 CPU 또는 대기 시간과 같은 활동성의 근원을 표시한다.
이 리포트에서 Activity Detail (sample #) 컬럼은 Id 2 (SALES의 TABLE ACCESS FULL)의 작업이 데이터베이스 시간을 100%
소비하고 있음을 보여준다. 지금까지 이 활동성은 4개의 샘플로 구성되며, 오직 CPU 속성만 포함되어 있다.
마지막 컬럼인 Progress는 V$SESSION_LONGOPS 뷰에서 각 작업에 관하여 모니터링 된 진행 정보를 보여준다. 이
리포트에서는 현재까지 TABLE ACCESS FULL 작업이 74% 완료되었다. 이 컬럼은 특정 시간이 지나고, 특정 행 원본에
대해서만 나타난다.
참고 : 이 특별한 리포트에서는 표시되지 않았지만, Memory와 Temp 컬럼에서는 실행 계획에서 해당 작업에 의해 소비된
메모리와 임시 공간의 양을 나타낸다.
실행 계획의 해석
그림 5.17
실행 계획 출력은 행 원본의 트리 표현이다.
각 단계(실행 계획에서 행 또는 트리에서 노드)는 하나의 행 원본을 나타낸다.
실행 계획에서 들여쓰기된 노드는 해당 노드가 부모 노드의 자식 노드임을 나타낸다.
부모 아래 노드의 순서는 해당 수준 내에서 노드의 실행 순서를 나타낸다.
만약, 두 개의 단계가 동일한 수준에서 들여쓰기 되어 있다면 첫 번째 단계가 먼저 실행된다.
트리 형식에서 트리의 각 수준에서 좌측 리프가 해당 실행 계획이 시작되는 지점이다.
실행 계획의 단계는 부여된 순번의 순서대로 수행되지 않는다. 여기에는 단계 간에 부모-자식 관계가 존재한다.
PLAN_TABLE과 V$SQL_PLAN에서 트리 구조를 읽어 오는 중요 요소는 ID, PARENT_ID, POSITION 컬럼이다. 트레이스
파일에서 이 컬럼들은 각각 id, pid, pos 항목이다.
실행 계획을 읽는 한가지 방법은 트리 구조를 갖는 그래프로 변환하는 것이다. id=1인 최상단, 즉, 트리의 루트 노드에서 시작한다.
다음은 이 루트 노드의 자식이 되는 작업을 찾아야 한다. 이것은 parent_id 또는 pid가 1인 작업을 찾으면 된다.
참고 : 여기에서 병렬 실행 계획은 논의하지 않는다.
실행 계획을 트리로 작성하는 방법은 다음과 같다.
1. ID 중에서 가장 번호가 낮은 값을 찾아서 최상단에 위치시킨다.
2. 이 값이 PID(부모)와 같은 행을 찾는다.
3. 이것을 부모 아래 위치시키는데, POS 값이 가장 낮은 번호부터 높은 번호를 순서대로 좌측에서 우측으로 진행해가며 위치시킨다.
4. 부모와 관련된 모든 ID가 발견된 후, 다음 ID로 진행해가면서 해당 과정을 반복하여 동일한 PID를 갖는 새로운 행들을 검색한다.
실행 계획을 결정하기 위해서, 해야 할 첫 번째 작업은 가장 먼저 실행 될 노드를 찾는 것이다. 그림에 나와 있는 방법이
이것을 설명하고 있지만, 복잡한 실행 계획의 경우 이 작업은 매우 어렵고 각 단계를 마지막까지 추적하는 것도 쉬운 일이
아니다. 대량의 실행 계획도 작은 실행 계획가 동일하지만 좀 더 많은 엔트리를 포함하고 있다. 동일한 기본 규칙이
적용된다. 많은 자원을 소비하지 않는 트리의 브랜치를 감추기 위해 실행 계획을 항상 축소해 둘 수 있다.
표준 실행 계획 해석은 다음과 같다.
1. 최상위에서 시작한다.
2. 데이터를 생성하지만 소비하지 않는 행 집합을 얻을 때까지 아래로 진행한다. 이것이 시작 행 원본이다.
3. 이 행 원본의 사촌을 찾는다. 이 행 원본이 다음에 실행된다.
4. 자식이 실행 된 후, 부모가 다음에 실행 된다.
5. 이 부모와 해당 자식이 완료되면, 트리를 거슬러 올라가 부모 행 원본의 사촌과 그의 부모를 찾는다. 이전에
설명한 것과 같이 실행한다.
6. 행 원본이 소진 될 때까지 실행 계획을 거슬러 올라간다.
표준 트리 해석은 다음과 같다.
1. 최상위에서 시작한다.
2. 가장 좌측의 노드에 도달 할 때까지 트리의 좌하단으로 진행한다. 이 노드가 가장 먼저 실행된다.
3. 이 행 원본의 사촌을 찾는다. 이 행 원본이 다음에 실행된다.
4. 자식들을 실행한 후, 부모가 다음에 실행된다.
5. 해당 부모와 자식들이 실행된 후, 트리를 거슬러 올라가 부모 행 원본의 사촌과 그 부모를 찾는다. 이전에 설명한 것과 같이 실행한다.
6. 행 원본이 소진 될 때까지 트리를 거슬러 올라간다.
만약, 실행 계획의 몇 가지 기본 규칙과 조금의 경험을 가지고 있다면, 대부분의 실행 계획을 쉽게 읽을 수 있다.
실행 계획 해석 : 예제 1
그림 5.18
실행 계획을 해석하는 방법을 설명하기 위해 예제 쿼리를 가지고 시작한다. 위 그림은 쿼리와 해당 쿼리와 연관된 실행
계획 및 트리 형식으로 표시한 동일한 실행 계획을 보여준다.
이 쿼리는 사원 중에서 자신의 급여가 급여 등급 테이블의 급여 범위에 해당 되지 않는 사원을 검색한다.
해당 쿼리는 두 개의 테이블과 급여 등급을 체크하기 위한 다른 테이블 기반 서브쿼리로 구성된 SELECT 문장이다.
그림의 예제를 기반으로 이 쿼리의 실행 순서를 살펴보면, 실행 순서는 3-5-4-2-6-1이다.
① 3 : EMP를 전체 테이블 스캔하면서 이 실행 계획이 시작된다(ID=3).
② 5 : 이 행들은 중첩 루프 조인 단계(ID=2)로 전달되고, 이 행들을 이용하여 ID=5인 PK_DEPT 인덱스에서 행들의 검색이 시작된다.
③ 4 : 인덱스로부터 읽어온 ROWID들을 이용하여 ID=4인 DEPT 테이블에서 그 외의 정보를 검색한다.
④ 2 : ID=2는 중첩 루프 조인 단계이며 작업이 완료 될 때까지 수행된다.
⑤ 6 : ID=2가 행 원본을 소진한 다음, ID=6에서 SALGRADE를 전체 테이블 스캔한다(트리의 동일한 수준에서 ID=2와 사촌간이다).
⑥ 1 : ID2와 ID6으로부터 읽어온 행들을 필터링하기 위해 사용된다.
자식들은 부모를 실행한 다음에 실행되므로, 자식을 실행하기 전에 조인을 위한 구조가 반드시 만들어져 있어야 하지만,
자식이 먼저 실행되는 것으로 기록됨을 주의해야 한다. 실행 계획이 완 료되는 순으로 생각하는 것이 가장 쉬운 방법이므로,
NESTED LOOPS 조인이 ID=2에서 표시되며, 두 개의 자식{ID=3 과 ID=4}은 ID=2가 완료되기 전에 작업을 완료하여야 한다.
그림 5.19
위 그림의 예제는 STATISTICS_LEVEL을 ALL로 설정하고 V$SQL_PLAN으로부터 실행 계획을 덤프한 것이다. 이 리포트는
EXPLAIN PLAN 명령의 출력과 비교 할 때, 몇 가지 중요한 추가 정보를 보여준다.
① A-Rows는 해당 행 원본에서 발생한 행의 개수이다.
② Buffers는 행 원본에 의해 수행된 일관성 읽기의 횟수이다.
③ Starts는 해당 작업을 수행하는데 소요된 시간을 나타낸다.
EMP 테이블의 각 행들에 대하여 시스템은 ENAME, SAL, JOB, DEPTNO를 읽어 온다. 그런 다음, 시스템은 이전 결과
집합에서 DEPTNO를 이용하여 DNAME을 읽기 위해 고유 인덱스(PK_DEPT)에 의해 DEPT 테이블을 액세스한다.
만약, 해당 통계 자료를 자세히 관찰해보면 EMP 테이블에 대한 TABLE ACCESS FULL 작업(ID=3)은 한번 실행되었지만
ID 5와 ID 4의 작업은 14번 실행 되었다. 즉, 각 EMP 행들에 대하여 한 번씩 실행되었다. 이 단계(ID=2)에서 시스템은
모든 ENAME, SAL, JOB, DNAME 컬럼을 읽는다
이제 시스템은 사원 중에서 급여가 급여 등급 테이블의 범위에 해당하지 않는 사람들을 필터링해야 한다. 이를 수행하기
위해 ID=2인 각 행에 대하여 SALGRADE 테이블을 FULL TABLE SCAN으로 액세스하여 해당 사원의 급여가 범위 내에
포함되는 체크한다. 이 작업은 오직 12번만 실행 되었는데, 그 이유는 EMP 테이블 내에서 급여의 고유 값이 12개였기
때문이다.
실행 계획 해석 : 예제 2
그림 5.20
이 쿼리는 부서가 Seattle에 있으며, 관리자를 가진 사원들의 이름, 부서명, 주소를 읽어 온다. 실행 계획은 첫 번째 컬럼에
ID와 두 번째 컬럼에 PID 컬럼을 가지고 있다. 들여쓰기가 위치에 반영되었으며, 실행 계획은 두 개의 중첩 루프 조인 작업을 보여준다.
이전 예제에서의 단계를 수행하면 다음과 같다.
1. ID=0인 최상단에서 시작한다.
2. 데이터를 생성하지만 소비하지 않는 행 원본을 얻을 때까지 아래로 진행한다. 이 경우에 ID 0, 1, 2, 3은 데이터를
소비한다. ID=4는 행 원본을 소비하지 않는다. 이것이 시작 행 원본이다. ID=4가 가장 먼저 시작되고, 인덱스 범위 스캔이
ROWID들을 검색하여, ID=3 에서 LOCATIONS를 검색하는데 사용된다.
3. 이 행 원본의 사촌을 검색한다. 이 행 원본이 다음에 실행된다. ID=3과 동일한 수준의 사 촌은 ID=5이다. 노드
ID=5는 자식 ID=6을 갖는다. 이것이 먼저 실행되며, 또 다른 인덱스 범위 스캔으로 ROWID들을 검색하여 ID=5인
DEPARTMENTS 테이블을 검색하는데 사용된다.
4. 자식 작업 후에 부모 작업이 실행된다. ID=2인 NESTED LOOPS 조인은 주요 데이터를 함께 읽어 오기 위해 실행된다.
5. 이 부모와 자식이 완료되면, 트리를 거슬러 올라가 부모 행 원본의 사촌과 그의 부모를 검색한다. 이전에 설명했던
것과 동일하게 실행된다. 실행 계획 내의 동일한 수준에서 ID=2의 사촌은 ID=7이며, 자식 ID=8을 가지고 있으므로,
자식이 먼저 실행된다. 인덱스 고유 스캔이 ROWID를 검색하고 ID=7에서 EMPLOYEES 테이블을 검색하는데 사용된다.
6. 모든 행 원본이 소비될 때까지 실행 계획을 거슬러 올라가며, 마침내 ID=1인 NESTED LOOPS까지 도달하고, 해당
결과를 ID=0에게 전달한다.
7. 실행 순서는 4-3-6-5-2-8-7-1-0이다.
이 실행 계획의 완전한 설명은 다음과 같다.
CITY 컬럼에 정의된 인덱스를 이용하여 드라이빙 테이블인 LOCATIONS 테이블을 대상으로 내부 중첩 루프가 실행된다.
그 이유는 Seattle에 위치한 부서를 검색하기 때문이다. 해당 결과는 LOCATION_ID 조인 컬럼에 정의된 인덱스를 이용하여
DEPARTMENTS 테이블과 조인 된다. 이 첫 번째 조인 작업의 결과는 두 번째 중첩 루프 조인을 위한 드라이빙 행 원본이 된다.
두 번째 조인은 EMPLOYEES 테이블의 EMPLOYEE_ID 컬럼에 정의된 인덱스를 이용하여 검증한다. 시스템은 첫 번째
조인으로부터 Seattle에 위치한 부서들의 모든 관리자들의 사원 ID를 알고 있기 때문이다. 이 컬럼은 기본 키이므로
고유 스캔이 수행되며, 결국, 마지막 사원의 이름을 읽어 올 때까지 EMPLOYEES 테이블을 액세스한다.
실행 계획 해석 : 예제 3
그림 5.21
그림의 실행 계획을 보고, 실행 계획이 실행되는 순서와 조인 순서(조인 테이블의 순서)가 어떻게 되는지 찾아본다.
ID는 첫 번째 컬럼이고 PID는 두 번째 컬럼이다. 들여쓰기가 위치에 반영되어 있다. 실행 계획의 조인 순서를 인식하는
것이 매우 중요하며 10053 이벤트 트레이스 파일에서 실행 계획을 찾을 수도 있다.
이 실행 계획의 해석은 다음과 같다.
① 시스템은 T3 테이블(ID=3)을 메모리에 해시한다.
② T1 테이블(ID=5)을 메모리에 해시한다.
③ T2 테이블의 스캔(ID=6)이 시작된다.
④ T2의 행을 읽고 T1을 검증한다(T1.i=T2.i).
⑤ 해당 행이 조건을 만족하면, T3를 검증한다(T1.i=T3.i).
⑥ 해당 행이 조건을 만족하면 다음 작업으로 전달한다.
⑦ 시스템은 이전 결과 집합으로부터 최대 값을 출력한다.
결과적으로 실행 순서는 3-5-6-4-2-1이다. 조인 순서는 T1-T2-T3이다. 실행 계획을 이해하기 위해 EM을 사용 할 수도
있으며, EM은 Order 컬럼을 표시해준다.
참고 : T3가 가장 먼저 표시되도록 특별한 힌트를 사용 할 수도 있다.
복잡한 실행 계획 읽기
그림 5.22
좌측의 실행 계획은 데이터 딕셔너리에 대한 쿼리로부터 생성된 것이다. 이 실행 계획을 분석하고, 첫 번째 작업을 찾기
위해 앞에서 설명한 방법을 적용하는 것은 매우 어렵다.
언제나 실행 계획을 읽기 쉽도록 접을 수 있으며, 접힌 동일한 실행 계획을 오른쪽에서 살펴 볼 수 있다. 그림에서
보여지는 것처럼 EM의 그래픽 인터페이스를 이용하면 이러한 작업을 쉽게 수행 할 수 있다. 이 실행 계획이 두 브랜치의
UNION ALL임을 쉽게 확인 할 수 있다. 데이터 딕셔너리에 대하여 사전 지식이 있으면 두 브랜치가 딕셔너리 관리
테이블 스페이스와 로컬 관리 테이블스페이스에 각각 해당됨을 이해 할 수 있다. 또한, 딕셔너리 관리 테이블스페이스가
존재하지 않는다는 사실도 알고 있다. 그래서, 만약 문제가 발생한다면 그것은 두 번째 브랜치가 원인일 것 이다. 이러한
가정을 확정하려면 가장 자원을 많이 소비하는 실행 계획의 부분을 찾아서 실행 계획 정보와 행 원본의 통계를 반드시
살펴봐야 한다. 그런 다음, 조사하기를 원하는 브랜치를 확장 할 필요가 있다. 이 방법을 사용하려면
V$SQL_PLAN_STATISTICS에서 일반적으로 발견되는 실행 통계와 트레이스 파일에서 만들어지는 tkprof 리포트를
확인해야 한다. 예를 들어, tkprof는 각 부모 작업이 수행되는 시간과 자신의 자식 작업들이 수행되는 시간을 합산한다.
실행 계획의 검토
OLTP 환경에서 SQL 문장을 튜닝 할 때, 목표는 가장 선택도가 높은 필터를 가진 테이블을 먼저
드라이빙하는 것이다. 이것이 의미하는 것은 가장 적은 행을 다음 단계로 전달하기 위한 것이다.
만약, 다음 단계가 조인이라면 더 적은 행을 조인하게 된다는 의미이다.
어떤 액세스 경로가 최적 인지 확인해야 한다. Optimizer의 실행 계획을 조사하는 경우, 다음과 같은 사항을 살펴보아야 한다.
① 실행 계획에서 드라이빙 테이블이 최상의 필터를 가지고 있는지 여부
② 각 단계의 조인 순서에서 가장 적은 행들이 다음 단계로 전달되는지 여부
③ 조인 순서는 가장 적은 행을 리턴하는데 적절한지 여부. 예를 들어, 인덱스를 경유한 중첩 루프 조인은 많은 행들을 리턴하는데 최적이 아닐 수도 있다.
④ 뷰가 효율적으로 사용되었는지 여부. 뷰에 대한 액세스가 필요한지 여부를 확인하기 위해 SELECT 목록을 확인한다.
⑤ 의도하지 않은 카르테시안 프로덕트가 존재하지 않는지 여부
⑥ 각 테이블이 효율적으로 액세스 되고 있는지 여부. SQL 문장 내의 조건 절과 테이블의 행 의 개수를 고려한다.
많은 행들을 가진 테이블에 전체 테이블 스캔과 같은 의심스러운 행위를 찾아서 WHERE 절에 조건 절이 있는지 확인한다.
또한, 전체 테이블 스캔은 소용 량의 테이블에 효율적이 될 수 있으며, 리턴되는 행의 수에 따라 더 나은 조인 방법을 사용 할 수 있다(예를 들어, hash_join).
만약, 이러한 조건들이 최적이 아니라면, SQL 문장을 재 작성하거나 테이블에 사용 가능한 인덱스를 재구성하는 것을 고려하도록 한다.
실행 계획을 넘어
실행 계획만 가지고는 잘 튜닝된 문장들과 제대로 수행되지 못하는 문장들을 구분 할 수는 없다. 예를 들어, EXPLAIN PLAN 출력에서
문장이 인덱스를 사용한다고 해서 해당 문장이 효율적으로 실행되고 있다는 것을 의미하지 않는다. 때때로 인덱스는 효율적이지 않을 수 있다.
액세스 실행 계획을 결정하기 위해서 EXPLAIN PLAN을 사용하는 것이 가장 좋으며, 테스트를 통해 해당 실행 계획이 최적이라는 것을 증명하여야 한다.
실행 계획을 평가할 때는 문장의 실제 자원 소비를 조사하여야 한다. 이 과정의 나머지 부분에서 이를 위한 여러 가지 방법을 보여 줄 것이다.
'oracle11R2 > SQL Tuning 11g' 카테고리의 다른 글
| 07장. 옵티마이저 통계 (0) | 2011.06.23 |
|---|---|
| 06장. 케이스 스터디 : 스타 변환 (0) | 2011.06.23 |
| 04장. 옵티마이저 연산자 (0) | 2011.06.15 |
| 03장. 옵티마이저 개요 (0) | 2011.06.15 |
| 02장. SQL 튜닝 개요 (0) | 2011.06.15 |
