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 확인
SQL 실행계획을 확인하고 싶을 때 SQL_TRACE 를 쓰면 더 구체적인 정보를 확인할 수 있지만 S...
blog.naver.com
https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS378
DBMS_XPLAN
format Controls the level of details for the plan. It accepts four values: BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option. TYPICAL: This is the default. Displays the most relevant information in th
docs.oracle.com
