Home arrow Oracle arrow Page 7 - Large Database Features In Oracle

Parallel Processing Configuration - Oracle

When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, the first of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).

  1. Large Database Features In Oracle
  2. Implement Data Partitioning
  3. Select the Type of Partitioning
  4. Define the Indexing Strategy
  5. Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
  6. Data Compression
  7. Parallel Processing Configuration
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 26
December 08, 2005

print this article



Generally, not much configuration is required for Oracle Database 10g to perform parallel processing. There are, however, a number of configuration options that are required and will affect the effectiveness of parallelism.

To begin with, parallel processing is enabled by default for DDL (for example, create and alter) and query (for example, select) commands, but disabled for DML (say, insert, update, delete, merge) commands. If you wish to execute a DML command in parallel mode, you must first issue the following command for the session in which the command is to be executed, as in the following.

alter session enable parallel dml;

Several database initialization parameters affect parallel processing. These are shown next.

Initialization Parameters

When an Oracle instance starts, the parameters in the initialization file are used to define or specify the settings for the instance. Table 9-15 identifies the initialization parameters that affect parallel processing. In many cases, the default values will provide adequate results for your large database. Specifics of your own environment will influence your decisions on the best values to use.

As you can see from Table 9-15, there are dependencies  between parameters. Modifying one may necessitate modifying others. If you modify any of the parallel processing parameters, you may also have to modify the following parameters:




Default Setting




When set to True, enables an



adaptive algorithm designed to



improve performance in multiuser



environments that use parallel






No longer used. Exists for backward



compatibility only.


Installation Dependent

Specifies the byte size of messages for



parallel processing.


Installation Dependent

Used in Real Application Cluster



environments to restrict parallel



query operations to a limited



number of database instances.


# of CPUs available to the

Specifies maximum number of parallel


database instance

processes for the database instance.



Specifies minimum percentage of



parallel processes required for parallel



processing. Value is a percentage of






Specifies minimum number of parallel



processes for the database instance.



Cannot be greater than value of





Usually set to 2, depending

Specifies the number of parallel


on operation system

processes per CPU.

TABLE 9-15.  Initialization Parameters Affecting Parallel


Invoke Parallel Execution

Parallel execution can be applied to tables, views, and materialized views. Assuming all necessary configurations have been made, there are several ways to invoke parallel execution. The first way is during table creation (including materialized views), using the parallel clause. If the table is being created using the results of a subquery, the loading of the table will be parallelized. In addition, by default, all queries that are executed against the table will be parallelized to the same extent. The next listing shows an example of specifying the parallel option for a table creation.

1 create table commission (
2  sales_rep_id     number,
3  prod_id          number,
4  comm_date        date,
5  comm_amt         number(10,2))
6 tablespace comm_ts pctfree 5 initrans 1 
  maxtrans 255
7 parallel;

The import line here is Line 7, specifying the parallel clause. This line could also have included an integer to specify the degree of parallelism—that is, the number of processes that are to be used to execute the parallel process. As the degree of parallelism is omitted in this example, the number of processes used will be calculated as number of CPUs × the value of the PARALLEL_THREADS_PER_CPU initialization parameter. The degree of parallelism for a table or materialized view can be changed using an alter statement.

Parallel processing can also be invoked when the parallel hint is used in a select statement. This hint will override any default parallel processing options specified during table creation. The following listing illustrates the use of the parallel hint. Line 1 contains the parallel hint, specifying the table to be parallelized (commission) and the degree of parallelism (4).

1 select /*+ parallel (commission, 4) */
2  prod_id, sum(comm_amt), count(*)
3 from commission
4 group by prod_id;

In some cases, Oracle Database 10g will alter how, or if, parallel processing is executed. Examples of these include the following:

  • Parallel processing will be disabled for DML commands (for example, insert, update, delete, and merge) on tables with triggers or referential integrity constraints.
  • If a table has a bitmap index, DML commands are always executed using serial processing if the table is nonpartitioned. If the table is partitioned, parallel processing will occur, but Oracle will limit the degree of parallelism to the number of partitions affected by the command.

Parallel processing can have a significant positive impact on performance. Impacts on performance are even greater when you combine range or hash-based partitioning with parallel processing. With this configuration, each parallel process can act on a particular partition. For example, if you had a table partitioned by month, the parallel execution coordinator could divide the work up according to those partitions. This way, partitioning and parallelism work together to provide results even faster.


Use Materialized Views

So far, we have discussed several features and techniques at our disposal to improve performance in large databases. In this section, we will discuss another feature of Oracle Database 10g that we can include in our arsenal: materialized views.

Originally called snapshots, materialized views were introduced in Oracle8 and are only available in the Enterprise Edition. Like a regular view, the data in a materialized view are the results of a query. However, the results of a regular view are transitory—they are lost once the query is complete and if needed again, the query must be reexecuted. In contrast, the results from a materialized view are kept and physically stored in a database object that resembles a table. This feature means that the underlying query only needs to be executed once and then the results are available to all who need them.

From a database perspective, materialized views are treated like tables:

  1. You can perform most DML and query commands such as insert, delete, update and select.
  2. They can be partitioned.
  3. They can be compressed.
  4. They can be parallelized.
  5. You can create indexes on them.

Materialized views are different in other ways and have some interesting features associated with them. Before we talk about those, let’s look at some ways to use materialized views.

Uses for Materialized Views

Materialized views are used as a performance enhancing technique. Following are some usage examples. In this section, we will be discussing the first three uses, as they are applicable to our topic of large databases.

  1. Performing data summarization (for example, sums, averages)
  2. Prejoining tables
  3. Performing CPU-intensive calculations
  4. Replicating and distributing data

In large databases, particularly data warehousing environments, there is always a need to summarize, join, perform calculations, or do all three at once, on large numbers of records for reporting and analysis purposes. To improve performance in the past, a combination of views and physical tables were usually implemented that contained the results of these operations. The summary tables would require some type of extraction, transformation, and load (ETL) process to populate and refresh them. In addition to the base tables containing the detailed data, the users would need to know which combinations of the views and/or summary tables to use. These structures are illustrated in Figure 9-7.

Using materialized views has several advantages over more traditional methods. These include the following:

  1. Materialized views have a built-in data refresh process, which eliminates the need for custom ETL.
  2. As we said earlier, the data in materialized views can be partitioned, using the same techniques that apply to tables.
  3. Materialized views are transparent to the users. This is probably the most attractive feature of using materialized views, and we will expand more on this in the next section when we discuss automatic query rewriting.

Figure 9-8 illustrates summarization using materialized views.

FIGURE 9-7.  Summarization using views and summary tables

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: