Jump to content
Sanjay Rawat

Huge Performance Impact after upgrade to 12.1.0.2 from 10.2.0.5

Recommended Posts

Facing huge performance issue with all sessions after upgrade from 10.2.0.5 to 12.1.0.2 recently. DB is single instance only and after upgrade all session taking more than expected time.

1. 4-5 min query taking around 1 hour and more.

2. All queries doing lots of IO as compare to old report.

3. CPU reaching to 100% every time.

4. Most Important thing is after upgrade database is not using old indexes and even tuning and access advisor not suggesting to create new indexes.

Steps I already implemented are as below.

SQL> show parameter DISK_ASYNCH_IO

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
disk_asynch_io                       boolean                          TRUE

SQL>  show parameter _optimizer_aggr_groupby_elim

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_optimizer_aggr_groupby_elim         boolean                          FALSE

SQL> show parameter _optimizer_reduce_groupby_key

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_optimizer_reduce_groupby_key        boolean                          FALSE

SQL> show parameter optimizer_dynamic_sampling

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_dynamic_sampling           integer                          0

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          FALSE

 

SQL> show parameter OPTIMIZER_ADAPTIVE_FEATURES

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          FALSE

 

  Please suggest ASAP as this prod DB and we getting WAR room on daily basis.

Share this post


Link to post
Share on other sites

Hi Sanjay,

Why did you did not notice this when you upgraded your test instance?

First, you were right in disabling OPTIMIZER_ADAPTIVE_FEATURES!

This is a common issue, and I have notes here:

http://www.dba-oracle.com/t_12c_upgrade_slow_performance.htm

As a temporary stopgap, you can set optimizer_index_cost_adj to a value of 10:

Please read this carefully:

http://www.dba-oracle.com/oracle_tips_cost_adj.htm

Good luck!

 

Share this post


Link to post
Share on other sites

this values is already set to 100, do you want me to decrease it ?

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_index_cost_adj             integer                          100
SQL> 
 

Share this post


Link to post
Share on other sites

Hi Sanjay,

Yes, setting OICA to 10 should force index usage, but ONLY until you find and fix the real cause of the problem.

This is only a stopgap measure.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×