Understanding Oracle Features and Options

Oracle Enterprise Edition

When choosing the appropriate Oracle edition for your shop you must recognize those database features that you really need. There are many features of EE that are not available in SE, for example.

Many companies that choose to forgo the advanced features of the Enterprise Edition may save thousands of dollars per year in licensing fees. The features available in Oracle Enterprise Edition that are not available in Oracle SE and SEO include many features that your application may not require.  Note:  This article was written in 2005 and things change, so please re-verify this information.

• Advanced Replication - This tool provides one-way and multi-master replication (via database links) for distributed systems using the dbms_repcat package.
   
• Transparent Application Failover (TAF) - Used to re-direct in-flight transactions to a failover database in the case of a server crash.   This is available in Oracle SEO as of release 10.1.
   
• Fast-start fault recovery - This is a method for quickly recover and synchronize the Oracle database in cases of hardware failure.
   
• Oracle Data Guard - Data Guard is a semi-automated standby/failover database for database replication.
   
• Advanced Queuing - A software scheduling package for advanced scheduling the serialization using the dbms_aqadm package. This is available in Oracle SEO as of release 10.1.
   
• SQL Optimizer Plan Stability (Stored Outlines) - This is a tool to freeze Oracle execution plans during software upgrades and change vendor SQL when you cannot touch the source SQL.
   
• Online index rebuilds - The ability to rebuild an Oracle index while it is being updated.
   
• Export transportable tablespaces - This provides the ability to transfer encapsulated tablespaces between databases.
   
• Materialized Views - This is a powerful tool to allow for the pre-summarization of aggregate data (averages, counts, sums) and pre-join tables together. Materialized views are especially useful in low-update databases and data warehouses.  This is available in Oracle SEO as of release 10.1.
   
• Bitmap indexes - Bitmaps are a unique indexing structure for fast combinations of low-cardinality data columns. Bitmap indexes are especially powerful for data warehouses and low-update databases.  Bitmap index can only be used with Oracle Enterprise Edition (EE), not Standard Edition.  Hence, STAR transformation queries are impossible on Oracle SE and SEO.
   
• Oracle Parallel Query (OPQ) - Parallel query is a divide-and conquer approach whereby Symmetric Multiprocessing (SMP) and Massively Parallel Processors (MPP) can get super-fast response time for large-table full-table scans
   
• Parallel DML - This is the ability to perform database changes (inserts, updates, deletes) in parallel.
   
• Parallel index rebuilding - This allows large Oracle indexes to be rebuilt in a fraction of the time required by a single CPU system.
   
• Parallel index scans - This feature allows for parallel fast full-index scans and multi-block reads of index data blocks.
   
• Parallel backup & recovery - This allows Oracle RMAN backups to be parallelized for super-fast backups of large databases.
   
• Incremental backup & recovery - This feature tracks database changes and only backs-up those components that have been changed since the last backup.
   
• Oracle connection manager (CMAN) - Thos tool is used for Oracle databases that must support large volumes of concurrent user connections.
   
• Oracle Streams - Oracle offers many different methods for database failover including standby database, Oracle Data Guard, Oracle RAC and Oracle Streams. Oracle Streams provides high-speed system replication and failover software for mission-critical systems.

Reader comment on Oracle Streams:  "I have found contradictory information about Oracle Streams… appears to be available with 10.1 SE/SEO, at least as far as AQ requires… The matrix on MOSC for “Differences Between Different Editions of Oracle Database 10G Release 1” says that Oracle Streams is not available; but, AQ uses Oracle Streams, and AQ is available, therefore, some aspect of Oracle Streams is available. "  - David Russell, 3E Company, 5./12/2009

Extra-cost Oracle Options

In addition, Oracle offers many extra-cost database features that you can buy independently from the edition. These include:

• Oracle Partitioning Option - Oracle divide-and-conquer approach for super-large databases. Partitioning is generally used for databases over 100 gigabytes.
   
• Oracle Real Application Clusters (RAC) - RAC is the Oracle flagship software for scalability and high availability. Free with Oracle Standard Edition and extra-cost with Enterprise Edition, RAC is an integral part of Oracle10g Grid computing, Oracle RAC is an extra-cost option for systems that require continuous availability and super-high scalability.
   
• Oracle OLAP - Oracle is a leader in data warehouse technology and has Online Analytical Processing (OLAP) tools for advanced decision support and multi-terabyte data warehouses.
   
• Oracle Data Mining - Advanced data warehouse systems require tools to locate hidden trends and correlations. Oracle offers a tool with advanced artificial intelligence for finding hidden data correlations in super-large databases.
   
• Oracle Database Diagnostic Pack - This covers access to the Oracle10g AWR and ADDM components:
   
  • AWR - Automatic Workload Repository
    • dbms_workload_repository package
    • awrrpt.sql and awrrpti.sql reports
    • dba_hist_* and v$active_session_history views
       
  • ADDM - Automatic Database Diagnostic Monitor
    • All dbms_advisor package with "ADDM" as the value to the "advisor_name" parameter.
    • The addmrpt.sql, addmrpti.sql reports
    • Views starting with dba_advisor_* of all tasks generated by ADDM (i.e. tasks with "ADDM" as the "advisor_name" column in dba_advisor_tasks view)
       
• Oracle advanced Security - Oracle advanced Security provides sophisticated security control for confidential and secure data systems.
   
• Oracle Tuning Pack - Oracle provides a full-suite of online tools for tuning the Oracle database. Integrated with the Oracle Enterprise Manager, Oracle tuning pack competes with many 3rd-party vendor tuning products.
   
• Oracle Change Management Pack - The change management pack provides complete version control and database change auditing.

Auditing use of Oracle features

Oracle10g has auditing to tell if you have been querying the Oracle10g Automated Session History views, an extra-cost option according to sources at Oracle. The DBA_FEATURE_USAGE_STATISTICS  and DBA_HIGH_WATER_MARK_STATISTICS views tell if you have been using specific Oracle features.

Conclusion

It is imperative that the Oracle professional understand the features and options within the bewildering array of Oracle software offerings. By choosing the correct options, Oracle pros can save their company thousands of dollars and ensure that they have the right software for their application.

BEWARE:  Just because a features works, that does not necessarily mean that you are licensed to use the feature!   

Some Oracle features are allowed without explicitly purchasing any extra-cost options.  

For example, querying any AWR tables is allowed in EE but it triggers an internal Oracle system audit that may require you to purchase the extra-cost performance and diagnostic packs.  

For more details on the features of Oracle standard edition vs. enterprise Edition (EE) see these MOSC notes:

•  Oracle 10g Standard vs. Enterprise Features:  MOSC Note 271886.1:   Differences Between Different Editions of Oracle Database 10G

• Oracle 10g r2 SE vs. EE Features:  MOSC Note 465465.1: Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2  

• Oracle 11g release 1 .1 SE vs. EE Features:  MOSC Note 465460.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1