Optimizing performance with Oracle parallel query Feb 15, 2001 Donald Burleson

In "Using parallel Oracle for high-speed e-commerce systems," I explained how Oracle's parallel query and Parallel Server features can help you build a better e-commerce system. Now it's time to turn our attention to some additional approaches for achieving that speedy e-commerce system.

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:

Invoking Parallelism

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) */ . . . .

Parallel DBA operations

With 24/7 e-commerce databases on the Web, the Oracle DBA is challenged to perform maintenance duties as fast as possible. 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 (through Oracle's Enterprise Backup Utility [EBU] or Recovery Manager [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 OPS environment without excessive pinging, so long as the Web servers are disconnected from the database.
   
• Parallelize the table reorganizations
  Oracle tables will fragment and become less efficient over time, and the DBA sometimes needs to reorganize the tables to improve performance. Before Oracle9i, if you are reorganizing the Oracle tables to re-sequence the rows, you can't use parallelism with the CREATE TABLE AS SELECT (CTAS) statement because you need to use an index hint to force the sequence of the rows. You can't use an index hint and a parallel hint in the same statement. However, you can submit concurrent CTAS tasks, one for each table, such that the elapsed time for the reorganization becomes the maximum time required for the largest table.

• Parallelization promises to become even more popular with the increased use of Oracle parallel server (OPS). Oracle8i's new parallel IDML features are also expected to improve the parallel capabilities of database administration. These features are the PARALLEL CREATE TABLE AS SELECT (PCTAS) statement and parallel index rebuilding, which I'll describe briefly in the following sections.
  
  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.
     
  • Oracle Parallel Server—Useful when running SMP of massively parallel processors.
     
  • 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