[ORACLE] 오라클 시스템 분석을 위한 Statspack 설치 및 사용

Oracle Statspack은 Oracle Database에 대한 resource 사용량분석 성능문제 분석을 위해 사용되는 툴이다. 

awrrpt를 사용하는것이 편리하나 awr의 경우 Oracle Enterprise Edition을 사용하더라도 Diagnostic Pack이 포함되어 있지 않으면 원칙적으로는 사용이 불가하다.

 

따라서 Diagnostick Pack을 구매하지 않은 경우 Awrrpt 기능응 사용할 수 없으나 statspack은 무료로 사용이 가능하다.

 

1. Statpack을 구성하는 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. Install

$ORACLE_HOME\rdbms\admin 으로 이동 → sqlplus ‘/as sysdba’ connect → exec @spcreate.sql

 

(1) Create perfstat Account

USER_PROFILE에 DEFAULT값이 ORACLE 기본 패스워드 정책을 따른다면 more 15 letters, 1 digits, 1 special character, 1 upper case 등을 포함하여 패스워드를 작성해야 한다.

 

(2) Tablespace 지정

sql 파일 실행 시 나오는 online-tablespace 목록 중 default 값이 될 수 있는 테이블스페이스를 지정한다.

 

(3) Temp tablespace 지정

sql 파일 실행 시 나오는 temp tablespace 목록 중 dafault 값이 되는 테이블스페이스를 지정한다.

 

(4) 유저 확인

SELECT *
FROM dba_users
WHERE username = 'PERFSTAT';

 

3. Data Gathering

사전 점검) TIMED_STATISTICS 파라미터를 TRUE로 설정.

SELECT *
FROM V$PARAMETER
WHERE NAME = 'timed_statistics'

 

3-1) Manual Data Gathering

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

 

SELECT SNAP_ID, SNAP_TIME
FROM STATS$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 계정으로 실행할 경우 ORA-27486 : 권한이 불충분합니다. error 발생

 

sys 계정으로 create job 권한 부여 후 재 실행.

GRANT CREATE job TO perfstat;

 

 

JOB 조회

SELECT 	 JOB,
         LOG_USER,
         PRIV_USER,
         NEXT_DATE,
         BROKEN,
         INTERVAL,
         WHAT
FROM DBA_JOBS
WHERE LOG_USER = 'PERFSTAT';

 

기본 interval 값은 trunc(SYSDATE+1/24,'HH') 으로 되어 있다. 

(PERFSTAT 계정으로 실행) JOB 실행 후 +1일 뒤 실행.

SQL> EXEC DBMS_JOB.INTERVAL(21, 'TRUNC(SYSDATE+1+2/24)');
SQL> EXEC DBMS_JOB.RUN(21);

 

3-3. Snapshot level setting

• 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;

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

 

/* 레벨 변경 */
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를 뽑는다.

 

Report 예시

 

5. Delete Snapshot 

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

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

 

1~4 까지의 Snapshot 삭제.