Oracle 9i de-support coming in July 2007

Oracle 9i de-support coming in July 2007

Oracle Tips by Burleson Consulting

Oracle has announced desupport for Oracle 9i release 2 as of July 31, 2007. This is an important announcement for any Oracle customers who must run on a fully-supported release of their Oracle database. You can see Metalink note 161818.1 for complete details on this announcement.

While Oracle offers a limited "extended support" plan, Oracle9i is still desupported for any new bug fixes as of July 31, 2007. For shops that are required to run a fully-supported release of Oracle, it's time to plan a migration to Oracle 10g release 2.

Moving to Oracle 10g

The 10g migration is straightforward, but the kernel source code has changed in Oracle10g, leading to unplanned changes in Oracle execution. This is largely due to changes in Oracle defaults for SQL optimization and initialization parameters.

It's never a good idea to plan an Oracle 10g migration without fully understanding the important changes in Oracle 10g, as many of the changes are complex and unobtrusive.

Understanding Oracle 10gNew Features

The benefits of an upgrade to Oracle 10g are very compelling. Oracle has completely rewritten the source code to make it far faster, and native PL/SQL can run many times faster than Oracle9i. Oracle 10g is also more stable and robust. Also many DBA features are automated in Oracle 10g including optimizer statistics collection and the new Automatic Workload repository which captures important time-series performance data.

Oracle10g is one of the most important new releases of Oracle in many years, and Oracle 10g provides a wealth of features that can be used to automate almost every aspect of its database administration. It is important to note that these automation features are optional, and they are not intended to replace standard DBA activities. Rather, the Oracle10g automation features are aimed at shops that do not have the manpower or expertise to manually perform the tasks. Click the link below to take a closer look at the top Oracle 10g new features. For complete details on Oracle 10g new features, see the book �Oracle 10g New Features� by Mike Ault, Madhu Tumma and Daniel Liu, and see the Oracle 10g Release 2 top new features:

Oracle 10g performance changes

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. 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 is the worlds 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.

These common Oracle 10g performance issues are addressed in the Donald K. Burleson book "Oracle Tuning: The Definitive Reference".

Here are some of the most common problems that shops encounter during a migration to Oracle 10g:

- Check optimizer parameters - Ensure that you are using the proper optimizer_mode (default is all_rows) 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).

- 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 try to minimize CPU by 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).

- 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.

Changes to Oracle 10g SQL processing

It�s not uncommon to find many SQL statements that run far faster on Oracle 10g, while some may degrade in performance. 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.

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.

Support for Oracle 10g Migrations

There are many perils and tricks in a 10g migration and it is highly recommended that Oracle9i shops who migrate to Oracle 10g consult with a 10g migration expert to ensure a smooth an optimal migration.

Burleson Consulting has extensive experience in ensuring smooth Oracle 10g migrations and you can call 800-755-1884 to speak with an Oracle migration expert.