Two of my clients have recently upgraded to
Oracle 12c 12.1.0.2
Oracle 11g 11.2.0.2
12c
12c
12c
11g
12c
Execution Plans
11g
VIEW PUSHED PREDICATE
12c
HASH JOIN
PUSHED PREDICATE
/*+ NO_MERGE(pt) PUSH_PRED(pt) */
12c
Crystal Reports
12c
12c
select pi.*
, pt.*
from policyissuance_oasis pi
, policytransaction_oasis pt
where
pi.newTranKeyJoin = pt.polTranKeyJoin(+)
and pi.policyNumber = '1-H000133'
and pi.DateChars='08/10/2017 09:24:51' -- 2016 data
--and pi.DateChars = '09/26/2016 14:29:37' --2013 data
order by pi.followup_time
We discovered the cause of the performance issue. The following 2 system parameters were changed at the system level by the DBAs for the main application that uses our client's Oracle server:
_optimizer_cost_based_transformation = OFF
_optimizer_reduce_groupby_key = FALSE
When we changed them at the session level to the following, the query above that joins the 2 views returns results in less than 2 seconds:
alter session set "_optimizer_cost_based_transformation"=LINEAR;
alter session set "_optimizer_reduce_groupby_key" = TRUE;
COMMIT;
Changing this parameter did not have an impact on performance:
alter session set optimizer_adaptive_features=FALSE;
COMMIT;
We also found that changing the following parameter improved performance even more for our more complex views:
alter session set optimizer_features_enable="11.2.0.3";
COMMIT;