SQL 관련	시스템 관련
v$sqlarea v$sqlstats v$sql_plan v$sql_plan_statistics	v$sysstat v$session v$pgastat, v$sgastat, v$osstat v$system_event, v$latch v$librarycache

2. Autotrace

수행중인 쿼리의 실행 계획이나 통계 정보를 보여줍니다.

Autotrace는 티베로(Tibero)에서 제공하는 SQL문장을 처리하는 대화용 유틸리티 ‘tbSQL 유틸리티’ 안에서만 사용할 수 있습니다.

SQL> set autot[race]

{off|on|trace[only]}

[exp[lain]] [stat[istics]] [plans[tat]]

또한 Autotrace는 동적성능뷰 (DPV) 결과를 가공해 제공합니다.

Explain: v$sql_plan
Statistics: vt_autotracestat (from v$sysstat)
Planstat: v$sql_plan_statistics

2.1. 수행예제

select emp.name emp_name,

dept.name dept_name

from emp, dept

where emp.dept = dept.id

and emp.name=‘홍길동’;

2.1.1. 예제1

실행결과 (On/Traceonly)

SQL> set autot on

SQL> /

EMP_NAME DEPT_NAME

———- ———-

홍길동 인사팀

1 row selected.

실행계획 (Explain)

SQL ID: 2278

Plan Hash Value: 3043701884

Execution Plan

————————————————————

1 HASH JOIN (Cost:52, %CPU:0, Rows:1)

2 TABLE ACCESS (FULL): EMP (Cost:26, %CPU:0, Rows:1)

3 TABLE ACCESS (FULL): DEPT (Cost:26, %CPU:0, Rows:1)

Predicate Information

————————————————————

1 – access: (“EMP”.”DEPT” = “DEPT”.”ID”) (1.000)

2 – filter: (“EMP”.”NAME” = ‘홍길동’) (1.000)

실행통계 (Statistics)

NAME VALUE

—————- ————–

db block gets 2

consistent gets 28

physical reads 0

redo size 0

sorts (disk) 0

sorts (memory) 0

rows processed 1

2.1.2. 예제2

실행결과 (On/Traceonly)

SQL> set autot on explain planstat

SQL> /

EMP_NAME DEPT_NAME

———- ———-

홍길동 인사팀

1 row selected.

실행계획 (Explain)

SQL ID: 2837

Plan Hash Value: 3043701884

Execution Plan

——————————————————————–

1 HASH JOIN (Cost:52, %CPU:0, Rows:1)

2 TABLE ACCESS (FULL): EMP (Cost:26, %CPU:0, Rows:1)

3 TABLE ACCESS (FULL): DEPT (Cost:26, %CPU:0, Rows:1)

Predicate Information

——————————————————————–

1 – access: (“EMP”.”DEPT” = “DEPT”.”ID”) (1.000)

2 – filter: (“EMP”.”NAME” = ‘홍길동’) (1.000)

SQL ID: 2837

Plan Hash Value: 3043701884

노드별 실행 통계 (planstat)

Execution Stat

——————————————————————–

1 HASH JOIN (Time:.03 ms, Rows:1, Starts:1)

2 TABLE ACCESS (FULL): EMP (Time:.06 ms, Rows:1, Starts:1)

3 TABLE ACCESS (FULL): DEPT (Time:.04 ms, Rows:1, Starts:1)

3. SQL trace

SQL 종류 및 실행 단계, physical disk read와 consistent read 횟수 등 수행하는 모든 SQL에 대해 추적 및 Trace 파일을 생성함으로써 수행 정보를 알 수 있습니다.

SQL Trace를 통해 DBMS를 사용하는 application에서 문제가 되는 원인 및 쿼리 종류 등을 쉽게 파악할 수 있습니다.

제공정보

Parse, execute, fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Userid under which each parse occurred
execution plan 정보

SQL Trace 설정

인스턴스 레벨	세션 레벨
$TB_SID.tip에 설정 GATHER_SQL_PLAN_STAT = Y \| N flag가 켜져 있는 경우, 모든 수행 query마다 statistics를 수집하게 되므로, 서버 운용 중에는 flag가 활성화되지 않도록 처리	세션 레벨에서 변경 가능 SQL> ALTER SESSION SET SQL_TRACE = [ TRUE \| FALSE ]; SQL> execute dbms_system.set_sql_trace_in_session (sid, serial#, [true\|false]);

특정 세션을 sql trace로 모니터링 시 v$session 뷰의 SQL_TRACE 컬럼 정보가 ‘ENABLED’로 보입니다.
SQL Trace로 모니터링이 끝나면 바로 sql trace를 종료합니다.
$TB_HOME/instance/{TB_SID}/log/sqltrace디렉토리에 trc 확장자로,
tb_sqltrace_[PID]_[TID]_[SESSION_SERIAL_NO].trc 파일이 생성됩니다.

3.1. tbprof

SQL Trace를 통해 만들어진 TRC 파일은 수행 중 최소한의 성능 피해를 주기 위한 포맷으로 정보를 남기므로 바로 읽고 분석하기에는 불편함이 있습니다.

이 때 같은 SQL 수행 별로 통합하거나 사용자가 보기에 더 편한 포맷으로 변환해주는 툴인 ‘tbprof’ 를 이용할 수 있습니다.

3.1.1. 사용방법

$ tbprof inputfile_name output_filename [SORT=sort_option] [PRINT=integer]

[AGGREGATE=YES|NO]

SORT: 결과를 정렬하는 방식으로 sort_option이 있는 경우 tbprof는 sort_option에 따라 결과들을 내림차순으로 정렬
AGGREGATE : 같은 SQL에 대한 정보를 합산할 것인지 결정

Example) $ tbprof tb_sqltrc_19693_19_8.trc test.prf sort=prsqry,exeqry,fchqry

3.1.2. 사용예시

실행쿼리

select emp.name emp_name, dept.name dept_name

from emp, dept

where emp.dept = dept.id and emp.name=’홍길동’

전체통계

stage count cpu elapsed current query disk rows

——————————————————————————————

parse 1 0.00 0.00 4 129 15 0

exec 1 0.00 0.00 0 0 0 0

fetch 1 0.00 0.00 1 17 16 1

—————————————————————————————–

sum 3 0.00 0.00 5 146 31 1

rows execution plan

———————————————————-

1 hash join (et=67, cr=0, cu=0, co=242, cpu=0, ro=1)

1 table access (rowid) EMP(1661) (et=58, cr=1, cu=0, co=216, cpu=0, ro=1)

1 index (range scan) IDX_EMP(1671) (et=14, cr=2, cu=0, co=215, cpu=0, ro=1)

101 table access (full) DEPT(1662) (et=329, cr=14, cu=1, co=26, cpu=0, ro=1)

↑ 결과row수 ↑Plan 노드타입 ↑실행통계 ↑옵티마이저 추정치 ↑노드별 통계

☞ “[Tibero] SQL Tuning (6)” 에서 계속됩니다.

Innovating today, leading tomorrow

[Tibero] SQL Tuning (5)

Tuning 대상 식별 도구