<오라클 성능 고도화 원리와 해법1> Ch03-04 DBMS_XPLAN 패키지

오라클 성능 고도화 원리와 해법1 - Ch03-04 DBMS_XPLAN 패키지

오라클 9.2 버전에 소개된 dbms_xplan 패키지를 통해 plan_table에 저장된 실행 계획을 좀 더 쉽게 출력해볼 수 있게 되었다. 오라클은 9i부터 plan_table에 더 많은 정보들을 담기 시작했고, 이 패키지를 이용하지 않더라도 직접 쿼리해보면 과거보다 더 많은 유용한 정보를 얻어낼 수 있다.

오라클 10g부터는 라이브러리 캐시에 캐싱돼 있는 SQL 커서에 대한 실행 계획은 물론 RowSource별 수행 통계까지 손쉽게 출력해볼 수 있도록 기능이 확장되었다. AWR에 수집된 과거 수행됐던 SQL에 대한 실행 계획을 확인하는 것도 가능하다.

(1) 예상 실행 계획 출력

앞에서 @?/rdbms/admin/utlxpls 스크립트를 사용해 실행 계획을 출력하는 방법을 이미 보았는데, 그 스크립트를 열어보면 내부적으로 dbms_xplan 패키지를 호출하고 있는 것을 볼 수 있다.

select plan_table_output
from table(dbms_xplan.display('plan_table', null, 'serial'));

첫 번째 인자에는 실행 계획이 저장된 plan_table 명을 입력하고, 두 번째 인자에는 statement_id를 입력하면 된다. 두 번째 옵션이 NULL일 때는 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행 계획을 보여준다. 병렬 쿼리에 대한 실행 계획을 수집했다면 @?/rdbms/admin/utlxplp 스크립트를 수행함으로써 병렬 항목에 대한 정보까지 볼 수 있다.

(2) 캐싱된 커서의 실제 실행 계획 출력

커서가 무엇인지는 4장에서 자세히 다루지만 여기서 간단히 정의해보면, 하드 파싱과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행 계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말한다. 오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행 통계를 볼 수 있도록 v$sql 뷰를 제공한다. 이 뷰의 활용방안에 대해서는 뒤쪽에서 다시 설명한다. 이것과 함께 sql_id 값으로 조인해서 사용할 수 있도록 제공되는 뷰가 몇 가지 더 있는데, 그 중 활용도가 가장 높은 것이 v$sql_plan과 v$sql_plan_statistics다. 그리고 이 두 뷰를 합쳐서 보여주는 것이 v$sql_plan_statistics_all이다.

v$sql_plan 뷰를 일반 plan_table처럼 쿼리해서 원하는 방식으로 포맷팅할 수 있지만 dbms_xplan.display_cursor 함수를 이용하면 편하다.

select * from table(dbms_xplan.display_cursor('[sql_id]',[child_no], '[format]'));

이 함수는 라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행 계획과 실행 계획을 만들면서 예상했던 Rows, Bytes, Cost, Time 정보를 보여준다. sql_id와 child_number를 매번 찾는 것이 귀찮다면(당연히 귀찮다.) 첫 번째, 두 번째 인자에 NULL 값을 넣어주면 된다. 맨 우측 format 인자에는 앞에서 dbms_xplan.display 함수에 사용했던 옵션들을 그대로 사용할 수 있고 출력 포맷도 같다.

(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 뷰를 이용하면 된다.

SQL 트레이스를 걸어도 같은 결과를 얻을 수 있으므로 TKProf를 이용하기 전에 먼저 Row Source Operation을 확인하고, Call 통계 정보는 필요할 때만 확인하는 식으로 이용할 수 있다.

아래는 출력된 결과 샘플인데, 여기서 E-Rows는 SQL을 수행하기 전 옵티마이저가 각 RowSource별로 예상했던 로우 수로서 v$sql_plan에서 읽어온 값이다. A-Rows는 실제 수행 시 읽었던 로우 수로서, v$sql_plan_statistics에서 읽어온 항목이다. 이처럼 옵티마이저의 예상 로우 수와 수행 시 실제 로우 수를 비교해 보여주므로 옵티마이저의 행동을 관찰할 때 유용하게 사용할 수 있다.