STATSPACK入門第1話

2024年5月24日 Oracle Database 管理

1 はじめに
2 STATSPACKとは？
3 次回へ続く

はじめに

～～～～～～～～～～～～～～～～～～～～～～～～～～～
これはSTATSPACKを理解したい戦士に向けた黙示録
さあ読み進めるがよい、汝の求める答えがあるだろう
～～～～～～～～～～～～～～～～～～～～～～～～～～～

「・・・黙示録ってなに？」

「うーん、わかんない」

「・・・答えってなに？」

「わ　か　ん　な　い」

本記事では以下の内容について全3話で解説していきます。
今回は第1話です！
====================================
第1話

STATSPACKとは？
STATSPACKの概要
STATSPACKによる情報取得
STATSPACKのインストール方法

第弐話

STATSPACKの取得レベル変更
STATSPACKレポートからアイドルの待機イベントの除外
STATSPACKの手動でのスナップショット取得方法
STATSPACKのスナップショット自動取得ジョブ作成
STATSPACKレポートの取得方法

第3話

STATSPACKレポートを見る
トップ5 イベント(Top 5 Timed Events)
インスタンス効率(Instance Efficiency Indicators)
長時間SQL(SQL ordered by Elapsed time)

====================================

「ねえ？こんなに誰が書くの？」

「さぁ、誰だろう・・・」

「ボク、やだよ」

「ボクも・・・」

「・・・そういえば黙示録って～」
「それはもういいよ！！」

突然の連載スタートです！
執筆者は当事業部の課長でとても頼りになる存在です。
是非、楽しんで読んで頂けたらと思います。

STATSPACKとは？

STATSPACKとは、Oracleデータベースの性能問題を調査・分析する為のツール(スクリプト群)です。
Oracleデータベースをインストールすれば Standard Edition 、及びStandard Edition 2 でも
利用可能でその有用性は非常に高く、是非使用をオススメするツールです。

利用可能なバージョンは8.1.6以降のバージョンとなっており、昨今導入ケースの多い19cでも
勿論利用可能です。名称は「STATISTICS PACKAGE」の略称のようです。赤字部分を名称に
採用しているようですね、STATSPACK。あれ、Sはどこから・・・

STATSPACKは 情報取得 ⇒ レポート出力 ⇒ 調査・分析といった流れで使用します。
取得している情報としては、ある期間でOracleが行なった処理の統計情報を取得しています。

具体的には以下のような情報です。

キャッシュヒット率
DB内の待ち時間の内訳
SQLの実行時間、実行回数等のトランザクション統計

などなど

上記のような情報を調査・分析することでDB内で何が起きていたかが分かります。

STATSPACKの概要

STATSPACKは調査・分析したい期間(性能問題が起きたタイミング等)の前後2地点における
データを取得し、それらの差分を元にその期間の統計データを結果レポートとして出力します。

文字だけだと分かり辛いですね。図で見てみましょう。

上記のように、とある２時点で取得した統計データ(STATSPACKスナップショット)の差分を元に、
その間のパフォーマンス統計データを結果レポート(STATSPACKレポート)に出力します。

例えば、B地点で取得されたSQLの実行回数が500回で、A地点で取得されたSQLの
合計実行回数が100回だった場合、A地点からB地点間のSTATSPACKレポートに出力される
SQLの実行回数は400回、つまりA地点からB地点までの時間内でSQLが
400回実行されていたという事が分かります。

このように、STATSPACKはDB内に蓄積されていく情報の「ある地点」の情報を取得しておき、
取得地点間の差分からDB内で何が起きていたかを調査・分析するツールです。

取得時の注意点として、情報取得をした地点間でDB再起動を実施しているとその再起動を挟んだ期間については正常な結果レポートを取得出来ないという仕様があります。気を付けましょう。

STATSPACKによる情報取得

STASPACKで情報取得をするにあたり、スナップショットの取得レベルと取得の間隔を意識しましょう。

STASPACKのスナップショットには取得レベルという設定があり、その設定により取得可能な情報が
異なります。基本的に高いレベルであればあるほど多くの情報が取得可能ですが、その分取得時の
負荷も高くなりますのでやみくもに高くすればよいという訳ではありません。

以下に各レベルにて取得可能な情報について記載します。

レベル5(デフォルト設定)・・・基本統計情報、アドバイス情報、SQL統計情報
レベル６・・・レベル5の取得情報＋SQLの詳細情報(SQL実行計画含む)
レベル7・・・レベル6の取得情報＋セグメント情報
レベル10・・・レベル7の取得情報＋ラッチ詳細情報

最低限のDBの健全性を調査・分析するという目的であればデフォルトのレベル5でも十分な情報を
取得可能ですが、個人的にはレベル7をオススメします。

レベル7でもレベル5と比較しそこまで大きな負荷は掛からない(あくまで経験談ですが)のと、SQLの実行計画を後から取得可能(レベル6)で且つセグメント情報(レベル7)が取れることで性能問題発生時のアプローチの幅が広がるのが魅力です。

レベル10は、そういえば試したこともありませんね。
昔偉い人から「余程のことが無い限り取らないよ」と言われ私の中では存在しないものとなっておりました。
取得時の負荷もかなり高いようでOracle社サポートから何か特別な指示が無い限りは実施しない方が良いでしょう。

次にSTATSPACKの取得間隔についてです。
STATSPACKレポートには、前述の通りスナップショットの2地点間の差分からその期間内で何が発生していたかが出力されますが、この特性から瞬間的に発生した負荷に対して発生したタイミングを的確に捉えるという点については苦手としており、それはスナップショットの2地点間の期間が長ければ長いほど顕著になります。

定期的にスナップショットを取得する際の設定としては、まずは30分～1時間程度の間隔で取得するのが個人的にはオススメです！

スナップショットの取得レベルの設定と取得方法の詳細については次回の記事にて
ご紹介させて頂きます。

STATSPACKのインストール方法

とにもかくにもインストールしないと話は始まりません。
早速以下の手順でSTATSPACKをインストールしてみましょう。

★ワンポイント★
コンテナ・データベースでSTATSPACKを使用する際ですが、ルートコンテナ(CDB$ROOT)に
対してのインストールは出来ません。インストールはPDBに実施しましょう。

①まずSTATSPACKのツールがあるディレクトリへ移動します。

$ cd $ORACLE_HOME/rdbms/admin
※以降、全てのコマンドについてこちらのディレクトリに移動している前提で記載致します。

②sqlplusでDBへsysでログインします。

$ sqlplus / as sysdba

※もしコンテナ・データベース環境であればSTATSPACKをインストールしたいPDBに接続しましょう。

SQL> alter session set container=<接続したいPDB名>;

③spcreate.sqlというスクリプトを実行します。これがSTATSPACKのインストール用スクリプトです。

SQL> @spcreate.sql

④perfstatというユーザがSTATSPACKの所有ユーザになります。
そのパスワードの入力を求められるので入力します。

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

perfstat_passwordに値を入力してください: <perfstatユーザのパスワード>

⑤perfstatユーザのデフォルト表領域を入力します。こちらにSTATSPACKのデータが格納されます。
デフォルトはSYSAUX表領域です。

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.

default_tablespaceに値を入力してください: <STATSPACKのデータを格納したい表領域名>

⑥perfstatユーザのデフォルト一時表領域を入力します。デフォルトはTEMP表領域です。

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.

temporary_tablespaceに値を入力してください: <perfstatユーザのデフォルト一時表領域名>

⑦ずらずらとログが流れた後にプロンプトが返ってくるので、sqlplusを終了します。