Oracle 10g Migration: slow performance after upgrade to Oracle10g Oracle Database Tips by Donald Burleson

Question:  I just upgraded to Oracle 10g and I'm seeing very bad SQL performance.  I had to set optimizer_features_enable=9.0.5.  What can I do to fix Oracle10g upgrade & migration performance tuning problems?

Answer:  Oracle has improved the cost-based Oracle optimizer in 9.0.5 and again in 10g, so you need to take a close look at your environmental parameter settings (init.ora parms) and your optimizer statistics.  I have complete directions in my book "Oracle Tuning - The Definitive Reference", but here are some notes.  See also Oracle tips for 10g migration. and Oracle 11g upgrade performance problems.

For Oracle 10g performance problems, call BC at 800-766-1884.  We are expert at Oracle10g performance problems and we know many tricks that are not published by Oracle for achieving optimal tuning.

First, make sure to see these important notes on _optimizer_cost_based_transformation issues when doing 10g upgrades. Also note that 9i has many optimizer bugs, notably issues that are corrected with _optimizer_transitivity_retain when using query re-write.

Properly configured, Oracle 10g should always faster than earlier releases, both for PL/SQL and SQL, so it is likely that any slow performance after an Oracle 10g upgrade is due to initialization parameter settings or incomplete CBO statistics. 

Oracle 10g Optimizer Issues

Oracle 10g automated statistics gathering caused problems because the default for estimate_percent is auto_sample_size and the default for method_opt is "for all columns size auto".

• In 10g - avoid auto_sample_size:  auto_sample_size fixed in 11g - only works when you specify estimate_percent = auto_sample_size.
  
• Histograms in 10g cause troubles because predicates with binds did not work (bind peeking issue)

Oracle 10g migration due diligence

Oracle 10g is the world's most flexible and complex database, and upgrading to Oracle 10g is very tricky.  Prior to putting your Oracle 10g upgrade into production, it's a best practice to obtain an independent Oracle health check to identify sub-optimal configuration settings. 

Remember that there were many optimizer enhancements and bug fixed in Oracle 10g and you can use the  v$system_fix_control  view to display specific optimizer features by release.

Reasons for sub-optimal Oracle 10g performance after migration

For complete insurance against bad performance after a 10g upgrade, see my book  "Oracle Tuning: The Definitive Reference".

• Check for 10g optimization bugs - See MOSC  Note 469972.1, note 240764.1, note 466181.1 and note 337096.1.

• Gather workload statistics - The 10g CBO requires workload information with dbms_stats.gather_system_stats

• Selectively disable dynamic sampling - Dynamic sampling is not for every database.  Dynamic sampling default levels change between releases, and you may want to turn-off dynamic sampling, depending on your database load.

•  Re-set optimizer costing - Consider unsetting your CPU-based optimizer costing (the 10g default, a change from 9i).  CPU costing is best of you see CPU in your top-5 timed events in your STATSPACK/AWR report, and the 10g default of _optimizer_cost_model=cpu will include CPU costs, sometimes invoking more full scans, especially in tablespaces with large blocksizes.  To return to your 9i CBO I/O-based costing, set the hidden parameter "_optimizer_cost_model"=io

• Verify deprecated parameters - you need to set  optimizer_features_enable = 10.2.0.2 and optimizer_mode = FIRST_ROWS_n (or ALL_ROWS for a warehouse, but remove the 9i CHOOSE default). 

• - Re-check your db_file_multiblock_read_count parameter - The Oracle 10g release 2 Performance Tuning Guide (page 14.4) notes:

  "The value of db_file_multiblock_read_count is set to the maximum allowed by the operating system by default. However, the optimizer uses mbrc=8 for costing.

  10gr2 Note:  Starting in Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.  For more details, see my notes on 10gR2 automatically tuned multi-block reads.

• Verify quality of CBO statistics - Oracle 10g does automatic statistics collection and your original customized dbms_stats job (with your customized parameters) will be overlaid.  You may also see a statistics deficiency (i.e. not enough histograms) causing performance issues.  Re-analyze object statistics using dbms_stats and make sure that you collect system statistics.  Also see 10g changes to dbms_stats for more details.
  
  
  execute dbms_stats.gather_system_stats('start');
  -- wait an hour or so
  execute dbms_stats.gather_system_stats('stop');
  
  

• Check optimizer parameters - Ensure that you are using the proper optimizer_mode (the new default is all_rows instead of choose) and check optimal settings for optimizer_index_cost_adj (lower from the default of 100) and optimizer_index_caching (set to a higher value than the default). 

• Check I/O timings - This guy has noted that in Linux 2.6 using ASM, "db file scattered reads" (full scan I/O) can become slower than "db file sequential reads" (usually single block gets) because of non-contiguous data block placement on disk.  He notes that full-scan access speed is aggravated by Oracle willy-nilly block placement in Automated Storage Management (ASM) and the use of bitmap freelists (Automated Segment Storage Management).

• Check Cartesian Merge Joins - Unnecessary cartesian merge joins have been reported as a source of performance issues.  Disabling Cartesian Merge Joins is possible but is discouraged.  It's always best to look for the root cause instead of using undocumented or hidden parameters as a workaround.  The warning below about modification of undocumented parameters should be taken very seriously.

To find the exact root cause of the changed SQL performance, start by collecting the execution plans and TKPROF output for the 10g default and again after setting optimizer_features_enable).  Compare the plans and then see how you might adjust initialization parameters and CBO statistics (using dbms_stats) to replicate the optimal SQL execution plan.

Tip - If you have a SQL statement which has poor performance after a 10g upgrade, try temporarily adding a /*+ rule */ hint to see if the problem is related to sub-optimal optimizer statistics. 

If the RULE hint optimizes the SQL, remove the RULE hint and adjust your statistics until it replicates the execution plan.

DBA Reports on 10g upgrade performance

This document shows some parameters which relieved slow SQL performance after a 10g upgrade by George Johnson:

After our upgrade from 9206 to 10201, we ended up with these parameters making the biggest difference to our slow query performance. The optimizer_index_cost_adj figure was arrived at after about 2 days of testing various troublesome
queries.

optimizer_secure_view_merging = false
_gby_hash_aggregation_enabled = FALSE
optimizer_index_cost_adj = 50
optimizer_index_caching = 0
_optimizer_cost_based_transformation = OFF

We were told by one Oracle guy that if your DB is not a warehouse and it's used batch and OLTP, the bottom four parameters should be set in 10g, without question to ensure the Warehouse components do not affect OLTP type activity!

Warning:  You should only change underscore undocumented parameters at the direct request of Oracle technical support.

This document notes other 10g upgrade issues with advanced SQL optimization in subquery un-nesting and outer hash joins:

"I have noticed after upgrading from 9i to 10g, the biggest impacts for query performance were -

(1) subquery unnesting (mostly, unnesting EXISTS to HASH JOIN SEMI), and

(2)right outer hash join (smaller table in an outer join *is* used as the hash table, unlike what was happening in pre-10g versions).

In some cases (till 10.1.0.4) we would have to hint the sub-query with NO_UNNEST to avoid the hash join semi. . .

The only ways to get rid of this problem (ora-979 is omitted if it is raised in inline view) is to use the hint NO_MERGE in 9i, and depending on 10g version and testmerge table columns - setting the '_OPTIMIZER_COST_BASED_TRANSFORMATION' to off on 10g."

This blog by Shervin Sheidaei notes a similar performance issues after a 10g upgrade and a change to "_optimizer_cost_based_transformation" =off; and "_gby_hash_aggregation_enabled"=false:

Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.

For figuring out whether or not performance issue is because of new enhanced features in 10g please turn off the following options and run query in 10g.

alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).

alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).

More information on the impact of hash aggregation on performance and the expected effect of disabling GROUP BY aggregation by setting _gby_hash_aggregation_enabled  to FALSE is available HERE. 

David Aldridge has concerns about disk read performance when using ASM and ASSM, noting that placement on the disk and competing I/O requests can impede scattered read access times (as the read-write head thrashes):

"Does the issue of the slowing of one read matter when other process are being equally served at the same time? That's a question at the very heart of i/o scheduling (and queuing theory, which is what all this is about) — what is meant by "equally served"?

If it means that the scheduler finishes with one read request and then immediately moves the disk heads to satisfy another, then moves the heads back to satisfy another request from the first process then that might seem equitable, but it's exactly analogous to a single check-in handling multiple check-ins at the airport at the same time.

Consider passenger A and passenger B, both waiting to be served. To check in each passenger takes five minutes, so passenger A is checked in in five minutes and passenger B waits for five minutes then gets checked in and is gone after a total wait of ten minutes.

If, in an effort to be equitable to both parties, the check-in agent flits between the two then the total time to check them both in is now eleven minutes (taking into account a total latency of one minute due to walking between the desks), and they both wait the full eleven minutes to be finished. Not equitable at all!"

We were able to identify (via SQLT reports) an issue with VARCHAR2 and NVARCHAR2.

Essentially the ADO.net application sends NVARCHAR2 data through ODBC to the Oracle database. The data columns in the Oracle database are defined as VARCHAR2.

There is a conversion going on that converts the column data in the database to NVARCHAR2 causing full table scans. We were able to programmatically change the application to explicitly say data in string is VARCHAR  thus eliminating the overhead of the conversion and the full table scans. This drastically reduced the overhead and dropped the CPU utilization in half. We also do not see any long running items in OEM.
 
The question for Oracle is:

WHY is the database converting the column data to nvarchar2 (using the SYS_OP_C2C function) when it should know that the data type is varchar2 and should process the Unicode appropriately?
 
What we see is the exact same query coming through as varchar2 in some cases and nvarchar2 in other cases.

Expert 10g performance support

You can also telephone me for Oracle upgrade performance consulting, to quickly solve your upgrade related performance tuning slowdowns.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.