リソースマネージャ：MTA環境のインスタンスケージングが効いているかざっくり確認するスクリプト

元になるビューは１分間隔で更新されているようで秒単位の粒度では確認できないもののざっくりでもいいからリアルタムに確認したい場合には便利

むかーしやった検証はnon CDBかつ、秒単位で見たいという要望だったため他のビューから算出したこともあり、ここで利用しているv$rsrcmetricの類は使わなかったことを思い出した。
完全に忘れてた(@@)。
２年も触らなきゃ忘れるさ。人間だものw　orz.

ということで、 Oracle Database 12c 12.1.0.2.0 向けのメモ

注）以下、CDB$ROOTに接続してSQL文を実行しています

利用環境は以下の通りのMTA

orcl12c@SYS> select * from v$version;

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

CPUは4 (VMですが）

orcl12c@SYS> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4

ORCLとORCL2という２つのPDB対してCPUのUTILIZATION_LIMITでCPU利用率を制限しています。
12.2以降はPDBのインスタンスケージングもnon-CDB環境同様に、CPU_COUNT設定＋リソースマネージャで制御できちゃうらしい。わかりやすくて良い！（時間があればいずれ）

orcl12c@SYS> select name from v$containers;

NAME
------------------------------
CDB$ROOT
PDB$SEED
ORCL
ORCL2


orcl12c@SYS> r
  1  SELECT
  2      vc.name
  3      , vp.utilization_limit
  4  FROM
  5      v$rsrc_plan vp
  6      INNER JOIN v$containers vc
  7      ON vp.con_id=vc.con_id
  8  ORDER BY
  9*     vc.con_id

NAME                           UTILIZATION_LIMIT
------------------------------ -----------------
CDB$ROOT
PDB$SEED
ORCL                                          25
ORCL2                                         50

それぞれのPDBでCPU数以上のCPUバウンドな処理を実行して負荷かけ中

resmgr:cpu quantumとう待機イベントはresource managerがCPUの利用率を制御していることを示す待機イベント！
リソース制御が効いていることを示してます。

orcl12c@SYS> select username,event from v$session where username is not null order by username;

USERNAME       EVENT
-------------- ----------------------------------------------------------------
SYS            Streams AQ: waiting for messages in the queue
SYS            Streams AQ: waiting for messages in the queue
SYS            SQL*Net message from client
SYS            Streams AQ: waiting for messages in the queue
SYS            Streams AQ: waiting for messages in the queue
SYS            resmgr:cpu quantum
SYS            SQL*Net message to client
SYS            SQL*Net message from client
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum
TOTHER01       resmgr:cpu quantum

vmstatでは全体のCPU利用率は見えますが複数のPDBが想定通りケージングされているかは見えません。

$ vmstat -t 5 1000
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- -----timestamp-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st                 JST
12  0      0 1487628  55660 4727120    0    0   125    13  452  262 21  1 78  0  0 2017-06-29 23:48:16
11  0      0 1480868  55668 4727176    0    0     0    28 3817 1459 69  2 29  0  0 2017-06-29 23:48:21
11  0      0 1480900  55676 4727068    0    0     0    16 3982 1480 73  2 25  0  0 2017-06-29 23:48:26
13  0      0 1480948  55684 4727008    0    0     0    17 3722 1383 68  2 30  0  0 2017-06-29 23:48:31
 1  0      0 1480956  55684 4726980    0    0     0    10 3765 1410 69  2 29  0  0 2017-06-29 23:48:36
 3  0      0 1480956  55692 4726984    0    0     0    18 3886 1417 76  2 22  0  0 2017-06-29 23:48:41
10  0      0 1480336  55716 4727044    0    0     0    35 3830 1441 69  2 29  0  0 2017-06-29 23:48:46
 0  0      0 1480336  55724 4727036    0    0     0    24 4033 1536 76  2 22  0  0 2017-06-29 23:48:51
14  0      0 1469888  55724 4727100    0    0     0    14 3722 1508 67  2 31  0  0 2017-06-29 23:48:56
11  0      0 1459988  55732 4727384    0    0     0    17 4049 1685 73  3 24  0  0 2017-06-29 23:49:01
13  0      0 1419652  55740 4728940    0    0   134    38 4299 1901 80  3 16  0  0 2017-06-29 23:49:06
10  0      0 1419676  55748 4728860    0    0     0    21 3861 1491 69  2 29  0  0 2017-06-29 23:49:11
10  0      0 1419768  55756 4729044    0    0     0    28 3914 1471 71  2 27  0  0 2017-06-29 23:49:16
12  0      0 1421380  55764 4729064    0    0     0    31 3697 1445 68  2 31  0  0 2017-06-29 23:49:21
11  0      0 1421412  55764 4729016    0    0     0    13 3735 1412 68  2 30  0  0 2017-06-29 23:49:26
10  0      0 1421544  55772 4728980    0    0     0    18 3986 1521 73  2 25  0  0 2017-06-29 23:49:31
11  0      0 1464824  55780 4728524    0    0     0    11 3702 1372 68  2 30  0  0 2017-06-29 23:49:36
10  0      0 1458468  55788 4728572    0    0     1    20 4030 1495 74  2 24  0  0 2017-06-29 23:49:41
10  0      0 1458624  55796 4728452    0    0     0    17 3836 1520 68  2 29  0  0 2017-06-29 23:49:46
 0  0      0 1458880  55804 4728400    0    0     0    20 3908 1522 70  2 28  0  0 2017-06-29 23:49:51
 0  0      0 1458864  55804 4728424    0    0     0    13 4039 1453 76  2 21  0  0 2017-06-29 23:49:56
 0  0      0 1458864  55812 4728420    0    0     0    18 3870 1471 70  2 28  0  0 2017-06-29 23:50:01
 1  0      0 1454680  55820 4728804    0    0     2    16 4541 2070 76  4 20  0  0 2017-06-29 23:50:06
 1  0      0 1454728  55828 4728688    0    0     0    29 3829 1453 70  2 28  0  0 2017-06-29 23:50:11
 9  0      0 1454048  55836 4728596    0    0     0    18 3751 1415 69  2 29  0  0 2017-06-29 23:50:16
10  0      0 1454056  55836 4728548    0    0     0    15 3867 1472 72  2 26  0  0 2017-06-29 23:50:21
11  0      0 1454088  55844 4728480    0    0     0    21 3704 1390 67  2 31  0  0 2017-06-29 23:50:26
10  0      0 1454252  55852 4728396    0    0     0    17 3774 1391 70  2 28  0  0 2017-06-29 23:50:31
10  0      0 1465692  55860 4728416    0    0     0    24 3914 1484 71  2 26  0  0 2017-06-29 23:50:36
10  0      0 1465468  55868 4728380    0    0     0    18 3794 1435 68  2 30  0  0 2017-06-29 23:50:41

最初に書いたように、１分間隔で少々粒度は粗めですがざっくり各PDBのCPU利用率を確認する場合には以下のようなスクリプトが便利！！
以下クエリをshellで定期的に実行するなり、随時実行するなりして確認すると便利、なお、gv$rsrcmgrmetricの代わりに、gv$rsrcmgrmetric_historyを
利用すれば過去１時間分だけですが、遡って確認することもできます。

orcl12c@SYS> !cat show_con_cpu.sql
SELECT
    to_char(begin_time, 'RR/MM/DD HH24:MI:SS') AS time
    , vc.con_id
    , vc.name
    , (SELECT value FROM v$osstat WHERE stat_name = 'NUM_CPUS') AS "CPUs"
    , round((sum(cpu_consumed_time) / 1000) / (60 * (SELECT value FROM v$parameter WHERE name = 'cpu_count'))*100,2) AS "%cpu"
FROM
    gv$rsrcmgrmetric gvr
    INNER JOIN v$containers vc
    ON
        vc.con_id = gvr.con_id
GROUP BY
    vc.con_id
    ,vc.name
    ,begin_time
ORDER BY
    begin_time
    ,vc.con_id
/

各PDBが25%、50%で制限されていることがわかります！！

orcl12c@SYS> @show_con_cpu.sql

TIME                  CON_ID NAME                                 CPUs       %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:48:30          1 CDB$ROOT                                4        .32
17/06/29 23:48:30          2 PDB$SEED                                4          0
17/06/29 23:48:30          3 ORCL                                    4      25.87
17/06/29 23:48:30          5 ORCL2                                   4       51.3

orcl12c@SYS> @show_con_cpu.sql

TIME                  CON_ID NAME                                 CPUs       %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:49:31          1 CDB$ROOT                                4        .03
17/06/29 23:49:31          2 PDB$SEED                                4          0
17/06/29 23:49:31          3 ORCL                                    4      25.31
17/06/29 23:49:31          5 ORCL2                                   4      50.74

しかし、おとといの無茶振りというか、俺、何スレッドで動作すればいいの的な、パススルーな振りに、疲れて
こんなネタにしてみましたよ。と。w

CDBとPDBの間で迷子になりそう PART3 - containers clause - その3

Previously on Mac De Oracle.

AWRでなんとか発行したSQL文以外に、containers句を元とする再帰SQL文を捕まえられたのはいいが、その再帰SQL文と元となるcontainers句を紐づけることが難しそう。
また、ヒントでチューニングするのも難しそう（これはそれっぽいのがあるけどまだ調べてないのでどこまでできるのか未知の領域）。
できるとすれば、SPMによるチューニングかな？、というところまではなんとか見えてきました。

---

AWRではcontainers句で生成される再帰SQL文を特定するのに難ありというところまでは見えてきたので、
最後の希望w　SQLトレースはどうなのか、気になっていたので試した見た。
（利用している表とSQL文は多少変えていますが、大きな差はありません）

SQLトレースをセッションレベルで有効化して（トレースファイルを特定しやすくしておくことをお忘れなく）、containers句を含むSQL文を実行して、SQLトレースを無効化。

orcl12c@C##HOGE> alter session set tracefile_identifier=containers;
orcl12c@C##HOGE> exec dbms_monitor.session_trace_enable(waits=>true); 
   
PL/SQL procedure successfully completed.

orcl12c@C##HOGE> select * from containers(hoge) where id in (1,3);

        ID DATA               CON_ID
---------- -------------- ----------
         3 test3                   4
         1 test1                   3

orcl12c@C##HOGE> exec dbms_monitor.session_trace_disable;

ここで、前々回、SQL監視のParallel Execution Detailsセクションをみてみると、スレーブプロセスがどうなっていたかわかりやすいですね！

Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |  Other   | Buffer | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Gets  | (sample #)  |
==========================================================================================
| PX Coordinator | QC    |         |    0.00 |    0.00 |     0.00 |        |             |
| p000           | Set 1 |       1 |    0.00 |    0.00 |          |        |             |
| p001           | Set 1 |       2 |    0.00 |         |     0.00 |        |             |
| p002           | Set 1 |       3 |    0.00 |         |     0.00 |      3 |             |
| p003           | Set 1 |       4 |    0.00 |    0.00 |          |      3 |             |
==========================================================================================

SQLトレースファイルも各プロセスごとの取得されていることがわかります!!!
contrainers句から生成される再帰SQLはAWRレポートより見つけやすいかもしれない（個人差はあると思われるw） :)

Cp000〜p003と前述のSQL監視の情報と合わせて見ると多少は見やすいですよね。（AWRレポートのみで捉えることと比べたら遥かに面倒だとは思いますが）

[oracle@vbgeneric admin]$ cd $ORACLE_BASE/diag/rdbms/orcl12c/orcl12c/trace/
[oracle@vbgeneric trace]$ ls -l *CONTAINERS*
-rw-r----- 1 oracle oinstall  95231  4月 1 11:36 orcl12c_ora_4609_CONTAINERS.trc
-rw-r----- 1 oracle oinstall    875  4月 1 11:36 orcl12c_ora_4609_CONTAINERS.trm
-rw-r----- 1 oracle oinstall  23140  4月 1 11:36 orcl12c_p000_3375_CONTAINERS.trc
-rw-r----- 1 oracle oinstall    197  4月 1 11:36 orcl12c_p000_3375_CONTAINERS.trm
-rw-r----- 1 oracle oinstall   2942  4月 1 11:36 orcl12c_p001_3377_CONTAINERS.trc
-rw-r----- 1 oracle oinstall    149  4月 1 11:36 orcl12c_p001_3377_CONTAINERS.trm
-rw-r----- 1 oracle oinstall  98455  4月 1 11:35 orcl12c_p002_3379_CONTAINERS.trc
-rw-r----- 1 oracle oinstall    705  4月 1 11:35 orcl12c_p002_3379_CONTAINERS.trm
-rw-r----- 1 oracle oinstall 168368  4月 1 11:35 orcl12c_p003_3381_CONTAINERS.trc
-rw-r----- 1 oracle oinstall   1138  4月 1 11:35 orcl12c_p003_3381_CONTAINERS.trm
[oracle@vbgeneric trace]$ 

とりあえず、のぞいて見ましょう。

[oracle@vbgeneric trace]$ tkprof orcl12c_ora_4609_CONTAINERS.trc orcl12c_ora_4609_container.txt explain=system/oracle@orcl12c sys=yes waits=yes

TKPROF: Release 12.1.0.2.0 - Development on Sat Apr 1 11:47:26 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

[oracle@vbgeneric trace]$ tkprof orcl12c_p000_3375_CONTAINERS.trc container_p000.txt explain=system/oracle@orcl12c sys=yes waits=yes

・・・中略・・・

[oracle@vbgeneric trace]$ tkprof orcl12c_p003_3381_CONTAINERS.trc container_p003.txt explain=system/oracle@orcl12c sys=yes waits=yes

TKPROF: Release 12.1.0.2.0 - Development on Sat Apr 1 11:47:26 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

[oracle@vbgeneric trace]$ ls -l *container*
-rw-r--r-- 1 oracle oinstall 47736  4月 1 11:47 orcl12c_ora_4609_container.txt
-rw-r--r-- 1 oracle oinstall  7104  4月 1 11:47 container_p000.txt
-rw-r--r-- 1 oracle oinstall  5228  4月 1 11:47 container_p001.txt
-rw-r--r-- 1 oracle oinstall 34869  4月 1 11:47 container_p002.txt
-rw-r--r-- 1 oracle oinstall 38321  4月 1 11:48 container_p003.txt
[oracle@vbgeneric trace]$ 

最初にorcl12c_ora_4609_container.txtから見てみますか。
SQLトレースを有効化して、containers句を含むSQL文を発行、それに伴いいくつかの再帰SQL文、最後にSQLトレースを無効化してる流れは確認できます。
途中、再帰SQLとしてhoge表を問い合わせるために利用すると思われるSQL文をパースだけしているとみられる箇所。興味深いです:)

・・・中略・・・

SQL ID: g2nujq01pmynd Plan Hash: 0

SELECT /* */ * 
FROM
 "C##HOGE"."HOGE"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
・・・中略・・・

この部分実際にPDBで実行される想定の再帰SQL文に近いけど、件数カウントしてるだけ..なんですね。なんで件数が必要なんだろ

SELECT count(*) 
FROM
 C##HOGE."HOGE" "HOGE" WHERE "HOGE"."ID"=1 OR "HOGE"."ID"=3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=14 us)
         0          0          0   CONCATENATION  (cr=0 pr=0 pw=0 time=7 us)
         0          0          0    INDEX UNIQUE SCAN SYS_C0010012 (cr=0 pr=0 pw=0 time=4 us)(object id 93050)
         0          0          0    INDEX UNIQUE SCAN SYS_C0010012 (cr=0 pr=0 pw=0 time=0 us)(object id 93050)

********************************************************************************
・・・中略・・・

そして実際にタイプしたSQL文の登場
実行計画は実行しているSQL文からは想像できない変わり果てた姿となっていて、実際に参照する表やオペレーションではなく、hoge表をアクセスしてる実行計画ではないのは見ての通り。

SQL ID: 25a37y74xrat4 Plan Hash: 1439328272

select * 
from
 containers(hoge) where id in (1,3)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          1           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.00       0.12          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.14          0          3          1           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  (C##HOGE)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  PX COORDINATOR  (cr=0 pr=0 pw=0 time=133547 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         0          0          0    PX PARTITION LIST ALL PARTITION: 1 254 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         0          0          0     FIXED TABLE FULL X$CDBVW$ (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)

パラレルクエリーの各スレーブプロセスはどうかというと...

p000のトレース（抜粋）

rowsが0、Fetch countも0、ふーん、なるほど。

SQL ID: 25a37y74xrat4 Plan Hash: 1439328272

select * 
from
 containers(hoge) where id in (1,3)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.06          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.06          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  (C##HOGE)   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         0          0          0    PX PARTITION LIST ALL PARTITION: 1 254 (cr=2 pr=0 pw=0 time=56179 us cost=-9223372036854775808 size=78 card=1)
         0          0          0     FIXED TABLE FULL X$CDBVW$ (cr=2 pr=0 pw=0 time=53500 us cost=-9223372036854775808 size=78 card=1)

AWR SQLレポートで捕まえた再帰SQL文、これで実際に表をアクセスしてデータを取得していると考えられる。
OR句は、index unique scanをUNION、ヒントだと USE_CONCATヒントを利用したのと同じ実行計画になってる。

Fetch=1で、rows=0、ふふふーん。　0件ということは、これはデータなし！、どちらのPDBにもヒットするデータが１件ある。
データがないのは、SEEDとCDBのみなので、それのいずれかということ。

そして、HOGEという表は、CDB$ROOTと残る２つのPDBにある。また、CDB$ROOTのHOGE表はcontainers句を動作させるための前提条件なので空の表！

なので、p000はROOT$CDBのHOGE表を問い合わせていることになる！！！のか？！

SQL ID: 1u03tbqvywyf8 Plan Hash: 3444470255

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,DATA 
FROM
 "C##HOGE"."HOGE" "HOGE" WHERE "HOGE"."ID"=1 OR "HOGE"."ID"=3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  RESULT CACHE  2fmgs0vhj7brb4c44nkh7shu6f (cr=2 pr=0 pw=0 time=76 us)
         0          0          0   CONCATENATION  (cr=2 pr=0 pw=0 time=38 us)
         0          0          0    TABLE ACCESS BY INDEX ROWID HOGE (cr=1 pr=0 pw=0 time=19 us)
         0          0          0     INDEX UNIQUE SCAN SYS_C0010012 (cr=1 pr=0 pw=0 time=11 us)(object id 93050)
         0          0          0    TABLE ACCESS BY INDEX ROWID HOGE (cr=1 pr=0 pw=0 time=10 us)
         0          0          0     INDEX UNIQUE SCAN SYS_C0010012 (cr=1 pr=0 pw=0 time=2 us)(object id 93050)

p001のトレース（抜粋）

不思議ですが、これは、元のクエリーがあるけど、データを取得する SQLID=1u03tbqvywyf8（前述したRESULT_CACHEヒントのついてるクエリ）が生成されていない！！
containers句を含むクエリのみあり、rowsも0、HOGEをといあわせる再帰SQL文を生成する必要のない表。HOGE表が存在しないのは今のところSEEDのみ。
HOGE表が存在しないので、　C##HOGE.HOGE表をアクセスするSQL文を実行したらエラーですからねぇ。無駄なことはしないはず。

ふむふむふむ。

・・・中略・・・

SQL ID: 25a37y74xrat4 Plan Hash: 1439328272

select * 
from
 containers(hoge) where id in (1,3)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  (C##HOGE)   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         0          0          0    PX PARTITION LIST ALL PARTITION: 1 254 (cr=0 pr=0 pw=0 time=189 us cost=-9223372036854775808 size=78 card=1)
         0          0          0     FIXED TABLE FULL X$CDBVW$ (cr=0 pr=0 pw=0 time=12 us cost=-9223372036854775808 size=78 card=1)

p002のトレースファイル（抜粋）

・・・中略・・・

SQL ID: 25a37y74xrat4 Plan Hash: 1439328272

select * 
from
 containers(hoge) where id in (1,3)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.05          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.05          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  (C##HOGE)   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         1          1          1    PX PARTITION LIST ALL PARTITION: 1 254 (cr=961 pr=0 pw=0 time=121936 us cost=-9223372036854775808 size=78 card=1)
         1          1          1     FIXED TABLE FULL X$CDBVW$ (cr=961 pr=0 pw=0 time=121610 us cost=-9223372036854775808 size=78 card=1)

・・・中略・・・

お！　Fetch=1で、rows=1 HOGE表から1行フェッチしているので２つのPDBのいずれかということになりますね！！
ORのUNION ALLへの書き換えがOR条件の順序通りであれば、以下の赤字部分の結果からみればたぶん、ID=3のでデータが存在している方のPDBであるはず。

SQL ID: 1u03tbqvywyf8 Plan Hash: 1725204971

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,DATA 
FROM
 "C##HOGE"."HOGE" "HOGE" WHERE "HOGE"."ID"=1 OR "HOGE"."ID"=3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  RESULT CACHE  6158j5h7rmww7g9fxb6fwawgvy (cr=3 pr=0 pw=0 time=120 us)
         1          1          1   CONCATENATION  (cr=3 pr=0 pw=0 time=44 us)
         0          0          0    TABLE ACCESS BY INDEX ROWID HOGE (cr=1 pr=0 pw=0 time=15 us)
         0          0          0     INDEX UNIQUE SCAN SYS_C0014566 (cr=1 pr=0 pw=0 time=9 us)(object id 99927)
         1          1          1    TABLE ACCESS BY INDEX ROWID HOGE (cr=2 pr=0 pw=0 time=18 us)
         1          1          1     INDEX UNIQUE SCAN SYS_C0014566 (cr=1 pr=0 pw=0 time=5 us)(object id 99927)

p003もp002のトレース同様　Fetch=1で、rows=1 HOGE表から1行フェッチしているので２つのPDBのいずれかということになりますね！！
p003のSQLトレースファイル

・・・中略・・・


SQL ID: 25a37y74xrat4 Plan Hash: 1439328272

select * 
from
 containers(hoge) where id in (1,3)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  (C##HOGE)   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=-9223372036854775808 size=78 card=1)
         1          1          1    PX PARTITION LIST ALL PARTITION: 1 254 (cr=1793 pr=0 pw=0 time=119492 us cost=-9223372036854775808 size=78 card=1)
         1          1          1     FIXED TABLE FULL X$CDBVW$ (cr=1793 pr=0 pw=0 time=119279 us cost=-9223372036854775808 size=78 card=1)

・・・中略・・・

plan hash = 0 でParse = 1なのでやはりパースだけ。

SQL ID: g2nujq01pmynd Plan Hash: 0

SELECT /* */ * 
FROM
 "C##HOGE"."HOGE"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

・・・中略・・・

やはり、rows=1 これも１行フェッチしているのでデータ登録済みの2PDBのいずれかで実行されているクエリだという点は間違いないらしい
そして、赤字部分のrowsからORの順番からして、ID=1のデータのあるPDBか。

SQL ID: 1u03tbqvywyf8 Plan Hash: 1990946707

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,DATA 
FROM
 "C##HOGE"."HOGE" "HOGE" WHERE "HOGE"."ID"=1 OR "HOGE"."ID"=3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  RESULT CACHE  24q4fuaf71g7jf1upwg76y3a3q (cr=3 pr=0 pw=0 time=94 us)
         1          1          1   CONCATENATION  (cr=3 pr=0 pw=0 time=49 us)
         1          1          1    TABLE ACCESS BY INDEX ROWID HOGE (cr=2 pr=0 pw=0 time=23 us)
         1          1          1     INDEX UNIQUE SCAN SYS_C0010048 (cr=1 pr=0 pw=0 time=11 us)(object id 92602)
         0          0          0    TABLE ACCESS BY INDEX ROWID HOGE (cr=1 pr=0 pw=0 time=11 us)
         0          0          0     INDEX UNIQUE SCAN SYS_C0010048 (cr=1 pr=0 pw=0 time=5 us)(object id 92602)

SQLトレースで見たのが一番追いやすかったのですが（個人的に）、実践でこれをやるのは、少々辛いのでcontainers句を業務APで使っててチューニングという状況には遭遇したくないなw）

containers句の場合、実際にユーザ表にアクセスするために生成される再起SQL部分をいかにして特定するかが、
チューニング前の課題になってくるのは間違いなさそう。（今の所は）
SQLレポートやSQL監視が進化して、containsers句の場合は該当再起SQL文と実行計画も表示してくれたら楽かもしれないですけどね（それはAWRレポートも同様）

ということで、今日はここまで。

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編 - その２

CDBとPDBの間で迷子になりそう　PART3 - containers clause

CDBとPDBの間で迷子になりそう　PART3 - containers clause - その２

CDBとPDBの間で迷子になりそう PART3 - containers clause - その２

Previously on Mac De Oracle.
CDBとPDBの間で迷子になりそう　PART3 - containers clause

昨日は、AWRで再帰SQL文として各PDBで実行されるSQL文を捉えた！ところまででした。

---

今日はそのAWRレポートを見てみようと思います。

それらしきSQL文は、AWRレポートのSQL ordered by User I/O Wait Timeなど幾つかのセクションで見つかった！！！（テキストフォーマットで出力しています）

元のSQL文で付加したコメントは再帰SQL文にはまったく引きづがれないので、多くのSQL文をcontainers句で実行していたら紐づけるの大変そうだろうという予感とともに、ヒントによるチューニングってほぼ無理なんじゃないかって思います。
（RDF Graph系の似たような句というか関数だと幾つかのヒントを渡せる仕組みもあるんですがねぇ、これは無理っぽい）

使えるとすればSPMかな。

で、recult cacheも使うようなので、そのあたりの待機イベントも多少気にかける必要もでてくるかもしれない。
と、とーくを見ながら...

どう使うか次第でしょうけど、もし、チューニングが必要な状態になったら辛くなりそうだ！、という気配だけは強く感じたところで、今日は時間切れ。

SQL ordered by User I/O Wait Time       DB/Inst: ORCL12C/orcl12c  Snaps: 71-72
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - User I/O Time as a percentage of Total User I/O Wait time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   44.2% of Total User I/O Wait Time (s):
-> Captured PL/SQL account for   39.8% of Total User I/O Wait Time (s):

  User I/O                UIO per           Elapsed
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------

・・・中略・・・

       0.0            1       0.00    2.6        0.0   92.9    6.9 fj6g3jvma49dq
Module: SQL*Plus
select /* test01 */ * from containers(emp) where empno=7369

・・・中略・・・

       0.0            1       0.00    1.1        0.0   88.2   11.4 0n3ta15r2qc98
Module: SQL*Plus
   PDB: PDBORCL12C
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ EMPNO,ENAME,JOB,MGR,HIREDA
TE,SAL,COMM,DEPTNO FROM "C##HOGE"."EMP" "EMP" WHERE "EMP"."EMPNO"=7369

       0.0            1       0.00    1.1        0.0   88.2   11.4 0n3ta15r2qc98
Module: SQL*Plus
   PDB: PDBORCL12C
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ EMPNO,ENAME,JOB,MGR,HIREDA
TE,SAL,COMM,DEPTNO FROM "C##HOGE"."EMP" "EMP" WHERE "EMP"."EMPNO"=7369

       0.0            1       0.00    1.0        0.0   89.8   12.0 0n3ta15r2qc98
Module: SQL*Plus
   PDB: PDBORCL12CLONED
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ EMPNO,ENAME,JOB,MGR,HIREDA
TE,SAL,COMM,DEPTNO FROM "C##HOGE"."EMP" "EMP" WHERE "EMP"."EMPNO"=7369

       0.0            1       0.00    1.0        0.0   89.8   12.0 0n3ta15r2qc98
Module: SQL*Plus
   PDB: PDBORCL12CLONED
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ EMPNO,ENAME,JOB,MGR,HIREDA
TE,SAL,COMM,DEPTNO FROM "C##HOGE"."EMP" "EMP" WHERE "EMP"."EMPNO"=7369

参考までに、各SQLの実行計画を(AWR SQLレポートより）

Execution Plan
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |       |       |     1 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |     1 |   100 |       |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |     1 |   100 |       |     1 |   254 |  Q1,00 | PCWC |            |
|   4 |     FIXED TABLE FULL    | X$CDBVW$ |     1 |   100 |       |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
fj6g3jvma49d select /* test01 */ * from containers(emp) where empno=7369



Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |     1 (100)|
|   1 |  RESULT CACHE                | bxqp7vj85m72tbusfcmqynsj1t |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                        |     1 |    87 |     0   (0)|
|   3 |    INDEX UNIQUE SCAN         | PK_EMP                     |     1 |       |     0   (0)|
------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
0n3ta15r2qc9 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ EMPNO, ENAM
             E, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM "C##HOGE"."EMP" "EM
             P" WHERE "EMP"."EMPNO"=7369

次回でこのシリーズは最終回にするかも。。もう１つ２つ追加するかも。。。

To be continued.

---

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編 - その２

CDBとPDBの間で迷子になりそう　PART3 - containers clause

CDBとPDBの間で迷子になりそう PART3 - containers clause

Previously on Mac De Oracle.

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編 - その２
auto traceでcontainers句の実行計画をながめたら、原型を留めないほど、変わり果てた実行計画をみて、ここから原型の実行計画をどうやったら見つけられるんだろうかと、途方にくれたところまででしたw

---

パラレル実行され、各pdbのemp表からempno=7369のデータがindex range scanで取得されているはず。
そして、再帰SQL文として、原文が各PDBで実行されているんだろうなぁ。
といところまではマニュアルの記述と、変わり果てた実行計画をみて、なんとなくイメージはできるんですが。。。

では、どうやったら各PDBで実行されているはずのSQL文や実行計画が見えるんだろう？？

auto traceでは見ることはできなかったので、次！、SQL監視で試してみます。（ダメだと思いますが、念のため）

C##HOGE@orcl12c> select /*+ monitor */ * from containers(emp) where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     CON_ID
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20          4
      7369 SMITH      CLERK           7902 80-12-17        800                    20          3

SQL監視をテキストモードで取得する自作スクリプトを実行して、実行計画を見ると....
んーっ。パラレルであるところは気づきやすいですが、やはり、原型を留めぬ変わり果てた実行計画しかみえませんねぇ。

C##HOGE@orcl12c> @show_realplan
1に値を入力してください: 
旧   1: select dbms_sqltune.report_sql_monitor('&1') from dual
新   1: select dbms_sqltune.report_sql_monitor('') from dual

DBMS_SQLTUNE.REPORT_SQL_MONITOR('')
--------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ * from containers(emp) where empno=7369

・・・中略・・・

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.01 |    0.01 |     0.00 |     2 |      6 |
=================================================

Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |  Other   | Buffer | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Gets  | (sample #)  |
==========================================================================================
| PX Coordinator | QC    |         |    0.00 |    0.00 |     0.00 |        |             |
| p000           | Set 1 |       1 |    0.00 |    0.00 |          |        |             |
| p001           | Set 1 |       2 |    0.00 |         |     0.00 |        |             |
| p002           | Set 1 |       3 |    0.00 |         |     0.00 |      3 |             |
| p003           | Set 1 |       4 |    0.00 |    0.00 |          |      3 |             |
==========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1439328272)
===================================================================================================================================
| Id |         Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                           |          | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
===================================================================================================================================
|  0 | SELECT STATEMENT          |          |         |      |         1 |     +0 |     5 |        2 |          |                 |
|  1 |   PX COORDINATOR          |          |         |      |         1 |     +0 |     5 |        2 |          |                 |
|  2 |    PX SEND QC (RANDOM)    | :TQ10000 |       1 |      |         1 |     +0 |     4 |        2 |          |                 |
|  3 |     PX PARTITION LIST ALL |          |       1 |      |         1 |     +0 |     4 |        2 |          |                 |
|  4 |      FIXED TABLE FULL     | X$CDBVW$ |       1 |      |         1 |     +0 |     4 |        2 |          |                 |
===================================================================================================================================

次は、AWRレポートでチャレンジ！

SYS@orcl12c> exec dbms_workload_repository.create_snapshot;

・・・中略・・・

C##HOGE@orcl12c> show con_name

CON_NAME
------------------------------
CDB$ROOT

C##HOGE@orcl12c> select * from containers(emp) where empno=7369;

・・・中略・・・

SYS@orcl12c> exec dbms_workload_repository.create_snapshot;

・・・中略・・・

SYS@orcl12c> @?/rdbms/admin/awrrpt

・・・中略・・・

....おかしいなぁ。捕まえられない！

AWRレポートで捕まえることができなかったのは環境上の影響(Shared Pool Sizeが小さ過ぎでエージアウトしちゃった、とか）もあったのかもしれません。しかもいろいろ動いていたので辛かったはず。
Shared Pool Sizeの下限値を大きめにして、

再チャレンジ。。。ん？　

こんどは取れたっぽい。原型とは多少違いますが、かなり近いSQL文が見つかりました！　そいつに違いない！！

というところで、今日は時間切れ、To be continued....

---

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編 - その２

CDBとPDBの間で迷子になりそう PART3 - SQL*PlusのAutotrace編 - その２

間が空いてしまいましたが、

CDBとPDBの間で迷子になりそう　PART3 - SQL*PlusのAutotrace編

のつづきです。

---

前回は、共通ユーザを作成し、CDB$ROOT、及び残りの2つのPDBでauto traceができるようになって、さりげなく、containers句なんてのを試していたところまででした。

今日はcontainers句で遊びはじめるところからスタート！
まず初めに、containters句でなにができるのかマニュアルで確認しておきましょう。（マニュアル読めよ！ お約束w

45.10 CONTAINERS句を使用したコンテナ間の問合せ
http://docs.oracle.com/cd/E82638_01/ADMIN/viewing-information-about-cdbs-and-pdbs-with-sql-plus.htm#GUID-B302A0DA-8A56-4C18-B140-ADD5E682DE60

45.10.1 CONTAINERS句を使用したコンテナ間の問合せの概要
http://docs.oracle.com/cd/E82638_01/ADMIN/viewing-information-about-cdbs-and-pdbs-with-sql-plus.htm#GUID-AD8C9596-67BB-47FA-A728-16F9C9B0AADF

45.10.3 アプリケーションPDB間のアプリケーション共通オブジェクトの問合せ
http://docs.oracle.com/cd/E82638_01/ADMIN/viewing-information-about-cdbs-and-pdbs-with-sql-plus.htm#GUID-7B4E4926-19C6-47A5-A4E3-6BD279F080F0

containers句を利用すると、
CDB$ROOTの共通ユーザから各PDBの同一共通ユーザにある同一表を問い合わせることができるようになる。
また、containers句を利用したクエリーから再帰SQLが生成され、デフォルトではパラレル化される。。と。
なお、パラレル化はされるが、パラレル文のキューイング対象でもない！　（え！　対象外なの？）

この時点で、癖者感がw

再帰的SQL実行と、さらり書かれているところ。。気になる気になる、気になりすぎて眠れないw

気になったら確かめないと！！

CDB$ROOTのsysユーザにて全PDBが起動していることを確認！

SYS@orcl12c> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL12C                     READ WRITE NO
         4 PDBORCL12CLONED                READ WRITE NO

CDB$ROOTの共通ユーザ:C##HOGEにEMP表（空）があるこを確認
CDB$ROOTにも同一名のオブジェクトが存在しないとエラーになるのでご注意を。（ここは別エントリで書くかも）

SYS@orcl12c> conn c##hoge/hoge@orcl12c
接続されました。
C##HOGE@orcl12c> select table_name from user_tables where table_name='EMP';

TABLE_NAME
------------------------------
EMP

C##HOGE@orcl12c> select count(*) from emp;

  COUNT(*)
----------
         0

以下、残る2つのPDBで、共通ユーザ:C##HOGEユーザにEMP表（データあり）が存在することを確認！

C##HOGE@orcl12c> conn c##hoge/hoge@pdborcl12c
接続されました。
C##HOGE@pdborcl12c> select table_name from user_tables where table_name='EMP';

TABLE_NAME
------------------------------
EMP

C##HOGE@pdborcl12c> select count(*) from emp;

  COUNT(*)
----------
        14

C##HOGE@pdborcl12c> conn c##hoge/hoge@pdborcl12cloned
接続されました。
C##HOGE@pdborcl12cloned> select table_name from user_tables where table_name='EMP';

TABLE_NAME
------------------------------
EMP

C##HOGE@pdborcl12cloned> select count(*) from emp;

  COUNT(*)
----------
        14

containers句を含む以下のクエリーをCDB$ROOTの共通ユーザ:C##HOGEユーザから実行！

SYS@orcl12c> conn c##hoge/hoge
接続されました。
C##HOGE@orcl12c> select * from containers(emp) where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     CON_ID
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20          4
      7369 SMITH      CLERK           7902 80-12-17        800                    20          3

auto traceでどんな実行計画なのか見てみます(CDB$ROOTでauto traceをやりたかったのはこういうことだったんでっす！w）

マニュアルに記載されている通り、パラレルクエリーになってます！
しかし、実行計画には、emp表という名前は見当たらず！！ しかも、EMPNOには主キー制約があるのに、Predicate Information にはfilter("EMPNO"=7369)とある。

....."生成される再帰的SQL文ということばが浮かんで消える"：） 不思議な実行計画が！！！！！！

でも、この実行計画、なんとなく見覚え.....ないですか？？？？

C##HOGE@orcl12c> set autot trace exp stat
C##HOGE@orcl12c> select * from containers(emp) where empno=7369;

実行計画
----------------------------------------------------------
Plan hash value: 1439328272

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |   100 |     0   (0)|       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |     1 |   100 |            |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |     1 |   100 |            |     1 |   254 |  Q1,00 | PCWC |            |
|*  4 |     FIXED TABLE FULL    | X$CDBVW$ |     1 |   100 |            |       |       |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

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

   4 - filter("EMPNO"=7369)


統計
----------------------------------------------------------
        303  recursive calls
          0  db block gets
        249  consistent gets
          9  physical reads
          0  redo size
       1220  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          2  rows processed

見覚えがあると思ったら、まさにこれですよね。
12.1.0.2で突然登場した機能のようにも見えるが、実は、12.1.0.1のころからあった機能らしい。CDB$VIEW clauseとして。
実行計画もそっくり

12.1.0.2 CDB views are now using CONTAINERS()　/ dbi services BLOG
https://blog.dbi-services.com/12102-cdb-views-are-now-using-containers/

SQL> select * from v$version;

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> select * from cdb$view(dual);


実行計画
----------------------------------------------------------
Plan hash value: 4256768476

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |     2 |     0   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |                  |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000         |     1 |     2 |     0   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|                  |     1 |     2 |     0   (0)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   4 |     FIXED TABLE FULL    | X$CDBVW$42ae64ee |     1 |     2 |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------



統計
----------------------------------------------------------
	 12  recursive calls
	  0  db block gets
	  6  consistent gets
	  0  physical reads
	  0  redo size
	688  bytes sent via SQL*Net to client
	544  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  2  rows processed

45.10.3 アプリケーションPDB間のアプリケーション共通オブジェクトの問合せ
これ、アプリケーションからお気軽に使っちゃってよい機能なんだろうか。。。どのようなユースケースを想定した機能なんだろう？。。。

to be continued...

CDBとPDBの間で迷子になりそう PART3 - SQL*PlusのAutotrace編

今回迷子になったのは、昔からあるSQL*PlusのAutotraceで.

Autotraceの使い方は、$ORACLE_HOME/sqlplus/admin/plustrce.sqlをSYSユーザーで実行し、PLUSTRACEロール作成されたら必要なユーザーへ該当ロールを付与。
あとは、SQL*Plusでautotraceを有効にする。

これだけです。

が、

マルチテナント（シングルテナント含む）化した途端に、迷子になりそうな場面に出会います！
（これにハマったことのあるかたは、#ローカルロール！　ってハッシュタグでtw...しなくてもいいですw）

マルチテナントでは、共通ユーザー、共通ロール、ローカルユーザー、ローカルロールという２つのタイプのユーザーとロールが登場しました。
CDB$ROOT/各PDBのディクショナリービュー同様、頭では理解したつもりでも指が勝手タイプして迷子になっちゃいうこともあるw　（実はOracleさんも？。。。だったりしてね。

余談はこれぐらいにして、とにかく試して見ましょう。

バージョンは以下のとおり

SYS@orcl12c> select * from v$version;

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

マルチテナントにしてあります。（シングルテナントでも同じですが）

SYS@orcl12c> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL12C                     READ WRITE NO
         4 PDBORCL12CLONED                READ WRITE NO

今日の主役、共通ユーザを作成します。
共通ユーザーとするためには、c##（デフォルト）という接頭子をユーザ名に付加する必要があります。（なれるまで辛いw）

SYS@orcl12c> create user c##hoge identified by hoge
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users
  5  container=all;

ユーザーが作成されました。

CDB$ROOT含む全PDBにC##HOGEユーザが作成されました。
（実はシステム定義の管理ユーザーは共通ユーザー扱になっていることも忘れちゃいけないんですよね。おそらく同じ癖というか特徴はあるはず）

SYS@orcl12c> select con_id,username,common from containers(dba_users) where username in ('SYS','C##HOGE')

    CON_ID USERNAME                       COM
---------- ------------------------------ ---
         1 SYS                            YES
         1 C##HOGE                        YES
         4 SYS                            YES
         4 C##HOGE                        YES
         3 SYS                            YES
         3 C##HOGE                        YES

※さりげなく、PDB CONTAINER句（赤字部分）を使ってますが、もともとそれで遊びたかっただけなんです。

今後のお遊び向けにいくつかのシステム権限の付与...

SYS@orcl12c> grant create session ,resource to c##hoge container=all;

権限付与が成功しました。

SYS@orcl12c> grant create table, create view to c##hoge container=all;

権限付与が成功しました。

SYS@orcl12c> grant create synonym to c##hoge container=all;

権限付与が成功しました。

共通ユーザーでもSQL*Plusのautotrace使いたいな〜と。
（最近はアダプティブな実行計画の影響で影が薄いAutotraceやExplain planですが、とりあえず見たい時や、実行統計を軽く見たいときには便利なんで）

と、思ったとことから、迷子になりまして、、はいw

CDB$ROOTの共通ユーザー接続して、autotraceを有効にしようとしました。
みなれたエラーがでるわけですよ。ロール作ってないので：）

”SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。”

SYS@orcl12c> conn c##hoge/hoge
接続されました。
C##HOGE@orcl12c> show con_name

CON_NAME
------------------------------
CDB$ROOT

C##HOGE@orcl12c> 
C##HOGE@orcl12c> set autot on
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

いつものように、$ORACLE_HOME/sqlplus/admin/plustrce.sqlを実行してPLUSTRACEロールを作成しようとすると。。。。。
なんと、ロールが作成できません！！！

そりゃ、そうですよね。CDB$ROOTには共通なものが載るわけですから、。。。
マルチテナント化してあるので、ロールも共通ロールを作成する必要があります。。。オラクルさん。。。。痛恨のミス？！w

PLUSTRACEというロールをCDB$ROOTに作成しようとするとローカルロールを作成している扱いになるので、共通ロールにしてねというエラーが返されます。

どうすんだよwこれw
と、初っ端から迷子ですw　（MOSは未確認。なにか記載されてそうな気はしますがw）

C##HOGE@orcl12c> conn / as sysdba
接続されました。
SYS@orcl12c> @?/sqlplus/admin/plustrce
SYS@orcl12c> 

...中略...

1SYS@orcl12c> create role plustrace;
create role plustrace
            *
行1でエラーが発生しました。:
ORA-65096: 共通ユーザーまたはロール名が無効です

共通ロールにしろというんだから、素直に共通ロールを作ってみます。
$ORACLE_HOME/sqlplus/admin/plustrce.sqlを元に、ロール名をPLUSTRACEからC##PLUSTRACEに変更したスクリプトを作成しました。

共通ロールについては、以下も参照のこと。
Oracle® Databaseセキュリティ・ガイド　12cリリース1 (12.1) 共通ロールの作成

以下のように、C##（共通ユーザやロールのデフォルト接頭子）を付加し共通ユーザ向けロールを作成すればできるようになりますよね！。使う機会があるかどうかは別ですがw

C##HOGE@orcl12c> !cat $ORACLE_HOME/sqlplus/admin/plustrce_common.sql 

drop role c##plustrace;
create role c##plustrace;

grant select on v_$sesstat to c##plustrace;
grant select on v_$statname to c##plustrace;
grant select on v_$mystat to c##plustrace;
grant c##plustrace to dba with admin option;

C##PLUSTRACEの作成ログ
うまくいきました！！！

SYS@orcl12c> drop role c##plustrace;
drop role c##plustrace
          *
行1でエラーが発生しました。:
ORA-01919: ロール'C##PLUSTRACE'は存在しません

SYS@orcl12c> create role c##plustrace;

ロールが作成されました。

SYS@orcl12c> grant select on v_$sesstat to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant select on v_$statname to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant select on v_$mystat to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant c##plustrace to dba with admin option;

権限付与が成功しました。

作成したautotrace用共通ロールをcontainter=allで共通ユーザに付与したのですが。。。。
実は、container=allとしても。PDB側では今まで通り、$ORACLE_HOME/sqlplus/admin/plustrce.sqlを各PDBのSYSユーザー実行し、PLUSTRACEロール（ローカルロール）をPDBに存在する共通ユーザ個別に付与する必要があるようです。（面倒くさい＞＜）

SYS@orcl12c> grant c##plustrace to c##hoge container=all;

C##HOGE@orcl12c> conn / as sysdba
接続されました。
SYS@orcl12c> 
SYS@orcl12c> conn c##hoge/hoge
接続されました。
C##HOGE@orcl12c> show con_name

CON_NAME
------------------------------
CDB$ROOT
C##HOGE@orcl12c> set autot trace exp stat
C##HOGE@orcl12c> select * from dual;

実行計画
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

PDB側のC##HOGEユーザーには繼承されない。。。。。かるく迷子になってる状況。

C##HOGE@orcl12c> conn c##hoge/hoge@pdborcl12c
接続されました。
C##HOGE@pdborcl12c> 
C##HOGE@pdborcl12c> show con_name

CON_NAME
------------------------------
PDBORCL12C
C##HOGE@pdborcl12c> set autot trace exp stat
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

各PDBのローカルユーザーは、以前からあるplustrce.sqlを各PDBごとに個別に作成、付与する必要があるみたい...もう完全に迷子w。
（本当はこれをネタしたかったわけじゃなかったんですけどね、トホホ）

C##HOGE@pdborcl12c> conn sys@pdborcl12c as sysdba
パスワードを入力してください: 
接続されました。
SYS@pdborcl12c> 
SYS@pdborcl12c> 
SYS@pdborcl12c> show con_name

CON_NAME
------------------------------
PDBORCL12C
SYS@pdborcl12c> 
SYS@pdborcl12c> @?/sqlplus/admin/plustrce

...中略...

SYS@pdborcl12c> conn c##hoge/hoge@pdborcl12c
接続されました。
C##HOGE@pdborcl12c> set autot trace exp stat
C##HOGE@pdborcl12c> select * from dual;


実行計画
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

次回、本当に試したかったことへ、つづく....（予定）
久々にその機能本気で使うのかって、ネタになりそうで。。。いいかも（こんな展開は予想してなかっただけに；）

以上、はじめての、 Yahoo! Japan Coworking Space LODGE　より

---

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

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;