Performance Tuning Tools (성능 튜닝 툴)

Performance Tuning Tools (성능 튜닝 툴)

 

이번장에서는 튜닝을 위한 Performance Tuning Tools을 이용한 tuning 지점을 찾아내는 방법에 대해 알아보겠습니다. 

 

우리는 누적통계(v$), Metrics, sampled 통계 형태로 통계정보를 확인할 수 있습니다.

 

Tuning Tool에는 다음과 같은 것들이 있습니다.

기본 툴 : EM(enterprise manager page)
             Alert log
             Trace files
             Dynamic performance views and tables

Add-in : statspack
Option : Diagnostics Pack
            Tuning Pack

 

본 문서에서는 위에선 언급한 tool 중  기본툴을 중심으로 다루어 보겠습니다.

 

 

1. EM (Enterprise manager page)

 

이미 수많은 실습을 통해 확인했던 것처럼

EM을 통해 우리는 많은 통계 정보를 확인해 볼 수 있습니다.

 

- EM은 Web brower를 통해 작동되며 접속하는 방법은 http://host name:port number/em

   URL을 사용해 가능합니다.

- 해당 EM의 포트번호는 $ORACLE_HOME/install/portlist.ini file을 통해 확인해 볼수 있습니다.

 

2. Alert Log

 

- 모든 database는 alert_<sid>.log 파일을 가지고 있으며 alert_log에는 다음과 같은 사항이 기록됩니다.

 

   1) startup시 사용된 모든 nondefault 초기화 파라미터 정보

   2) ORA-600 에러를 비롯한 block curruption, deadlock 등 오류 메세지 관련정보

   3) 관리자의 동작 : SQL을 통한 테이블스페이스 생성, 변경, Alter 명령을 통한 시스템 변경 등.

   4) 데이터베이스의 시작과 종료

   5) 백그라운드 프로세스의 시작과 종료

   6) LGWR가 기록한 로그 순차번호 및 로그 스위치 관련 정보.

 

    ... 등등.

 

    여기서 nondefault parameter 는  오라클의 default parameter 인 내장된 hidden parameter를 제외한 파라미터들을

    말합니다.

 

- 우리는 alert.log의 다음과 같은 정보를 통해 tuning에 필요한 정보를 얻을 수 있습니다.

 

  1) 불완전 체크포인트

  2) archiving 동작 시점

  3) instance 복구의 시작과 완료 시점

  4) 데드락(Dead lock), time out 에러 정보

  5) checkpoint의 시작과 끝 시점

      : alert.log에서 RBA(Redo byte address)를 확인하기 위해서는 LOG_CHECKPOINTS_TO_ALERT 파라미터를

        True로 설정해야 합니다.

 

실습1  : alert.log를 통한 Deadlock 관련 정보 확인하기
           
Deadlock 이 발생하여, 서로 lock이 걸려 이에따른 wait가 일어나고 있으며 이는 응답시간(DB time)이 길어지고 있는상황입니다. 이때, tunning 포인트는 해당 Deadlock 지점을 확인해 해당 세션을 끈어주는 것이 방법이 되겠습니다. 


1) Deadlock 발생

====  세션 1

SCOTT>update emp set sal=sal*1.2
  2  where empno=7788;

1 row updated.

==== 세션 2

SCOTT>update emp set sal=sal*1.2
  2  where empno=7654;

1 row updated.

SCOTT>select sal from emp
  2  where empno=7788;

       SAL
----------
      3500

SCOTT>update emp set sal=sal*1.2
  2  where empno=7788;

 wait 발생 세션 1이 해당 열에 lock(TX)를 걸었기 때문입니다.

====  세션 1

SCOTT>
SCOTT>update emp set sal=sal*1.2
  2  where empno=7654;
update emp set sal=sal*1.2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

deadlock이  발생했습니다.

          
2) alter log에서 해당 deadlock 정보 확인

  ..
  ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/orcl/udump /orcl_ora_18749.trc.


3) 해당 trace 파일을 통한  deadlock 정보 확인

DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update emp set sal=sal*1.2
where empno=7654
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00020015-000002d3        15     144     X             22     159           X
TX-0005002d-000002ea        22     159     X             15     144           X
session 144: DID 0001-000F-00000018     session 159: DID 0001-0016-00000074
session 159: DID 0001-0016-00000074     session 144: DID 0001-000F-00000018
Rows waited on:
Session 159: obj - rowid = 0000CDD7 - AAAM3XAAEAAAAOuAAH
  (dictionary objn - 52695, file - 4, block - 942, slot - 7)
Session 144: obj - rowid = 0000CDD7 - AAAM3XAAEAAAAOuAAE
  (dictionary objn - 52695, file - 4, block - 942, slot - 4)
Information on the OTHER waiting sessions:

SCOTT>
SCOTT>select dbms_rowid.rowid_object('AAAM3XAAEAAAAOuAAH') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAM3XAAEAAAAOUAAH')
---------------------------------------------
                                        52695

SYS>select owner, object_name, object_type
 2  from dba_objects
 3  where object_id=52695

OWNER           OBJECT_NAME                    OBJECT_TYPE
--------------- ------------------------------ ---------------
SCOTT           EMP                            TABLE


alter log에서 확인한 trace 파일을 열어 문제의 rowid를 확인하고 해당 지점을
파악할 수 있습니다.

 

 

3. User Trace Files

 

 - 사용자 추적파일(user trace file)은 서버 프로세스에 의해 기록되며 주로 사용자들의 SQL 문장을 수집하여

    SQL문장을 튜닝하고 하는 경우 사용된다. 

 - Show parameter user_dump를 통해 user trace file의 위치를 확인할 수 있습니다.

 - trace file에 식별을 위한 식별자 달기

   SQL>alert session set tracefile_identifier="minho'

 - 이 파일을 분석하여 DBA는 튜닝을 위한 진단을 할 수 있다.

 

 

실습2  : User trace file을 통한 사용자가 사용한 SQL 문장 정보 확인하기 (tkprof 사용하기)
           
User trace file사용해 사용자가 적적한 SQL문장을 사용하고 있는 확인 및 SQL을 분석해
튜닝해야할 지점을 예측해 볼 수 있다.

1) 세션 조회

SYS>select username, sid, serial#, status from v$session;

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
SYS                                   144         77 ACTIVE
SYS                                   158         15 INACTIVE
HR                                    159         82 INACTIVE


2) 해당 세션에 trace 파일 생성 설정

SYS>exec dbms_monitor.session_trace_enable (159, 82, waits=>TRUE, binds=>TRUE);

PL/SQL procedure successfully completed.

SYS>

3) 해당 세션이 작업진행

SQL> select * from employees;
SQL> select * from employees where employee_id=100;
SQL> select * from employees order by 1,2;
SQL> select * from job_history;
SQL> exit

등등..


4) tkprof를 통한 trace 파일 분석

[ocp@orcl : /u01/app/oracle/admin/orcl/udump]$ tkprof orcl_ora_19547.trc output.txt explain=hr/hr sys=no

TKPROF: Release 10.2.0.1.0 - Production on Mon Sep 21 16:26:05 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

[ocp@orcl : /u01/app/oracle/admin/orcl/udump]$ ls
orcl_ora_19547.trc  output.txt

output.txt 파일을 통해 사용자의 작업에 대한 추적이 가능하다.

 

 

4. Dynamic Performance Views

 

 - 인스턴스 내에서 이루어지는 변경사항을 실시간으로 조회 할 수 있는 View이다.

 - startup 이후로 부터 쌓아지 누적통계 정보이다.

 - 소유자는 sys임

 

 

5. 통계 levels

 

 - 통계 level에는 basic, typical, all 형태가 있다.

 

   1) basic 레벨 : 통계수집을 하지 않는다.

   2) typical 레벨 : default값으로 기본적인 사항의 통계수집이 이루어짐

   3) all 레벨 : typical 의 통계자료이외에 추가적인 통계수집이 이루어지며, SQL 진단기능을 제공한다.

 

 6. System Statistic Classes

 

 - 시스템 통계정보는 v$SESSTAT와 v$SYSSTAT 등의 뷰에서 확인할 수 있다

 - 이들 뷰에는 통계정보에 대한 class 구분이 되어져 있어서 문제가 발생하는 요소에 대한

    진단을 용이하게 하고 있다.

 - class 구분

    1, user

    2, redo

    4, enqueue

    8, cache

    16, os

    32, real application clusters

    64, sql

    128, debug

 

   만약 class 값이 72였다면 64+8을 의미하므로 sql과 cache에 관한 문제가 있음을 예측할 수 있다.

 

7. Wait event

 

 - wait 는 문제의 원인이 아닌 문제에 대한 징후, 현상이다.

 - 오라클 10g 에서는 약 870개의 wait 이벤트가 존재한다.

    : buffer busy wait, free buffer wait, library cache wait 등..

 - wait 또한 특성에 따라 class로 구분하여 볼수 있다.

    관련뷰 : V$SESSION_WAIT_CLASS, V$SYSTEM_WAIT_CLASS, V$SERVICE_WAIT_CLASS, V$EVENT_NAME

 

 - 세션 레벨 wait 이벤트는 다음 3가지로 view로 나누어 볼수 있다.

    1) V$SESSION_EVENT : 과거의 wait 정보를 확인

    2) V$SESSION_WAIT : 현재의 wait 정보를 확인

    3) V$SYSTEM_EVENT : 과거와 현재 모두(전체) wait 정보 확인

 

8. Time model

 

 - 튜닝의 목적은 DB time 사용자 관점으로 볼때 응답시간을 줄이는 것이 목표이다.

 - 튜닝지점은 가장 튜닝시 가장 impact가 큰 지점을 대상으로 한다.

 - SQL 튜닝이 보편적으로 가장 핵심적인 튜닝 요소이다.

ORA-60 Dead Lock Detected 에러가 발생하면 다음과 같이 데드록 그래프와 관련된 프로세스 및 세션 그리고 트랜잭션 정보 및 수행중인 SQL 문을 담고있는 Trace 파일을 생성시켜 주고 있다. 일반적으로 발생하는 로우레벨 록(row-level lock)에 의한 데드록은 이정보가 문제상황을 어느정도 설명하는데 부족함이 없으나, 로우레벨록이 아닌 다른 모드의 록이 개입될 경우에 대한 분석을 사례를 통해 알아보자.
결론적으로 이 문제는 Exclusive 모드의 TX 록과 ITL의 부족으로 인한 공유모드의 TX 록에 의한 데드록 상황이었다. 분석 과정에서 사용된 툴은 아래의 On Error Event Trace를 사용하였으며, 이 정보들을 분석하여 좀 더 명확히 당시 상황을 확인할 수 있었다.

 

주의 : System State Dump는 오라클 인스턴스를 구성하고 있는 모든 프로세스에 대한 Process State Dump를 기록하기 때문에 경우에 따라서는 매우 많은 디스크 용량 및 IO 작업을 필요로 할 수 있다.

다음은 ORA-60 에러 발생시 발생되는 Trace 정보이다(Real Application Cluster 환경에서는 LMD 프로세스의 Trace 파일에 데드록 그
래프에 대한 정보를 Global WFG에서 제공한다.)

 

*** 2005-02-18 02:54:25.153
*** SESSION ID:(7.7) 2005-02-18 02:54:25.134
DEADLOCK DETECTED
Current SQL statement for this session:
update tctab set name = null where id = 5342
The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020020-0000013f 8 7 X 11 11 X
TX-0008001c-00000126 11 11 X 8 7 S
session 7: DID 0001-0008-00000002 session 11: DID 0001-000B-00000002
session 11: DID 0001-000B-00000002 session 7: DID 0001-0008-00000002
Rows waited on:
Session 11: obj - rowid = 00007B9B - AAAHubAABAAARLiAF9
(dictionary objn - 31643, file - 1, block - 70370, slot - 381)
Session 7: obj - rowid = 00007B9B - AAAAAAAABAAARLjAAA
(dictionary objn - 31643, file - 1, block - 70371, slot - 0)
SQL statements executed by the waiting sessions:
Session 11:
update tctab set name = null where id = 4966

 

위의 내용으로 SID-7 세션과 SID-11 세션이 데드록 상태로 다음과 같은 상태였음을 알 수 있다.

• SID-11
- TX-0008001c-00000126를 X 모드에서 잡고 TX-00020020-0000013f를 X 모드에서 기다리고 있음.
- update tctab set name = null where id = 4966
- 위의 Update 문으로 변경되는 로우의 RowID가 ‘AAAHubAABAAARLiAF9’로 file /block/slot이 1/70370/381 로우를 업데이트하려고 기다리던 상태

• SID-7
- TX-00020020-0000013f를 X 모드로 잡고 있으며 TX-0008001c-00000126을 공유 모드에서 기다리고 있음.
- update tctab set name = null where id = 5342
- 위의 Update 문으로 변경되는 로우의 RowID가 ‘AAAAAAAABAAARLjAAA’로 file /block/slot이 1/70371/0 로우를 업데이트하려고 기다리던 상태

 

위 정보를 보면 SID-7이 공유모드로 TX 록을 기다리던 상태로 보아 일반적인 로우레벨록에 의한 데드록 문제가 아님을 알 수 있다.

TX 록을 공유모드로 기다리게 되는 경우는 다음과 같다.

• Unique or Primary Key Constraint
- Unique Constraint나 PK Constraint가 비활성화되어 있는 상태에서 동일한 키를 입력하는 세션들로 인하여 발생되는 경우.

 

Eg:
Ses#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
Ses#1: insert into tx_eg values (10,'New','MALE');
Ses#2: insert into tx_eg values (10,'OtherNew',null);

 

• ITL 슬롯 부족
- DML 수행중 테이블 또는 인덱스 록에 처음 변경을 하기 위해서는 우선 블록헤더에 트랜잭션 정보를 저장시킬 트랜잭션 엔트리(ITL 슬롯)를 확보한 후에 로우에 대한 변경 작업이 가능하다. 이때 이 블록에서 사용 가능한 ITL 슬롯들을 다른 트랜잭션이 사용중인 경우 공유 모드로 특정 TX가 종료되기를 기다리게된다.
• 동일한 비트맵 인덱스의 프래그먼트에서 관리되는 로우들에 대한 업데이트 작업 비트맵 인덱스의 동일한 프래그먼트 내에 아직 종료되지 않는 TX에 의하여 변경된 키가 존재하는 경우 후속TX는 공유 모드로 선행TX가 종료되기를 기다리게된다.
• Prepared State TX
- Prepare 상태의 TX를 지닌 블록의 경우 액세스(SELECT/DML)시 이 Prepared State TX를 공유 모드로 기다리게 된다(실제 이 사례의 경우 공유 모드로 기다리던 프로세스가 ORA-60이 발생되어 Errorstack Dump의 Stack Trace의 기능을 확인하여ITL 할당중에 있었음을 확인하였음.)

위의4가지 경우에 해당하는지는 당시 업데이트되던 테이블의 조건 및 인덱스종류등을확인하여ITL 부족에의한문제이거나Prepared State TX 에 의한 문제로 압축되었다.

다시 위 2가지 경우에 대한 추가 분석을 위하여 Systemstate Dump에서 SID-7이 작업 중이던 블록(1/70371)에 대한 덤프를 추가로 분석하였다.

 

다음은 블록(1/70371)에 대한 현재 상태의 버퍼 핸들 및 블록 덤프 내용이다.

 

BH (0xc000000072fcab00) file#: 1 rdba: 0x004112e3 (1/70371) class 1 ba: 0xc0000000728a0000
... <생략> ...
st: XCURRENT, md: NULL, rsop: 0x0000000000000000, tch: 3
... <생략> ...
Block header dump: 0x004112e3
Object id on Block? Y
seg/obj: 0x7b9b csc: 0x00.6b504 itc: 2 flg: - typ: 1 - DATA
^^
총ITL 슬롯개수
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0008.01c.00000126 uba: 0x0080007a.007b.01 ---- 1 fsc 0x0009.00000000
0x02 xid: 0x0006.019.000001be uba: 0x00800068.006f.03 ---- 1 fsc 0x0009.00000000
=> 2개의slot 모두active TX의의하여사용중.

 

data_block_dump
===============
tsiz: 0x1fa0
hsiz: 0x30e
pbl: 0xc0000000728a005c
bdba: 0x004112e3
flag=-------------
ntab=1
nrow=382
frre=-1
fsbo=0x30e
fseo=0x43d
avsp=0x1ba => 가용공간이442 byte
tosp=0x1cc
0xe:pti[0] nrow=382 offs=0
0x12:pri[0] offs=0x1f8e
0x14:pri[1] offs=0x1f7c

 

위 블록에서 사용 가능한 스페이스가442바이트로 비교적 여유가 있는 상태로 ITL 슬롯의 총 수를 제한하는 Maxtrans를 확인해 본 결과 Maxtrans 파라미터가 2로 설정되어있어 이에 의한 문제로 확인이 되었다. 사실이 문제는 ORA-60 에러에 대한 Errorstack Dump가 마침 공유모드로 TX를 기다리던 프로세스에 대하여 발생되어 이 Trace 파일의 Call Stack에서 분석 초기부터 ITL 슬롯의 할당 문제임이 확인되었었다.
그 이유는 이 프로세스가 ORA-60 에러를 만났을 때 수행 중이던 오라클 기능이 ITL 슬롯을 할당받는 과정이었던 상태임을 설명하고 있기 때문이다.

 

Call Stack Trace
• Ksqgel - get an enqueue
• ktcwit1 - transaction controlfile wait
• ktbgfi - find an itl entry

Reference by http://cafe.naver.com/metanetbts.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=491&