오라클 19c Statspack 리포트 생성 가이드

OS환경 : Oracle Linux 7.5 (64bit)

DB 환경 : Oracle Database 19.3.0.0

방법 : 오라클 19c Statspack 리포트 생성 가이드

Statspack 이란?

Staspack은 Oracle Database에 대한 부하 및 resource 사용량의 trend 분석이나 성능 문제 분석을 위하여 사용되는 툴임

성능관련 통계정보들이 perfstat 유저에 누적되어 저장되므로 원하는 기간별로 비교 분석이 가능함

DBMS_JOB이나 OS Utility (예를 들면 cron)등을 사용하여 주기적으로 Data의 수집 할 수 있음

AWR은 유료이지만 Statspack는 무료임

AWR처럼 Top 5 Timed Event 및 메모리 Advisory 도 확인 가능함

자세한 리포트 확인방법은 추가 게시물 참고

Statspack 사용방법

Statspack 용 테이블스페이스 생성(선택사항)

SQL> create tablespace statspack_ts datafile '/app/oracle/oradata/ORCL19/statspack_ts01.dbf' size 100m autoextend on;
 
Tablespace created.

Statspack 설치

SQL> connect /as sysdba
SQL> @?/rdbms/admin/spcreate.sql
Enter value for perfstat_password: [패스워드 입력]
Enter value for default_tablespace: [perfstat 용 테이블스페이스 입력, 엔터시 default 테이블스페이스로 지정됨]
Enter value for temporary_tablespace: [perfstat 용 템프 테이블스페이스 입력 , 엔터시 default 템프 테이블스페이스로 지정됨]

Statspack을 설치하면 perfstat라는 유저를 생성하고, 이 사용자에 여러 개의 STAT$ 테이블을 생성하고, 

성능분석데이터는 이 테이블들에 저장됨

Statspack 용 스냅샷 생성

SQL> conn perfstat/perfstat
Connected.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.

(리포트 생성을 위해 스냅샷 2개 생성)

생성된 스냅샷 확인

SQL> select snap_id, snap_time from stats$snapshot;
 
   SNAP_ID SNAP_TIME
---------- ---------
     1 28-JUL-20
     2 28-JUL-20
 
2 rows selected.

Statspack 리포트 생성

SQL> conn perfstat/perfstat
Connected.
SQL> @?/rdbms/admin/spreport.sql
.
.
Listing all Completed Snapshots
 
                               Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL19         ORCL19          1 28 Jul 2020 23:21      5
                  2 28 Jul 2020 23:23      5
 
 
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.
.
Enter value for begin_snap: 1 [스냅샷 시작점 입력(스냅샷 ID)]
Begin Snapshot Id specified: 1
 
Enter value for end_snap: 2  [스냅샷 종료점 입력(스냅샷 ID)]
End   Snapshot Id specified: 2
 
Enter value for report_name: [리포트 이름 입력, 엔터시 sp_(스냅샷 시작점)_(스냅샷 종료점).lst 으로 파일 생성됨]
.
.
      -------------------------------------------------------------
 
End of Report ( sp_1_2.lst )

현재 스냅샷이 2개밖에 없어서 1~2번 스냅샷으로 리포트를 생성함

sqlplus 종료 후 현재 위치에서 해당 파일 vi로 확인

$ vi sp_1_2.lst
 
STATSPACK report for
 
Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
           323838205 ORCL19              1 02-Jul-20 14:59 19.0.0.0.0  NO
 
Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     ORACLE19         Linux x86 64-bit           1     1       1          3.8
 
Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          1 28-Jul-20 23:21:03       56      10.0
  End Snap:          2 28-Jul-20 23:23:37       56      10.0
   Elapsed:       2.57 (mins) Av Act Sess:       0.0
   DB time:       0.04 (mins)      DB CPU:       0.03 (mins)
 
Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       784M              Std Block Size:         8K
     Shared Pool:       304M                  Log Buffer:     7,364K
 
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                2.2        0.00        0.43
       DB CPU(s):                0.0                1.5        0.00        0.31
       Redo size:           18,566.0        2,859,168.0
   Logical reads:              135.1           20,806.0
   Block changes:               51.8            7,973.0
  Physical reads:                0.8              115.0
 Physical writes:                7.1            1,096.0
      User calls:                0.0                5.0
          Parses:                6.2              957.0
     Hard parses:                1.5              231.0
W/A MB processed:                0.1               17.3
          Logons:                0.0                0.0
.
.

작업 후 불필요한 스냅샷 삭제

SQL> @?/rdbms/admin/sppurge.sql
 
 
Database Instance currently connected to
========================================
 
                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
  323838205 ORCL19          1 ORCL19
 
 
Snapshots for this database instance
====================================
 
                   Base-  Snap
 Snap Id   Snapshot Started    line? Level Host        Comment
-------- --------------------- ----- ----- --------------- --------------------
       1  28 Jul 2020 23:21:03         5 ORACLE19
       2  28 Jul 2020 23:23:37         5 ORACLE19
 
 
Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id''s specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.
 
It is NOT possible to rollback changes once the purge begins.
 
You may wish to export this data before continuing.
 
 
Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1  [삭제하고픈 스냅샷 시작점 입력(스냅샷 ID)]
Using 1 for lower bound.
 
Enter value for hisnapid: 2  [삭제하고픈 스냅샷 종료점 입력(스냅샷 ID)]
Using 2 for upper bound.
 
Deleting snapshots 1 - 2.
 
Number of Snapshots purged: 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Purge of specified Snapshot range complete.

스냅샷 목록 확인

SQL> select snap_id, snap_time from stats$snapshot;
 
no rows selected

정상적으로 삭제됨

테스트용 스냅샷 몇개 더 생성

SQL> conn perfstat/perfstat
Connected.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.
 
SQL> exec statspack.snap;
 
PL/SQL procedure successfully completed.

스냅샷 목록 확인

SQL> select snap_id, snap_time from stats$snapshot;
 
 Snap Id SNAP_TIME
-------- ---------
      11 28-JUL-20
      12 28-JUL-20
      13 28-JUL-20
      14 28-JUL-20
      15 28-JUL-20
 
5 rows selected.

스냅샷 자동 생성

방법 1. spauto.sql 사용

oracle job을 이용해 스냅샷을 생성함

SQL> @?/rdbms/admin/spauto.sql
 
PL/SQL procedure successfully completed.
 
 
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
 
     JOBNO
----------
     1
 
 
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes             integer     1
 
 
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
 
       JOB NEXT_DATE NEXT_SEC
---------- --------- ----------------
     1 11-AUG-20 09:00:00

spauto.sql 을 수정하여 job 시간 수정가능

spauto.sql 내용

$ cat $ORACLE_HOME/rdbms/admin/spauto.sql
Rem
Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
Rem
Rem spauto.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      spauto.sql
Rem
Rem    DESCRIPTION
Rem      SQL*PLUS command file to automate the collection of STATPACK
Rem      statistics.
Rem
Rem    NOTES
Rem      Should be run as the STATSPACK owner, PERFSTAT.
Rem      Requires job_queue_processes init.ora parameter to be
Rem      set to a number >0 before automatic statistics gathering
Rem      will run.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    12/06/99 - 1059172, 1103031
Rem    cdialeri    08/13/99 - Created
Rem
 
 
spool spauto.lis
 
--
--  Schedule a snapshot to be run on this instance every hour, on the hour
 
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/
 
 
prompt
prompt  Job number for automated statistics collection for this instance
prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt  Note that this job number is needed when modifying or removing
prompt  the job:
print jobno
 
prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt  Below is the current setting of the job_queue_processes init.ora
prompt  parameter - the value for this parameter must be greater 
prompt  than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt
 
prompt
prompt  Next scheduled run
prompt  ~~~~~~~~~~~~~~~~~~
prompt  The next scheduled run for this job is:
select job, next_date, next_sec
  from user_jobs
 where job = :jobno;
 
spool off;

방법 2. crontab 이용

statspack.sh 파일 생성

$ cat statspack.sh 
sqlplus perfstat/perfstat << EOF
execute statspack.snap;
EOF

실행권한 부여

$ chmod +x statspack.sh

crontab 등록

$ crontab -l
0 * * * * /home/oracle/script/statspack.sh

매일 1시간마다 스냅샷을 생성하도록 crontab 등록

참조 crontab 등록형식

* * * * *  수행할 명령어
┬ ┬ ┬ ┬ ┬
│ │ │ │ │
│ │ │ │ │
│ │ │ │ └───────── 요일 (0 - 6) (0:일요일, 1:월요일, 2:화요일, …, 6:토요일)
│ │ │ └───────── 월 (1 - 12)
│ │ └───────── 일 (1 - 31)
│ └───────── 시 (0 - 23)
└───────── 분 (0 - 59)

스냅샷 일괄 삭제

SQL> @?/rdbms/admin/sptrunc.sql 
 
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.
 
 
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>
 
 
Enter value for begin_or_exit: [엔터]
Entered at the 'begin_or_exit' prompt
 
... Starting truncate operation

스냅샷 목록 확인

SQL> select snap_id, snap_time from stats$snapshot;
 
no rows selected

정상적으로 삭제됨

Statspack 삭제

SQL> conn / as sysdba
SQL> @?/rdbms/admin/spdrop.sql

참조 : http://www.gurubee.net/lecture/1915

http://www.gurubee.net/lecture/1916

http://haisins.epac.to/wordpress/?p=2577