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

[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没有变化。