Migration to Oracle 9i - Understanding the features

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 asm_lmt_tsdatafile 'c:\oracle\oradata\diogenes\asm_lmt.dbf'size 5mEXTENT MANAGEMENT LOCAL -- Turn on LMTSEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;

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. begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => 7 );end;

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