<오라클 성능 고도화 원리와 해법1> Ch03-02 AutoTrace

오라클 성능 고도화 원리와 해법1 - Ch03-02 AutoTrace

AutoTrace 결과에는 SQL을 튜닝하는 데 유용한 정보들을 많이 포함하고 있어 가장 즐겨 사용되는 도구 중 하나다.

아래와 같은 옵션 조합에 따라 필요한 부분만 출력해 볼 수 있다.

① set autotrace on SQL을 실제 수행하고 그 결과와 함께 실행 계획 및 실행 통계를 출력한다.

② set autotrace on explain SQL을 실제 수행하고 그 결과와 함께 실행 계획을 출력한다.

③ set autotrace on statistics SQL을 실제 수행하고 그 결과와 함께 실행 통계를 출력한다.

④ set autotrace traceonly SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행 계획과 통계만을 출력한다.

⑤ set autotrace traceonly explain SQL을 실제 수행하지 않고 실행 계획만을 출력한다.

⑥ set autotrace traceonly statistics SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행 통계만을 출력한다.

①~③은 수행 결과를 출력해야 하므로 쿼리를 실제 수행한다. ④, ⑥는 실행 통계를 보여줘야 하므로 쿼리를 실제 수행한다. ⑤는 실행 계획만 출력하면 되므로 쿼리를 실제 수행하지 않는다. SQL*Plus에서 실행 계획을 가장 쉽고 빠르게 확인해볼 수 있는 방법이다.

AutoTrace 기능을 실행 계획 확인용도로만 사용한다면 plan_table만 생성돼 있으면 된다. 하지만 실행 통계까지 함께 확인하려면 v$sesstat, v$statname, v$mystat 뷰에 대한 읽기 권한이 필요하다. 따라서 dba, select_catalog_role 등의 롤(Role)을 부여받지 않은 일반 사용자들에게는 별도의 권한 설정이 필요하다. 이들 뷰에 대한 읽기 권한을 일일이 부여해도 되지만 plustrace 롤(role)을 생성하고 필요한 사용자들에게 이 롤을 부여하는 것이 관리상 편리하다. 아래처럼 하면 된다.

SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;

참고로, statistics 모드로 AutoTrace를 활성화시키면 새로운 세션이 하나 열리면서 현재 세션의 통계 정보를 대신 쿼리해서 보여준다. 쿼리를 실행하기 전 현재 세션의 수행 통계 정보를 어딘가에 저장했다가 쿼리 실행 후 수행 통계와의 델타 값을 계산해 보여주는 방식이다. 만약 같은 세션에서 수행한다면 세션 통계를 쿼리할 때의 수행 통계까지 뒤섞이기 때문에 별도의 세션을 사용하는 것이다.