lstebbins lstebbins - 3 years ago 201
SQL Question

Performance issues with outer joins to view in Oracle 12c

Two of my clients have recently upgraded to

Oracle 12c 12.1.0.2
. Since the upgrade I am experiencing significant performance degradation on queries using views with outer joins. Below is an example of a simple query that runs in seconds on the old
Oracle 11g 11.2.0.2
database but takes several minutes on the new
12c
database. Even more perplexing, this query runs reasonably fast (but not as fast) on one of the
12c
databases, but not at all on the other. The performance is so bad on the one
12c
database that the reporting I've developed is unusable.

I've compared indexes and system parameters between the
11g
and two
12c
databases, and have not found any significant differences. There is a difference between the
Execution Plans
, however. On
11g
the outer join is represented as
VIEW PUSHED PREDICATE
but on
12c
it is represented as a
HASH JOIN
without the
PUSHED PREDICATE
.

When I add the hint
/*+ NO_MERGE(pt) PUSH_PRED(pt) */
to the query on the
12c
database, then the performance is within seconds.

Adding a hint to the SQL is not an option within our
Crystal Reports
(at least I don't believe so and also there are several reports), so I am hoping we can figure out why performance is acceptable on one 12c database but not on the other.

My team and I are stumped at what to try next, and particularly why the response would be so different between the two
12c
databases. We have researched several articles on performance degradation in
12c
, but nothing appears particularly applicable to this specific issue. As an added note, queries using tables instead of views are returning results within an acceptable timeframe. Any insights or suggestions would be greatly appreciated.

11g database

12c database

Query:

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

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download