Performance issues with outer joins to view in Oracle 12c

as krokodilko says, perform these :

explain plan for 
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;
select * from table(dbms_xplan.display());

and then, you probably see this at the bottom of the plan :

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

there,

dynamic sampling

concept should be the center of concern for performance problems(level=2 is the default value, ranges between 0-11 ).

In fact, Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The most common misconception is that DS can be used as a substitute for optimizer statistics, whereas the goal of DS is to augment optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates.

For serial SQL statements the dynamic sampling level is controlled by the

optimizer_dynamic_sampling

parameter but note that from Oracle Database 12c Release 1 the existence of SQL plan directives can also initiate dynamic statistics gathering when a query is compiled. This is a feature of adaptive statistics and is controlled by the database parameter

optimizer_adaptive_features (OAF)

in Oracle Database 12c Release 1 and

optimizer_adaptive_statistics (OAS)

in Oracle Database 12c Release 2.

In other words, from Oracle Database 12c Release 1(we also use db12.1.0.2 at office) onwards, DS will be used if certain adaptive features are enabled by setting the relevant parameter to TRUE.

Serial statements are typically short running and any DS overhead at compile time can have a large impact on overall system performance (if statements are frequently hard parsed). For systems that match this profile, setting OAF=FALSE is recommended( alter session set optimizer_adaptive_features=FALSE notice that

you shouldn't alter system but session

).

For Oracle Database 12c Release 2 onwards, using the default OAS=FALSE is recommended.

Parallel statements are generally more resource intensive, so it's often worth investing in additional overhead at compile time to potentially find a better SQL execution plan.

For serial type SQL statements, you may try to manual set the value for optimizer_dynamic_sampling(assuming that there were no relevant SQL plan directives). If we were to issue a similar style of query against a larger table that had the parallel attribute set we can see the dynamic sampling kicking in.

When should you use dynamic sampling? DS is typically recommended when you know you are getting a bad execution plan due to complex predicates. But shouldn't be system-wide as i mentioned before.

When is it not a good idea to use dynamic sampling? If the queries compile times need to be as fast as possible, for example, unrepeated OLTP queries where you can't amortize the additional cost of compilation over many executions.

As the last word, for your case, it could be beneficient to set optimizer_adaptive_features parameter to FALSE for individual SQL statements and see the gained results.

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;