[Oracle] oracle statspack 설치

Oracle Statspack은 Oracle Database에 대한 resource 사용량분석 성능문제 분석을 위해 사용되는 툴이다. 
awrrpt를 사용하는것이 편리하나 awr의 경우 Oracle Enterprise Edition을 사용하더라도 Diagnostic Pack이 포함되어 있지 않으면 원칙적으로는 사용이 불가하다.

 

따라서 Diagnostick Pack을 구매하지 않은 경우 Awrrpt 기능응 사용할 수 없다.

반면에 statspack은 무료로 사용이 가능하다.

설치방법 및 사용방법에 대해 알아보습니다.

우선 Statspack을 구성하는 script를 확인해보겠습니다.

1. Statspack을 구성하는 script

- Spcreate.sql  : statspack 설치 script
- Spreprot.sql  : statspack reporting script
- Spdoc.txt  : 영문 사용자 메뉴얼
- Sppurge.sql  : delete statspack script
- spdrop.sql  : drop statspack script
- spupYYY.sql  : statspack upgrade script
- spuexp.par  : statspack user export file

 

2. 설치

$ORACLE_HOME/rdbms/admin/spcreate.sql 로 statspack을 설치한다.

 

 

perfstat user의 password를 설정하지 않으면 설치를 진행할 수 없다. 라는 메시지와 함께 password를 지정해 주어야 한다.

 

아래 목록들은 on line tablespace의 목록들이며 perfstat 사용자의 tablespace를 지정합니다. 

주의할 점은 system tablespace를 지정하면 실패한다고 합니다.
online tablesapce 목록 중에서 하나를 선택합니다.

 

아래 목록은 temp tablespace목록이며 perfstat사용자의 temp tablespace를 지정해야 합니다.

 

SELECT *
FROM dba_users
WHERE username = 'PERFSTAT';

 

위 쿼리를 사용해 PERFSTAT계정이 생성되었음을 확인 할 수 있다.

 

 

3. Statpack을 이용한 Data Gathering (데이터 수집)

우선 통계를 수집하기 전에 TIMED_STATISTICS 파라미터가 TRUE로 설정되어 있는지 확인하고 FALSE라면 TRUE로 바꾸는 작업을 먼저 해준다.

 

 

 

3-1. Manual Data Gathering 

 * statspack snapshot은 perfstats user로 statspack.snap을 수행하면 얻을 수 있다.
 * statspack report는 2개의 snapshot을 이용하여 얻어지므로 report를 뽑으려면 최소 2개 이상의 snapshot이 존재해야 한다. 그리고 그 snapshot사이에는 shutdown 작업이 없어야한다.

 

위 사진처럼 현재는 snapshot 데이터가 하나도 없는데

 

 

를 통해 수동으로 statspack.snap을 수행시킨다. 꼭 perfstats 사용자로 실행할 것.

 

위 사진 처럼 snapshot을 데이터가 하나 생성됨을 확인 할 수 있다.

 

 

3-2. Automatic Data Gathering

 * 주기적으로 data를 수집해랴 할 경우 자동으로 할 수 있다.
 * DBMS_JOB, JOB, cron등을 사용한다.
 * $ORACLE_HOME/rdbms/admin/spauto.sql을 수행하여 database job을 쉽게 등록할 수 있다. 이 script는 반드시 perfstat user로 수행을 해야한다. job interval은 1시간으로 되어있다.

 

 

perfstat로 spauto.sql을 실행했다가는 위 와 같이
ORA-27486 : 권한이 불충분합니다. 라는 error를 맞게된다.

아래처럼 create job 권한을 부여하면 해당 error를 해결 할 수 있다.

GRANT CREATE job TO perfstat;

 

 

job 번호가 2번으로 등록되는 모습을 확인 할 수 있다.

 

interval은 처음부터 trunc(SYSDATE+1/24,'HH') 즉 1시간 마다 실행되도록 정해져있다. 본인이 정하고싶은대로 interval을 수정할 수 있다.

 

 

EXEC dbms_job.interval(2, 'trunc(sysdate+ 10/1440, ''MI'')');
EXEC DBMS_JOB.RUN(2);

위 쿼리를 통해서 10분에 한번씩 snapshot을 저장하도록 interval을 수정할 수 있다.

 

3-3. snapshot level 설정

 * LEVEL 0  : 기본적인 데이터베이스 통계만을 수집 [cpu사용량, I/O통계, 버퍼캐시 사용량 등]

 * LEVEL 5 : LEVEL0에 포함된 모든정보를 수집하고, 추가로 일부 SQL통계를 포함한다 (이 레벨은 일반적인 성능 모니터링에 적합하며, 가장 일반적으로 사용 되는 레벨이다)

 * LEVEL 6 : LEVEL5에 포함된 모든정보를 수집하고, 더 상세한 SQL통계정보를 수집한다. (이 레벨은 각 SQL문장의 성능에 대한 더 많은 정보를 제공하므로 복잡한 성능문제를 분석할때 사용한다)

 * LEVEL 7 : LEVEL6에 포함된 모든정보를 수집하고, 세그먼트 통계정보를 수집한다 개별세그먼트(테이블,인덱스)에서 발생하는 I/O활동과 같은 세부 정보를 제공한다.

 * LEVEL 10 : LEVEL7에 포함된 모든정보를 수집하고, 레치, 뮤텍스와 같은 낮은 레벨의 데이터베이스 동기화 메커니즘에 대한 상세정보를 포함한다.

 - LEVEL이 높을 수록 많은 자원을 필요오 하게 되며 특히 LEVEL10의 경우 반드시 필요한 경우에만 사용하여야 한다.

 

 

레벨을 변경할 때는 아래 포멧을 사용해서 변경할 수 있다.

EXEC statspack.snap(i_snap_level => [변경할 레벨]);

 

스냅샷 레벨의 디폴트값을 변경하는 방법은 아래의 포멧을 사용할 수 있다.

EXEC statspack.modify_statspack_parameter(i_snap_level => [변경할 레벨]);

 

 

스냅샷 레벨의 디폴트 값은 아래 쿼리로 조회가 가능하나. 현재 스냅샷 레벨을 확인하는 방법은 없습니다. 현재 스냅샷 레벨이 궁금하면 생성된 스냅샷의 데이터를 분석하여 어떤 유형의 데이터가 수집되었는지 확인하여야 합니다.

/*스냅샷 레벨 디폴트값 조회*/
SELECT
snap_level
FROM stats$statspack_parameter;

 

 

4. statspack report작성

 * statspack report는 2개의 statspack snapshot을 이용하여 작성된다.
 * 이 snapshot은 동일한 db에서 얻어진 것이어야 하며 선택된 snapshot사이에는 database의 shutdown이 없어야 한다.

 

Spreprot.sql을 사용하여 report를 뽑는다.

SQL> @spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1673219058 ORCL                1 orcl



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1673219058        1 ORCL         orcl         DESKTOP-A10P
                                               F5M

Using 1673219058 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 24 1월  2024 10:4     5
                                    4
                                  2 24 1월  2024 12:2     5
                                    2
                                  3 24 1월  2024 13:0     5
                                    0
                                  4 24 1월  2024 13:1     5
                                    1
                                  5 24 1월  2024 13:2     5
                                    2
                                  6 24 1월  2024 13:2     5
                                    3
                                  7 24 1월  2024 14:3     5
                                    8
                                  8 24 1월  2024 14:4     5
                                    9
                                  9 24 1월  2024 14:5     0
                                    2



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snap의 값을 입력하십시오: 1
Begin Snapshot Id specified: 1

end_snap의 값을 입력하십시오: 9
End   Snapshot Id specified: 9



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_9.  To use this name,
press <return> to continue, otherwise enter an alternative.

report_name의 값을 입력하십시오: C:\statspack_test_report.txt

결과가 끝나면 C 드라이브에 statspack_test_report.txt 파일이 생성됩니다.

 

 

5. 불필요 snapshot 삭제

report가 끝났다면 snapshot을 삭제하는게 좋다. 정기적으로 snapshot을 수집하는 경우에 snapshot이 남을 텐데 이미 reporting을 한 후면 사용을 할 일이 없기 때문이다.

sppurge.sql을 사용하여 삭제 할 수 있다.

 

SQL> @sppurge.sql


Database Instance currently connected to
========================================
                               Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 1673219058 ORCL              1 orcl


Snapshots for this database instance
====================================
                              Base-  Snap
 Snap Id   Snapshot Started    line? Level Host
-------- --------------------- ----- ----- ---------------
Comment
--------------------
       1  24 1월  2024 10:44:0           5 DESKTOP-A10PF5M
         1

       2  24 1월  2024 12:22:3           5 DESKTOP-A10PF5M
         0

       3  24 1월  2024 13:00:2           5 DESKTOP-A10PF5M
         5

       4  24 1월  2024 13:11:2           5 DESKTOP-A10PF5M
         8

       5  24 1월  2024 13:22:0           5 DESKTOP-A10PF5M
         3

       6  24 1월  2024 13:23:2           5 DESKTOP-A10PF5M
         2

       7  24 1월  2024 14:38:5           5 DESKTOP-A10PF5M
         4

       8  24 1월  2024 14:49:4           5 DESKTOP-A10PF5M
         1

       9  24 1월  2024 14:52:5           0 DESKTOP-A10PF5M
         2



Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
losnapid의 값을 입력하십시오: 1
Using 1 for lower bound.

hisnapid의 값을 입력하십시오: 7
Using 7 for upper bound.

Deleting snapshots 1 - 7.
Number of Snapshots purged: 7
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.

 

이렇게 하면 1~7 까지의 snapshot id를 삭제한다는 뜻이된다.

 

출처 : gurubee.net/lecture/1915