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.
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:
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 (
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) */
In some cases, Oracle Database 10g will alter how, or if, parallel processing is executed. Examples of these include the following:
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.
CRITICAL SKILL 9.5
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:
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.
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:
Figure 9-8 illustrates summarization using materialized views.
FIGURE 9-7. Summarization using views and summary tables
blog comments powered by Disqus