転送(エクスポート/インポート)した AWR内 の SQL実行計画 を DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY で確認する。(Oracle Database)

表題の通り 転送(エクスポート/インポート)した AWR内 の SQL実行計画 を DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY で確認してみますやで。
彡(ﾟ)(ﾟ)

# 1. 前提知識・その1(AWR = Automatic Workload Repository について)

AWR は Oracle Database が自身で収集する性能関連の統計値を収集したテーブル／ディクショナリ群の総称、
あるいは自動収集する機能のことで、お客様からご提供いただく html や text のレポートは AWR そのものでは無いです。

> 自動ワークロード・リポジトリ
> https://docs.oracle.com/cd/F39414_01/tgdba/gathering-database-statistics.html#GUID-CE73D449-0EE9-4022-B1F1-AA12F0955C03
> AWRは、問題の検出および自己チューニングを目的として、パフォーマンス統計を収集、処理およびメンテナンスします。
> この収集されたデータは、メモリーとデータベースの両方に格納され、レポートおよびビューに表示されます。

html や text の レポート は AWR に格納されたデータを一定のフォーマットで出力したもので、
AWR そのものではありません。AWR には SQL の実行計画や ASH(Active Session History) など、
AWRレポートに出力される以外の様々な情報が格納されています。


# 2. 前提知識・その2(DISPLAY_AWRファンクションの非推奨)

Oracle Database 19c や 21c で DISPLAY_AWRファンクションは非推奨になりました。

ローカルDB の AWR に格納された実行計画は引き続き確認可能ですが、
AWR を転送(エクスポート/インポート)したようなケースでは利用できなくなっています。

> Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 21c
> 218.5.4 DISPLAY_AWRファンクション
> https://docs.oracle.com/cd/F39414_01/arpls/DBMS_XPLAN.html#GUID-E234ED21-21A9-4237-9DF3-8E420CDDBC70
> ノート:この関数は非推奨です。かわりにDISPLAY_WORKLOAD_REPOSITORYを使用します。
> DISPLAY_AWRはローカルDBIDのスナップショットでのみ動作しますが、DISPLAY_WORKLOAD_REPOSITORYは、
> リモートおよびインポートされたスナップショットを含むAWR内のすべてのスナップショットをサポートします。

上記の通り DISPLAY_AWR のかわりに DISPLAY_WORKLOAD_REPOSITORY を使用してみます。


# 3. AWR の転送(エクスポート／インポート)

AWR はマニュアルの下記機能で別の Oracle Database に転送できます。

> AWRデータのエクスポート
> https://docs.oracle.com/cd/F39414_01/tgdba/gathering-database-statistics.html#GUID-CC6A87BD-BFEA-4A1B-9FD1-3445DC11BE72
> 
> AWRデータのインポート
> https://docs.oracle.com/cd/F39414_01/tgdba/gathering-database-statistics.html#GUID-C916E80F-B819-4452-ABFC-18CA70D8A501

本記事では 転送した AWR に格納されている SQL の実行計画を DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY で参照してみます。


# 4. AWR_ROOT_SQL_PLANビューから DBID と CON_DBID を確認

前提として実行計画を確認したい SQL の sql_id は解っているものとします。

AWR_ROOT_SQL_PLANビュー から 該当SQL の DBID, CON_DBID を確認します。
今回は sql_id として '4f7w8vvvdz6q5' を使用します。

```sql
SELECT DBID, CON_DBID, CON_ID, COUNT(*) 
  FROM AWR_ROOT_SQL_PLAN
 WHERE SQL_ID = '4f7w8vvvdz6q5'
 GROUP BY DBID, CON_DBID, CON_ID;

      DBID   CON_DBID     CON_ID   COUNT(*)
---------- ---------- ---------- ----------
2198735651  981234127                     3
```

# 5. DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY で AWR内 の実行計画を出力

DISPLAY_WORKLOAD_REPOSITORYファンクション の定義は以下の通りです。

> 218.5.9 DISPLAY_WORKLOAD_REPOSITORYファンクション
> https://docs.oracle.com/cd/F39414_01/arpls/DBMS_XPLAN.html#GUID-D416125C-FED5-4704-A371-B5EECFCE1429

```sql
DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY(
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER   DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT 'TYPICAL'
   dbid              IN      NUMBER   DEFAULT NULL,
   con_dbid          IN      NUMBER   DEFAULT NULL,
   awr_location      IN      VARCHAR2 DEFAULT 'AWR_ROOT'
);
```

上記 4. で確認した dbid と con_dbid を セットして DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY を実行します。

```sql
SET LINESIZE 300;
SET PAGESIZE 1000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY('4f7w8vvvdz6q5', NULL, 'ALL OUTLINE', 2198735651, 981234127, 'AWR_ROOT'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4f7w8vvvdz6q5
--------------------
select * from USERTABLDATA where Id = :1

Plan hash value: 1933442528

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| USERTABLDATA |     1 |  4159 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C009224  |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / "USERTABLDATA"@"SEL$1"
   2 - SEL$1 / "USERTABLDATA"@"SEL$1"

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      FORCE_XML_QUERY_REWRITE
      FORCE_JSON_TABLE_TRANSFORM
      XML_DML_RWT_STMT
      XMLINDEX_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      NO_COST_XML_QUERY_REWRITE
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "USERTABLDATA"@"SEL$1" ("USERTABLDATA"."ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:1)


45 rows selected.
```

DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY でうまく実行計画を取得できました。
彡(^)(^)

# 6. まとめ

DISPLAY_AWRファンクションは永年お役に立っていたので、
いつの間にか非推奨になってしまってたのは一抹の寂しさがありますやね……彡(-)(-)

とまれ替わりに DISPLAY_WORKLOAD_REPOSITORYファンクションをどんどん使って行きましょうね彡(^)(^)