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. 수집 주기는 최대 1시간으로 하라.
  • 2. 오래된 스냅샷들은 주기적으로 export 하라.
  • 3. 백업 후 오래된 스냅샷들을 지울 때는 sppurge, sptruncate를 적절히 사용하라.(공간확보)
  • 4. 성능 저하에 대한 분석시에는 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