Diagnosing Performance Issue Due To Unnest Subquery

History:

There were two databases(ABCD2 and ABCD3) on a server. Due to increased workload and limited resources, it was decided to migrate ABCD2 database to new server. New database was created on new server with the name of ABCD. Full database was replicated and all the MVIEWS got replicated same way as it was on ABCD2 database. Now, all the applications had to be migrated which were running on ABCD2 database. Finally one of the applications was identified to migrate first.

Problems:

After pointing change from existing database(ABCD2 which was running on 10.2.0.4) to new one(ABCD database which was running on 10.2.0.5), number of active sessions got increased. Run queue reached more than 200 within 5  minutes. Changes were reverted back.

Analysis:

During analysis of root cause, it was found that plans were changed due to unwanted unnest subqueries(VW_SQ_n) in plans. In good plan, there was only one unnest subquery.
Bad plans had 5 unnest subqueries((VW_SQ_1 to VW_SQ_5).

Resolution:

After setting below hidden parameter at instance level, plan changed into good one and issue got resolved.

alter system set "_unnest_subquery"=false;

After setting this hidden parameter all plan of queries changed as expected except few queries.

For those few queries, stored outlines were created after setting _unnest_subquery to true at session level.

In this way over all issue got resolved and all the applications were migrated smoothly.

Benefits for other databases:

Later, all ABCD databases were upgraded to 10.2.0.5, the bug was fixed in similar way.