요즘에는 awr을 더 많이 쓰죠~


STATSPACK 수행방법 ( STATSPACK SNAPSHOT 수집 )
==============================================

PURPOSE
-------
이 자료는 oracle 8.1.6부터 새로이 제공되는 성능 진단도구인 statspack의
수행 방법에 대해 기술한다.


Explanation
-----------

1. StatsPack snapshot 수집

snapshot 수집을 위한 가장 단순한 방법은 SQL*Plus에 PERFSTAT 사용자로
login한 후, statspack.snap 프로시져를 수행시키는 것이다.

예.
SQL> connect perfstat/perfstat
SQL> execute statspack.snap;


참고사항 : OPS 환경에서는 데이터를 수집하고자 하는 대상 instance에
접속하여 수행하여야 한다. snapshot은 모든 인스턴스에서 수집되어야만
비교를 해 볼 수 있다. 하나의 인스턴스에서 수집된 snapshot 은 동일한
인스턴스에서 수집된 또 다른 snapshot과 비교가 된다.

성능 분석을 용이하게 하기 위해 initSID.ora 파라미터 파일에서
timed_statistics 값을 true로 지정하는 것이 좋다. timed_statistics
파라미터는 alter system 명령으로, 동적으로 변경 가능한 값이다. 시간
정보는 그 자체로도 중요할 뿐더러 Oracle 기술 지원에 성능관련 문제 분석
을 요청할 때에도 필요하다.

statspack.snap 및 statspack.modify_statspack_parameter 프로시져에는
필요한 파라미터를 지정할 수 있다.

=============================================================================
Parameter Name Valid Values Value Meaning
=============================================================================
i_snap_level 0, 5, 10 5 Snapshot 레벨
------------------------------------------------------------------------------
i_ucomment Text Blank Snapshot과 함께 저장할 설명문구
------------------------------------------------------------------------------
i_executions_th Integer >=0 100 SQL 관련 임계치: 실행된 횟수
------------------------------------------------------------------------------
i_disk_reads_th Integer >=0 1,000 SQL 관련 임계치: 디스크 READ 횟수
------------------------------------------------------------------------------
i_parse_calls_th Integer >=0 1,000 SQL 관련 임계치: parse call 횟수
------------------------------------------------------------------------------
i_buffer_gets_th Integer >=0 10,000 SQL 관련 임계치: buffer get 횟수
------------------------------------------------------------------------------
i_session_id Valid sid 0 (no 세션단위의 성능 정보를 수집하기
from session) 위한 대상 session id
v$session
------------------------------------------------------------------------------
i_modify_parameter True, False False 파라미터를 앞으로의 snapshot에서도
계속 사용하기 위해 저장할 지 여부
=============================================================================


2. 수집되는 데이터의 양을 지정하는 방법

1) Snapshot 레벨

패키지에 의해 수집되는 데이터 양은 snapshot 레벨에 따라 조정된다.
다시 말해 지정된 레벨은 수집될 데이터의 양을 결정한다.

Level >= 0 일반적인 성능 통계
수집되는 통계 정보:
0 이상의 모든 레벨에서는 일반적인 성능 관련 통계 정보가
수집된다. 여기에는 wait statistics, system event,
system statistics, rollback segment 데이터, row cache, SGA,
background event, session event, lock statistics,
buffer pool statistics, parent latch statistics 등이 포함된다.

Level >= 5 추가적인 데이터 : SQL 문자
이 레벨에서는 하위 레벨에서 수집되는 모든 데이터 이외에도
추가적으로, 자원을 많이 사용하는 SQL 문장과 관련된 성능
정보를 수집한다.

SQL 관련 '임계치'
statspack에 의해 수집되는 SQL 문장은 미리 지정된 다음
조건 중 하나라도 넘을 경우 수집된다.
- SQL 실행 횟수 (기본값 100 )
- SQL 처리시 발생하는 disk read (기본값 1,000 )
- SQL parse call 횟수 (기본값 1,000 )
- SQL 처리시 발생하는 buffer gets (기본값 10,000)

위에 나열된 각각의 임계치는 어떤 SQL 관련 정보를 수집할
것인지를 결정하는 데 사용된다. 위에 언급된 임계치 가운데
하나라도 넘는 SQL 문장과 관련된 정보가 snapshot을 처리
하는 동안 수집된다.

SQL 임계치 레벨 각각은 stats$statspack_parameter 테이블에
저장되어 있거나, snapshot 수집 당시 파라미터에 의해 지정
가능하다.


2) snapshot SQL 임계치

레벨 이외에도 추가적으로 구성 가능한 파라미터가 있다. 이 파라미터들은
SQL 수집 시 사용되는 임계치들로, 임계치를 넘어서는 모든 SQL 문장들이
수집된다.

snapshot level 및 패키지에 의해 사용되는 임계치 관련 정보는
stats$statspack_parameter 테이블에 저장된다.


3) snapshot level과 SQL 관련 임계치 값을 변경하는 방법

snapshot 수집 작업 시 사용되는 기본 파라미터 값은 조정 가능하며,
이 값을 조정함으로써 인스턴스에 미치는 부하를 줄이면서, 원하는
수준의 정보를 수집할 수 있다.

* snapshot을 받아내변서, 새로운 값을 데이터베이스에 저장
( statspack.snap 호출 시, i_modify_parameter 변수 값을 지정 )

SQL> execute statspack.snap -
(i_snap_level=>10, i_modify_parameter=>'true');

i_modify_parameter 를 true로 지정하면, 이 값이 stats$statspack_parameter
테이블에 저장되며; 이 값이 이후 실행되는 snapshot에 사용된다.
i_modify_parameter 값을 false로 지정하거나 생략하면, 해당
snapshot 작업에만 지정된 정보가 사용되며, 이후 작업에는
stats$statspack_parameter 에 지정된 값을 사용하여 작업이 수행된다.

* snapshot 수행을 하지 않고 기본값을 바꾸기 위해서는
statspack.modify_statspack_parameter 프로시져를 사용한다.
예를 들어 snapshot level을 10으로 변경하고, SQL 관련 임계치 가운데
buffer_gets, disk_reads 값을 변경하고자 할 때 다음과 같이 한다.

SQL> execute statspack.modify_statspack_parameter -
(i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);

이 프로시져를 수행시키면, snapshot 수행 없이, 변경 사항이
계속해서 남게 된다.

modify_statspack_parameter 프로시져에 지정 가능한 전체 파라미터의
전체 목록은 snap 프로스져와 동일하다.


4) Session ID 지정

특정 세션과 관련된 정보를 수집하고자 할 경우, StatsPack 호출 시
세션 id 지정이 가능하다. 세션과 관련해서 수집되는 정보에는
세션 통계, 세션 이벤트, lock activity 등이 있다. 기본적으로는
세션 레벨의 통계는 수집하지 않게 되어 있다.

SQL> execute statspack.snap(i_session_id=>3);


3. StatsPack snapshot 수집의 자동화

일중, 주간, 년간 정보를 비교하기 위해서는, 일정 기간 동안 수집된
여러 개의 snapshot 정보가 필요하다. 애플리케이션이나 데이터베이스의
성능 관련 특성을 파악하기 위해서는 최소한 두 개의 snapshot 정보가 필요
하다.

snapshot을 수집하는 최선의 방법은, 일정 주기로 snapshot 수집을
자동화시키는 것이다. 이를 위해 2가지 방법을 사용할 수 있다.

- 데이터베이스가 제공하는 dbms_job 프로시져를 사용하여 snapshot
수집 작업을 스케쥴러에 등록한다.

- OS 시스템 유틸리티( unix의 cron 이나 NT 의 at 명령 )를 사용하여
snapshot을 스케쥴러에 등록한다.

1) DBMS_JOB 패키지를 사용한 StatsPack snapshot 작업의 자동화

오라클 내부적으로 통계정보 수집을 자동화하고자 한다면, dbms_job
패키지를 사용하면 된다. 샘플은 oracle 8.1.6의 경우 $ORACLE_HOME/rdbms/admin/statsuato.sql
( 8.1.7이상은 $ORACLE_HOME/rdbms/admin/spauto.sql) 에 들어 있으며,
매 시간 마다 snapshot을 등록하는 예제이다.

dbms_job을 사용하여 snapshot을 자동화시키기 위해서는, init 파일의
job_queue_processes 파라미터가 0 이상이어야만 한다.


init.ora 예제:
# Set to enable the job queue process to start. This allows dbms_job
# to schedule automatic statistics collection using STATSPACK
job_queue_processes=1

OPS 환경에서 statsauto.sql 을 사용한다면, statsauto.sql 스크립트는
클러스터 내의 모든 인스턴스에서 한번 씩 수행되도록 하여야 한다.
마찬가지로 job_queue_processes 파라미터 값도 모든 인스턴스에서
0 이상의 값으로 지정되어 있어야 한다.

dbms_job에 대한 자세한 사용방법은 를 참조한다.

2) OS 시스템 유틸리티를 사용한 StatsPack snapshot 작업의 자동화.

unix의 cron에 등록하는 방법은 을, NT 의 at 명령을 사
용하는 방법은 을 참조한다.


4. 통계정보 수집 주기의 변경 방법

통계정보 수집 주기를 변경하기 위해서는 dbms_job.interval 프로시져를
사용한다.

예.
execute dbms_job.interval(1,'SYSDATE+(1/48)');

위에서 SYSDATE+(1/48) 는 통계정보가 1/48 일에 한번씩 ( 매 30분 마다)
수집되도록 지정한 예이다.

job이 즉시 실행되도록 하기 위해서는
execute dbms_job.run();

job을 스케쥴러에서 제거하기 위해서는
execute dbms_job.remove();


5. STATSPACK의 삭제 및 snapshot 데이타 제거 방법.

1) Statspack 삭제

Statspack 환경 삭제를 위해서는 oracle 8.1.6의 경우는 statsdrp.sql을
oracle 8.1.7 이상의 경우는 spdrop.sql을 수행한다.

cd $ORACLE_HOME/rdbms/admin
SQL> connect / as sysdba
SQL> @statsdrp

2) Snapshot 데이타 제거

불필요한 snapshot 데이타를 제거하기 위해서 oracle 8.1.7부터는
sppurge.sql과 sptrunc.sql을 제공한다.

특정 구간 사이의 snapshot정보를 제거하려면 sppurge.sql을 수행한다.
이 script를 수행하면 삭제할 시작과 끝의 snapshot id를 지정하게 되며 지정
된 구간의 snapshot 데이타가 제거된다.

모든 snapshot 정보를 삭제하려면 sptrunc.sql을 수행한다.

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
SQL> @sptrunc


Example
-------
none

Reference Documents
-------------------