表題の通り 転送(エクスポート/インポート)した 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 に格納されている 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' を使用します。
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
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 を実行します。
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ファンクションをどんどん使って行きましょうね彡(^)(^)