Oracle 19c: Bug 27175987 — Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c

 

As we see 19c comes with lot of performance instability ,  recently  we been  facing issues with sql on partition tables . 

It was  observed that After upgrade to 19c, partition pruning  not work for the SQL with predicates of user defined function, while partition pruning works for the same SQL prior to 19c.

This is bug 27175987, also not published. Made as fix_control, can be seen in v$system_fix_control, can be disabled if desired.

Fix 1) 

Enable partition pruning disable the fix of Bug 27175987 by setting "_fix_control" = '27175987:off'

alter session set "_fix_control" = '27175987:off';

or

Add hint /*+ OPT_PARAM('_fix_control' '27175987:off') */:

Fix 2 ) 

Change user defined function to deterministic PL/SQL functions if it is non-deterministic, and add the DETERMINISTIC clause to the function if the function is truly deterministic

SQL> CREATE or REPLACE FUNCTION <USER_FUNCTION_NAME>(ARG VARCHAR2) RETURN VARCHAR2 deterministic IS -- Add "deterministic"

BEGIN

  RETURN ARG;

END;

/

Reference : 

1) Query Performance Issues after upgrade to 19C (Doc ID 2739411.1)

2) Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c (Doc ID 2717940.1)

3) Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)