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
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
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
Again, these are core features of Oracle9i, and you
will immediately see improvements within these areas.
However there are hundreds of optional features within
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.
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.
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:
EXTENT MANAGEMENT LOCAL -- Turn on LMT
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM
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
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.
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.
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.
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 new Oracle9i
dbms_metadata package is a handy way for the DBA
to punch table and index definitions out of the data
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
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
- Scalar subqueries—Starting in Oracle9i,
you can place subqueries inside the SELECT clause.
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
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