Statspack

설치하기

• $ORACEL_HOME/rdbms/admin/spcreate.sql로 설치

SQL> @spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: ********


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
CONTEXT                        PERMANENT
EDUTBS                         PERMANENT
GISCVI01                       PERMANENT
ICMLFQ32                       PERMANENT
ICMLNF32                       PERMANENT
ICMLSNDX                       PERMANENT
ICMSFQ04                       PERMANENT
ICMVFQ04                       PERMANENT
INDX                           PERMANENT
INGRIAN                        PERMANENT
LOB_IDX                        PERMANENT
LOB_TB                         PERMANENT
LOGMIR_T                       PERMANENT
MAIL2006_DAT                   PERMANENT
MAIL2006_IDX                   PERMANENT
PERFSTAT                       PERMANENT
SELF2006_DAT                   PERMANENT
SELF2006_IDX                   PERMANENT
SIGNGATE                       PERMANENT
SYSAUX                         PERMANENT *
SYSMASTER_DATA                 PERMANENT
TEST                           PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: perfstat

Using tablespace PERFSTAT as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *
TEMP01                         TEMPORARY
TEMP02                         TEMPORARY

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views


... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

(중간 생략)

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

리포지터리 구성하기

• 구정정보는 perfstat 스키마의 stats$statspack_parameter 테이블에 저장

파라미터

• 스냅샷 레벨: 스냅샷을 찍을 때 저장될 데이터를 정의

• SQL 임계치: SQL을 수집할지 판단할 때 사용되는 6개의 임계치, 이 중 하나 이상을 초과한 SQL 수집

• 세그먼트 통계 임계치: 세그먼트 통계 수집 여부를 판단할 때 사용되는 7개 임계치,이 중 하나 이상을 초과한 세그먼트 통계 수집

SQL> SELECT parameter, value
FROM stats$statspack_parameter
UNPIVOT( value FOR parameter IN ( SNAP_LEVEL,
                                  EXECUTIONS_TH,
                                  PARSE_CALLS_TH,
                                  DISK_READS_TH,
                                  BUFFER_GETS_TH,
                                  SHARABLE_MEM_TH,
                                  VERSION_COUNT_TH,
                                  SEG_PHY_READS_TH,
                                  SEG_LOG_READS_TH,
                                  SEG_BUFF_BUSY_TH,
                                  SEG_ROWLOCK_W_TH,
                                  SEG_ITL_WAITS_TH,
                                  SEG_CR_BKS_SD_TH,
                                  SEG_CU_BKS_SD_TH)
);

PARAMETER        VALUE
---------------- ---------
SNAP_LEVEL               5
EXECUTIONS_TH          100
PARSE_CALLS_TH        1000
DISK_READS_TH         1000
BUFFER_GETS_TH       10000
SHARABLE_MEM_TH    1048576
VERSION_COUNT_TH        20
SEG_PHY_READS_TH      1000
SEG_LOG_READS_TH     10000
SEG_BUFF_BUSY_TH       100
SEG_ROWLOCK_W_TH       100
SEG_ITL_WAITS_TH       100
SEG_CR_BKS_SD_TH      1000
SEG_CU_BKS_SD_TH      1000

--파라미터 변경
EXEC perfstat.statspack.modify_statspack_parameter(i_snap_level => 6);

스냅샷 찍기와 삭제하기

스냅샷 찍기

• statspack 패키지의 snap(함수, 프로시저)

--파라미터 없이 스냅샷 찍기
EXEC perfstat.statspack.snap();

스냅샷 삭제

• 시작 스냅샷ID와 종료 스냅샷ID사이의 모든 스냅샷:i_begin_snap, i_end_snap
• 시작일과 종료일 사이에 생성된 모든 스냅샷:i_begin_date, i_end_date
• 지정한 날짜 이전에 생성된 모든 스냅샷:i_purge_before_date
• 지정한 일수보다 오래된 모든 스냅샷 삭제: i_num_days

--i_extended_purge: SQL과 실행 계획까지 삭제
EXEC perfstat.statspack.purge(
    i_purge_before_date => to_date('2017-01-01', 'YYYY-MM-DD'),
    i_extended_purge => TRUE
);

베이스라인 관리

베이스라인 설정

EXEC perfstat.statspack.make_baseline(
    i_begin_date => to_date('2017-11-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    i_end_date   => to_date('2017-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
);

베이스라인 해제

EXEC perfstat.statspack.clearx c_baseline(
    i_begin_date => to_date('2017-11-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    i_end_date   => to_date('2017-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
);