[Tibero] SQL Tuning (5)

Tuning 대상 식별 도구

본 문서에서는 SQL, 시스템 등 실행 쿼리에 대한 정보를 확인할 수 있는 도구들에 대해

안내합니다.

​​

1. 동적성능 뷰 (DPV, Dynamic Performance Veiws)

DBMS 조작 시 필요한 정보인 DBMS의 상태와 성능확인, 모니터링 등을 위한 뷰 입니다.

 

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)에서 계속됩니다.​

​

​

​