Deffensive upgrade methods, but still no pain no gain

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’));

Turkcell R&D – Software Development Internship 2007 started

I have been working on my company’s summer internship program for the last five years; http://tonguc.oracleturk.org/index.php?s=turkcell+staj

Every year it starts with choosing the right people and matching them with the right team, we work on small assignments with candidates; https://tonguc.wordpress.com/2006/12/25/hello-world/

With the chosen ones we start for a three months internship and support them with an education program; http://apex.oracle.com/pls/otn/f?p=20898:1:332182477501552

This year Bilal is writing his experiences, this is unique since anyone will have a chance to follow the program with his chronicles;
http://www.bhatipoglu.com/entry.asp?id=6

It is always a great experience for me to study with young talented software engineers, I am sure someday these guys will have important contributions to this community :)