Statspack

Statspack이란?

Oracle이 리소스 사용량의 분석이나 성능 분석을 위해서 사용하는 Tool로써 UTLBSTAT/UTLESTAT이 제공하는 기능을 수정/보완하여 Oracle 8.1.6버전부터 제공하고 있는 무료 Tool입니다.

 

 

특징

Oracle DB내의 특정 시간대의 데이터베이스에 대한 성능과 관련 데이터를 수집하여 DB에 저장하여 두고 이로부터 성능 분석 report를 생성해 내는 script로 구성되어 있습니다.

 

Spcreate.sql : Statspack설치 script

Spreport.sql : 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

 

 

설치

설치를 하면 PERFSTAT이라는 유저가 생성되고 그 유저의 테이블들에서 성능관련 통계정보들이 시간대별로 누적되어 저장되어집니다. (DBMS_JOB 이나 OS의 cron 등을 이용해서 주기적으로 데이터의 수집이 가능합니다.

 

 

SQL> @/u01/app/oracle/product/11.2.0/xe/rdbms/admin/spcreate.sql

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

Enter value for perfstat_password: oracle  #PERFSTAT 유저 패스워드 입력
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
------------------------------ --------- ----------------------------
ORA_SQL_TEST_TS                PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

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

Enter value for default_tablespace: #유저가 사용할 테이블스페이스 입력 입력안할 시 SYSAUX가 Default

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: #유저가 사용할 TEMP 테이블스페이스 입력 입력안할 시 TEMP가 Default

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages
...생략

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

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

 

설치 완료

 

 

통계분석에 필요한 스냅샷 생성 방법

수동으로 생성

PERFSTAT USER로 statspack.snap을 수행하면 됩니다.

 

 

수행 전 확인 쿼리

SQL> select snap_id,snap_time from stats$snapshot;

no rows selected

 

statspace.snap 수행

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

 

수행 후 확인 쿼리

SQL>  select snap_id,snap_time from stats$snapshot;

   SNAP_ID SNAP_TIME
---------- ---------
         1 02-MAR-21

1 row selected.

 

 

DB Job으로 자동으로 수집되게 하기

DB job으로 자동으로 수집되게 하기 위해서는 job_queue_process가 1보다 큰 값으로 설정되어 있어야 합니다.

$ORACLE_HOME/rdbms/admin.spauto.sql을 이용하여 JOB을 등록합니다.

이 스크립트는 PERFSTAT유저로 수행하며 JOB INTERVAL은 기본 1시간으로 되어 있습니다. 

 

job_quere_process 확인

 

SQL> show parameter job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4

 

시간과 관련된 통계정보를 수집하기 위한 파라미터인 timed_statistics 파라미터가 TRUE인지 확인합니다.

SQL> show parameter timed_statistics
-TRUE로 설정
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

 

 

 

spauto.sql 수행

JOBNO가 21번인 JOB이 생성된 것을 확인할 수 있습니다.

 

SQL> @$ORACLE_HOME/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
----------
        21


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     4


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
        21 02-MAR-21 04:00:00

 

 

리포트 생성하기

SYS유저로 생성 

 

SYS@XE> @/u01/app/oracle/product/11.2.0/xe/rdbms/admin/spreport.sql

 

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2938035446 XE                  1 XE

1 row selected.

Elapsed: 00:00:00.00
SP2-0311: string expected but not found


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 2938035446        1 XE           XE           ysbae

Using 2938035446 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
XE           XE                   1 02 Mar 2021 02:56     5 (null)
                                  2 02 Mar 2021 04:00     5 (null)
                                  3 02 Mar 2021 05:00     5 (null)
                                  4 02 Mar 2021 06:00     5 (null)
                                  5 02 Mar 2021 07:00     5 (null)
                                  6 02 Mar 2021 08:00     5 (null)
                                  7 02 Mar 2021 09:00     5 (null)

...생략

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 30 #시작 스냅샷 번호
Begin Snapshot Id specified: 30 

Enter value for end_snap: 40
End   Snapshot Id specified: 40 #종료 스냅샷 번호

 

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_30_40.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 30-40.txt #리포트 이름

Using the report name 30-40.txt

 

...생략

끝.

 

리포트 확인

리포트는 OS의 디렉토리에 위치하고 있습니다.

 

 

스냅샷 레벨

LEVEL 0 : 일반적인 성능 통계정보를 수집합니다.

LEVEL 5 : default값으로 일반적인 통계정보 + resource를 많이 사용하는 SQL에 대한 정보 포함

LEVEL 6 : LEVEL5 + SQL상세 실행계획 포함

LEVEL7 : LEVEL6 + 세그먼트 정보 포함

LEVEL 10 : LEVEL7 + 부모 latch,자식 latch정보 포함

레벨이 높을수록 리소스가 많이 필요하게 되며 LEVEL 10은 필요한 경우에만 사용해야 합니다.

 

execute statspack.snap(i_snap_level => 10, i_modify_parameter => ‘true’);

 

 

 

출처 : http://www.gurubee.net/lecture/1915

Statspack 사용방법

 

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

AWR 은 유료 ? STATSPACK 은 무료 ! – DBA 의 정석

출처 : https://gkstamin.tistory.com/entry/Oracle-Statspack-%EC%84%A4%EC%B9%98-%EB%B0%8F-report-%EC%83%9D%EC%84%B1

[Oracle] Statspack 설치 및 report 생성