Oracle Essentials Oracle Database 11g (7 page)

OWB is included with the Oracle database as of Oracle Database 10
g
Release 2. We

describe it further in Chapter 10.

Optionally, Oracle also offers a data integration tool, Oracle Data Integrator (ODI), that is not as Oracle database-centric as OWB (although the Oracle database can be a source and/or target). Oracle Data Integrator is based on a product and company that Oracle acquired named Sunopsis. In addition to providing ETL capabilities, ODI can generate code as web services for SOA deployment and is a key part of Oracle’s SOA integration strategy.

Data Movement Features

|

19

Database Performance Features

Oracle includes several features specifically designed to boost performance in certain situations. We’ve divided the discussion in the following subsections into two categories: database parallelization and data warehousing.

Database Parallelization

Database tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operations. Examples of query features implemented in parallel include:

• Table scans

• Nested loops

• Sort merge joins

• GROUP BYs

• NOT IN subqueries (anti-joins)

• User-defined functions

• Index scans

• Select distinct UNION and UNION ALL

• Hash joins

• ORDER BY and aggregation

• Bitmap star joins

• Partition-wise joins

• Stored procedures (PL/SQL, Java, external routines)

In addition to parallel query, many other Oracle features and capabilities are parallel-

ized. Parallel operations are further identified and described in Chapter 7.

Data Warehousing and Business Intelligence

While parallel features improve the overall performance of the Oracle database, Oracle also has particular performance enhancements for business intelligence and data warehousing applications. We introduce many of them here, but see
Chapter 10
for more detailed explanations of products and features specific to data warehousing and business intelligence.

20

|

Chapter 1: Introducing Oracle

Bitmap indexes

Oracle added support for stored bitmap indexes to Oracle 7.3 to provide a fast way of selecting and retrieving certain types of data. Bitmap indexes typically work best for columns that have few different values relative to the overall number of rows in a table.

Rather than storing the actual value, a bitmap index uses an individual bit for each potential value with the bit either “on” (set to 1) to indicate that the row contains the value or “off” (set to 0) to indicate that the row does not contain the value. Bitmap

indexes are described in more detail in Chapter 4.

Star query optimization

Typical data warehousing queries occur against a large
fact table
with foreign keys to much smaller
dimension tables
. Oracle added an optimization for this type of
star
query
in Oracle 7.3. Performance gains are realized through the use of Cartesian product joins of dimension tables with a single join back to the large fact table.

Oracle8 introduced a further mechanism called a
parallel bitmap star join
, which uses bitmap indexes on the foreign keys to the dimension tables to speed star joins involving a large number of dimension tables.

Materialized views

Since Oracle8
i
, materialized views have provided another means of achieving a significant speedup of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit. Oracle has continued to improve optimizer usage of materialized views with each new release of the database.

Analytic functions

A growing trend in Oracle and other databases is inclusion of SQL-accessible analytic and statistical functions in the database. Oracle first introduced such capabilities in Oracle8
i
with the CUBE and ROLLUP functions. Today, the functionality provided also includes ranking functions, windowing aggregate functions, lag and lead functions, reporting aggregate functions, statistical aggregates, linear regression, descriptive statistics, correlations, crosstabs, hypothesis testing, distribution fitting, and Pareto analysis.

OLAP Option

The OLAP Option physically stores dimensionally aware cubes in the Oracle relational database. These cubes are most frequently accessed using SQL, although a
Database Performance Features

|

21

Java API is also supported. As of Oracle Database 11
g
, Oracle’s optimizer recognizes the levels within these cubes. As a result, any business intelligence tool that submits SQL to an Oracle database can transparently take advantage of the improved performance offered by deployment of this option. Refreshes of the values in these cubes are now maintained similar to refreshing materialized views.

Data Mining Option

Since Oracle9
i
, popular data-mining algorithms have been embedded in the database through the Data Mining Option and are exposed through a PL/SQL or Java data-mining API. Data-mining applications that use these algorithms are typically built using Oracle’s DataMiner or using data-mining tools from Oracle partners such as InforSense and SPSS. Algorithms available in the Data Mining Option for Oracle Database 11
g
include Naïve Bayes, Associations, Adaptive Bayes Networks, Clustering, Support Vector Machines (SVM), Nonnegative Matrix Factorization (NMF), Decision Trees, and Generalized Linear Models.

Business intelligence tools

Oracle data warehouses are often accessed using business intelligence tools from other popular vendors. However, Oracle’s own tools became more common for such deployment as Oracle grew its offerings through acquisitions. Oracle’s initial offering included Oracle Discoverer and Reports, and these tools remain available in the Application Server or as a standalone Oracle Business Intelligence Standard Edition Suite.

Oracle’s flagship product in this area is Oracle Business Intelligence Enterprise Edition Suite (OBI EE) originally consisting of former Siebel Analytics, including Oracle Answers, Dashboards, Delivers, BI Publisher, and Office Plug-ins. Oracle expanded this offering in OBI EE Plus adding Hyperion components that include Foundation Services, Interactive Reporting, SQR production reporting, Financial Reporting, SmartView for Office, and Web Analysis.

Essbase is available as an option for providing an OLAP cube and functionality independently of the data warehouse database. A subset OBI EE is included in Business Intelligence Standard Edition One, along with the Oracle Standard Edition One database and Oracle Warehouse Builder.

Oracle also offers business intelligence applications that include data models and reporting and analysis with prepopulated business metadata. Flagship applications include Oracle’s Business Intelligence Applications (the former Siebel Business Analytics applications) and Hyperion Financial Performance Management applications.

22

|

Chapter 1: Introducing Oracle

Database Management Features

Oracle includes many features that make the database easier to manage. Ease in Oracle management fundamentally improved with the introduction of Oracle Database 10
g
, and has continued to evolve toward being more self-tuning and self-managing with the release of Oracle Database 11
g
. If youare still managing Oracle databases using techniques (such as scripts) from previous releases and are moving to one of the newer releases, now is the time to reevaluate your thinking on management.

Starting with Oracle Database 10
g
, statistics are automatically gathered to an Automatic Workload Repository (AWR) within the database. Oracle’s Automatic Database Diagnostic Monitor (ADDM) evaluates the statistics on a regular basis and sends alerts of potential problem conditions to Oracle Enterprise Manager, where youcan evaluate the condition in more detail and potentially take corrective actions. Some of the newer fully automated features, such as Automatic Memory Management, also leverage data gathered in the AWR.

Oracle has a near real-time view of current database conditions as it makes automated recommendations. Such recommendations will often be more accurate than would be possible with the manual processes you might have used in the past. In the following subsections we’ll introduce the impact this has on Oracle Enterprise Manager and add-on packs, Information Lifecycle Management, backup and recovery, and database availability.

Oracle Enterprise Manager

Oracle includes Oracle Enterprise Manager (EM) with its most widely deployed database products. EM provides a database management tool framework and an HTML-based interface used to manage database users, instances, and features. EM

can also manage Oracle Application Server, Oracle Applications, Oracle’s Linux release, and software products from other vendors.

The database console in Oracle’s current version provides information on database status, availability, schema, data movement configuration, and software maintenance. New with Oracle Database 11
g
is the Support Workbench and diagnosability infrastructure leveraged in reporting problems to Oracle Support. Multiple database administrators can access the EM repository at the same time.

EM can be deployed in several ways: as a central console for monitoring multiple databases leveraging agents, as a “product console” (installed by default with each individual database), or through remote access, also known as “studio mode.” When deployed as a central console, Enterprise Manager is referred to as “Grid Control” and can be used for rapid installation of Oracle software, provisioning, and automated rolling patch updates.

Database Management Features

|

23

A subset of Enterprise Manager functionality is accessible through Microsoft Pocket PC Internet Explorer on wireless PDAs using EM2Go. EM2Go can monitor the status of the Oracle database and Oracle Application Server.

Information Lifecycle Management and ILM Assistant

Introduced in 2006, Information Lifecycle Management (ILM) provides a means to

define classes of data and storage tiers and move the data to the storage tiers that

provide the right combination of performance and cost. The ILM Assistant interface

for setting up and managing ILM can be downloaded from the Oracle Technology

Network at
http://otn.oracle.com
. For more details, see Chapter 5.

Backup and Recovery

As every database administrator knows, backing up a database is a rather mundane but necessary task. An improper backup makes recovery difficult, if not impossible.

Unfortunately, people often realize the extreme importance of this everyday task only after losing business-critical data resulting from a failure of a related system.

The following sections introduce some features used in performing database backup operations. We discuss backup and recovery strategies and options in much greater

detail in Chapter 11.

Recovery Manager

Typical kinds of backups include complete database backups (the most common type), tablespace backups, datafile backups, control file backups, and archivelog backups. Oracle8 introduced the Recovery Manager (RMAN) for the server-managed backup and recovery of the database, leveraging a Recovery Catalog stored in the database. RMAN can automatically locate, back up, restore, and recover datafiles, control files, and archived redo logs. Since Oracle9
i
, RMAN can restart backups and restore and implement recovery window policies when backups expire. Oracle Enterprise Manager provides a GUI-based interface to RMAN. Oracle Enterprise Manager 10
g
introduced an improved job scheduler that can be used with RMAN for managing automatic backups to disk.

Incremental backup and recovery

RMAN can perform incremental backups of Enterprise Edition databases. Incremental backups will back up only the blocks modified since the last backup of a datafile, tablespace, or database; thus, they’re smaller and faster than complete backups.

RMAN can also perform point-in-time recovery, which allows the recovery of data until just prior to an undesirable event (such as the mistaken dropping of a table).

24

|

Chapter 1: Introducing Oracle

Oracle Secure Backup

Various media-management software vendors leverage Oracle’s RMAN, but starting with Oracle Database 10
g
, the database also includes an entry-level tape storage management solution of its own known as Oracle Secure Backup XE. Optionally, Oracle offers an enterprise-class backup solution simply named Oracle Secure Backup.

Database Availability

Database availability depends upon the reliability and management of the database, the underlying operating system, and the specific hardware components of the system. Oracle has improved availability by reducing backup and recovery times by:

• Providing online and parallel backup and recovery

• Improving the management of online data through range partitioning

• Leveraging hardware capabilities for improved monitoring and failover The relevant features are described in the following subsections.

Partitioning option

Oracle introduced partitioning as an option with Oracle8 to provide a higher degree of manageability and availability. Youcan take individual partitions offline for maintenance while other partitions remain available for user access. In data warehousing implementations, partitioning is sometimes used to implement rolling windows based on date ranges. Other partitioning types include hash partitioning (used to divide data into partitions using a hashing function and providing an even distribution of data) and list partitioning (enabling partitioning of data based on discrete values such as geography). Starting with Oracle Database 11
g
, interval partitioning can also be used to automatically create new fixed ranges as needed during data insertions.