Jump to content

Sanjay Rawat

Members
  • Content count

    2
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Sanjay Rawat

  • Rank
    Newbie
  1. 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>
  2. 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.
×