Oracle Database TIP
Statspack 사용방법 13 1 99,999+

by 구루비 STATSPACK [2006.06.18]


1. Statspack

1.1 Statspack 이란?

  - Staspack은 Oracle Database에 대한 부하 및 resource 사용량의 trend 분석이나 성능 문제 분석을 위하여 사용되는 tool로 과거 UTLBSTAT/UTLESTAT이 제공하던 기능을 수정 보완하여 oracle 8.1.6 부터 제공되고 있는 tool이다.

  - UTLBSTAT/UTLESTAT 튜닝 스크립트 보다 향상된 기능을 제공하며, 성능관련 통계정보들이 PERFSTAT USER에 누적되어 저장되므로 원하는 기간별로 비교 분석이 가능 하다.

  - DBMS_JOB이나 OS Utility (예를 들면 cron)등을 사용하여 주기적으로 Data의 수집 할 수 있다.

1.2 Statspack의 특징

  - Statspack은 Oracle DB내에서 특정 시간대의 데이터베이스에 대한 성능과 관련 데이터를 수집하여 database에 저장하여 두고 이로 부터 성능 분석 report를 생성해 내는 script들로 구성이 되어 있다.

  - 한 시점의 성능 data들은 snapshot이라고 하며, Statspack report는 두 시점의 snapshots들로부터 얻어 진다.

1.3 Statspack 을 구성하는 script들
  • - Spcreate.sql : statspack 설치 script
  • - Spreport.sql : statspack reporting script
  • - Spdoc.txt : 영문 사용자 메뉴얼
  • - Sppurge.sql : delete statspack data script
  • - Spdrop.sql : drop statspack script
  • - spupYYY.sql : statspack upgrade script
  • - spuexp.par : statspack user export file

2. Statspack 설치

  - Statspack 설치는 sys user에서 statspack 설치 script를 수행하여 이루어진다.

  - Statspack을 설치하기 위해서는 PERFSTAT USER의 패스워드 그리고 USER가 사용하는 DEFAULT TABLESPACE, TEMPORARY TABLESPACE를 지정해야 한다.

  - PERFSTAT 계정의 DEFAULT TABLESPACE 또는 TEMPORARY TABLESPACE로 SYSTEM 테이블스페이스를 지정하게 되면 설치가 실패한다.

  - ORACLE_HOME/rdbms/admin/spcreate.sql 스크립트를 실행시켜서 설치 한다.

  - Statspack을 설치하면 PERFSTAT라는 사용자를 생성되고, 이 사용자에 여러 개의 STAT$ 시리즈 테이블을 생성하고, 성능분석데이터는 이 테이블들에 저장 된다.

 
-- Oracle 8.1.6
SQL> connect / as sysdba
SQL> @ORACLE_HOME/rdbms/admin/statscre.sql
 
-- Oracle 8.1.7 이상 버전
SQL> connect /as sysdba
SQL> @ORACLE_HOME/rdbms/admin/spcreate.sql
 
PERFSTAT_PASSWORD의 값을 입력하십시오:
DEFAULT_TABLESPACE의 값을 입력하십시오:
TEMPORARY_TABLESPACE의 값을 입력하십시오:
    

3. StatPack을 이용한 Data Gathering

  통계를 수집하기 전에 TIMED_STATISTICS 데이터베이스 초기 파라미터 를 TRUE로 설정해야 한다.

3.1 Manual Data Gathering

  - Statspack snapshot을 얻으려면 아주 간단히 perfstat user로 statspack.snap을 수행시키면 된다.

  - Statspack report는 2개의 snapshot 을 이용하여 얻어지기 때문에 사전에 적어도 2개 이상의 snapshot들이 수집되어 있어야 하며 그 사이에 shutdown 작업이 없어야 한다.

 
SQL> CONN PERFSTAT/PERFSTAT
SQL> EXEC STATSPACK.SNAP

-- 스냅샷 데이터 확인
SQL> SELECT snap_id, snap_time FROM STATS$SNAPSHOT;
    

3.2 Automatic Data Gathering

  - 주기적으로 수집을 해야 필요가 있는 경우 일정한 간격으로 자동으로 수집이 되도록 자동화할 수 있다.

  - 이를 위하여 DBMS_JOB을 사용하거나 OS tool인 os 차원에서 cron과 같은 utility를 사용 한다.

  - Database job을 사용할 경우 instance parameter인 job_queue_process가 1 보다 큰 값으로 설정이 되어야 한다.

  - $ORALCE_HOME/rdbms/admin/spauto.sql을 수행하여 database job을 등록할 수 있다. 이 script는 반드시 statspack owner인 PERFSTAT user로 수행을 하며, job interval은 1시간으로 되어 있다.

 
-- sys권한으로 접속
SQL> conn sys/pwd as sysdba
 
SQL> ALTER SYSTEM SET TIMED_STATISTICS=TRUE;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES =1;

-- perfstat 유저로 접속.
SQL> CONNECT PERFSTAT/PERFSTAT 

-- 정기적으로 스냅샷샷을 실행하여 정보를 취득 할 수 있도록 한다.
SQL> start C:\oracle/rdbms/admin/spauto.sql 
    

3.3 스냅샷 레벨(Snapshot Level)
  • - LEVEL0:일반적인 성능 통계 정보를 수집한다.
  • - LEVEL1:(R9.2.0~)
  • - LEVEL5:default 값으로 일반적인 통계정보에 추가하여 resource를 많이 사용하는 SQL에 대한 정보를 포함한다.
  • - LEVEL6:LEVEL5+ SQL상세 실행계획 정보를 포함한다.(R9.0.1~)
  • - LEVEL7:LEVEL6+ 세그먼트 정보를 포함한다.(R9.2.0~)
  • - LEVEL10:LEVEL7+ 부모 Latch,자식 Latch 정보등을 포함한다.
  • - Level이 높을 수록 많은 resource를 필요로 하게 되며 특히 level 10의 경우 반드시 필요한 경우에만 사용해야 한다.
 
-- 스냅샷 레벨의 설정 
SQL> EXEC STATSPACK.SNAP(i_snap_level =>10);
 
-- 스냅샷 레벨의 디폴트값 변경 
SQL> EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level => 0)
     

4. StatsPack Report

4.1 StatsPack Report 작성

  - Statspack report는 2개의 Statspack snapshot들을 이용하여 얻어진다.

  - 이 snapshot 들은 동일한 database에서 얻어진 것이어야 하며 snapshot time 사이에 database server가 re-start되지 않아야 한다.

  - PERFSTAT USER에서 Statspack Report 생성 Script을 수행하고 beginning snapshot id 그리고 ending snapshot id 이렇게 2개의 snapshot ID와 report name를 입력하면 된다.

  - report script들은 아래와 같다.
    .Oraclce 8.1.6 : ORACLE_HOME/rdbms/admin/statsrep.sql
    .Oracle 8.1.7이상 : ORACLE_HOME/rdbms/admin/spreport.sql

 
-- 리포트 생성 예제
SQL> CONN PERFSTAT/PERFSTAT
SQL> @C:\oracle/rdbms/admin/spreport.sql
Enter value for begin_snap: 1
End Snapshot Id specified: 12
report_name : C:\report.txt
 
-- 지정 해야하는 스냅샷ID는 STATS$SNAPSHOT 테이블을 참조해 확인가능 하다.
SQL> SELECT snap_id, snap_time FROM STATS$SNAPSHOT;
     

4.2 리포팅후의 불필요한 데이터의 삭제

  불필요한 데이터는 ORACLE_HOME/rdbms/admin/sppurge.sql 스크립트를 실행시켜 삭제 할 수 있다.

 
SQL> CONNECT PERFSTAT/PERFSTAT

-- snap_id가 1부터 21까지 삭제하는 예제이다.
SQL> @C:\oracle/rdbms/admin/sppurge.sql
losnapid의 값을 입력하십시오: 1
hisnapid의 값을 입력하십시오: 21    
     

4.3 모든 데이터의 삭제(truncate)

  ORACLE_HOME/rdbms/admin/sptrunc.sql 스크립트로 모든 스냅샷 데이터를 삭제 할 수 있다.

 
SQL> CONNECT PERFSTAT/PERFSTAT
SQL> @C:\oracle/rdbms/admin/sptrunc.sql 
     

참고링크

- 강좌 URL : http://www.gurubee.net/lecture/1915

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 손님 [2013.10.01 07:51:04]
오라클 입문 중인데 책에서 부족한 부분을 많이 배우고 급니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입