月別アーカイブ: 2020年12月

RDS for OracleSE でいろいろやってみた

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に関して以下のガイドがあります。

Oracle Statspack

Statspackレポートを出力する頻度が多い割りにガイドにある手順が面倒なので、簡略化のため以下の3つのステップ毎にスクリプトを作成しました。

1. Snap Shot Idを確認する

最初は、レポート出力に必要なSNAP IDの確認です。

下のように、「showsnap.sql」を開始日付、終了日付の2つの引数を指定して実行します。

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

1つの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ではシステムテーブル等へのアクセスを制限するため固有のコマンド群が用意されています。(備忘録としてリンクを貼っておきます。)

Oracle DB インスタンスの一般的な DBA タスク

単性能試験実施時にキャッシュのクリアが必要な場合のためにスクリプトを用意してみました。

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%の10区間移動平均を追加したのが黒の実線です。

V$OSSTATから取得したCPU使用率推移をグラフ化

RDS for Oracle なかなか面白いです!!