Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






What to expect when you migrate to Oracle9i

Oct 16, 2015 - Donald Burleson

With the impending desupport of Oracle8i in December 2015, many shops are preparing to make their move to Oracle9i. So, what can you expect when you upgrade to Oracle9i? Actually, Oracle9i is very stable and most shops are quite thrilled with the performance improvements and new features. Let's take a closer look at what you should expect after you migrate.

Core features of Oracle9i

There are many enhancements that will be immediately available when you first start your database using the Oracle9i software. The core improvements in Oracle9i that you will immediately see include:

  • Improved Optimizer—The Oracle9i Cost-base Optimizer (CBO) has been improved to generate more efficient execution plans, and it also has new SQL execution plans, including the index skip scan, index-only scans on function-based indexes, and new optimizer modes including first_rows_1, first_rows_10, and first_rows_100. There are also bitmap join indexes for faster SQL execution:
  • Optimized PL/SQL—Oracle9i has greatly improved the speed of PL/SQL execution, and most PL/SQL applications will run faster.
  • New Dictionary Views—The data dictionary has been enhanced to allow the DBA to see the execution plan for SQL in the library cache with the new v$sql_plan view.

Again, these are core features of Oracle9i, and you will immediately see improvements within these areas. However there are hundreds of optional features within Oracle9i.

Optional features of Oracle9i

Oracle introduced hundreds of optional enhancements and new features and utilities in Oracle9i, and it is sometimes difficult to wade through the huge menu of features deciding which are right for you. In the months since Oracle9i has become available, some optional new features have become very popular. Let's discuss some of these features.

Dynamic SGA management

Starting in Oracle9i, almost all of the initialization parameters can be changes which alter system commands. This removes the requirement to bind the database each time a change is made to initialization parameters. More importantly, the dynamic SGA features allow the DBA to resize the shared pool and data buffers in real-time whenever processing patterns change.

Bitmap freelists

By creating tablespaces with automatic segment space management (ASSM) you can replace the old-fashioned, one-way linked-list freelists with bitmap freelists. For systems that experience high-volume updating, bitmap freelists can virtually eliminate the segment header contention (i.e., buffer busy waits) that occur during high-volume updates, as in this example:

create tablespace

Automatic PGA consolidation

In Oracle9i you can remove the cumbersome external PGA RAM regions and replace them with a single, shared RAM area for sorting and hash joins. This is done by setting the pga_aggregate_target parameter and removing the obsolete sort_area_size and hash_area_size parameters. Using the pga_aggregate_target, a single shared RAM area is used for sorting and hashing, and Oracle automatically manages the internal details.

Large blocksizes for indexes

Many shops have realized a reduction in disk I/O by moving their index tablespaces into a 32-KB block size. The Oracle9i database supports multiple blocksizes, and you can define a db_32k_cache_size RAM area to separately cache your new 32-KB blocks. Researchers have found that moving to a 32-KB blocksize reduces disk I/O for index range scans and also creates more uniform b-tree structures.

Online table reorganization

The new dbms_redefinition package allows the DBA to reorganize tables while the table is open and accepting updates. This allows the DBA to perform database maintenance without interrupting system availability.

Automatic histogram collection

The new dbms_stats package (available with release 2) will automatically detect skewed columns, and create histograms only for those columns where the skew would influence the CBO's execution plan decisions.

The auto option withindbms_stats is used when Oracle table monitoring is implemented using the alter table xxx monitoring; command. The auto option, shown below, creates histograms based upon data distribution and the manner in which the column is accessed by the application (i.e., the workload on the column as determined by monitoring). Using method_opt=>'auto' is similar to using the gather auto in the option parameter of dbms_stats.

     ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size auto',
      degree           => 7

The dbms_metadata package

The new Oracle9i dbms_metadata package is a handy way for the DBA to punch table and index definitions out of the data dictionary.

Fine-grained auditing (FGA)

Starting in Oracle9i there is an easy way to audit access to confidential table rows. Using the new dbms_fga package, the DBA can implement a complete audit trail of all SELECT statements, recording the name, time of access and columns viewed. This is especially important for health care shops that are required by HIPAA to audit viewing of confidential patient information.

Oracle SQL syntax enhancements

Oracle9i has made substantial extensions to SQL syntax allow for powerful new operations:

  • CASE statement—The cumbersome decode syntax is replaced with easy-to-read CASE syntax.
  • Natural joins—In Oracle9i, it is no longer necessary to specify the join keys for multitable SQL statements.
  • Scalar subqueries—Starting in Oracle9i, you can place subqueries inside the SELECT clause.

External tables

One of the most exciting new features of Oracle9i is the ability to take a flat file on the operating system and define it to Oracle as if it were a table. This allows you to run SQL against the flat file data, and you can even make Microsoft-Excel spreadsheets accessible via SQL.


As you can see, there are a host of benefits in migrating to Oracle9i, and a wealth of choice in which new features to implement. Here, I have shown only some of my favorites; there are many more features to explore. For a complete list of Oracle9i new features, see the Oracle online documentation.

If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference". 

Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.