目次
はじめに
「・・・はぁ」
「この↑のイラストのハイテンションどこいったの」
「・・・これ書くの変わってよ」
「そしたらテンション上がるよ」
「いいねぇ！今のローテンションもいいよー！がんばってー！」
「きみさぁ・・・」
本記事は「STATSPACK入門 第1話」の続きです。
今回は第弐話です！
====================================
第1話
第弐話
- STATSPACKの取得レベル変更
- STATSPACKレポートからアイドルの待機イベントの除外
- STATSPACKの手動でのスナップショット取得方法
- STATSPACKのスナップショット自動取得ジョブ作成
- STATSPACKレポートの取得方法
第3話
- STATSPACKレポートを見る
- トップ5 イベント(Top 5 Timed Events)
- インスタンス効率(Instance Efficiency Indicators)
- 長時間SQL(SQL ordered by Elapsed time)
====================================
「記事に載せるコマンド実行用のDB環境作らないと・・・」
「だれが？」
「ボク、やだよ」
「ボクも・・・」
♪～～～(日曜夕方アニメのあの次回予告の曲)
「まだ早いよ！！」
今回もとてもユニークな課長の連載、第2弾です！
とても人気なので是非楽しんで下さいね。中身は役立つ情報ばかりです。
STATSPACKの設定
前回STATSPACKのインストールが完了したので、続いてSTATSPACKの設定に入っていきましょう。
STATSPACKの取得レベル変更
スナップショットを取得する前に、まずは取得レベルを目的に合わせ変更しておきましょう。
デフォルトの5のままで良ければこちらの対応は実施する必要はありません。
今回は取得レベルを7に変更します。
- sqlplusでDBへperfstatでログインします。
$ sqlplus perfstat/<perfstatユーザのパスワード>※PDBの場合はこちら
$ sqlplus perfstat/<perfstatユーザのパスワード>@<DBホスト名 or IPアドレス>:<リスナーポート>/<PDB名> - レベル変更前の設定を確認してみましょう。以下のコマンドを実行します。
SQL> select snap_level from stats$statspack_parameter; レコードが選択されませんでした。おや、レベル5とは表示されないのですね。レコードが選択されないという事は
明示的にレベルの設定がされていないという事になり、つまり現在のレベルの設定はデフォルトの5になっているという事を示しております。 - では以下のコマンドでレベルを7に変更しましょう。
SQL> execute statspack.modify_statspack_parameter(i_snap_level=> 7) PL/SQLプロシージャが正常に完了しました。 - 変更されたか確認すると・・・
SQL> select snap_level from stats$statspack_parameter; SNAP_LEVEL ---------- 7
無事7になってますね！これで変更完了です。
STATSPACKレポートからアイドルの待機イベントの除外
STATSPACKレポートの「Top 5 Timed Events」セクションにアイドルの待機イベント情報が含まれないよう除外をします。
諸々次回解説しますが、STATSPACKレポートには「Top 5 Timed Events」というDB内で多くの時間を占めていたイベントを表示してくれる項目があります。
除外対応をしないとこのTop5イベントに「アイドルの待機イベント」という性能調査・分析に不要な情報が含まれてしまうため、本対応を実施します。
こちらの対応はお使いのバージョンが12.2以降の時にのみ実行してください。
では以下の手順を実施して除外を出来るようにしましょう。
- sqlplusでDBにperfstatでログインします。
$ sqlplus perfstat/<perfstatユーザのパスワード>※PDBの場合はこちら
$ sqlplus perfstat/<perfstatユーザのパスワード>@<DBホスト名 or IPアドレス>:<リスナーポート>/<PDB名> - 以下のinsertコマンドを実行します。
SQL> insert into stats$idle_event 2 select name from v$event_name where wait_class='Idle' 3 minus 4 select event from stats$idle_event; 30行が作成されました。
- 忘れずにcommitもしましょう。
SQL> commit; コミットが完了しました。これで除外対応は完了です。
STATSPACKの手動でのスナップショット取得方法
スナップショットについては手動での実行と、ジョブによる自動取得の2種類の取得方法があります。
本項目では手動での取得方法について記載します。
- sqlplusでDBにperfstatでログインします。
$ sqlplus perfstat/<perfstatユーザのパスワード>※PDBの場合はこちら
$ sqlplus perfstat/<perfstatユーザのパスワード>@<DBホスト名 or IPアドレス>:<リスナーポート>/<PDB名> - 以下のコマンドでスナップショットを取得します。
SQL> execute statspack.snap PL/SQLプロシージャが正常に完了しました。 - スナップショットが取得できていることを確認します。
SQL> select snap_id,instance_number,to_char(snap_time,'YYYYMMDD HH24:MI:SS') snap_time,snap_level 2 from stats$snapshot order by instance_number,snap_id; SNAP_ID INSTANCE_NUMBER SNAP_TIME SNAP_LEVEL ------------ ----------------------- ---------------------- ----------- 1 1 20240509 17:15:01 7
無事に取得出来ていますね！
意図的にSQLやトランザクションを実行可能で、その時の性能状態を確認したい場合には、自動取得より手動実行の方が意図した期間のスナップショットを取得しやすく、取り回しが良いケースもあります。
状況によって使い分けていきましょう。
STATSPACKのスナップショット自動取得ジョブ作成
続いてスナップショットの自動取得のためのジョブ作成手順について記載します。
注意点があり、19cの環境においてperfstatユーザは作成時点ではCREATE JOBの権限が付与されていないため、権限付与を行うところからの手順を記載しております。
既に権限が付与されている環境の場合は「②」の実行はスキップし「③」の権限付与の確認から
実施頂ければ問題ありません。
ではやっていきましょう。
- sqlplusでDBへsysでログインします。
$ sqlplus / as sysdba※コンテナ・データベース環境であれば、STATSPACKをインストールしたPDBに接続しましょう。
SQL> alter session set container=<接続したいPDB名>; - perfstatユーザにCREATE JOB権限を付与します。
SQL> grant create job to perfstat; 権限付与が成功しました。 - 権限が付与されていることを確認します。
SQL> select grantee,privilege from dba_sys_privs 2 where grantee='PERFSTAT'; GRANTEE PRIVILEGE -------- ---------------------------------------- PERFSTAT DROP PUBLIC SYNONYM PERFSTAT CREATE TABLE PERFSTAT CREATE JOB PERFSTAT ALTER SESSION PERFSTAT CREATE VIEW PERFSTAT CREATE PUBLIC SYNONYM PERFSTAT CREATE PROCEDURE PERFSTAT CREATE SESSION PERFSTAT CREATE SEQUENCE 9行が選択されました。
無事perfstatユーザに対してCREATE JOB権限の付与が出来ていますね。
では続けていきます。 - perfstatユーザでDBにログインします。
SQL> conn perfstat/<perfstatユーザのパスワード>※PDBの場合はこちら
SQL> conn perfstat/<perfstatユーザのパスワード>@<DBホスト名 or IPアドレス>:<リスナーポート>/<PDB名> - spauto.sqlを実行します。このスクリプトがスナップショット自動取得ジョブ作成用のスクリプトです。
※第1話からの続きとなりますが、もし未実施であれば事前に
「cd $ORACLE_HOME/rdbms/admin」しておいてくださいSQL> @spauto.sql PL/SQLプロシージャが正常に完了しました。 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 80 Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is: JOB NEXT_DATE NEXT_SEC ---------- ------------------- -------------------------------- 21 2024/05/14 11:00:00 11:00:00
これでスナップショット自動取得ジョブの作成が完了です！
上記の結果から「2024/5/14 11:00」から動き始めることが分かりますね。
そしてジョブナンバー(JOBNO)は21です。このジョブナンバーはジョブの設定を変更する場合等に使用します。
作成されたジョブですが、デフォルトでは1時間ごとに実行されるようになっております。
ではその実行間隔に関する情報も見てみましょう。
- 自動取得ジョブの詳細情報を表示します。
SQL> set lines 1000 SQL> col schema_user for a15 SQL> col interval for a25 SQL> select job,schema_user,next_date,interval 2 from dba_jobs where schema_user='PERFSTAT'; JOB SCHEMA_USER NEXT_DATE INTERVAL ---------- ------------------ -------------------------- ------------------------------- 21 PERFSTAT 2024/05/14 11:00:00 trunc(SYSDATE+1/24,'HH')
注目して頂きたいのはINTERVALの値で「SYSDATE+1/24」となっており1時間ごとの設定になっていることが分かります。
こちらのジョブはDBMS_JOBパッケージにより作成されており、実行間隔の変更等については通常のジョブと同じように変更可能です。
参考としてジョブの実行間隔を30分に変更するSQLと、開始時間をキリのいい時間に変更するSQLを
以下に記載しておきます。
こちらは要件に合わせ適宜実施をしてもらえればと思います。
参考①：ジョブの実行間隔を30分に変更するSQL。commitも忘れずにね。
SQL> execute dbms_job.interval(<ジョブナンバー>, 'SYSDATE+(1/48)'); 実行例：execute dbms_job.interval(21, 'SYSDATE+(1/48)'); PL/SQLプロシージャが正常に完了しました。 SQL> commit; コミットが完了しました。
参考②：参考①のコマンドを実行すると、コマンド実行後から30分後にスケジュールされるため、
キリのいい開始時間に変更するSQLを実行します。commitも忘れずにね。
※以下の例では2024年5月14日 15時00分が開始時間になるように設定
SQL> execute dbms_job.next_date(<ジョブナンバー>, to_date('<次回実行日時>','YYYY/MM/DD HH24:MI:SS')); 実行例：execute dbms_job.next_date(21, to_date('2024/05/14 15:00:00','YYYY/MM/DD HH24:MI:SS')); PL/SQLプロシージャが正常に完了しました。 SQL> commit; コミットが完了しました。
おまけ
スナップショットの自動取得ジョブを実行させたままにすると、当然ですがスナップショットが取得され続け領域を圧迫してしまいます。
スナップショットは必要な分だけ保持し、定期的に削除するようにしましょう。
スナップショットは以下のようなコマンドで削除可能です。
※perfstatユーザで実行してください
例：2024年5月14日より前に取得されたスナップショットを削除するコマンド
SQL> exec statspack.purge(to_date('20240514','YYYYMMDD'),i_extended_purge=>TRUE);
※こちらは処理の中で自動でcommitされるので手動でのcommit実施は不要です
PL/SQLプロシージャが正常に完了しました。
削除処理の中身はDELETE処理であり、削除する量によっては実行時に負荷が高くなる場合もありますので
一度に削除する量をはじめは少なくしたり、システムとして負荷の低いタイミングで削除処理を実施する事をオススメします。
STATSPACKレポートの取得方法
取得したスナップショットからSTATSPACKレポートを取得する手順を記載します。
この手順により出力されたレポートを使って調査・分析を行います。
- sqlplusでDBへperfstatでログインします。
$ sqlplus perfstat/<perfstatユーザのパスワード>※PDBの場合はこちら
$ sqlplus perfstat/<perfstatユーザのパスワード>@<DBホスト名 or IPアドレス>:<リスナーポート>/<PDB名> - spreport.sqlを実行します。こちらがSTATSPACKレポート取得用のスクリプトです。
SQL> @spreport.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 2466673670 TEST 1 test Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 2466673670 1 TEST test otamesi Using 2466673670 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 Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ ------------ ---------------------- ------- -------------------- test TEST 1 09 5月 2024 17:15 7 11 14 5月 2024 10:00 7 12 14 5月 2024 11:00 7 13 14 5月 2024 12:30 7 15 14 5月 2024 13:00 7 16 14 5月 2024 13:30 7 17 14 5月 2024 14:00 7 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ begin_snapに値を入力してください:
- 上記のように取得したスナップショットの一覧が表示され、開始地点となるスナップショットの
入力を求められます。ここでは16を入力して進めます。begin_snapに値を入力してください:16 - 次に終了地点となるスナップショットの入力を求められます。ここでは17を入力して進めます。
end_snapに値を入力してください:17 - 次にレポート名の入力を求められます。何も入力せずエンターを押すと下記の「赤字部分.lst」という
ファイル名で出力されます。ここでは「/home/oracle/sp_16_17」と入力します。Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_16_17. To use this name, press <return> to continue, otherwise enter an alternative. report_nameに値を入力してください:/home/oracle/sp_16_17 STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 2466673670 test 1 09-5月 -24 16:1 19.0.0.0.0 NO 2 ～～省略～～ End of Report ( /home/oracle/sp_16_17.lst )
処理が終了するとプロンプトが返ってきます。最後のメッセージを見ると
パス、及びファイル名を指定した場合も自動的に拡張子「.lst」が付くようですね。 - では実際に出力されているか確認してみましょう。
SQL> exit $ ls -la /home/oracle/sp_* -rw-r--r-- 1 oracle oinstall 139775 5月 14 14:41 /home/oracle/sp_16_17.lst
無事出力されていますね！これでSTATSPACKレポートの取得は完了です。
次回へ続く
======================================================
♪～～～(ゲリって名前の付くアニメのあの次回予告の曲)
予告
読者はボクに勝つ(じゃんけんで)。だがそれはすべての始まりにすぎなかった。
現実から逃げるボク。ボクの傲慢は、ボク自身を救おうと決心させる。
次回「見知らぬ、上司」
この次も、サービス♪サービスゥ♪
======================================================
引き続き絶妙なテンションですね(笑)
当社は STATSPACK を用いたサービスも提供しています。
お気軽にお問合せ下さいね！
投稿者プロフィール
Infrastructure(IaaS)2024年6月4日OCI ブート・ボリュームのバックアップ・リストア
Oracle Cloud2024年5月31日OCIのMySQLでリードレプリカを構成してみました！
Oracle Database 管理2024年5月28日STATSPACK入門 第3話
Oracle Database 管理2024年5月27日STATSPACK入門 第弐話
