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

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)

- LEVEL０：일반적인 성능 통계 정보를 수집한다.
- LEVEL１：（R9.2.0～）
- LEVEL５：default 값으로 일반적인 통계정보에 추가하여 resource를 많이 사용하는 SQL에 대한 정보를 포함한다.
- LEVEL６：LEVEL５＋ SQL상세 실행계획 정보를 포함한다.（R9.0.1～）
- LEVEL７：LEVEL６＋ 세그먼트 정보를 포함한다.（R9.2.0～）
- LEVEL10：LEVEL７＋ 부모 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