Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

July 16, 2007

Deffensive upgrade methods, but still no pain no gain

Filed under: Oracle 10g New Features,Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 9:35 am

As Oracle continues to develop the Database Software, changes are introduced in the optimizer that are designed to give better performance. Occasionally, changes that provides improved performance for many, can have an adverse effect for a small number of others. New versions can also require different approaches in system management to maintain, or achieve, better performance.

The following notes give guidance on what to do when upgrading to ensure good performance is maintained and what to do if a performance regression is encountered:

Metalink Note 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
Metalink Note 258167.1 Upgrading from 8.1.X to 9.X – Potential Query Tuning Related Issues
Metalink Note 258945.1 Upgrading from 8.1.X to 9.X – Subquery Issues – Diagnosing and Resolving
Metalink Note 258946.1 Upgrading from 8.1.X to 9.X – View Issues – Diagnosing and Resolving
Metalink Note 259126.1 Upgrading from 8.1.X to 9.X – Btree Bitmap Plan Issues – Diagnosing and Resolving
Metalink Note 295819.1 Upgrading from 9i to 10g – Potential Query Tuning Related Issues
Metalink Note 223806.1 Query with unchanged execution plan is slower after database upgrade

As a summary I suggest you do good testing before you do a major Oracle release upgrade. Dump and store all your critical execution plans with Event 10132 trace, use those trace files as a library to check for the problems that may occur after migration.

Some default values of hidden optimizer parameters change unfortunately so try below to disable these new behaviours at session level during your tests;

— during 9i -> 10g
alter session set “_optimizer_cost_based_transformation” =off;
alter session set “_gby_hash_aggregation_enabled” = FALSE;

— during 8i -> 9i
alter session set “_UNNEST_SUBQUERY” = false;
alter session set “_ALWAYS_SEMI_JOIN” = off;
alter session set “_ALWAYS_ANTI_JOIN” = off;
alter session set “_COMPLEX_VIEW_MERGING” = false;
alter session set “_B_TREE_BITMAP_PLANS” = false;

— with below hint after 10g you can also manuplate a parameter at statement level
select /*+ opt_param(‘hash_join_enabled’,’false’) */ empno
from emp e, dept d where e.ename=d.dname;

When you have time these may also have your interest;

https://tonguc.wordpress.com/2007/03/01/another-great-undocumented-oracle-hint/
Metalink Note:398838.1 FAQ: Query Tuning Frequently Asked Questions
Metalink Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Metalink Note 163563.1 TROUBLESHOOTING: Advanced Query Tuning

ps: with 10g I love playing with this view;

SELECT *
FROM (SELECT rownum rn, a.sql_text, a.CPU_TIME, a.executions
FROM v$sqlstats a
ORDER BY a.CPU_TIME DESC)
WHERE rn < 11

and this package;

explain plan set statement_id ‘tong’ for
SELECT …
;

SELECT plan_table_output
FROM TABLE(dbms_xplan.display(NULL, ‘tong’, ‘ALL’));

2 Comments »

  1. Upgrade from Oracle 9i to 10g: What to expect from the optimizer
    Oracle white paper, Feb 2008
    http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf

    Comment by H.Tonguç Yılmaz — February 27, 2008 @ 8:54 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: