[20241224]提示enable_parallel_dml光标不共享.txt

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SCOTT@book01p> @ sqlhint enable_parallel_dml
SCOTT@book01p> @ pr
==============================
NAME                          : ENABLE_PARALLEL_DML
SQL_FEATURE                   : QKSFM_DML
CLASS                         : ENABLE_PARALLEL_DML
INVERSE                       : DISABLE_PARALLEL_DML
TARGET_LEVEL                  : 1
PROPERTY                      : 0
VERSION                       : 11.2.0.4
VERSION_OUTLINE               :
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> CREATE TABLE empx AS SELECT * FROM emp WHERE 1=0;
Table created.

SCOTT@book01p> INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp;
14 rows created.

SCOTT@book01p> rollback ;
Rollback complete.

SCOTT@book01p> INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp;
14 rows created.

SCOTT@book01p> rollback ;
Rollback complete.

SCOTT@book01p> INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp;
14 rows created.

SCOTT@book01p> @hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
4253983228 g369cg3ysx8gw            2      41468      3956160932  fd8ea1fc  2024-12-24 16:04:37    16777219

SCOTT@book01p> rollback ;
Rollback complete.

--//执行3次.

SYS@book> @ sharepool/shp4  g369cg3ysx8gw 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   00000000616C9BB0 00000000616CB310 INSERT /*+ enable_parallel_dml */ INTO e          0          0          1 00000000616C73C0 00000000616CA938       8080      28320       5062     41462      41462 4253983228 g369cg3ysx8gw          0
child handle address   00000000616C6E20 00000000616CB310 INSERT /*+ enable_parallel_dml */ INTO e          0          0          0 00000000616C6C60 00000000616CADD0       8080      28320       5062     41462      41462 4253983228 g369cg3ysx8gw          1
child handle address   00000000616813F0 00000000616CB310 INSERT /*+ enable_parallel_dml */ INTO e          0          0          0 0000000061681230 00000000616817A0       8080      28320       5062     41462      41462 4253983228 g369cg3ysx8gw          2
parent handle address  00000000616CB310 00000000616CB310 INSERT /*+ enable_parallel_dml */ INTO e          0          0          1 00000000616CB150 00                     8128          0          0      8128       8128 4253983228 g369cg3ysx8gw      65535

--//每次执行光标不共享!!

SCOTT@book01p> @ share g369cg3ysx8gw
SQL_TEXT                       = INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp
SQL_ID                         = g369cg3ysx8gw
ADDRESS                        = 00000000616CB310
CHILD_ADDRESS                  = 00000000616C9BB0
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>24</ID><reason>Parallel DML Environment mismatch(1)</reason><size>2x4</size><cursorPdmlMode>1</cursorPdmlMode><sessPdmlModec>0</sessPdmlModec></ChildNode>
--------------------------------------------------
SQL_TEXT                       = INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp
SQL_ID                         = g369cg3ysx8gw
ADDRESS                        = 00000000616CB310
CHILD_ADDRESS                  = 00000000616C6E20
CHILD_NUMBER                   = 1
PDML_ENV_MISMATCH              = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>24</ID><reason>Parallel DML Environment mismatch(1)</reason><size>2x4</size><cursorPdmlMode>1</cursorPdmlMode><sessPdmlModec>0</sessPdmlModec></ChildNode>
--------------------------------------------------
SQL_TEXT                       = INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp
SQL_ID                         = g369cg3ysx8gw
ADDRESS                        = 00000000616CB310
CHILD_ADDRESS                  = 00000000616813F0
CHILD_NUMBER                   = 2
PDML_ENV_MISMATCH              = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

SCOTT@book01p> @ unshare g369cg3ysx8gw
SQL_ID        NONSHARED_REASON                COUNT(*)
------------- ----------------------------- ----------
g369cg3ysx8gw PDML_ENV_MISMATCH                      2

SCOTT@book01p> column SQL_TEXT format a62
SCOTT@book01p> SELECT sql_id, sql_text, child_number, optimizer_env_hash_value  FROM   v$sql where sql_id='g369cg3ysx8gw';

SQL_ID        SQL_TEXT                                                       CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE
------------- -------------------------------------------------------------- ------------ ------------------------
g369cg3ysx8gw INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp             0                927111890
g369cg3ysx8gw INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp             1                927111890
g369cg3ysx8gw INSERT /*+ enable_parallel_dml */ INTO empx SELECT * FROM emp             2                927111890

--//OPTIMIZER_ENV_HASH_VALUE没有变化。