4. DBMS_XPLAN 패키지
오라클 9.2버전에 소개된 dbms_xplan 패키지를 통해 plan_table에 저장된 실행계획을 좀 더 쉽게 출력해 볼 수 있게 되었다.
오라클은 9i부터 plan_table에 더 많은 정보를 담기 시작했고, 이 패키지를 이용하지 않더라도 직접 쿼리해 보면 과거보다 더 많은 정보를 얻어낼 수 있다.
오라클 10g부터는 라이브러리 캐시에 캐싱돼 있는 SQL 커서에 대한 실계획은 물론 Row Source별 수행통계까지 손쉽게 출력해 볼 수 있도록 기능이 확정되었다.
AWR에 수집된 과거 수행됐던 SQL에 대한 실행계획들도 확인 가능하다.
1. 예상 실행계획 출력
utlxpls 스크립트를 사용해 실행계획을 출력하는 방법을 열어보면 내부적으로 dbms.xplan 패키지를 호출하고 있는 것을 볼 수 있다.
select plan_table_output
from table(dbms_xplan.display('plan_table', null, 'serial'));dbms_xplan.display의 인자
-
첫 번째 인자 : 실행계획이 저장된 plan_table명
-
두 번째 인자 : statement_id
-> null 입력 시 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행계획 보여줌 -
세 번째 인자
-
BASIC : Id, Operation, Name 컬럼
-> format 인자를 통해 Rows, Bytes, Cost 컬럼도 추가 가능 -
ALL : 모든 컬럼 다 보여줌
-
OUTLINE : 실행계획을 수립하는 데 필요한 힌트 목록 알려줌
-
ADVANCED : All + outline
-
2. 캐싱된 커서의 실제 실행계획 출력
커서란, 하드 파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말한다.
오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공한다. 이 뷰와 sql_id 값으로 조인해서 사용할 수 있도록 제공되는 뷰 중 활용도가 가장 높은 것이 v$sql_plan과 v$sql_statistics이다. 그리고 이 두 뷰를 합쳐서 보여주는 뷰가 v$sql_plan_statistics_all이다.
set serveroutput off먼저 쿼리 수행 전에 serveroutput을 off 시킨다
그러고 마지막 수행한 SQL의 sql_id와 chile_number 값을 알아낸다.
v$sql_plan 뷰를 일반 plan table처럼 쿼리해서 원하는 방식으로 포맷팅할 수 있지만 dbms.xplan.display_cursor 함수를 이용하면 편하다.
select * from table(dbms_xplan.display.cursor('[sql_id]', [child_number], '[ format]'));dbms.xplan.display_cursor 함수는 라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행계획과, 실행계획을 만들면서 예상했던 Rows, Bytes, Cost, Time 정보를 보여준다.
sql_id, child_number에 각 null 대입하면 가장 최근 SQL이 나온다.
참고로 dbms.xplan.display_cursor 함수를 이용하면 AWR에 수집된 과거 수행했던 SQL에 대해서도 분석 작업을 진행할 수 있다.
3. 캐싱된 커서의 Row Source 수행 통계 출력
SQL문에 gather_plan_statistics 힌트를 사용하거나, 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all로 설정하면, 오라클은 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계(Row Source)별로 수행 통계를 수집한다.
참고로, '_rowsource_execution_statistics' 파라미터를 true로 설정하거나 SQL 트레이스를 걸어도 Row Source별 수행 통계가 수집된다.
조회할 때는 v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하면 된다.
출력된 결과의 컬럼들
-
E-Rows : SQL을 수행하기 전 옵티마이저가 각 Row Source별로 예상했던 로우 수
-> v$sql_plan에서 읽은 값 -
A-Rows : 실제 SQL 수행 시 읽었던 로우 수
-> v$sql_plan_statistics에서 읽은 값
5. V$SYSSTAT
오라클은 성능 측정 지표로서 활용 가능한 항목들을 선정하고, SQL이 수행되는 동안 지속적으로 그 항목들에 대한 누적 통계치를 저장한다.
v$sesstat
- 인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용하는 뷰이다
v$sysstat
- 현재 접속해 있는 본인 세션에 대한 수행 통계를 보는 뷰이다
시스템 수행 통계 및 분석
v$sesstat 이나 v$sysstat에 나타나는 값들은 인스턴스 기동 후 또는 세션 수립 후 현재까지 누적된 값이므로, 그 값의 크고 작음만으로 의미있는 정보를 얻기는 어렵다.
이를 제대로 활용하는 방법은, 두 구간 사이의 변화량을 구해 SQL 수행 도중에 내부적으로 어떤 일들이 발생했는지를 판명하는 것이다.
Ratio 기반 성능 분석
시스템 통계에서 수집된 수행 통계 자료를 이용해 Ratio 값들을 구할 수 있다.
Ratio 값들은 주로 공유 리소스 사용빈도와 경합 발생 비율을 점검한다.
-
Buffer Nowait %
버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율
-
Redo Nowait %
Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
-
Buffer Hit %
디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율
-
Latch Hit %
래치 경합 없이 첫 번째 시도에서 곧바로 래치를 획득한 비율
-
Library Hit %
라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나 오브젝트 정보를 읽으려 할 때 해당 커서 또는 오브젝트 정보를 힙 영역에서 찾기를 성공한 비율
-
Soft Parse %
실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지않고 SQL을 수행한 비율
(Parse count - 하드파싱 횟수) / (Parse count) * 100
-> 이 비율이 낮다면 바인드 변수를 사용하도록 개선해야 한다 -
Execute to Parse %
Parse Call 없이 곧바로 SQL을 수행한 비율
즉, 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율 -
Parse CPU to Parse Elapsd %
파싱 총 소요 시간 중 CPU time이 차지한 비율
(CPU time / Elapsed time) * 100
이 비율이 낮다는 것은 하드 파싱 부하가 크다는 뜻 -
Non-Parse CPU %
SQL을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
이 비율이 낮다면 파싱 과정에서 소비되는 CPU time 비율이 높은 것
파싱 부하를 줄이도록 애플리케이션을 개선 -
In-memory Sort %
전체 소트 수행 횟수에서 In-memory 소트 방식으로 수행한 비율
-
Memory Usage %
Shared Pool 내에서 현재 사용 중인 메모리 비중
-
SQL with executions>1
전체 SQL 개수에서 두 번 이상 수행된 SQL이 차지하는 비중
-
Memory for SQL w/exec>1
전체 SQL이 차지하는 메모리 중 두 번 이상 수행된 SQL이 차지하는 메모리 비중
지금까지 살펴본 Ratio 기반 성능 진단 항목들은 아직까지도 매우 유용한 지표로서 활용되고 있다.
다만, 이 값들은 문제가 발생했을 때 그 원인을 분석하는 데에 취약하다는 단점이 있다.
6. V$SYSTEM_EVENT
v$system_event는 인스턴스 기동 후 현재까지 누적된 이벤트 발생 현황을 시스템 레벨로 확인하고자 할 때 사용된다. 개별 세션별로 누적치를 확인하려면 v$session_event 뷰를 조회하면 된다. 세션별로 현재 진행 중이거나 바로 직전에 발생했던 이벤트를 확인하려면 v$session_wait 사용하면 된다.
v$session_wait 뷰를 조회했을 때 쿼리 결과는 WAIT_TIME 값에 따라 해석이 달라진다.
WAIT_TIME
- > 0 : 마지막 대기 이벤트를 대기한 시간
- = 0 : 이 이벤트를 현재 대기중
- = -1 : 마지막 대기 이벤트를 대기한 시간이 10ms 미만
- = -2 : 타이밍이 활성화되지 않음
