JPOUG Advent Calendar 2020 14日目のエントリーです。13日目はmultilayerさんの記事「Oracle GDSの時代がやって来た！」でした。
最近の当ブログは、ホントに年1回の投稿になってきました。。。
このところAmazon RDS for Oracleに携わる機会が増えてきたので、ちょとした工夫、気づきをいくつか紹介していきたいと思います。
Statspackレポート出力手順を簡単にしてみた
オンプレミスからAWS RDS for Oracleへのクラウドリフトを考えた場合、ライセンスコストに鑑みてStandard Editionへの移行というケースが意外に多いのではないかと思います。
必然的にAWRの代わりにStatspackを使うことになりますが、AWSのドキュメントでStatspackに関して以下のガイドがあります。
Statspackレポートを出力する頻度が多い割りにガイドにある手順が面倒なので、簡略化のため以下の３つのステップ毎にスクリプトを作成しました。
1. Snap Shot Idを確認する
最初は、レポート出力に必要なSNAP IDの確認です。
下のように、「showsnap.sql」を開始日付、終了日付の２つの引数を指定して実行します。
SQL> @showsnap <開始日付> <終了日付> ←yyyymmddで指定。同一日は同じ値を指定。
例
SQL> @showsnap 20201209 20201209
旧 2: where trunc(SNAP_TIME) between to_date(&BEGINDT,'yyyymmdd') and to_date(&ENDDT,'yyyymmdd') order by 1,2,3
新 2: where trunc(SNAP_TIME) between to_date(20201209,'yyyymmdd') and to_date(20201209,'yyyymmdd') order by 1,2,3
STARTUP SDATE STIME SNAP_ID
------------------- ---------- -------- ----------
2020/11/13 19:02:28 2020/12/09 00:00:32 7806
00:09:26 7807
01:00:32 7808
02:00:32 7809
03:00:32 7810
04:00:32 7811
05:00:32 7812
06:00:32 7813
07:00:32 7814
08:00:32 7815
09:00:32 7816
10:00:32 7817
10:28:19 7818
11:00:32 7819
12:00:32 7822
13:00:32 7823
14:00:32 7824
15:00:32 7825
16:00:32 7826
17:00:33 7827
18:00:32 7828
19:00:32 7829
20:00:32 7830
21:00:32 7831
22:00:32 7832
23:00:32 7833
26行が選択されました。
「showsnap.sql」の実際は以下のScrapboxのリンク先を参照してください。
（今後、本ブログで紹介するスクリプトやコマンドは原則的にScrapboxにて管理＆公開することにします。）
https://scrapbox.io/scripts/showsnap.sql
2. Statspackレポートを作成する
得られたSNAP IDを指定して、以下のように「mksprep.sql」を実行します。
このスクリプトにより、指定されたSNAP IDの範囲でStatspackレポートが作成され、RDSADMIN.TRACEFILE_TABLE表に格納されます。
SQL> @mksprep <前snapid> <後snapid> 例） SQL> @mksprep 7830 7832 PL/SQLプロシージャが正常に完了しました。 PL/SQLプロシージャが正常に完了しました。 FILENAME TYPE FILESIZE MTIME ---------------------------------------- ------------ ---------- ------------------------- XXXXXXX_spreport_7796_7803.lst file 164 2020-12-09 11:49 XXXXXXX_spreport_7830_7832.lst file 188 2020-12-10 15:10
上例のように、（過去に作成されたものを含む）複数のレポートが表示されることがありますが、MTIME列を見て次のステップでファイル出力するレポートを特定します。（mksprep.sql）
3. Statspackレポートをファイルに出力する
次に、SQL*Plusのスプール機能を使用して、レポートをテキストファイルに出力します。（getsprep.sql）
SQL> @getsprep XXXXXXX_spreport_7830_7832.lst
PL/SQLプロシージャが正常に完了しました。
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2169958367 XXXXXXX 1 13-Nov-20 19:02 19.0.0.0.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
ip-xxx-xx-xx-xxx Linux x86 64-bit 16 8 1 62.1
..............................................................................
standby_file_management AUTO
undo_tablespace UNDO_T1
use_large_pages ONLY
-------------------------------------------------------------
End of Report ( /rdsdbdata/log/trace/XXXXXXX_spreport_7830_7832.lst )
2990行が選択されました。
Statspackデータからの実行計画の確認を簡単にしてみた
1. PLAN HASH VALUEを確認する
StatspackレポートにはSQL_IDではなく、「OLD HASH VALUE」が出力されています。
この値をキーとして実行計画を特定する「PLAN HASH VALUE」を確認します。（getphv.sql）
SQL> @getphv 3464557777
OLD_HASH_VALUE SNAP_ID PLAN_HASH_VALUE TEXT_SUBSET
-------------- ---------- --------------- -------------------------------
3464557777 7796 1601398191 select /*+ USE_HASH(t2) */ t
１つのOLD HASH VALUEに複数のPLAN HASH VALUEが存在する可能性がありますが、その場合はSNAP_IDを見てどのPLAN HASH VALUEが対象なのかを判断します。
2. 実行計画を確認する
得られたPLAN HASH VALUEをキーにして、実行計画を確認します。（showspplan.sql）
SQL> @showspplan 1601398191
旧 31: where PLAN_HASH_VALUE = &PHV
新 31: where PLAN_HASH_VALUE = 1601398191
PLAN_HASH_VALUE SNAP_ID ID Operation Name Rows Query Block Name / Object Alias Bytes Cost Srch Cols
--------------- ---------- ----- ------------------------- ------------------------- ----------- ---------------------------------------- ---------- ----------- ----------
1601398191 3395 0 SELECT STATEMENT 190 0
1601398191 3395 1 SORT ORDER BY 7 SEL$1 5789 190 0
1601398191 3395 2 HASH JOIN OUTER 7 5789 189 0
1601398191 3395 3 VIEW 3 SET$1 / T1@SEL$1 2439 60 0
1601398191 3395 4 UNION-ALL SET$1 0
1601398191 3395 5 HASH JOIN 1 SEL$2 879 27 0
1601398191 3395 6 NESTED LOOPS OUTER 6 4530 24 0
1601398191 3395 7 NESTED LOOPS 6 2532 23 0
1601398191 3395 8 NESTED LOOPS 6 1218 11 0
1601398191 3395 9 NESTED LOOPS 6 618 11 0
...........................................................................................................................................................................
オンプロミス環境で $ORACLE_HOME/rdbms/admin/sprepsql.sql を使用して出力したSQL詳細レポートでも実行計画は確認できます（早い話スクリプトをSQL*Plusクライアントに持って行けば使えます。）が、簡単に実行計画を得るための方法として参考にしてください。
RDS for OracleのDBAコマンド
セッションの切断（DISCONNECT SESSION）や終了（KILL SESSION）等DBAタスクに関して、RDS for Oracleではシステムテーブル等へのアクセスを制限するため固有のコマンド群が用意されています。（備忘録としてリンクを貼っておきます。）
単性能試験実施時にキャッシュのクリアが必要な場合のためにスクリプトを用意してみました。
https://scrapbox.io/scripts/flusys.sql(RDS)
参考までに、通常のALTER SYSTEMコマンドによるスクリプトは
https://scrapbox.io/scripts/flusys.sql
となります。
V$OSSTATからCPU使用率を取得してみた
Amazon Cloudwatchでは、高分解能カスタムメトリクスを使用すれば分解能 1 秒までのメトリクスを取得できます。Amazon CloudWatch よくある質問
同様にRDSのみでCPU使用率を分解能1秒で取得できないかV$OSSTATを使って試してみました。
1. 取得準備
CloudWatchのようにリアルタイムで可視化できない点がイケてませんが、PERFSTATスキーマにデータ蓄積用のテーブル（USER$OSSTAT）を作成します。
構成はV$OSSTATSとほぼ同じですが、先頭に日付時間情報カラム（SDATE）を設けています。
SQL> desc USER$OSSTAT Name Null? Type ------------ -------- ---------------------------- SDATE DATE IDLE_TIME NUMBER BUSY_TIME NUMBER USER_TIME NUMBER SYS_TIME NUMBER IOWAIT_TIME NUMBER NICE_TIME NUMBER LOAD NUMBER
2. データ取得用スクリプト
まず、USER$OSSTAT表にデータをINSERTするSQLスクリプトです。（osstat.sql）
これを1秒間隔で実行させるシェルスクリプトです。（osstat.sh）時間間隔はSLEEP_SEC変数に設定した秒数で調整できます。（引数で指定するのもありですが、煩雑なので固定値にしました。）
さらに、実行時間（分）を指定してosstat.shを起動するシェルスクリプトです。（getcpu）引数を省略すると24時間動きます。
3. データ表示用スクリプト
指定時間後又はCtrl+Cでgetcpuを終了させたのち、蓄積データを一覧表示させるSQLスクリプトです。（cpu.sql）
IDLE、USER、SYS、NICE、WIOそれぞれで現在行と1行前の時間差を求め、(IDLE_TIME+BUSY_TIME)を分母としたCPU使用率を算出します。従って1行目は無視します。
SQL> @cpu SDATE IDLE% USER% SYS% NICE% WIO% LOAD ------------------- ------ ------ ------ ------ ------ ------ 2020/08/06 11:09:59 0.3 2020/08/06 11:09:59 99.6 0.0 0.1 0.2 0.0 0.3 2020/08/06 11:10:00 93.3 2.2 4.3 0.2 0.0 0.3 2020/08/06 11:10:01 85.9 2.8 11.2 0.1 0.1 0.4 2020/08/06 11:10:02 87.1 2.4 10.3 0.2 0.0 0.4 2020/08/06 11:10:03 90.6 3.8 5.6 0.1 0.0 0.4 2020/08/06 11:10:04 95.4 0.1 0.1 4.4 0.0 0.4 2020/08/06 11:10:05 93.3 0.1 0.2 6.4 0.0 0.4 2020/08/06 11:10:06 93.3 0.1 0.3 6.3 0.0 0.4 2020/08/06 11:10:07 93.3 0.1 0.2 6.4 0.2 0.4 2020/08/06 11:10:08 93.3 0.1 0.2 6.4 0.0 0.4 2020/08/06 11:10:09 93.0 0.4 0.2 6.4 0.0 0.4 2020/08/06 11:10:10 93.3 0.2 0.2 6.3 0.0 0.4 2020/08/06 11:10:11 93.0 0.2 0.4 6.4 0.0 0.4 2020/08/06 11:10:12 93.2 0.2 0.3 6.3 0.0 0.4 .............................................................
以下は、CPU 1コアを使い切るHeavy SQLをSQL*Plusから1→2→3→2→1セッションと同時実行させた場合のCPU使用率の推移です。
このインスタンスは16コアなので、
- 1セッション：1/16=6.25%
- 2セッション：2/16=12.5%
- 3セッション：3/16=18.75%
と計算どおりの結果となりました。
なお、USER%が変化していくと思ったのですが、NICE%が変化していきました。
また、5分間隔でSYS%が約12%、20秒間隔でNICE(USER)%で大小0.5〜6%程度のスパイクが見られました。これらの正体は追いかけていませんが、定期的な監視等によるものと思われます。これらのスパイクが気になったので、NICE%の１０区間移動平均を追加したのが黒の実線です。
V$OSSTATから取得したCPU使用率推移をグラフ化
RDS for Oracle なかなか面白いです！！