現在実行中のsqlのうち、ディスク読み込みが多いものを上位N件取得

表題のクエリ

hash_value,address単位でサマっているのは実行スキーマ問わず、親カーソル単位で求めるため。USERS_EXECUTING列の値が0より大きいSQL文が、現在実行中のSQL文らしい。以下の参考文献を参照。

V$SQL

説明
command_type oracleコマンド・タイプ定義
cpu_time このカーソルが解析、実行およびフェッチのために使用するcpu時間(マイクロ秒)
disk_reads この子カーソルに対するディスク読取り数
elapsed_time 解析、実行およびフェッチに対してこのカーソルで使用される経過時間(マイクロ秒)です。カーソルがパラレル実行を使用する場合、elapsed_time_totalは、問合せコーディネータへの時間にすべてのパラレル問合せスレーブ処理を加えた累積時間です。
executions このオブジェクトがライブラリ・キャッシュに入れられた後で行われた実行数
rows_processed 解析されたsql文が戻す行数の合計
users_executing 文を実行しているユーザーの数
buffer_gets この子カーソルに対するバッファ取得数
コード表示

WITH sub AS (
	SELECT
		s2.username
		,s1.hash_value
		,s1.address
		,s1.sql_id
		,decode(s1.command_type,2,'select',3,'insert',6,'update',7,'delete',189,'merge') AS command_type
		,substr(MIN(s1.sql_text),1,10) AS sql_txup10
		,SUM(s1.executions) AS executions
		,SUM(s1.disk_reads) AS disk_reads
		,SUM(s1.buffer_gets) AS buffer_gets
		,SUM(s1.rows_processed) AS rows_processed
		,SUM(s1.cpu_time) AS cpu_time_mic
		,SUM(s1.elapsed_time) AS elapsed_time_mic
		,round((SUM(s1.cpu_time)/(1000*1000))/SUM(s1.executions),2) AS cpu_time_sec_per_exec
		,round(SUM(s1.cpu_time)/(1000*1000),2) AS cpu_time_sec
		,round((SUM(s1.elapsed_time)/(1000*1000))/SUM(s1.executions),2) AS elapsed_time_sec_per_exec
		,round(SUM(s1.elapsed_time)/(1000*1000),2) AS elapsed_time_sec
	FROM
		v$sql s1
		,dba_users s2
	WHERE
		s1.command_type IN (2,3,6,7,189)
	AND s1.users_executing > 0
	and s1.PARSING_USER_ID = s2.USER_ID
	GROUP BY
		s2.username
		,s1.hash_value
		,s1.address
		,s1.sql_id
		,decode(s1.command_type,2,'select',3,'insert',6,'update',7,'delete',189,'merge')
	ORDER BY
		SUM(s1.disk_reads) DESC
)
SELECT
	*
FROM
	sub
WHERE
	ROWNUM <= 3
;

初期化パラメータ

回数は分かったけど、実際読み込むブロック数とかの量はどうなの??と思ったので、調べた。以下の初期化パラメータについてまず調べた。またアクセスパスに関しては以下を参照。

Oracle アクセスパス

db_file_multiblock_read_count

TABLE ACCESS FULLやINDEX FAST FULL SCAN時に1回のI/Oで読み込まれるデータブロック数を指定。この値が大きいほど、上記のスキャン時のコストが低く、見積もられるため、実行計画として選択されやすくなるらしい。基本的にデフォルトでいいとおもわれ。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'db_file_multiblock_read_count';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 19:59:16 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 19:57:43 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'db_file_multiblock_read_count';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
db_file_multiblock_read_count                      128

1 row selected.

Elapsed: 00:00:00.01

optimizer_index_caching

索引ブロックがバッファキャッシュ上でヒットすると考える割合を指定。デフォ値は0。DWH系はデフォのままでいいけど、OLTp系は90ぐらいがおすすめらしい。索引ブロックはキャッシュ上にヒットする確率が高いかららしい。この値に応じて、索引走査やネステッドループ結合のコストを調整。この値が大きいほど、索引がキャッシュ上にあると考えられるので、実行計画に索引スキャンが選択されやすくなるぽい。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_caching';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 20:01:23 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 19:59:16 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_caching';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
optimizer_index_caching                            0

1 row selected.

Elapsed: 00:00:00.00

optimizer_index_cost_adj

索引アクセスのコストを通常の何%で計算するかを指定。デフォ値は100。この値が小さいほど索引アクセスのコストを低く見積もるように修正され、実行計画として索引スキャンが選択されやすくなるぽい。DWH系はデフォ値のまま。OLTP系は索引スキャンが大前提と考えられるため、25などと設定して、索引走査のコストを意図的に低く見積もらせたりすることもあるそう。。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_cost_adj';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 20:01:56 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 20:01:23 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_cost_adj';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
optimizer_index_cost_adj                           100

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1>

TABLE ACCESS FULLは1回のI/Oで初期化パラメタに指定した値分、複数ブロック数読み込めるけど、INDEX FAST FULL SCANを除くインデックススキャンは単一ブロックしか読み込めない。らしい。こういう話になるとランダムI/OだったりシーケンシャルI/Oの話が必ず絡んでくるので、ここで調べておく。以下の資料を参考にする。

津島博士のパフォーマンス講座 第12回 I/O周りについて

単一ブロック読み込みはランダム読み込みで複数ブロック読み込みはシーケンシャル読み込みになる。(ランダム読み込みは単一ブロック読み込みで、シーケンシャル読み込みは複数ブロック読み込みになるとも思われる。)大量データを一度に扱う場合を考えたときに、ランダムI/Oのほうがシーク大変そう。シーケンシャルのほうがどかっと作業できて効率よさそうね。少量データならランダムI/Oのほうが不必要なブロック見に行く必要がないから効率よさそうね。ちなみにdb file sequential readとかdb file scattered readもよく出てくるからここで調べておく。以下の資料を参考にする。

ランダム読み込み⇔単一ブロック読み込み 
シーケンシャル読み込み⇔複数ブロック読み込み

db file sequential read
 
db file scattered read
 
津島博士のパフォーマンス講座 第3回 Statspackから探る、パフォーマンス問題の原因特定方法
 
門外不出のOracle現場ワザ 第1章 目からウロコのOracleパフォーマンス分析テクニック
 
なぜランダムアクセスを「db file sequential read」、シーケンシャルアクセスを「db file scattered read」というのか?
 
10 パフォーマンス・ビューを使用したインスタンスのチューニング 10.3.2 db file scattered read 10.3.3 db file sequential read

db file sequential read⇔ランダム読み込み⇔単一ブロック読み込み 
db file scattered read⇔シーケンシャル読み込み⇔複数ブロック読み込み

ディスクから読み取ったブロックをメモリ上にどのように展開するかを意識しないと多分理解しにくい。メモリのアーキテクチャ。
複数ブロック読み込んだら、メモリの不連続領域に展開するから、scattered。
単一ブロック読み込んだら、メモリの連続領域に展開するから、sequential。

あとは待機クラスと待機イベントとか首つっこみたいなー。

oracle cursor 親カーソル 子カーソル

参考文献

15 カーソル共有によるReal-World Performanceの改善

プライベートSQL領域と共有SQL領域

同じセッションまたは異なるセッションにある複数のプライベートSQL領域が単一の共有SQL領域を参照できることをカーソル共有とよぶ。複数セッションからアクセスされる共有SQL領域は、共有カーソルとよぶ。

ハードパース手順

SQL文のリテラルが完全一致しないとハードパースされて、別の共有SQL領域に格納される。一致の判断はハッシュ化した値で比較するらしい。。

初期化パラメータCURSOR_SHARING

2つの文で検索条件の値のみ異なる場合でもCURSOR_SHARINGの値に応じて例外的にSQL領域を共有できるらしい。CURSOR_SHARINGパラメータがFORCEに設定されている場合で共有できる。

コード表示

SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;

初期化パラメータCURSOR_SHARINGを確認してみた。

コード表示

col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%cursor_sharing%';

EXACTだった。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 19:47:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 27 2019 12:27:53 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col name for a24;
AINE@pdb1> col value for a12;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%cursor_sharing%';

NAME                     VALUE
------------------------ ------------
cursor_sharing           EXACT

1 row selected.

Elapsed: 00:00:00.03

初期化パラメータCURSOR_SHARINGに関しては以下の内容を参照。

1.46 CURSOR_SHARING

プロパティ 説明
FORCE 既存のカーソルを共有する場合、またはカーソル・プランが最適ではない場合に、新しいカーソルの作成が許可されます。
EXACT 同一のテキストを含む文のみに、前述のカーソルの共有が許可されます。

以下のSQLで変更できる。

コード表示

ALTER SESSION SET CURSOR_SHARING=FORCE;
コード表示

[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 21:14:39 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Feb 02 2019 21:14:22 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col value for a12;
AINE@pdb1> col name for a24;
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%cursor_sharing%';

NAME                     VALUE
------------------------ ------------
cursor_sharing           EXACT

1 row selected.

Elapsed: 00:00:00.02
AINE@pdb1> ALTER SESSION SET CURSOR_SHARING=FORCE;

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> select name,value from V$PARAMETER where NAME like '%cursor_sharing%';

NAME                     VALUE
------------------------ ------------
cursor_sharing           FORCE

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1>

親カーソルと子カーソル

すべての解析済のSQL文には、1つの親カーソルと1つ以上の子カーソルがあります。親カーソルにはSQL文のテキストが格納されます。2つの文のテキストが同一である場合、これらの文は同じ親カーソルを共有します。しかし、テキストが異なる場合、データベースは別の親カーソルを作成します。

以下のsqlを同一のプラガブルデータベースのaineユーザーkuraineユーザーに流す。さらに別のプラガブルデータベースのaineユーザーkuraineユーザーにもながす。

コード表示

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;
DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

プラガブルデータベースの確認

今のデータベースにプラガブルデータベースが何個あるか確認。$ORACLE_BASE/oradata/ORCLに存在するPDBを確認。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@6898242bcdd5 ORCL]$ pwd
/opt/oracle/oradata/ORCL
[oracle@6898242bcdd5 ORCL]$ ll
total 3272756
drwxr-x---. 2 oracle oinstall        104 Jan 20 08:55 PDB1
-rw-r-----. 1 oracle oinstall   18726912 Feb  2 22:14 control01.ctl
-rw-r-----. 1 oracle oinstall   18726912 Jan 27 14:55 control02.ctl
drwxr-x---. 2 oracle oinstall        111 Jan 20 08:49 pdbseed
-rw-r-----. 1 oracle oinstall  209715712 Feb  2 15:55 redo01.log
-rw-r-----. 1 oracle oinstall  209715712 Feb  2 19:45 redo02.log
-rw-r-----. 1 oracle oinstall  209715712 Feb  2 22:14 redo03.log
-rw-r-----. 1 oracle oinstall  314580992 Feb  2 19:50 stats.dbf
-rw-r-----. 1 oracle oinstall 1111498752 Feb  2 22:10 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  912269312 Feb  2 22:10 system01.dbf
-rw-r-----. 1 oracle oinstall   34611200 Feb  2 22:05 temp01.dbf
-rw-r-----. 1 oracle oinstall  335552512 Feb  2 22:10 undotbs01.dbf
-rw-r-----. 1 oracle oinstall    5251072 Feb  2 19:50 users01.dbf
[oracle@6898242bcdd5 ORCL]$

PDB1の1つだけだったので、複製して2個にする。PDB1を元にしてPDB2を作成する。SYSユーザーで作業。

コード表示

create pluggable database PDB2 from PDB1 FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCL/PDB1','/opt/oracle/oradata/ORCL/PDB2');

PDB1を元にしてPDB2を作成。

コード表示

[oracle@f285aba0589a ~]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@f285aba0589a ORCL]$ ll
total 2372364
drwxr-x---. 2 oracle oinstall       104 Feb  2 22:40 PDB1
-rw-r-----. 1 oracle oinstall  18726912 Feb  2 22:49 control01.ctl
-rw-r-----. 1 oracle oinstall  18726912 Feb  2 22:49 control02.ctl
drwxr-x---. 2 oracle oinstall       111 Feb  2 22:34 pdbseed
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:40 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:49 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:40 redo03.log
-rw-r-----. 1 oracle oinstall 534781952 Feb  2 22:45 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 880812032 Feb  2 22:45 system01.dbf
-rw-r-----. 1 oracle oinstall  34611200 Feb  2 22:37 temp01.dbf
-rw-r-----. 1 oracle oinstall 340795392 Feb  2 22:45 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Feb  2 22:40 users01.dbf
[oracle@f285aba0589a ORCL]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 22:50:00 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> create pluggable database PDB2 from PDB1 FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCL/PDB1','/opt/oracle/oradata/ORCL/PDB2');

Pluggable database created.

SQL> host ls -l
total 2372364
drwxr-x---. 2 oracle oinstall       104 Feb  2 22:40 PDB1
drwxr-x---. 2 oracle oinstall       104 Feb  2 22:51 PDB2
-rw-r-----. 1 oracle oinstall  18726912 Feb  2 22:52 control01.ctl
-rw-r-----. 1 oracle oinstall  18726912 Feb  2 22:52 control02.ctl
drwxr-x---. 2 oracle oinstall       111 Feb  2 22:34 pdbseed
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:40 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:51 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Feb  2 22:40 redo03.log
-rw-r-----. 1 oracle oinstall 534781952 Feb  2 22:50 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 880812032 Feb  2 22:51 system01.dbf
-rw-r-----. 1 oracle oinstall  34611200 Feb  2 22:37 temp01.dbf
-rw-r-----. 1 oracle oinstall 340795392 Feb  2 22:50 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Feb  2 22:40 users01.dbf

PDB1を元にして作成されたPDB2をオープン。

コード表示

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

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

PDB2に入るために接続識別子を編集

コード表示

[oracle@f285aba0589a ~]$ find / -name tnsnames.ora -type f 2>/dev/null
/opt/oracle/product/18c/dbhome_1/network/admin/samples/tnsnames.ora
/opt/oracle/oradata/dbconfig/ORCL/tnsnames.ora
[oracle@f285aba0589a ~]$ vi /opt/oracle/oradata/dbconfig/ORCL/tnsnames.ora

ORCL=localhost:1521/ORCL
PDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)
PDB2=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB2)
  )
)

PDB2への接続確認

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb2 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 23:07:59 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb2> show con_name

CON_NAME
------------------------------
PDB2
SYS@pdb2> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 23:08:40 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> show con_name

CON_NAME
------------------------------
PDB1
AINE@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@f285aba0589a ~]$ sqlplus kuraine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 23:09:09 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

KURAINE@pdb1> show con_name

CON_NAME
------------------------------
PDB1
KURAINE@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb2

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 23:09:28 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb2> show con_name

CON_NAME
------------------------------
PDB2
AINE@pdb2> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@f285aba0589a ~]$ sqlplus kuraine/ORACLE_PWD@pdb2

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 2 23:09:40 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

KURAINE@pdb2> show con_name

CON_NAME
------------------------------
PDB2

プラガブルデータベースへデータ投入

以下の表にまとめた構成でデータ投入する

プラガブルデータベース ユーザー オブジェクト
CDB sys
PDB1 aine warehouse_mst
PDB1 aine item_mst
PDB1 kuraine warehouse_mst
PDB1 kuraine item_mst
PDB2 aine warehouse_mst
PDB2 aine item_mst
PDB2 kuraine warehouse_mst
PDB2 kuraine item_mst

親カーソル

以下のsqlで件数を確認

コード表示

WITH sub AS (
	SELECT
		ROWNUM AS rn
		, 'select '
		  || ''''
		  || table_name
		  || ''' as tbl'
		  || ', count(*) as cnt'
		  || ' from '
		  || table_name AS b_sql
	FROM
		user_tables
) SELECT
	b_sql
	||
		CASE
			WHEN NOT EXISTS (
				SELECT
					1
				FROM
					sub s2
				WHERE
					s1.rn < s2.rn
			) THEN ';'
			ELSE ' union all '
		END
	AS b_sql
  FROM
	sub s1;

aine@pdb1で確認。kuraine@pdb1、aine@pdb2、kuraine@pdb2でも同様に確認。

コード表示

AINE@pdb1> col B_SQL for a100;
AINE@pdb1> WITH sub AS (
  2   SELECT
  3    ROWNUM AS rn
  4    , 'select '
  5      || ''''
  6      || table_name
  7      || ''' as tbl'
  8      || ', count(*) as cnt'
  9      || ' from '
 10      || table_name AS b_sql
 11   FROM
 12    user_tables
 13  ) SELECT
 14   b_sql
 15   ||
 16    CASE
 17     WHEN NOT EXISTS (
 18      SELECT
 19       1
 20      FROM
 21       sub s2
 22      WHERE
 23       s1.rn < s2.rn
 24     ) THEN ';'
 25     ELSE ' union all '
 26    END
 27   AS b_sql
 28    FROM
 29   sub s1;

B_SQL
----------------------------------------------------------------------------------------------------
select 'WAREHOUSE_MST' as tbl, count(*) as cnt from WAREHOUSE_MST union all
select 'ITEM_MST' as tbl, count(*) as cnt from ITEM_MST;

2 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1> col tbl for a30;
AINE@pdb1> col cnt for 99999999999;
AINE@pdb1> select 'WAREHOUSE_MST' as tbl, count(*) as cnt from WAREHOUSE_MST union all
  2  select 'ITEM_MST' as tbl, count(*) as cnt from ITEM_MST;

TBL                                     CNT
------------------------------ ------------
WAREHOUSE_MST                             3
ITEM_MST                                 39

2 rows selected.

Elapsed: 00:00:00.00

pdb1でaineユーザーからsql発行

コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 14:53:29 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Feb 02 2019 23:34:53 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select /* test */ count(*) from warehouse_mst;

     COUNT(*)
-------------
            3

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> select /* test */ count(*) from Warehouse_mst;

     COUNT(*)
-------------
            3

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

pdb1でkuraineユーザーからsql発行

コード表示

[oracle@f285aba0589a ~]$ sqlplus kuraine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 15:02:45 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 03 2019 14:51:15 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

KURAINE@pdb1> select /* test */ count(*) from warehouse_mst;

     COUNT(*)
-------------
            3

1 row selected.

Elapsed: 00:00:00.01
KURAINE@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

pdb1でsysユーザーから発行したsqlを確認

コード表示

COL con_id FOR 99

COL child# FOR 99999

COL exec FOR 9999

COL schema FOR a24

COL sql_text FOR a100

SELECT
	con_id
	, sql_id
	, parsing_schema_name AS schema
	, sql_text
	, child_number AS child#
	, executions AS exec
FROM
	v$sql
WHERE
	sql_text LIKE '%test%'
	AND sql_text NOT LIKE '%SQL_TEXT%'
ORDER BY
	sql_id;
コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 15:05:22 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> show pdbs

       CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------------- ------------------------------ ---------- ----------
            3 PDB1                           READ WRITE NO
SYS@pdb1> col con_id for 99
SYS@pdb1> col child# for 99999
SYS@pdb1> col exec for 9999
SYS@pdb1> col schema for a24
SYS@pdb1> col sql_text for a100
SYS@pdb1> SELECT
con_id
  3  , sql_id
  4  , parsing_schema_name AS schema
  5  , sql_text
  6  , child_number AS child#
  7  , executions AS exec
  8  FROM
v$sql
 10  WHERE
 11  sql_text LIKE '%test%'
 12  AND sql_text NOT LIKE '%SQL_TEXT%'
 13  ORDER BY
 14  sql_id;


CON_ID SQL_ID        SCHEMA                   SQL_TEXT                                                                                             CHILD#  EXEC
------ ------------- ------------------------ ---------------------------------------------------------------------------------------------------- ------ -----
     3 0gr3t1hhdyspp KURAINE                  select /* test */ count(*) from warehouse_mst                                                             0     2
     3 0gr3t1hhdyspp AINE                     select /* test */ count(*) from warehouse_mst                                                             1     1
     3 9uuu6rhhn9sdk AINE                     select /* test */ count(*) from Warehouse_mst                                                             0     1

3 rows selected.

Elapsed: 00:00:00.06

con_idの値からもプラガブルデータベースはカーソルは独立したものとして扱われていることが分かる。sql_idが0gr3t1hhdysppのものは1つの親カーソルと2つの子カーソル(子0と子1)がある。EXEC回数は2回。sql_idが9uuu6rhhn9sdkのものは異なる親カーソルと1つの子カーソル(子0)のみがある。v$sqlは以下を参照。

V$SQL

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 15:15:18 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@ORCL> show pdbs

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

親カーソルおよびV$SQLAREA

V$SQLAREAビューには、すべての親カーソルに対して1つの行が含まれている。

コード表示

COL sql_text FORMAT a100
SELECT
	sql_text
	, sql_id
	, version_count
	, hash_value
FROM
	v$sqlarea
WHERE
	sql_text LIKE '%test%'
	AND sql_text NOT LIKE '%SQL_TEXT%';

異なるSQL_IDで識別される2つの親カーソルを示す。VERSION_COUNTは子カーソルの数を示す。

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 15:30:54 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> COL sql_text FORMAT a100
SYS@pdb1> SELECT
  2  sql_text
  3  , sql_id
  4  , version_count
  5  , hash_value
  6  FROM
  7  v$sqlarea
  8  WHERE
  9  sql_text LIKE '%test%'
 10  AND sql_text NOT LIKE '%SQL_TEXT%';

SQL_TEXT                                                                                             SQL_ID        VERSION_COUNT    HASH_VALUE
---------------------------------------------------------------------------------------------------- ------------- ------------- -------------
select /* test */ count(*) from Warehouse_mst                                                        9uuu6rhhn9sdk             1     558162354
select /* test */ count(*) from warehouse_mst                                                        0gr3t1hhdyspp             2     551510709

2 rows selected.

Elapsed: 00:00:00.13

子カーソルおよびV$SQL

すべての親カーソルには1つ以上の子カーソルがあります。子カーソルには、実行計画、バインド変数、問合せで参照されるオブジェクトに関するメタデータ、オプティマイザ環境およびその他の情報が含まれています。親カーソルとは異なり、子カーソルにはSQL文のテキストは格納されません。文で親カーソルを再利用できる場合、既存の子カーソルを再利用できるかどうかがチェックされます。SQL文やPL/SQLブロック内でスキーマ・オブジェクトを参照する際には、同じスキーマ内の同じオブジェクトである必要があります。オプティマイザ・モードが同一であるかどうかも判定する。初期化パラメータOPTIMIZER_MODE

コード表示

col name for a24;
col value for a12;
select name,value from V$PARAMETER where NAME like '%optimizer_mode%';

OPTIMIZER_MODEに関しては以下を参照。

OPTIMIZER_MODE

プロパティ 説明
first_rows_n オプティマイザは、コストベースの方法を使用して、最短の応答時間で最初のn行(n=1、10、100、1000)を戻すために最適化します。
first_rows オプティマイザは、コストと発見的方法を組み合せて使用し、最初の数行を迅速に配信するための最適な計画を判断します。
all_rows オプティマイザは、セッション内のすべてのSQL文に対してコストベース方法を使用し、最高のスループット(リソース使用量を最小限に抑えて、文全体を実行すること)を得るために最適化します。
コード表示

SYS@pdb1> col name for a24;
SYS@pdb1> col value for a12;
SYS@pdb1> select name,value from V$PARAMETER where NAME like '%optimizer_mode%';

NAME                     VALUE
------------------------ ------------
optimizer_mode           ALL_ROWS

1 row selected.

Elapsed: 00:00:00.02

OPTIMIZER_MODEを変更してみる。

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 3 15:58:38 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> col name for a24;
SYS@pdb1> col value for a24;
SYS@pdb1> select name,value from V$PARAMETER where NAME like '%optimizer_mode%';

NAME                     VALUE
------------------------ ------------------------
optimizer_mode           ALL_ROWS

1 row selected.

Elapsed: 00:00:00.01
SYS@pdb1> alter session set optimizer_mode=first_rows_1000;

Session altered.

Elapsed: 00:00:00.00
SYS@pdb1> select name,value from V$PARAMETER where NAME like '%optimizer_mode%';

NAME                     VALUE
------------------------ ------------------------
optimizer_mode           FIRST_ROWS_1000

1 row selected.

Elapsed: 00:00:00.01
SYS@pdb1> alter session set optimizer_mode=first_rows;

Session altered.

Elapsed: 00:00:00.01
SYS@pdb1> select name,value from V$PARAMETER where NAME like '%optimizer_mode%';

NAME                     VALUE
------------------------ ------------------------
optimizer_mode           FIRST_ROWS

1 row selected.

Elapsed: 00:00:00.00

PLAN_HASH_VALUE列が同一の値であれば、3つのすべてのSQL文では、同じ実行計画が使用されている。

コード表示

SELECT
	s.CON_ID
	,s.sql_text
	, s.sql_id
	, d.username AS usr
	, s.child_number AS child#
	, s.hash_value
	, s.plan_hash_value AS plan_hashv
FROM
	v$sql s
	, dba_users d
WHERE
	sql_text LIKE '%test%'
	AND sql_text NOT LIKE '%SQL_TEXT%'
	AND d.user_id = s.parsing_user_id;

カーソルの不一致およびV$SQL_SHARED_CURSOR

親カーソルに複数の子カーソルがある場合、V$SQL_SHARED_CURSORビューは、カーソルが共有されなかった理由に関する情報を提供します。複数のタイプの非互換性について、TRANSLATION_MISMATCH列では値YまたはNで不一致を示します。TRANSLATION_MISMATCH列は、2つの文が異なるオブジェクトを参照したことを示しており、最後の文でTRANSLATION_MISMATCHの値がYになります。共有できなかったため、CHILD_NUMBERが0と1で示されているように、それぞれの文には別の子カーソルがあります。

コード表示

SELECT
	s.con_id
	, s.sql_text
	, s.sql_id
	, d.username AS usr
	, s.child_number AS child#
	, s.hash_value
	, s.plan_hash_value AS plan_hashv
	, c.translation_mismatch
FROM
	v$sql s
	, dba_users d
	, v$sql_shared_cursor c
WHERE
	sql_text LIKE '%test%'
	AND sql_text NOT LIKE '%SQL_TEXT%'
	AND d.username NOT IN (
		'SYS'
	)
	AND d.user_id = s.parsing_user_id
	AND s.child_address = c.child_address;