1. Statspack의 개요

1.1. Statspack의 사용처

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

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 

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