EBS R12 / RDBMS -- optimizer_features_enable setting for discoverer desktop sessions "only".

Discoverer is not certified with Oracle Database 19C.. So, EBS 19C upgrade customer should keep that in mind.. Oracle Support says, "Discoverer will not be tested or certified on Oracle Database 19C". Besides, Discoverer moved to Sustaining Support on Dec 2014. So, Business Intelligence Enterprise Edition or Oracle Analytics Cloud is the recommended reporting platform now..

Anways, we have tested discoverer with 19C in a couple of EBS environment and I can say that Discoverer is working with 19C. But! It may have severe performance problems..

So, EBS 19C Upgrade customers should test their discoverer workload carefully.  If they can't fix their performance problem quickly, we may have a workaround. 

A trigger like below may save their day. Trigger below will set the optimizer_features_enable parameter to the old version (in this case 11.2.0.4). This move can be a try and may be a quick win in some cases.. Ofcourse this is a short-term fix.. I mean, even if it saves the day for certain cases, the real cause behind those slow running queries must be found.

Note that, with only some minor updates, the trigger can be changed to make the same settings for other programs as well.

Optimizer_features_enable parameter -> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html

--11.2.0.4 optimizer_features_enable setting for discoverer desktop sessions "only".--

Note that, this is like a customization and it is your choice to take the risk.

CREATE OR REPLACE TRIGGER SYSTEM.set_optimizer_parameter_disco
AFTER LOGON
ON DATABASE when (user in ('APPS'))
DECLARE
v_program v$session.program%TYPE;
CURSOR user_prog
IS
SELECT program
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
BEGIN
OPEN user_prog;
FETCH user_prog
INTO v_program;
CLOSE user_prog;
IF LOWER (v_program) LIKE ('%dis51%')
THEN
EXECUTE IMMEDIATE 'alter session set optimizer_features_enable="11.2.0.4"';
END IF;
END;
/

The following underscore paramaters may also help.. (in our case, they helped a lot..)

_optimizer_mjc_enabled=false

_optimizer_cartesian_enabled=false

So in our case, we included the parameters (above) to our Disco trigger as well..

EXECUTE IMMEDIATE 'alter session set "_optimizer_mjc_enabled"=false' ;

EXECUTE IMMEDIATE 'alter session set "_optimizer_cartesian_enabled"=false' ;

What about the scheduled Disco reports?

Well, we needed to modify the EUL5_BATCH_USER package to make the scheduled Disco reports get our alter session settings. 

We just added our alter session settings to the EUL5_BATCH_USER ( into the PROCEDURE ExecuteQuery) and that worked. 

Note that, you need to be careful while modifying the EUL package, or creating a Disco trigger.. (you need to get downtime for this -- just in case..) 

You need to test your environment well..

These moves are unsupported.. So the risk is yours.. But! In our case they saved our day!