FAQ になりそうな、12c MTA環境での統計履歴管理 ー CDBとPDBの間で迷子になりそう PART2

PART1ってあったっけ？　というのは置いといて、
PART III以降もなにかありそうなアトモスフィアなのですが、ここで書いておかないとハマりそうなので備忘録代わりに。

みなさん、統計履歴って知ってます？
dbms_stats.gather_*_stats を実行するとオプティマイザ統計情報が取得された表、索引、列統計が自動的にバックアップされることを。
そのバックアップは統計履歴と呼ばれています。（履歴統計と呼んでる人のほうが多いと思うのですが。。。マニュアルだと統計履歴って書いてますね。。。。誰も履歴統計を統計履歴だろ、それ！　と突っ込んでくれなてなかった気がw）

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) オプティマイザ統計の保存の管理
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1(12.1) DBMS_STATS.GET_STATS_HISTORY_RETENTIONファンクション
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1(12.1) DBMS_STATS.ALTER_STATS_HISTORY_RETENTIONプロシージャ
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1(12.1) PURGE_STATSプロシージャ
Oracle® Database SQL言語リファレンス 12cリリース1 (12.1) SYS_CONTEXT

で、オプティマイザ統計収集が原因と思われる事象があった場合、統計履歴をリストアしてトラブルを華麗に回避なんて手法もあるんです。
古くは小田さんの記事でも取り上げられています。
門外不出のOracle現場ワザ 第4章　Oracleデータベースの頭脳 「オプティマイザ」徹底研究

と、ここまでは、11gとか12cでも非MTA環境のお話です。

先日、12cのMTA環境で統計履歴をリストアしようとしたら。。。統計履歴。。。なんとなく言いづらい。。。履歴統計が無いw
履歴統計の保存期間はデフォルトで31日なのでパージされる前に保存期間を無期限に変更しました。。。（その時はMTA環境の罠にハマったとは気付かず、無期限にしたからもう気にしなくていいや！　と思ったんです）

ところが、いざ履歴統計をみると。。。。パージされてる。。。。。焦りましたw　

どういうことだったのか、最初に書いてしまうと、

履歴統計は、CDB/PDB、それぞれ独立して管理されてまっす！。

（え〜〜〜〜〜〜〜〜っ。知らんかったというか、マニュアルのどこかに記載されているのなら、どなたかそのページへのリンクをおしえてくだしぁ。。。）

久々に長〜い前置きはこれぐらいにして確認してみましょう。(--;

PDBが1つあるシングルテナント構成（1 PDB/CDB)で確認してみました。

CDB及び、PDBの履歴統計保存期間を確認します。（デフォルト設定のままです）
なにもしてないデフォルト状態では、CDBもPDBも履歴統計保存期間は31日となっています。

CDB側の履歴統計保存期間

SYSTEM@orcl12c> @show_stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              CDB$ROOT

stats history retention
-----------------------
                     31

PDB側の履歴統計保存期間

SYSTEM@pdborcl12c> @show_stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              PDBORCL12C

stats history retention
-----------------------
                     31

では最初にどちらも履歴統計を保存しない状態にしてみます。

CDB
履歴統計を保存なしに設定して既存履歴統計をパージ。
事前作成しておいた　stats_hist_retention.sqlを実行。（スクリプトはエントリの最後に記載しておきました。）

DBMS_STATS.GATHER_DATABASE_STATSを実行しても履歴統計数の増加なし。想定通り。

SYSTEM@orcl12c> exec dbms_stats.alter_stats_history_retention(0);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@orcl12c> exec dbms_stats.purge_stats(null);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@orcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              CDB$ROOT


stats history retention
-----------------------
                      0

PL/SQLプロシージャが正常に完了しました。


number of tab stats histories
-----------------------------
                            0

PDB

SYSTEM@pdborcl12c> exec dbms_stats.alter_stats_history_retention(0);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@pdborcl12c> exec dbms_stats.purge_stats(null);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@pdborcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              PDBORCL12C

stats history retention
-----------------------
                      0

PL/SQLプロシージャが正常に完了しました。

number of tab stats histories
-----------------------------
                            0

さて、次にCDBの履歴統計保存期間を無期限に変更し、PDBは履歴統計保存なしにしてみます。

CDBの履歴統計数は増加し、PDB側は変化なしということになるはず。。。

CDB

SYSTEM@orcl12c> exec dbms_stats.alter_stats_history_retention(-1);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@orcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              CDB$ROOT

stats history retention
-----------------------
                     -1

PL/SQLプロシージャが正常に完了しました。

number of tab stats histories
-----------------------------
                         2499

CDB側の履歴統計数が増加した。まあ、そうだよね〜。

PDB
PDB側は履歴統計無しのままなので変化はないです。個別に設定できてるからそうなんでしょうね。多分。

SYSTEM@pdborcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              PDBORCL12C


stats history retention
-----------------------
                      0

PL/SQLプロシージャが正常に完了しました。

number of tab stats histories
-----------------------------
                            0

逆にしてみます。CDBは履歴統計無し、PDBの履歴統計保存期間を無期限にしてみます。
履歴統計はCDB/PDB個別管理ならCDBの履歴統計なし、PDB側には履歴統計ありという構成もできるはず！！

なお、確認しやすくするために、CDB側は履歴統計無しに変更後、履歴統計を手動パージしておきます。

CDB

SYSTEM@orcl12c> exec dbms_stats.alter_stats_history_retention(0);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@orcl12c> exec dbms_stats.purge_stats(null);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@orcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              CDB$ROOT

stats history retention
-----------------------
                      0

PL/SQLプロシージャが正常に完了しました。

number of tab stats histories
-----------------------------
                            0

PDB
これが確認できれば問題なさそうですね。CDB/PDBの履歴統計保存期間の管理及びパージは個別管理できるってことで。。。。
Workload RepositoryはCDBでのみ管理できるようになっているので間違いやすいぞ、と
この辺りをまとめたマニュアルがあれば解りやすいのになぁ（ボソっ

SYSTEM@pdborcl12c> exec dbms_stats.alter_stats_history_retention(-1);

PL/SQLプロシージャが正常に完了しました。

SYSTEM@pdborcl12c> @stats_hist_retention

cdb name             container name
-------------------- --------------------
orcl12c              PDBORCL12C

stats history retention
-----------------------
                     -1

PL/SQLプロシージャが正常に完了しました。

number of tab stats histories
-----------------------------
                         2659

履歴統計は、CDB/PDB個別管理なので、履歴統計保存期間、履歴統計のパージはCDB/PDBそれぞれで行えるようですが、
Workload Repository：AWRのスナップショットの保存期間やパージなどは、CDBのみでしか行えない。（AWRのスナップショットはCDB/PDBどこで実行しても全体が取得されます。）

それに、PDBで変更できる初期化パラメータと変更できないパラメータのまとめがあればもっと便利かも。
以前調べたPDB側で変更できる初期化パラメータの保存場所とかも合わせてまとめとくか。。。

PDB毎に初期化パラメータを変更できるんですよ！（制限はあるけど） #1
PDB毎に初期化パラメータを変更できるんですよ！（制限はあるけど） #2 - PDBの初期化パラメータは何処！？
PDB毎に初期化パラメータを変更できるんですよ！（制限はあるけど） #3 - 消えたPDBの初期化パラメータの謎... Truth is out there.

いろいろ整理できてないので只今混乱中w

---

今回試した環境

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

使ったクエリーなど
stats_hist_retention.sql

SELECT 
	SYS_CONTEXT('USERENV', 'CDB_NAME') AS "cdb name"
	, SYS_CONTEXT('USERENV', 'CON_NAME') AS "container name" 
FROM 
	dual;
	
SELECT 
	DBMS_STATS.GET_STATS_HISTORY_RETENTION AS "stats history retention" 
FROM 
	dual;

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

SELECT 
	COUNT(1) AS "number of tab stats histories" 
FROM 
	dba_tab_stats_history;

show_stats_hist_retention.sql

SELECT 
	SYS_CONTEXT('USERENV', 'CDB_NAME') AS "cdb name"
	, SYS_CONTEXT('USERENV', 'CON_NAME') AS "container name" 
FROM 
	dual;

SELECT 
	DBMS_STATS.GET_STATS_HISTORY_RETENTION AS "stats history retention" 
FROM 
	dual;