[oracle] SE(standard edition)의 AWR - STATSPACK 구성

 

statspack 구성

PERFSTAT 스키마 생성

sqlplus로 접속하여 spcreate.sql 스크립트 실행하면 statpack 관련 오브젝트가 생성됩니다.
중간에 비밀번호, 테이블스페이스 등 선택하는 단계가 나오니 선택해 주세요

SQL> @?/rdbms/admin/spcreate.sql

Session altered.


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

Enter value for perfstat_password: oracle
oracle


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
----------------------------
SYSAUX                         PERMANENT
*

USERS                          PERMANENT



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

Enter value for default_tablespace:

Using tablespace SYSAUX 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             *

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

Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views


... Granting privileges

...
...

스냅샷 생성 스케줄 JOB 생성

statpack 오브젝트 생성 후 일정 주기로 스냅샷을 생성하기 위해 스케줄 JOB을 생성해 줍니다

-- scheduler job 생성
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'PERFSTAT.JOB_STATSPACK_SNAPSHOT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN PERFSTAT.STATSPACK.SNAP; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; BYMINUTE=0,30', -- 30분 주기
    auto_drop       => FALSE,
    enabled         => TRUE,
    comments        => 'Statspack Snapshot Job'
  );
END;
/
 

-- 확인
SELECT
job_name, repeat_interval, enabled, last_start_date, NEXT_RUN_DATE
FROM dba_scheduler_jobs
WHERE owner = 'PERFSTAT'
;

JOB_NAME              |REPEAT_INTERVAL                |ENABLED|LAST_START_DATE|NEXT_RUN_DATE                |
----------------------+-------------------------------+-------+---------------+-----------------------------+
JOB_STATSPACK_SNAPSHOT|FREQ=MINUTELY; BYMINUTE=0,20,40|TRUE   |               |2024-09-15 09:00:44.228 +0900|

1 row(s) fetched.

-- 스냅샷 수동 생성
begin
perfstat.statspack.snap;
end;
/

-- 스냅샷 생성 확인
select 
snap_id,snap_time
from perfstat.stats$snapshot
order by snap_time
;

스냅샷 삭제(PURGE) 스케줄 JOB 생성

생성된 스냅샷은 자동으로 삭제되지 않고 계속 쌓이기 때문에 일정기간만 보관하고 삭제(PURGE)하는 스케줄 JOB 생성합니다

-- PURGE 스케줄 JOB 생성(매일 오전 06:00 실행, 최근 30일 보관)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'PERFSTAT.JOB_STATSPACK_PURGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN PERFSTAT.STATSPACK.PURGE(i_extended_purge => true, i_num_days => 30); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=0; BYSECOND=0',
    auto_drop       => FALSE,
    enabled         => TRUE,
    comments        => 'Statspack Purge Job'
  );
END;
/


-- 확인
SELECT 
job_name, repeat_interval, enabled, last_start_date, NEXT_RUN_DATE
FROM dba_scheduler_jobs
WHERE owner = 'PERFSTAT'
;

JOB_NAME              |REPEAT_INTERVAL                             |ENABLED|LAST_START_DATE|NEXT_RUN_DATE                |
----------------------+--------------------------------------------+-------+---------------+-----------------------------+
JOB_STATSPACK_SNAPSHOT|FREQ=MINUTELY; BYMINUTE=0,30             |TRUE   |               |2024-09-15 09:00:44.228 +0900|
JOB_STATSPACK_PURGE   |FREQ=DAILY; BYHOUR=6; BYMINUTE=0; BYSECOND=0|TRUE   |               |2024-09-16 06:00:00.776 +0900|

2 row(s) fetched.

스케줄JOB 삭제

미사용 등의 이유로 스케줄JOB삭제 시

BEGIN
dbms_scheduler.drop_job('PERFSTAT.JOB_STATSPACK_PURGE');
dbms_scheduler.drop_job('PERFSTAT.JOB_STATSPACK_SNAPSHOT');
END;
/