Performance issues with outer joins to view in Oracle 12c

Question

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

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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;

Performance issues with outer joins to view in Oracle 12c

Sign up