| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 28 | 29 | 30 |
Tags
- grid
- MySQL
- prompt
- PERL
- perl one-liner
- dba
- command & perl
- patch
- perl string
- Network
- perl + 정규표현식
- memory
- oracle install
- Linux
- cygwin
- sqlplus
- fdisk
- 연산자
- solaris
- php5
- RHEL4
- SQL
- oracle
- bash
- mariaDB
- Unix
- solaris network
- Oracle RAC
- rac
- 오라클
Archives
- Today
- Total
DATA 전문가로 가는 길
[Oracle] SQL 실행 계획 확인 방법 2편 (SQL Trace, TKPROF) 본문
Data Architecture/Tunning
[Oracle] SQL 실행 계획 확인 방법 2편 (SQL Trace, TKPROF)
EstenPark 2011. 3. 31. 21:411. SQL Trace 기능 활성화
1-1 SQL Trace란?
SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듭니다.
SQL Trace는 세션과 인스턴스 레벨에서 SQL 문장들을 분석 할 수 있습니다.
SQL Trace에 의해 생성된 파일의 확장자는 .trc 입니다.
.trc 파일은 직접 읽기 불편하고, TKPROF 유틸리티를 이용하면 쉽게 분석 할 수 있습니다.
인스턴스 레벨로 Trace를 수행시키면 전체적인 수행능력이 20~30% 정도 감소하므로, 될 수 있으면 세션 레벨로 Trace 파일을 생성해야 합니다.
1-2 SQL Trace 기능
SQL> set echo off
모든 SQL 수행에 대한 Trace 파일을 생성
- 인스턴스 레벨 추적(많은 부하 발생)
- 세션 레벨 추적
구문 분석, 실행 및 인출 단계에 대한 크기 및 시간 통계 정보
시스템의 전체적인 분석에 필요
관련 초기화 피일
- USER_DUMP_DEST : Trace 파일이 생성되는 디렉토리를 지정
- TIMED_STATISTICS = true : RDBMS가 추가적인 CPU 시간, 실행 시간 등을 모을 수 있게 하며, 이 시간 통계는 SQL 악성 여부를 판단하는 중요한 요소가 됩니다. ALTER SESSION SET TIMED_STATISTICS=ture 또는 PFILE인 init[SID].ora파일에 설정 할 수 있습니다.
- SQL_TRACE : Trace의 수행여부, ALTER SESSION SET sql_trace=TRUE 또는 init.ora 파일에 설정
- MAX_DUMP_FILE_SIZE : 트레이스 파일의 최대 크기(단위: OS 블럭 수)
1-3 SQL Trace 기능 활성화
-- 다른 유저에 권한 부여 SQL> GRANT ALTER SESSION TO SCOTT; Grant succeeded. -- SESSION LEVEL로 실행 방법 SQL> ALTER SESSION SET SQL_TRACE = TRUE; 또는 SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(TRUE) 또는 SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(session_id, serial_id, TRUE) Session altered. -- SESSION LEVEL로 종료 방법 SQL> ALTER SESSION SET SQL_TRACE = FALSE; Session altered. -- 10046 TRACE EVENT를 이용하는 방법 SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Session altered. -- PFILE를 이용한 인스턴스 LEVEL로 실행 하는 방법 [2011-04-05 18:46:32]-[oracle@estenpark:/massdb/app/oracle/product/10g/db_1/dbs] $ grep SQL_TRACE initMASSDB.ora SQL_TRACE=TRUE
1-4 TKPROF(trace 파일 출력) 란?
TKPROF는 SQL Trace를 통해 생성된 Trace 파일을 분석이 가능한 형식으로 전환하여 출력 해준다.
* 문법
TKPROF tracefile outputfile [SORT=nmber] [PRINT=number] [EXPLAIN=username/password]
- tracefile : SQL Trace의해 생성된 통계정보를 가진 유저 덤프 파일명
- outputfile : TKPROF가 읽기 가능한 텍스트 파일로 생성 할 파일명
- sort : 지정된 Option(EXECUP, FCHDSK, PRSCPU)에 ASC 순으로 SQL 문을 정렬
ex) SORT=EXECPU -> 가장 나쁜 Excute CPU 값을 가진 통계 값을 가장 먼저 출력
- print : 지정된 수의 SQL문에 대해서만 TRACE 결과를 출력
- explain : SQL문의 EXCUTION PLAN(실행계획)을 수립하고 저장
1-5 TKPROF 통계 정보 설명
| 로우/컬럼 | 설 명 |
|---|---|
| Parse | SQL문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함 된다. |
| Execute | SQL문의 실행 단계에 대한 통계. Update, Insert, Delete 문장들은 여기에 수행한 결과만 나온다. |
| Fetch | SQL문이 실행되면서 페치된 통계 |
| count | SQL문이 파싱/실행/페치가 수행된 횟수 |
| cpu | parse, execute, fetch가 실제로 사용한 CPU시간 |
| elapsed | 작업의 시작에서 종료시까지 실제 소요된 시간 |
| disk | 디스크에서 읽혀진 데이터 블럭의 수 |
| query | 메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나 아직 커밋되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭 수. SELECT문에서는 대부분 여기에 해당하며 Update, Insert, Delete 작업시에는 소량만 발생 합니다. |
| current | 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한 블럭(Dirty Block)을 액세스한 블럭 수. 주로 Update, Insert, Delete 작업시 많이 발생 한다 |
| rows | SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수 |
2-1 SQL Trace 설정 및 실행
SQL> GRANT ALTER SESSION TO SCOTT;
Grant succeeded.
SQL> conn scott/oracle
Connected.
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL> conn / as sysdba
Connected.
SQL> R
1 SELECT s.sid, p.pid, p.spid, s.username, s.machine
2 FROM v$process p, v$session s
3* WHERE p.addr=s.paddr
SID PID SPID USERNAME MACHINE
---------- ---------- ------------ ---------- ----------
170 2 862 estenpark
169 3 864 estenpark
168 4 866 estenpark
167 5 868 estenpark
166 6 870 estenpark
165 7 872 estenpark
164 8 874 estenpark
163 9 876 estenpark
162 10 878 estenpark
161 11 880 estenpark
160 12 882 estenpark
SID PID SPID USERNAME MACHINE
---------- ---------- ------------ ---------- ----------
159 15 945 SCOTT estenpark
157 16 890 estenpark
156 17 892 estenpark
155 18 1095 SYS estenpark
152 19 896 estenpark
158 20 904 estenpark
143 21 1094 estenpark
145 22 906 estenpark
146 25 1012 estenpark
20 rows selected.
SQL> show parameter USER_DUMP_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /massdb/app/oracle/admin/MASSDB/udump
SQL> conn scott/oracle
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
[2011-04-05 20:38:33]-[oracle@estenpark:/massdb/app/oracle/admin/MASSDB/udump]
$ ls -al *945*
-rw-r----- 1 oracle oinstall 1287 Apr 5 18:43 massdb_ora_945.trc
[2011-04-05 20:38:40]-[oracle@estenpark:/massdb/app/oracle/admin/MASSDB/udump]
$ tkprof massdb_ora_945.trc storm2.txt
TKPROF: Release 10.2.0.2.0 - Production on Tue Apr 5 20:39:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
[2011-04-05 20:41:08]-[oracle@estenpark:/massdb/app/oracle/admin/MASSDB/udump]
$ cat storm2.txt
TKPROF: Release 10.2.0.2.0 - Production on Tue Apr 5 20:46:30 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: massdb_ora_945.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************
select *
from
dept
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=160 us)
********************************************************************************
SELECT *
FROM
DEPT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 8 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=42 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 16 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.00 0 16 0 8
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: massdb_ora_1158.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
43 lines in trace file.
21 elapsed seconds in trace file.
'Data Architecture > Tunning' 카테고리의 다른 글
| [Oracle] Not Null 제약 조건에 따른 인덱스 실행 계획 (0) | 2015.12.23 |
|---|---|
| [Oracle] Blocking Lock 발생과 해결 방법 (0) | 2015.12.03 |
| [Oracle] 데이터베이스 Call 최소화 원리 (0) | 2015.12.02 |
| [Oracle] Oracle Cardinality(오라클 카디날리티) (0) | 2015.12.02 |
| [Oracle] SQL 실행 계획 확인 방법 1편 (Autotrace) (0) | 2011.03.31 |
'Data Architecture/Tunning' Related Articles
more
Comments