[Oracle Cloud] Statspack を取得してみた

# はじめに

Oracle Database では、Statspack と呼ばれる各種 Metric を取得するためのツールが提供されています。オンプレミスだけではなく、Oracle Cloud 上の Database Cloud Service(DBCS) でも利用が出来ます。
Internet 上で見かける Statspack に関する記事では、取得方法・どのように読み解くか、といった解説記事が多くあります。

ただ、Statspack の Report で表示できる実際の項目は省略して書かれていることが多いです。今回は、自分のもっている環境で、取得した Statspack の Report 内容を丸ごと載せます。備忘録のために。



# Statspack の実際の中身

ということで、さっそく Statspack で取得した Report を Github 上に公開しています。とてもたくさんの Metric が取得できていますね。ちなみに、Level 7 で取得しています。
https://github.com/Sugi275/oracledatabase-statspack-sample-level7/blob/master/testreport.lst

では、この Statspack をどのように取得したのか手順を残しておきます。

# Oracle Database の情報

- DBCS Standard Edition 以上
- Version 11.2.0.4


# Statspack Install

SQL*Plus に接続

```
sqlplus / as sysdba
```



インストールがされていないことを確認

```
SELECT user_id, username FROM dba_users WHERE username = 'PERFSTAT';
```



実行例
`no rows selected` となっており、Statspack を取得するための、PERFSTAT ユーザーが存在していません。間接的に Statspack がインストールされていないことがわかります

```
SQL> SELECT user_id, username FROM dba_users WHERE username = 'PERFSTAT';

no rows selected

SQL>
```



Statspack で取得した情報は表領域に格納されます。新たに表領域を作成してもいいのですが、今回は Simple にするために既存の表領域を使っていきます。既存の表領域はこんな感じです。
本番環境では、Statspack 専用の表領域を新たに作るのが良いでしょう

```
SQL> select tablespace_name, file_name from dba_data_files;

UNDOTBS1
+DATA/oradb11_nrt1mk/datafile/undotbs1.263.1047446849

SYSAUX
+DATA/oradb11_nrt1mk/datafile/sysaux.262.1047446849

SYSTEM
+DATA/oradb11_nrt1mk/datafile/system.261.1047446849

USERS
+DATA/oradb11_nrt1mk/datafile/users.266.1047447151

SQL>
```



Statspack を Install します

```
@?/rdbms/admin/spcreate.sql
```



PERFSTAT ユーザーのパスワードを何にするか聞かれるので、適当に入力します。
`Sug1_Passw0rd_dayo`

```
SQL> @?/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:ここに入力
```



既存の USERS 表領域を指定

```
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:USERS
```



一時表領域を指定

```
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
```



たくさんの文字が流れます。Statspack を格納するための、Table など作成されている様子が見えます。

```
省略
SQL> set echo off;
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.

SQL>
```



インストール後に確認します。PERFSTAT が存在しているので、正常に Install 出来たことが分かります。

```
SQL> SELECT user_id, username FROM dba_users WHERE username = 'PERFSTAT';

   USER_ID USERNAME
---------- ------------------------------
        76 PERFSTAT

SQL>
```



ちなみに、Install に使われるファイルはコレです。ORACLE_HOME 配下に、インストール用のファイルが準備されています。

```
[oracle@oradb11se admin]$ cat $ORACLE_HOME/rdbms/admin/spcreate.sql
Rem
Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
Rem
Rem spcreate.sql
Rem
Rem Copyright (c) 1999, 2002, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      spcreate.sql - Statistics Create
Rem
Rem    DESCRIPTION
Rem      SQL*PLUS command file which creates the STATSPACK user,
Rem      tables and package for the performance diagnostic tool STATSPACK
Rem
Rem    NOTES
Rem      Note the script connects INTERNAL and so must be run from
Rem      an account which is able to connect internal.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    12/06/99 - 1103031
Rem    cdialeri    08/13/99 - Created
Rem

--
--  Create PERFSTAT user and required privileges
@@spcusr

--
--  Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
--  Create the statistics Package
@@spcpkg
[oracle@oradb11se admin]$
```



# Statspack 1時間毎に実行

Statspack をインストール後、一定時間毎にSnapshot 取得が必要です。今回は Oracle Database 側で用意されている、1時間毎に自動的に取得してくれる設定をします。
SQL*Plus に接続します

```
sqlplus perfuser/Sug1_Passw0rd_dayo as sysdba
```



Statspack の取得レベル設定をします。Default は 5 ですが、より詳細な情報を取得するために 7 を設定してみます。

```
execute statspack.modify_statspack_parameter(i_snap_level=> 7)
```



実行例

```
SQL> execute statspack.modify_statspack_parameter(i_snap_level=> 7)

PL/SQL procedure successfully completed.

SQL>
```



現在の Snapshot を確認します。

```
select snap_id,to_char(snap_time,'yy-mm-dd hh24:mi:ss') snap_time, snap_level from stats$snapshot order by snap_id;
```



特に何も Snapshot を取っていないので、何もなしです

```
SQL> select snap_id,to_char(snap_time,'yy-mm-dd hh24:mi:ss') snap_time, snap_level from stats$snapshot order by snap_id;

no rows selected

SQL>
```



自動的に1時間毎に取得する設定をします。このコマンドを実行するだけで、自動的に取得してくれます。便利ですね。

```
@?/rdbms/admin/spauto.sql
```



実行例

```
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     1000


Next scheduled run

The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
         1 03-AUG-20 11:00:00

SQL>
```



# 実行結果確認

約12時間後に確認

```
SQL> select snap_id,to_char(snap_time,'yy-mm-dd hh24:mi:ss') snap_time, snap_level from stats$snapshot order by snap_id;

   SNAP_ID SNAP_TIME         SNAP_LEVEL
---------- ----------------- ----------
         1 20-08-03 11:00:02          7
         2 20-08-03 12:00:02          7
         3 20-08-03 13:00:02          7
         4 20-08-03 14:00:02          7
         5 20-08-03 15:00:03          7
         6 20-08-03 16:00:03          7
         7 20-08-03 17:00:03          7
         8 20-08-03 18:00:03          7
         9 20-08-03 19:00:03          7
        10 20-08-03 20:00:04          7
        11 20-08-03 21:00:04          7

11 rows selected.

SQL>
```



取得した Snapshot から report を作成します。report を作成すると、SQL*Plus を実行した時のカレントディレクトリにファイルが出力されます。(出力先を指定も出来ます)

```
@?/rdbms/admin/spreport.sql
```



Snapshot の中から、どれとどれを比較するのかを選択します。今回は、1 と 2 で比較してみます。

```
Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
oradb11      ORADB11              1 03 Aug 2020 11:00     7
                                  2 03 Aug 2020 12:00     7
                                  3 03 Aug 2020 13:00     7
                                  4 03 Aug 2020 14:00     7
                                  5 03 Aug 2020 15:00     7
                                  6 03 Aug 2020 16:00     7
                                  7 03 Aug 2020 17:00     7
                                  8 03 Aug 2020 18:00     7
                                  9 03 Aug 2020 19:00     7
                                 10 03 Aug 2020 20:00     7
                                 11 03 Aug 2020 21:00     7



Specify the Begin and End Snapshot Ids

Enter value for begin_snap:1を入力
```



2 を入力

```
Enter value for end_snap: 2を入力
```



report名を入力

```
Specify the Report Name

The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: testreport
```



たくさんの文字が流れます。SQL*Plus を実行したカレントディレクトリに、`testreport.lst` ファイルが出力されています。

```
[oracle@oradb11se ~]$ ls -hla | grep testreport
-rw-r--r-- 1 oracle oinstall 115K Aug  3 21:41 testreport.lst
```



testreport.lstの実際の中身が、冒頭に記載したとおり、次に公開されています。

https://github.com/Sugi275/oracledatabase-statspack-sample-level7/blob/master/testreport.lst





# 参考URL

http://fulfillment-c.com/database/statspack/

https://qiita.com/mkyz08/items/729545aab4751f3002d0

https://www.oracle.com/assets/how-to-get-statspack-3232083-ja.pdf