autotrace나 sql trace를 수행하기 위해선 tbsql을 수행하거나 OS의 tracefile을 확인해야 하므로 tbAdmin, Orange와 같은 GUI Tool에서만 주로 작업을 하게 되는 개발자는 활용이 어려운 면이 있다.

v$sql_plan은 SQL Cache내 캐싱되어 있는 실제로 수행된 실행계획을 select만으로 간편하게 조회가 가능하므로 SQL 튜닝시 훌륭한 대안이 될 수 있다.

데이터베이스 관리자도 개발자가 접속하는 Database 계정에 관련 뷰 몇 개만 select권한을 부여하면 되므로 보안상으로도 안전하다.

사용자는 상세정보 수집을 위해 SQL 수행 세션에 GATHER_SQL_PLAN_STAT 파라미터를 Y로 설정하고 해당 VIEW를 알맞은 형태로 포맷팅 해주는 DBMS_XPLAN을 통해 조회하면 된다.

상세정보 수집

alter session set GATHER_SQL_PLAN_STAT=’Y’;

권한 부여

• GRANT SELECT ON SYS.V$SQL TO TIBEROTEST;
• GRANT SELECT ON SYS.V$SQL_PLAN TO TIBEROTEST;
• GRANT SELECT ON SYS.V$SQL_PLAN_STATISTICS TO TIBEROTEST;

조회

select * from table(dbms_xplan.display_cursor(‘SQL_ID’, ‘OPTION’));

DBMS_XPLAN.DISPLAY_CURSOR OPTION

1) 실행계획을 확인할 대상 SQL 수행

@tibero:/tibero/tibero6/scripts # tbsql sys/*****

-- 필요 권한 부여
GRANT SELECT ON SYS.V$SQL TO TIBEROTEST;
GRANT SELECT ON SYS.V$SQL_PLAN TO TIBEROTEST;
GRANT SELECT ON SYS.V$SQL_PLAN_STATISTICS TO TIBEROTEST;

@tibero:/tibero/tibero6/scripts # tbsql tiberotest/tibero

--보유 권한 확인
SQL> SELECT TABLE_NAME, PRIVILEGE FROM USER_TAB_PRIVS;

---------------------- -------------
TABLE_NAME             PRIVILEGE
---------------------- -------------
DBMS_SYSTEM            EXECUTE
V$SESSION              SELECT
V$SQL                  SELECT
V$SQL_PLAN             SELECT
V$SQL_PLAN_STATISTICS  SELECT

--실행계획 수행
alter session set GATHER_SQL_PLAN_STAT='Y';

select --test01
sum(1) from TIBEROTEST.CODE;
(--test01과 같이 임시적으로 id를 부여하면 sql_id를 검색할 때 편리하다)

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

select sql_id from v$sql where sql_text like '%select --test01%';
-------------------
sql_id
------------------
3n67fdy080g3f

select * 
  from table(dbms_xplan.display_cursor('3n67fdy080g3f', 'ALL'));

-- DBMS_XPLAN.DISPLAY_CURSOR은 다음 V$SQL_PLAN 및 
-- V$SQL_PLAN_STATISTICS를 다음의 결과와 같이 포맷팅 해주는 함수이다

SELECT * FROM V$SQL_PLAN WHERE SQL_ID = '3n67fdy080g3f';
SELECT * FROM V$SQL_PLAN_STATISTICS WHERE SQL_ID = '3n67fdy080g3f';

---------결과-----------------------
SQL ID           : 3n67fdy080g3f
HASH VALUE       : 2155887726
PLAN HASH VALUE  : 748753906
EXECUTIONS       : 1
FETCHES          : 1
LOADED AT        : 2016/04/19 13:18:30
TOT ELAPSED TIME : 00:00:00.0020
AVG ELAPSED TIME : 00:00:00.0020 

결과 항목 설명