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

1. Statspack의 개요
2. Statspack의 설치방법
1. 2.1. Perfstat 유저가 사용할 테이블스페이스 생성
2. 2.2. spcreate.sql 스크립트 실행
3. Statspack의 사용방법
4. Statspack 스냅샷의 삭제방법
1. 4.1. sppurge
2. 4.2. sptrunc
5. Statspack을 통해 생성된 리포트의 분석방법

1. Statspack의 개요

1.1. Statspack의 사용처

AWR이 제공되지 않는 버전(8.1.6 - )의 서버
Oracle Standard Edition을 사용하는 서버
Standard Edition에서는 tuning pack, diagnostic pack이 제공되지 않음에 따라 AWR을 사용할 수 없다.

그 이외의 경우에는 AWR(EM)을 사용하는 것이 좀 더 직관적이고 확인하기 쉽다.

1.2. Statspack이란?

Oracle Database에 대한 부하 및 resource 사용량의 trend 분석이나 성능 문제 분석을 위하여 사용되는 tool로 과거 utle|estat 이 제공하던 기능을 수정 보완하여 oracle 8.1.6부터 제공되고 있는 tool 이다.
UTLBSTAT/UTLESTAT 튜닝 스크립트 보다 향상된 기능을 제공하며, 성능관련 통계정보들이 PERFSTAT USER에 누적되어 저장되므로 원하는 기간별로 비교 분석이 가능하다
DBMS_JOB이나 OS Utility (예를 들면 cron)등을 사용하여 주기적으로 Data를 수집 할 수 있다.

1.3. Statspack의 특징

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

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

Statspack을 구성하는 스크립트들은 아래와 같다.

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

1.4. Statspack으로 구성된 테이블의 구조

2. Statspack의 설치방법

Statspack 설치는 sys user에서 statspack 설치 스크립트를 수행하여 이루어진다.
Statspack을 설치하면 PERFSTAT이라는 사용자가 생성되고 이 사용자에 여러 개의 STAT$ 테이블을 생성하고 성능분석데이터는 이 테이블에 저장된다.

2.1. Perfstat 유저가 사용할 테이블스페이스 생성


create tablespace statspack 
datafile '/data01/oradata/BLOG8DB/statspack01.dbf' 
size 2000m;

2.2. spcreate.sql 스크립트 실행

sys 계정으로 접속한다.

sqlplus "/as sysdba"

spcreate.sql 스크립트를 실행한다.

SQL> @?/rdbms/admin/spcreate.sql

perfstat 유저의 패스워드를 설정한다.

perfstat 유저가 사용할 테이블스페이스를 지정한다.

PERFSTAT 유저가 사용할 DEFAULT TABLESPACE 또는 TEMPORARY TABLESPACE로 SYSTEM 테이블스페이스를 지정하게 되면 설치가 실패한다.

perfstat 유저가 사용할 템프 테이블스페이스를 지정한다.

3. Statspack의 사용방법

통계를 수집하기 전에 TIMED_STATISTICS 패러미터가 TRUE로 설정되어 있어야 하며 보통 디폴트로 TRUE로 설정되어 있다.

3.1. 수동 스냅샷 수집

Statspack snapshot을 얻으려면 아주 간단히 perfstat user로 statspack.snap을 수행시키면 된다.
Statspack report는 2개의 snapshot 을 이용하여 얻어지기 때문에 사전에 적어도 2개 이상의 snapshot들이 수집되어 있어야 하며 그 사이에 shutdown 작업이 없어야 한다.

SQL>conn perfstat/perfstat execute statspack.snap;

생성된 스냅샷의 관한 정보는 아래의 쿼리로 확인한다.

SQL> select snap_id, snap_time from STATS$SNAPSHOP;

3.2. 자동 스냅샷 수집

주기적으로 수집을 해야 할 필요가 있는 경우 DBMS_JOB을 이용하여 일정한 간격으로 자동으로 수집이 되도록 자동화할 수 있다.
Job_queue_process가 1보다 큰 값으로 설정이 되어야 하며 보통 1보다 큰 값으로 설정되어 있다.
스냅샷은 디폴트로 1시간 간격으로 수집된다.
스냅샷 생성은 3초 이내로 완료되며 스냅샷 생성 시 15~20 정도의 cpu를 사용한다.(quard core cpu 2개 환경에서 레벨 10 으로 스냅샷 생성 시)


SQL>conn perfstat/perfstat
SQL> @?/rdbms/admin/spauto.sql

3.3. 스냅샷 생성 관련 패러미터 지정

스냅샷 레벨 관련 패러미터

그 밖의 패러미터

3.4. 리포트의 생성

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를 입력하면 된다.
리포팅 파일은 명령을 수행한 현 디렉토리에 생성된다.


SQL>conn perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

4. Statspack 스냅샷의 삭제방법

4.1. sppurge

지정된 스냅샷 범위만큼을 삭제한다.


SQL>conn perfstat/perfstat
SQL> @?/rdbms/admin/sppurge.sql

4.2. sptrunc

저장된 모든 스냅샷을 삭제한다.


SQL>conn perfstat/perfstat
SQL> @?/rdbms/admin/sptrunc.sql

Statspack 사용 가이드라인

수집 주기는 최대 1시간으로 하라.
오래된 스냅샷들은 주기적으로 export 하라.
백업 후 오래된 스냅샷들을 지울 때는 sppurge, sptruncate를 적절히 사용하라.(공간확보)
성능 저하에 대한 분석시에는 system peek time, batch 작업 시간을 걸치도록 스냅샷을 수집하라.

5. Statspack을 통해 생성된 리포트의 분석방법

5.1. Summanry Information

인스턴스의 정보와 생성된 스냅샷에 대한 정보를 보여준다.

5.2. Instance Workload Information

Instance cache Information

SGA에 대한 정보를 보여준다. Std block Size는 표준 블록 사이즈를 의미한다.

Load profile Information

워크로드를 판단할 수 있는 정보를 알 수 있다.

Instance Efficiency Ratios

어느 부분에 문제가 있는지를 판별할 수 있는 정보를 제공한다.

Top 5 Events

CPU Time

그 밖의 정보

Time Model System Stats
Foreground Wait Events
Background Wait Events
Both Wait Events
Wait Event Histogram

5.3. 자원을 많이 사용한 SQL문장

SQL ordered by CPU

SQL orered by Elapsed time

SQL orered by Gets

버퍼를 많이 사용한 문장 순으로 나타낸다.

SQL orered by Reads

Disk I/O가 많이 발생한 문장 순으로 나타낸다.

SQL orered by Execution

수행횟수가 많은 문장 순으로 나타낸다.

SQL orered by Parse Calls

SQL orered by Sharable memory

Library cache내 많은 메모리를 사용하고 있는 문장 순으로 나타낸다.

SQL orered by Version Count

Child cursor들을 많이 갖고 있는 문장 순으로 나타낸다.

5.4. 그 밖의 리포팅 목록

Instance Activity Stats
OS Statistics
IO Stat by Function
Tablespace IO Stats
File IO Stats
File Read Histogram Stats
Instance Recovery Stats
Memory Target Advice
Memory Dynamic Components
Buffer Pool Advisory
Buffer Pool Statistics
PGA Aggr Target Stats
PGA Cache Hit
PGA Aggr Target Histogram
PGA Memory Advisory
Process Memory Summary Stats
Top Process Memory (by component)
Latch Activity
Segments by Logical Reads
Segments by Physical Reads
Dictionary Cache Stats
Library Cache Activity
Propagation Receiver
Streams Apply
Buffered Queues
Buffered Queue Subscribers
Rule Sets
Streams Pool Advisory
Shared Pool Advisory
SGA Target Advisory
SGA Memory Summary
SGA breakdown difference
SQL Memory Statistics
init.ora Parameters