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
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 Remote 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
Remote 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
10g New Features” by Mike Ault, Madhu Tumma and Daniel Liu, and see the
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
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
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.
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.