はじめに
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の実際の中身が、冒頭に記載したとおり、次に公開されています。