当执行计划中存在“PARTITION LIST SUBQUERY”时，某些内容（错误？）会取消实例化包

这是 Oracle 12c 的错误吗？

我在 Oracle Linux 上运行 64 位 Oracle 12.1.0.2。
遇到一件奇怪的事情：当执行计划切换到使用“PARTITION LIST SUBQUERY”时，受影响的查询中使用的包将丢失其所有变量的值。看起来就像运行 DBMS_SESSION.RESET_PACKAGE 后一样，有些东西取消了包的实例化。
该查询使用分区表，该分区表通过与另一个表连接来限制，该表使用确定性“getter”函数读取的包中的变量进行限制。如果我将函数更改为非确定性的，或者更改任何内容以使解释计划不使用“分区列表子查询”，则不会出现问题。

请参阅在我的数据库上使用“PARTITION LIST SUBQUERY”生成解释计划的综合示例（在 4 上尝试过）：

-- Clean-up ----------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE facts CASCADE CONSTRAINTS;
DROP TABLE DIM_CALENDAR CASCADE CONSTRAINTS;
DROP PACKAGE Parameters_PKG;

-- First, partitioned table  ----------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE factS
( mth VARCHAR2(6 BYTE)    NOT NULL
, just_data VARCHAR2(120 BYTE)  NOT NULL
)
PARTITION BY LIST (mth)
(  
   PARTITION M01 VALUES ('M01')
,  PARTITION M02 VALUES ('M02')
,  PARTITION M03 VALUES ('M03')
,  PARTITION M04 VALUES ('M04')
,  PARTITION M05 VALUES ('M05')
,  PARTITION MAX_VALUE VALUES (DEFAULT)
    LOGGING
    ROW STORE COMPRESS BASIC
)
;
INSERT INTO facts SELECT 'M' || TO_CHAR(LEVEL, 'FM09'), STANDARD_HASH(LEVEL, 'SHA384' ) FROM dual connect BY LEVEL < 4;
COMMIT;

-- Second table to iterate the partitions of the first table ------------------------------------------------------------------------------------------------
CREATE TABLE DIM_CALENDAR
(
  CLIENT_ID  VARCHAR2(10 BYTE)              NOT NULL,
  mth        VARCHAR2(6 BYTE)               NOT NULL
)
;
INSERT INTO DIM_CALENDAR SELECT 'TEST', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;
INSERT INTO DIM_CALENDAR SELECT 'OTHER', 'M' || TO_CHAR(LEVEL, 'FM009') FROM dual connect BY LEVEL < 10;
INSERT INTO DIM_CALENDAR SELECT 'ANOTHER', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;
COMMIT;

-- Analyzing both tables to get to the desired explain plan ----------------------------------------------------------------------------------------------------
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (NULL, NULL);

-- A package with deterministic ffunction ---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE Parameters_PKG
AUTHID DEFINER
AS
   FUNCTION  get_Client_ID RETURN VARCHAR2 deterministic; 
   PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
END Parameters_PKG
;
CREATE OR REPLACE PACKAGE BODY Parameters_PKG
AS
   Client_ID  VARCHAR2(255);
FUNCTION get_Client_ID   RETURN VARCHAR2   
AS
   PRAGMA UDF;
BEGIN
   IF Client_ID IS NULL THEN 
      RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
   END IF;
   RETURN Client_ID;
END get_Client_ID;
PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
IS
BEGIN
   Client_ID := UPPER(TRIM(p_Client_ID));
END Set_Client_ID;
END Parameters_PKG
;
----------------------------------------------------------------------------------------------------------------------------------------------------------------


-- The test ----------------------------------------------------------------------------------------------------------------------------------------------------
-- exec Parameters_PKG.Set_Client_ID('TEST') -- this should return 2 rows
exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned') 
SELECT Parameters_PKG.Get_Client_ID FROM dual; -- a check that the value is really set.

SELECT f.mth, f.just_data  
FROM facts f, DIM_CALENDAR c  
WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
AND F.mth = C.mth
;


/* Failing explain plan with  "PARTITION LIST SUBQUERY"

SELECT STATEMENT  ALL_ROWSCost: 43.194  Bytes: 336  Cardinality: 3              
 4 HASH JOIN  Cost: 43.194  Bytes: 336  Cardinality: 3          
  2 PARTITION LIST SUBQUERY  Cost: 43.107  Bytes: 303  Cardinality: 3  Partition #: 2  Partitions accessed #KEY(SUBQUERY)   
   1 TABLE ACCESS FULL TABLE FACTS Cost: 43.107  Bytes: 303  Cardinality: 3  Partition #: 2  Partitions accessed #KEY(SUBQUERY)
  3 TABLE ACCESS FULL TABLE DIM_CALENDAR Cost: 87  Bytes: 44  Cardinality: 4    

*/

删除“确定性”关键字。确定性意味着对于同一组输入，我可以返回相同的输出，而无需再次评估该函数。你有no输入到该函数，所以我们永远不需要运行它。因此我们将返回 null，例如（省略您的设置代码）

--
-- with deterministic
--
SQL> CREATE OR REPLACE PACKAGE Parameters_PKG
  2  AUTHID DEFINER
  3  AS
  4     FUNCTION  get_Client_ID RETURN VARCHAR2 deterministic;
  5     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  6  END Parameters_PKG
  7  ;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG
  2  AS
  3     Client_ID  VARCHAR2(255);
  4  FUNCTION get_Client_ID   RETURN VARCHAR2
  5  AS
  6     PRAGMA UDF;
  7  BEGIN
  8     IF Client_ID IS NULL THEN
  9        RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
 10     END IF;
 11     RETURN Client_ID;
 12  END get_Client_ID;
 13  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
 14  IS
 15  BEGIN
 16     Client_ID := UPPER(TRIM(p_Client_ID));
 17  END Set_Client_ID;
 18  END Parameters_PKG;
 19  /

Package body created.

SQL>
SQL>
SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;
FROM facts f, DIM_CALENDAR c
     *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Fatal error.
ORA-06512: at "MCDONAC.PARAMETERS_PKG", line 9


SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;
SELECT f.mth, f.just_data
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Fatal error.
ORA-06512: at "MCDONAC.PARAMETERS_PKG", line 9

--
-- without deterministic
--
SQL>
SQL> CREATE OR REPLACE PACKAGE Parameters_PKG
  2  AUTHID DEFINER
  3  AS
  4     FUNCTION  get_Client_ID RETURN VARCHAR2;
  5     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  6  END Parameters_PKG
  7  ;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG
  2  AS
  3     Client_ID  VARCHAR2(255);
  4  FUNCTION get_Client_ID   RETURN VARCHAR2
  5  AS
  6     PRAGMA UDF;
  7  BEGIN
  8     IF Client_ID IS NULL THEN
  9        RAISE_APPLICATION_ERROR(-20001, 'Fatal error.');
 10     END IF;
 11     RETURN Client_ID;
 12  END get_Client_ID;
 13  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
 14  IS
 15  BEGIN
 16     Client_ID := UPPER(TRIM(p_Client_ID));
 17  END Set_Client_ID;
 18  END Parameters_PKG;
 19  /

Package body created.

SQL>
SQL>
SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;

MTH    JUST_DATA
------ ------------------------------------------------------------------------------------------------------------------------
M01    C6537FE410CFA617AFE7F17E6DD72BD9A6EF9ED08CA1216A811320A31A1FE0F9E57D832061B1A7EAA3534D8473098CBF

1 row selected.

SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = Parameters_PKG.Get_Client_ID
  4  AND F.mth = C.mth
  5  ;

no rows selected

SQL>
SQL>

这是使用 CONTEXT 变量的替代方法

SQL> CREATE TABLE factS
  2  ( mth VARCHAR2(6 BYTE)    NOT NULL
  3  , just_data VARCHAR2(120 BYTE)  NOT NULL
  4  )
  5  PARTITION BY LIST (mth)
  6  (
  7     PARTITION M01 VALUES ('M01')
  8  ,  PARTITION M02 VALUES ('M02')
  9  ,  PARTITION M03 VALUES ('M03')
 10  ,  PARTITION M04 VALUES ('M04')
 11  ,  PARTITION M05 VALUES ('M05')
 12  ,  PARTITION MAX_VALUE VALUES (DEFAULT)
 13      LOGGING
 14      ROW STORE COMPRESS BASIC
 15  )
 16  ;

Table created.

SQL> INSERT INTO facts SELECT 'M' || TO_CHAR(LEVEL, 'FM09'), STANDARD_HASH(LEVEL, 'SHA384' ) FROM dual connect BY LEVEL < 4;

3 rows created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> CREATE TABLE DIM_CALENDAR
  2  (
  3    CLIENT_ID  VARCHAR2(10 BYTE)              NOT NULL,
  4    mth        VARCHAR2(6 BYTE)               NOT NULL
  5  )
  6  ;

Table created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'TEST', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;

1 row created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'OTHER', 'M' || TO_CHAR(LEVEL, 'FM009') FROM dual connect BY LEVEL < 10;

9 rows created.

SQL> INSERT INTO DIM_CALENDAR SELECT 'ANOTHER', 'M' || TO_CHAR(LEVEL, 'FM09') FROM dual connect BY LEVEL < 2;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> create context my_context using Parameters_PKG;

Context created.

SQL> CREATE OR REPLACE PACKAGE Parameters_PKG AS
  2     PROCEDURE Set_Client_ID (p_Client_ID     VARCHAR2);
  3  END Parameters_PKG;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Parameters_PKG AS
  2
  3  PROCEDURE Set_Client_ID (P_Client_ID VARCHAR2)
  4  IS
  5  BEGIN
  6     dbms_session.set_context('MY_CONTEXT','CLIENT_ID',UPPER(TRIM(p_Client_ID)));
  7  END Set_Client_ID;
  8  END Parameters_PKG;
  9  /

Package body created.

SQL> exec Parameters_PKG.Set_Client_ID('TEST')

PL/SQL procedure successfully completed.

SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = sys_context('MY_CONTEXT','CLIENT_ID')
  4  AND F.mth = C.mth
  5  ;

MTH
------
JUST_DATA
----------------------------------------------------------------------------------------------------
M01
C6537FE410CFA617AFE7F17E6DD72BD9A6EF9ED08CA1216A811320A31A1FE0F9E57D832061B1A7EAA3534D8473098CBF


1 row selected.

SQL>
SQL> exec Parameters_PKG.Set_Client_ID('wrong_value_to_have_0_rows_returned')

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT f.mth, f.just_data
  2  FROM facts f, DIM_CALENDAR c
  3  WHERE c.CLIENT_ID = sys_context('MY_CONTEXT','CLIENT_ID')
  4  AND F.mth = C.mth
  5  ;

no rows selected

SQL>