우리 회사 데이터베이스를 티베로로 변경하기
v$sql_plan 0 0 2,280

by 티베로 티베로 실행계획 [2018.10.11]


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
IOSTATS 수행 정보 중 IO와 관련된 모든 항목을 보여준다(TEMPREAD + TEMPWRITE + BUFGETS)
MEMSTATS 수행 정보 중 메모리와 관련된 모든 항목을 보여준다(USEDMEM).
ALLSTATS 수행 정보 중 IO, 메모리와 관련된 모든 항목을 보여준다(IOSTATS + MEMSTATS).
BASIC 출력 기본 포맷으로 optimizer에서 예측한 cardinality, cost와 마지막 수행에 대한 노드별 수행 시간을 출력한다(CARDS + COST + PART + ELAPTIME + LAST).
TYPICAL TYPICAL BASIC 포맷에 추가로 마지막 수행에 대한 노드별 처리 row 수와 predicate 정보, remote sql 정보를 출력한다(BASIC + PE + ROWS + STARTS + PRED + REMOTE + PRECISE)
ALL TYPICAL 포맷에 ALLSTATS 항목을 보여준다(TYPICAL + ALLSTATS).
LAST 수행 정보 값을 마지막 수행 정보 값만 출력한다. 지정하지 않은 경우 수행 정보 값은 모든 수행에 대한 누적 값을 보여준다.
PRECISE CARDS, ROWS에 대해 반올림 없이 실제 값을 보여준다.
HEADER 플랜의 기본 정보(sql id, hash value, 총 수행 횟수, 총 패치 횟수, 플랜 수행시간)를 보여준다
SQL 플랜 생성에 사용된 쿼리문을 보여준다.

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 

결과 항목 설명
CARDS optimizer에서 예측한 해당 플랜 노드의 row 수이다.
COST optimizer에서 예측한 해당 플랜 노드의 cost이다.
PARTITION 파티션 관련 정보이다.
PARALLEL parallel execution 관련 정보이다.
PREDICATE 플랜 노드별 predicate 정보이다.
REMOTE 플랜 노드별 데이터베이스 링크에 수행한 쿼리 내용이다.
ROWS 해당 플랜 노드에서 실제 수행된 row 수이다.
ELAPSTIME 해당 플랜 노드에서 실제 수행된 시간이다.
USEDMEM 해당 플랜 노드에서 실제 사용된 메모리 양이다.
TEMPREAD 해당 플랜 노드에서 실제 사용된 temp read 횟수이다.
TEMPWRITE 해당 플랜 노드에서 실제 사용된 temp write 횟수이다.
BUFGETS 해당 플랜 노드에서 실제 요청한 buffer get 횟수이다
STARTS 해당 플랜 노드가 실제 재시작된 횟수이다.

  • - 해당 강좌는 도서 " [우리 회사 데이터베이스를 티베로로 변경하기]"의 내용을 옮겼습니다.
  • - 해당 도서는 기간계 DBMS(DATABASE MANAGEMENT SYSTEM)를 티베로로 전환하는 실제 프로젝트를 수행한 실무자가 DBMS 전환 과정과 실제 적용 사례, 문제 해결 과정 등을 자세하게 설명하고 있습니다.

- 강좌 URL : http://www.gurubee.net/lecture/4154

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입