Oracle parallel tips Feb 15, 2001, updated 2/11/2016 Donald Burleson

Also see these notes on Oracle parallel features.

Oracle parallel query is very important as Oracle database move onto SMP servers with 8, 16, 32 and 64 CPU processors.  On these servers, the speed of full-table scans and index fast-full scans can be greatly improved:

See here for a list of Oracle parallel query parameters.

Invoking Oracle Parallel query

There are several ways to invoke Oracle parallel query, and some of them are very dangerous because they influence the costs assigned to full-table scans.

• System-level parallelism - Up to 11g release 1, setting the parallel automatic tuning parameter (In 11g release 2 and beyond, the parallel_degree_policy parameter)  may cause the cost-based optimizer to perceive full-table scans as cheaper.  System-level parallelism is best for data warehouse and DSS systems and should be avoided for OLTP systems.
   
• Session-level parallelism - Using the alter session force parallel query syntax.
  
• Object level parallelism - You can say "alter table customer parallel degree 15", but beware, this influences the SQL optimizer into thinking that full-scans are "cheaper" then index access. I recommend invoking OPQ with hints, on a statement-by-statement basis.
   
• SQL-level parallel query - This parallel hint is the preferred method for invoking parallel query, selectively, based on the operation:

     select /*+ parallel (c, 31) (e, 31) */ . . . .

Monitoring Oracle parallel query

You can use the following query against gv$systat to see your parallel query operations:

select
   name,
   value
from
   gv$sysstat
where
   upper(NAME) like '%PARALLEL OPERATIONS%'
OR
   upper(NAME) like '%PARALLELIZED%'
OR
   upper(NAME) like '%PX%';

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
queries parallelized                                                                 731688
DML statements parallelized                                                               2
DDL statements parallelized                                                               5
DFO trees parallelized                                                               737418
Parallel operations not downgraded                                                   737535
Parallel operations downgraded to serial                                           53149762
Parallel operations downgraded 75 to 99 pct                                               1
Parallel operations downgraded 50 to 75 pct                                               1
Parallel operations downgraded 25 to 50 pct                                               0
Parallel operations downgraded 1 to 25 pct                                                0
PX local messages sent                                                           2353220306
PX local messages recv'd                                                         2353217461
PX remote messages sent                                                           625655784
PX remote messages recv'd                                                         653926701
queries parallelized                                                                1076558
DML statements parallelized                                                               0
DDL statements parallelized                                                              24
DFO trees parallelized                                                              1090977
Parallel operations not downgraded                                                  1100690
Parallel operations downgraded to serial                                               2110
Parallel operations downgraded 75 to 99 pct                                               1
Parallel operations downgraded 50 to 75 pct                                               0
Parallel operations downgraded 25 to 50 pct                                               0
Parallel operations downgraded 1 to 25 pct                                                0
PX local messages sent                                                           2363728660
PX local messages recv'd                                                         2363725960
PX remote messages sent                                                           653929832
PX remote messages recv'd                                                         625652879

Also see Monitoring Oracle parallel query tips

Oracle Parallel query DBA operations

Oracle has several tools that can implement the parallelization approach to improve manageability and speed up the processing performance of Oracle database administration.

There are several areas where the DBA can use parallel operations to speed Oracle maintenance.

• Parallelize the Oracle backup
  The time required for backups can be both multiplexed using RMAN and parallelized (with such third-party tools as Legato, ADSM, or Veritas) to speed the elapsed time for the backup. Creating a separate backup thread for each available tape drive will accomplish this. Hence, a backup that takes eight hours with one tape drive will only take 30 minutes with 16 tape drives.
   
• Parallelize the index rebuilds
  Oracle indexes become out-of-balance and inefficient in highly active e-commerce databases. It is possible to submit many index rebuild tasks concurrently by performing parallel ALTER INDEX REBUILD commands. This can also be done within an RAC environment without excessive pinging, so long as the Web servers are disconnected from the database.
   
• Parallelize table reorganizations
  Oracle tables will fragment and become less efficient over time, and the DBA sometimes needs to reorganize the tables to improve performance. You can parallelize your dbms_redefinition scripts

Parallelization promises to become even more popular with the increased use of RAC. Oracle  parallel DML features are also expected to improve the parallel capabilities of database administration.

PARALLEL CREATE TABLE AS SELECT (PCTAS)

The PARALLEL CREATE TABLE AS SELECT (PCTAS) statement can be useful in an Oracle environment where tables are reorganized by copying them to another tablespace. For example, you could specify the number of parallel processes to compute the monthly summary values from your fact table. In the following example, we assign five processes to simultaneously read the blocks from a fact table in a warehouse that was populated from operational data:

CREATE TABLE
   SALESPERSON_SUMMARY_03_00
PARALLEL (degree 5)
AS
SELECT
   region,
   salesperson,
   sum(sale_amount)
FROM
   FACT
WHERE
   month = 3
AND
   year = 2000
GROUP BY
   region,
   salesperson;

Here, we dedicate five query servers to extract the data from the fact table and five query servers to populate the new summary table.

• Parallel index builds
  
  Parallel index builds are often useful to the Oracle administrator who needs to rebuild indexes that have either spawned too many levels or that contain too many deleted leaf rows. Parallel index creation is also useful when importing large tables. Because so many tables are large, Oracle exports never capture the indexes and only export the row values. If a recovery of a table becomes necessary, the Oracle administrator can use a parallel CREATE to speed up the index re-creation. Parallel index creation takes place by allowing the degree of parallelism to be specified in the CREATE INDEX statement. For example:

  ALTER INDEX customer_name_idx REBUILD PARALLEL 10;

  Because this type of index creation always involves reading the old index structure and performing a large sort operation, Oracle is able to dedicate numerous independent processes to simultaneously read the base index and collect the keys for the new index structure.
  
  Now, we have saved the best for last. The final approach—parallelized Oracle queries—can be used by all e-commerce databases to improve performance.
  

  Parallelized Oracle queries
  
  The parallelized query technique allows a single program to make multiple requests to the Oracle database in parallel. It is not a feature of the Oracle database, but a method that is very popular with programmers who need to perform many simultaneous queries against a database.
  
  To illustrate how parallelized queries work, consider the following example. Assume that we have an Oracle database that contains electronic books. In addition to the book text, we also have another table that contains notes about the text.
  
  At page retrieval time, we need the following information:

  • The page text
  • The illustrations and figures for the page
  • Any notes that were placed into the text

  As we see below, instead of waiting for each query to complete independently for a total elapsed time of six seconds, each query is submitted to Oracle at the same instant for a total elapsed time of three seconds.

  Parallelized Oracle queries

  While the C or Java program is waiting for the Oracle queries to complete, it is free to do other work. For example, since the page text is returned in only one second, the program can map out the text to the end user while waiting for the illustration and notes queries to complete.
  
  Basic Web server multithreading of parallelized Oracle queries
  
  Within the Web server code, multithreading can be implemented by using POSIX extensions that allow for multithreading within the same program address space. Internally to the C++ program, the multithreading is done with a function call, where multiple copies of the function may simultaneously exist within the address space of the C++ program.
  
  To make concurrent database requests from C++ programs, some programmers choose to set a semaphore or use an interrupt to manage the simultaneous external requests. However, this approach requires that the programmer create a listener thread to determine when the simultaneous requests have completed.
  
  A more popular solution to multiple simultaneous database requests is to use the POSIX library. POSIX offers the pthread_create function, which can be used to create a new thread within the program. POSIX also offers a pthread_join function that will signal the program when the external function has completed. Using these methods, the programmer can write Web server code that will submit and manage multiple simultaneous requests to Oracle.
  
  Summary
  
  The four components of Oracle parallelism can be used together to create an e-commerce database that runs very fast and can be quickly maintained. To recap, here are the basic features:

  • Oracle parallel query—Useful for Web data warehouses where full-table scans exist.
     
  • Parallel DBA tools—Help minimize downtime for database maintenance.
     
  • Manual parallelization—The most common way to speed Web-based queries, by breaking a single large query into many small queries and executing them in parallel