꿈꾸는 개발자, DBA 커뮤니티 구루비

TPT

Download : http://tech.e2sn.com/oracle-scripts-and-tools
TPT 스크립트는 Tanel Poder 가 공개한 오라클 성능 트러블슈팅 라이브러리이다.
Tanel Poder Troubleshooting Scripts의 약자.
TPT 스크립트의 특징은 데이터베이스에 별도의 오브젝트를 만들지 않고, 순수 PL/SQL 블록만으로 구현되어 사용하기가 매우 편리하다.
가능한 V$뷰나 X$테이블만을 참조하도록 구현되어 성능면에서도 최적화 되어 있다.

예제

접속만해도 아래와같이 세션 정보등이 나옴


SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 11 18:04:26 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  BWD          r3dev                     586   12       10.2.0.4.0 20120511 1040800         13    1290836         0700000188BA37C8 07000001887FC5A0
-ne

1 . session Snapper

세션 프로파일링 기능을 제공
특정 세션의 SQL 문장, 대기이벤트 별 성능 정보가 일목요연하게 정리되어 출력이 된다.


SQL> @snapper ash 5 1 474 -- V$ACTIVE_SESSION_HISTORY로부터 데이터를 읽되, 5초 동안 1번의 스냅샷을 통해 474번 세션의 성능 정보 리포트
Sampling SID 474 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com)

---------------------------------------------------------------------------------
Active% | SQL_ID          | EVENT                               | WAIT_CLASS
---------------------------------------------------------------------------------
    52% | gy5xm0s9qsupc   | read by other session               | User I/O
    35% | gy5xm0s9qsupc   | db file sequential read             | User I/O
    13% | gy5xm0s9qsupc   | ON CPU                              | ON CPU
--  End of ASH snap 1, end=2012-05-11 18:18:24, seconds=5, samples_taken=46

PL/SQL procedure successfully completed.

2. ASH, ASH1, ASH2 등의 파라미터를 이용해 여러관점에서 액티브 세션 히스토리 정보를 프로파일링 할 수 있다.

세션 아이디에 ALL을 부여함으로써 모든 세션에 대해 프로파일링을 수행 할 수도 있다.



SQL>@snapperash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all

Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com)

------------------------------------------------------------------------
Active% |    SID | EVENT                               | WAIT_CLASS
------------------------------------------------------------------------
   100% |    221 | db file sequential read             | User I/O
   100% |    250 | db file sequential read             | User I/O
    98% |     94 | db file sequential read             | User I/O
    98% |    472 | db file sequential read             | User I/O
    95% |    372 | db file sequential read             | User I/O
    90% |    231 | db file sequential read             | User I/O
    68% |    392 | db file sequential read             | User I/O
    63% |    398 | db file sequential read             | User I/O
    18% |    256 | db file sequential read             | User I/O
    15% |    321 | db file sequential read             | User I/O
---------------------------------------------------
Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
---------------------------------------------------
   268% |            |            | 1n0qp2ppx4xb6
   100% |            |            | 4r2z4grh7z216
   100% |            |            | 9bkwsu6h2h054
    65% |            |            | 1ajy753y74q4r
    63% |            |            | 78dx9cvwzk9nq
    25% |            |            | 1rtxjds0zcugz
    23% |            |            | bzwp1br33tuhn
    18% |            |            | 57gb6v1btkf06
    15% |            |            | 376qq9b4g5jtk
    15% |            |            | 4jgqmk2fs974h
-------------------------------------------------------------------------------------------
Active% | PROGRAM                   | MODULE                    | ACTION
-------------------------------------------------------------------------------------------
   168% | dw.sapPRD_D00@r3prdap2 (T | SAPLZSD_CLOSING_TUTOR_N   | 1608
   100% | dw.sapPRD_D00@r3prdap1 (T | SAPLXRSA                  | 7530
   100% | dw.sapPRD_D00@r3prdap1 (T | ZSDC0506_DAILY            | 512
   100% | dw.sapPRD_D00@r3prdap3 (T | SAPLZSD_CLOSING_TUTOR_N   | 1608
    98% | dw.sapPRD_D00@r3prdap3 (T | SAPLZFI_DUTY_AMOUNT       | 638
    95% | dw.sapPRD_D00@r3prdap2 (T | ZSDSINFO                  | 2917
    63% | dw.sapPRD_D00@r3prdap3 (T | SAPLXRSA                  | 7602
    20% | dw.sapPRD_D00@r3prdap1 (T | RSM13000                  | 11698
    20% | dw.sapPRD_D00@r3prdap2 (T | SAPMV45A                  | 31173
    13% | dw.sapPRD_D00@r3prdap3 (T | ZSDA0012                  | 2000
--  End of ASH snap 1, end=2012-05-11 18:31:48, seconds=5, samples_taken=40

PL/SQL procedure successfully completed

TINCLUDE, SINCLUDE 파라미터를 이용하면 타임모델(V$SESS_TIME_MODEL 뷰)과 통계정보(V$SESSTAT뷰)에 대한 프로파일링 결과도 볼 수 있다.
Latch Profiler
Latch Profiler는 래치프로파일링기법[3장 대기 이벤트 분석]을 구현한 것.
PL/SQL블록으로 되어 있어 간편하게 사용가능

예제)



SQL> @latchprof name % % 100000

-- LatchProf 2.00 by Tanel Poder ( http://www.tanelpoder.com )

NAME                                      Held       Gets  Held %     Held ms Avg hold ms
----------------------------------- ---------- ---------- ------- ----------- -----------
cache buffers chains                       311        287     .31       9.112        .032
simulator lru latch                          7          7     .01        .205        .029
cache buffers lru chain                      6          6     .01        .176        .029
row cache objects                            5          5     .01        .147        .029
SQL memory manager latch                     4          1     .00        .117        .117
undo global data                             2          2     .00        .059        .029
enqueue hash chains                          1          1     .00        .029        .029
active checkpoint queue latch                1          1     .00        .029        .029

8 rows selected.



-- _Parameter 1 specifies which columns from V$LATCHHOLDER to report and group by. In the case below I just want to report latch holds by latch name (and not even break it down by SID for starters)_


-- _Parameter 2 specifies which SIDs to monitor. In the case below, I am interested in any SID which holds a latch (%)_


-- _Parameter 3 specifies which latches to monitor. This can be set either to latch name or latch address in memory. In the case below, I monitor all latches (%)_


-- _Parameter 4 specifies how many times to sample V$LATCHHOLDER. I use 100000 samples below, which completed in a couple of seconds on my test database. The sampling speed depends on your server CPU/memory bus speed and the value of processes parameter. You should start from lower number like 1000 and adjust it so that LatchProf would complete its sampling in a couple of seconds, and that is usually enough for diagnosing ongoing latch contention problems. You shouldn't keep sampling for long periods since LatchProf runs constantly on the CPU_

-- _Name -Latch name_

-- _Held - During how many samples out of total samples (100000) the particular latch was held by somebody_

-- _Gets -How many latch gets against that latch were detected during LatchProf sampling_

-- _Held % -How much % of time was the latch held by somebody during the sampling. This is the main column you want to be looking at in order to see who/what holds the latch the most (the latchprof output is reverse-ordered by that column)_

-- _Held ms -How many milliseconds in total was the latch held during the sampling_

-- _Avg hold ms -Average latch hold time in milliseconds (normally latches are held from a few to few hundred microseconds_

데이터베이스에 어떤 오브젝트도 만들지 않고 SQL*Plus 와 PL/SQL 블록만으로 이정도의 기능을 구현한 것은 정말대단.
또한 세션의 성능 문제를 파악하는데 있어 V$SESSION 뷰나 V$ACTIVE_SESSION_HISTORY 뷰가 얼마나 다양한 정보를 제공하는지도 알 수 있다.