Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Performance Features

Oracle includes several features specifically designed to boost performance in certain situations. We’ve divided the discussion in this section 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.

Parallel query features became a standard part of Enterprise Edition beginning with Oracle 7.3. 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)

When you’re using Oracle, by default the degree of parallelism for any operation is set to twice the number of CPUs. You can adjust this degree automatically for each subsequent query based on the system load. You can also generate statistics for the cost-based optimizer in parallel.

Availability

You can perform maintenance functions such as loading (via SQL*Loader), backups, and index builds in parallel in Oracle Enterprise Edition. Oracle Partitioning for the Enterprise Edition enables additional parallel Data Manipulation Language ...