« 2017年1月 | トップページ

2017年3月24日 (金)

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

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

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

To be continued.


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

| | コメント (0) | トラックバック (0)

2017年3月22日 (水)

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

Previously on Mac De Oracle.

CDBとPDBの間で迷子になりそう PART3 - SQL*PlusのAutotrace編 - その2
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編 - その2

| | コメント (0) | トラックバック (0)

2017年3月21日 (火)

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

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

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

| | コメント (0) | トラックバック (0)

2017年3月 7日 (火)

Oracle Database Connect 2017

Oracle Database Connect 2017 ~ 最新のデータベース技術がここにある ~

が明日開催されます。


昨年は、LTでしたが、今年は好評のJPOUG in 15minitesを行います。
セッション・オーガナイザーはJPOUGのサイトにてご確認ください。
http://www.jpoug.org/2017/02/14/odc2017


アジェンダには記載されていない登壇者は諸橋さんのブログで公開されていますが

ablog - 畔勝さん
wmo6hash::blog - 諸橋さん
コーソル DatabaseエンジニアのBlog - 渡部さん

そして、最近、ひな壇エンジニアリングに目覚めつつあるw 私


会場でお会いできることを楽しみにしています。



あ、そうそう、

昔、奥さんが書いてた「今日のゴハン」ってブログで、ダーリンと書かれてたのですが、
おら!オラ!Oracleのペンネーム:ダーリンが誰なのか知ったのは、2011年か2012年ごろ。

ネタを探してググっていたら、そのダーリンのエントリが!!! 

待ちイベントに関する検証 その7 - ペンネーム: ダーリン
http://www.insight-tec.com/mailmagazine/ora3/vol327.html


| | コメント (0) | トラックバック (0)

2017年3月 3日 (金)

号外:遅まきながら、Oracle Database 12c R2インストールフェスタ参戦w

日本のサイトにはまだ見当たりませんが、本家のサイトからOracle Database 12c Release 2がダウンロード可能となったのをTanelのつぶやきで知ったのはいいが、仕事疲れで帰宅後、爆睡して出遅れ感満載で参戦中w

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

20170303_53712

ダウンロードしてるだけなんで、まだ、遊ぶ余裕はないかもしれないのですが:)

20170303_53944


| | コメント (0) | トラックバック (0)