dbms_xplan 패키지 (튜닝 tool)

dbms_xplan 패키지에 대하여

 

SQL 의 실행계획 및 옵티마이져 통계를 보기 위해서는 SQL_TRACE를 사용하면 더 구체적인 정보를 확인할 수 있지만 SQL_TRACE를 사용할 수 있는 권한이 없는 경우도 있고, 

굳이 파일로 생성하여 확인하는 과정이 번거롭다고 느낄 수도 있다.

 

이 때 dbms_xplan 패키지를 활용하여 SQL plan을 쉽게 얻을 수 있다.

 

주요 함수들

dbms_xplan.display

dbms_xplan.display_awr

dbms_xplan.display_cursor

dbms_xplan.display_sql_plan_baseline

dbms_xplan.display_sqlset

 

 

1. 권한 설정

 

dbms_xplan을 사용하기 위해선느 다음과 같이 일부 뷰들에 대한 select 권한이 부여되어 있어야 한다.

 

grant select on v_$session to <username>;

grant select on v_$sql_plan_statistics_all to <username>;

grant select on v_$sql to <username>;

 

# statistics_level을 아래와 같이 all로 설정한 경우, gather_plan_statistics

힌트를 주지 않아도 실행 통계 확인이 가능하다. 권장 사항은 아니다. 

 

alter session set statistics_level = all;

 

SQL Plan 확인

 

1. hint 추가

 

조금 더 유용한 정보를 얻기 위해서는 SQL에 gather_plan_statistics 힌트를 주어야 한다. 이 힌트가 포함되지 않은 SQL은 실실행계획에서 옵티마이저가 실행 전에 예상한 값만을 저장하고, 힌트가 포함된 경우는 실행 이후의 실제 실행통계 정보도 함께 포함된다.

 

select /*+ gather_plan_statistics plan_test */ col1

from tab1;

 

 

2. 실행한 SQL의 sql_id 확인

 

SQL을 실행한 이후 해당 SQL에 대해서만 plan을 추출하기 위해 sql_id를 확인한다. sql_text 컬럼을 조회해보면 library_cache에 저장된 sql들이 모두 조회되므로 원하는 sql의 id만 추출해낼 수 있도록 where 조건을 통해 필터링 해야 한다.

 

이때 명확하게 구별해낼 수 있도록 SQL 힌트에 유니크한 문자열을 주는 것도 한 방법이다.

(여기서는 plan_test라는 문자열을 추가로 넣어주었다.)

 

select sql_id, sql_text

from v$sql

where sql_text not like '%v$sql%'

and sql_text like '~~~~'

and sql_text like '%plan_test%'

order by last_active_time desc;

 

 

 

3. SQL Plan 조회

 

위 과정에서 조회한 sql_id 를 아래의 sql_id 부분에 넣어주고 실행

 

select *

from table(dbms_xplan.display_cursor(sql_id, null, 'allstats');

 

sql_id를 조회하기가 번거롭고 사용자가 많지 않은 경우

가장 마지막에 실행된 sql에 대해 아래와 같이 간단하게 조회할 수 있다.

 

select *

from table(dbms_xplan.display_cursor(null,null,'allstats last -rows'));

 

예시)

SQL> SELECT * 
     FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ak7vxhj055996', 1, 'ALLSTATS LAST'));

-- 실제 수행한 결과
---------------------------------------------------------------------------------------
|Id  | Operation                  | Name   | E-Rows | A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP    | 1      | 0      |00:00:00.01 | 4       |
|* 2 | INDEX UNIQUE SCAN          | EMP_U1 | 1      | 1      |00:00:00.01 | 3       |
---------------------------------------------------------------------------------------

-- 예측 결과
-------------------------------------------------------
|Id  | Operation                  | Name   | E-Rows |
-------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP    | 1      |
|* 2 | INDEX UNIQUE SCAN          | EMP_U1 | 1      |
-------------------------------------------------------

-- 예측 실행계획과 실제 실행계획 비교
 - A-Rows, A-Time, Buffer 추가 정보 보여줌
 - E : Estimate, A : Actual
   

 

 

 

4. 예측 실행계획만 확인하고 싶을 때

 

SQL 실행 전, 그냥 실행계획만을 확인하고 싶은 경우는 display_cursor 대신 display를 사용하면 된다.

 

# 실행계획 수집

explain plan for

select *

from tab1

where condition is not null;

 

# 실행계획 조회

select * from table(dbms_xplan.display);

 

참고사이트 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=itperson&logNo=220840180396 

[Oracle] dbms_xplan 을 활용한 SQL Plan 확인

https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS378

DBMS_XPLAN

 

https://argolee.tistory.com/8