Thanks for the question, Ahmed.
Asked: October 22, 2016 - 10:41 am UTC
Last updated: October 24, 2016 - 1:22 am UTC
Version: 12.1.0.2
Viewed 10K+ times! This question is 
You Asked
Hello,
I'm recently upgraded my Oracle BRM [Billing and Revenue Management] database from 11.2.0.3 on HPUX to 12.1.0.2 on Linux X64 using XTTS.
after upgrade I copied schemas statistics from 11g to new 12c database and the performance was good as old production.
once I gather statistics or I do any maintenance task on 12c database lime index rebuild or gather statistics the performance becomes very bad.
I checked the execution plan for the SQL statements taking most of the database resources they have the same execution plan like old 11g production.
Please help to identify the root cause of the issue and how to solve.
and Connor said...
Some ideas
1) grab some AWR reports so you have a list of the problem SQL's
2) restore the statistics that were making performance run fine. Alternatively, set optimizer_features_enable to 11.2.0.3 to restore performance to as before whilst you do some analysis
3) gather new statistics in PENDING mode (ie, not published)
4) run those problem SQL's in a session where your have set
alter session set optimizer_use_pending_statistics=true;
so they will pick up the new (but not yet activated) statistics
See how they go. Tune SQL's appropriately.
Check out both the optimizer blog (blogs.oracle.com/optimizer) and the upgrade blog (blogs.oracle.com/upgrade). They both have entries on handling optimizer changes between versions.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment