Why does Oracle performance go bad after a migration?

So, what do you do if you find that your well-optimized database performs poorly after a migration to a new release?  In my experience it’s most likely sub-optimal SQL execution, and rarely, sub-optimal PL/SQL.  I follow these steps:

1 – Compare an execution plan from before the migration to a post-migration execution plan

2 – Ensure that you have re-analyzed your schema using the dbms_stats utility

3 – Check the Oracle docs for recommended changes to init.ora parameters.

Here are details:

1 - Find a super bad-performing SQL and dive-in to see exactly why the SQL is executing in a sub-optimal fashion.  Use the "set autotrace traceonly explain" command in SQL<*Plus, get the execution plan, and compare the execution plan to the same SQL in your old database.  For details on evaluating execution plans, see this article:

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

2 - Make sure to re-analyze your schema using dbms_stats.  High-quality metadata can make all the difference.  Read this:

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

3 - Try re-setting Oracle initialization parameters: 

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

• optimizer_mode – If you have an OLTP database, try first_rows.  Also, if your old optimizer_mode was “choose” you will need to pick a new one in Oracle10g, usually all_rows or first_rows (sometimes first_rows_100 too).
   
• optimizer_index_cost_adj = 25 – This adjusts the optimizer to favor index access
   
• optimizer_index_caching = set to percentage of indexes that are buffered according to x$bh buffer data.  A script to give you the best initial setting can be downloaded here:

             http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

Remember, the all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query: