当"PARTITION LIST SUBQUERY"时，执行计划中有什么(错误?)实例化了程序包 [英] When "PARTITION LIST SUBQUERY" is in the execution plan something (a bug?) de-instantiates the package

查看：70
本文介绍了当"PARTITION LIST SUBQUERY"时，执行计划中有什么(错误?)实例化了程序包的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是Oracle 12c的错误吗?

Is this an Oracle 12c bug?

我在Oracle Linux上运行64位Oracle 12.1.0.2.
遇到了一件奇怪的事情:当执行计划切换为使用"PARTITION LIST SUBQUERY"时，受影响的查询中使用的程序包将失去其所有变量值.就像在运行DBMS_SESSION.RESET_PACKAGE之后，会取消实例化程序包一样.
该查询使用分区表，该分区表通过与另一个表连接而受到限制，该另一个表使用确定性"getter"函数从使用软件包读取的变量中进行限制. 如果我更改功能不是确定性的，或进行了任何更改以使解释计划未使用"PARTITION LIST SUBQUERY"，则不会出现问题.

I run 64-bit Oracle 12.1.0.2 on Oracle Linux.
Came across a strange thing: when the execution plan switches to using "PARTITION LIST SUBQUERY" then the package used in the affected query is loosing all of its variable's values. It looks like something de-instantiates the package just like after running DBMS_SESSION.RESET_PACKAGE.
The query uses a partitioned table which partitions are limited by joining with another table limited using a variable from the package read using a deterministic "getter" function. If I change the function not to be deterministic, or change anything so the explain plan is not using "PARTITION LIST SUBQUERY" the problem does not appear.

请参见在我的数据库上生成带有"PARTITION LIST SUBQUERY"的解释计划的综合示例(尝试4):

See the synthetic example that produces the explain plan with "PARTITION LIST SUBQUERY" on my databases (tried on 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    

*/

推荐答案

删除确定性"关键字.确定性意味着对于同一组输入，我可以返回相同的输出，而无需再次评估该函数.您没有对该功能的输入，因此我们永远不需要运行它.因此，我们将返回null，例如(省略您的设置代码)

Remove the 'deterministic' keyword. DETERMINISTIC means for the same set of inputs I can return the same output without evaluating the function again. You have no inputs to that function, so we never need to run it. So we'll return null, eg (with your setup code omitted)

--
-- 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变量的替代方法

And here is an alternative using a CONTEXT variable

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>

这篇关于当"PARTITION LIST SUBQUERY"时，执行计划中有什么(错误?)实例化了程序包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆