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을 통해 생성된 리포트의 분석방법
- 인스턴스의 정보와 생성된 스냅샷에 대한 정보를 보여준다.
- SGA에 대한 정보를 보여준다. Std block Size는 표준 블록 사이즈를 의미한다.
- 워크로드를 판단할 수 있는 정보를 알 수 있다.
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