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

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

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...