この記事は約64分3秒で読むことができます。

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;

Leave a Reply

Your email address will not be published. Required fields are marked *