Oracle's Parallel Offerings

Australia
By Mark Gurry

The Oracle Parallel Option has received a lot of publicity, but little is known of how it works in a production environment. It has even been mistaken with Oracle's Parallel Server. This article will discuss the capabilities and proper usage of Oracle Paralell Options, including instructions on tuning it.

Parallel Query Option

This article documents our findings on the Parallel Query Option (PQO) and makes recommendations to how to use it most effectively. The article also makes reference to other features Oracle provides that can be used with PQO to get the best possible performance. Many of the INIT.ora parameters documented in this article will provide pleasing performance improvements even if you are not using PQO.

The Parallel Query Option was introduced with the release of Oracle 7.1. Two things come to mind about PQO. First, I am amazed at the number of people that believe that parallel server is the same as parallel query. Second, I am equally amazed at how few sites use parallel query, let alone use it effectively.

The distinction between parallel server and PQO is that the former uses multiple instances and their associated SGAs against a single database to assist with your site's scalability and recoverability. PQO uses multiple processes against the one database and has the primary purpose of improving your site's performance. To complicate matters, parallel server and parallel query can be used together. Using the two together is an option, not a necessity, and to the best of my knowledge is rarely used. My advice to sites not using PQO is to start using it. Oracle7.1.6 and later has made huge advances in PQOs ability to improve your sites performance.

PQO can provide you with impressive performance improvements if it is used appropriately. Before reading any further, be aware PQO assists database queries that perform at least one Full Table Scans ONLY! PQO can still be used, however, even if only part of the query contains a Full Table Scan. A typical example may be a join with one table being accessed via a Full Table Scan, and the other via an index. Users of tuned OLTP applications with large numbers of concurrent users using PQO rarely experience improved online performance. OLTP applications still have their long running overnight reports, data loads and large tables and indexes to administer, which makes PQO a valuable option. PQO is most useful for long running batch reporting, overnight data loads and many of the long running database administration tasks, such as rebuilding indexes, performing database re-organisations and performing database recovery. It is also often useful for conversions and is a must for Decision Support applications. PQO provides you with the largest benefits when dealing with very large tables, especially sorting or creating indexes on the tables.

At the time of writing this article, Parallel Query option does not come free-of-charge. Oracle bills you for PQO, classifying it as a separate option to the Oracle7 Server. Is it worth purchasing?

Before you make that decision, consider the following:

PQO can usually only optimise your processing if you are using Symmetric Multi Processors (SMP), Massively Parallel Systems or Loosely Coupled Systems (Clusters). PQO will run on other configurations, the only problem being that it will often make your processing run considerably slower. The exception is where a full table access without sorting and joining is I/O bound on a single processor machine. You can often improve your performance in this case by striping your table and using PQO. Most single processor machines that have a well layed out and properly tuned database and application are CPU bound, not I/O bound. If your machine fits the configuration required, and your application is suited to PQO, it may provide excellent performance improvements.

Oracle is very committed to PQO and each new release of Oracle7 provides improvements to an already proven performance enhancing product. We have observed performance improvements by as many as five times using Oracle7.2, compared with Oracle7.1. Oracle7.3 PQO is significantly faster again.

Oracle7.2 provided the CREATE TABLE tname AS SELECT * FROM tname; which has the ability to run in parallel mode for selecting the data as well as for inserting into the new table. The option enforces the NOT NULL and other check constraints as the rows are inserted. Oracle7.3 went one further in significantly improving the parallel query optimization which is now built into the access path selection. The optimizer is aware of the number of CPUs on the machine and the number of disks that store the table. It may choose a parallel table scan, which is cheaper than a sequential index scan. Explain Plan has been expanded to provide additional information to assist the tuning task prior to running your query.

Other new features Oracle has provided with each major release include: parallel index creation in Oracle7.1.3, direct reading (avoiding the buffer cache) in Oracle7.1.5, the ability to create a table as select in parallel, sort direct write (avoiding having to write through the buffer cache), an index direct write (avoiding the buffer cache) and an unrecoverable option (which avoids writing to the redo logs) for index and table creation in Oracle7.2. Oracle7.3 introduced the hash join, histograms, MPP device affinity, parallel anti-join to speed up NOT IN, cartesian product, parallel UNION and UNION ALL and async readahead.

The PQO architecture is as follows:

PICTURE HERE!!!!

The dedicated server process has the ability to partition some types of queries to assist with throughput. If a sort statement is written as follows: 
SELECT /*+ PARALLEL(electoral_roll 2) */ *
   FROM electoral_roll
  ORDER BY surname;

PQO may assign two Query Servers to select the data from the electoral_roll table, one Query Server process to sort A to M and one Query Server to sort N to Z. What this means is that even though the degree of parallelism is two, the actual number of server processes may be five with two being used to retrieve rows from the database and sort the retreived data. There will also be a fifth process, the dedicated server process. The operations that can take advantage of PQO include:

You can use PQO for SELECT statements and subqueries in UPDATE, DELETE, INSERT and CREATE TABLE statements. The PQO does not operate for INSERT, UPDATE and DELETE statements, apart from subqueries. UNION, INTERSECT and MINUS are also not parallelised, although the UNION and UNION ALL are able to be run in parallel using hash joins in Oracle7.3 and later. PQO can also be used for performing parallel loads into a table using SQL*Loader and parallel recovery of a database.

To make PQO operate for your SQL statement, you can either set a hint in your statement or set the degree of parallelism using the CREATE TABLE or ALTER TABLE statements. The hint in the SQL statement will take precedence over the degree of parallelism specified in the CREATE or ALTER table statement.

In special cases, we recommend the use of hints to make your statement self documenting and to ensure that your statement uses PQO consistently even where the parallel clause against the table is mistakenly set incorrectly or forgotten. The downside of using hints is that you cannot always guarantee that the hint will work. For example, a hint may refer to an index that does not exist. Another reason why you may re-consider using hints is if you perform a hardware upgrade. 

SELECT /*+ PARALLEL(electoral_roll, 2) */ *
   FROM electoral_roll
  ORDER BY surname;

If you use a table alias, you must specify the alias in the hint as shown in the following example:

SELECT /*+ PARALLEL(er, 2) */ *
   FROM electoral_roll er
  ORDER BY surname;

If you do not wish to use PQO for the query, enter the following statement:

SELECT /*+ NOPARALLEL */ *
   FROM electoral_roll
  ORDER BY surname;

To specify the number of servers in the CREATE TABLE statement, structure the statement as follows:

CREATE TABLE electoral_roll
     (Electoral_No number(10) not null,
       Surname   varchar2(40) not null)
  STORAGE (INITIAL 200M NEXT 200M)
  PARALLEL (DEGREE 2);

In the example shown, the degree of parallelism equals two. If PARALLEL was specified without a degree of parallelism (two in the example), Oracle obtains the degree of parallelism based on an estimate of the size of the table and the INIT.ora parameter PARALLEL_DEFAULT_SCANSIZE. Oracle7.3 makes the parameter PARALLEL_DEFAULT_SCANSIZE obsolete and replaces it with a much more sensible default of the minimum of the number of CPUs on your machine or the number of disk devices that the table is spread across.

Please note that you have the option of specifying the PARALLEL 5 syntax or PARALLEL (DEGREE 5) syntax in your DDL statements. Both have the same meaning. 

ALTER TABLE electoral_roll
PARALLEL (DEGREE 2);

This will alter the table's degree of parallelism and the following statement will turn off PQO for Full Table Scans against the table:

ALTER TABLE electoral_roll
NOPARALLEL;

We strongly recommend that you do not rely on Oracle's estimate of the degree of parallelism for a table or index. You should always specify the degree of parallelism if you use the PARALLEL in the CREATE or ALTER TABLE command e.g. PARALLEL (DEGREE 4); not PARALLEL. You also have the option to specify the degree of parallelism using a hint in the SQL statement, which gives you total control over the degree of parallelism for each statement.

Some statements have a "where clause" that will use an existing index, such as the example shown below. In this case the index will be used in preference to the parallel full table scan, despite the fact that the table has been created with the parallel option.

SELECT *
   FROM electoral_roll er
  WHERE state_code in ('VIC', 'NSW')
   ORDER BY surname;

If you wish to use the parallel query processing and force a Full Table Scan, you can use the following query:

SELECT /*+ PARALLEL(er, 2) FULL(er) */ *
   FROM electoral_roll er
 WHERE state_code in ('VIC', 'NSW')
  ORDER BY surname;

The PARALLEL hint alone does not force a full table scan. It simply provides Oracle with the degree of parallelism to use if a full table scan is required. To force a full table scan, you must use the PARALLEL anf FULL hints together.

Deciding on Whether to Use Denormalised Summary Tables or PQO

PQO provides the largest benefit to applications where large tables need to be scanned. In the case of a financial transaction table, each account may have many thousands of transactions. The table may contain millions of transactions spread over five financial years of data. The design decision would usually be made to create a denormalized summary table by financial year with running financial totals being maintained. You would also consider having a summary table on a per account per financial year basis, because financial year totals are commonly queried online and by batch reports. The denormalized tables would usually be maintained by application source code (or trigger) at the time the transaction is applied to the database.

PQO purists may argue that the denormalized summary tables are unnecessary. You can scan a huge table using PQO considerably faster than a serial table scan (not using PQO). From our experience, the summary tables will usually run considerably faster than PQO, which is of most benefit where the access rules to the table are not so obvious. Your users may require all sorts of summary information and have a need to access the data in a multitude of groupings. You would be required to maintain many summary tables and PQO is ideally suited to this scenario.

Our recommendation is to treat each case on its merits. No hard and fast rules exist as to whether your application should use PQO. It is usually best for your application to maintain summary tables as the transactions are entered if the summary access path requirements are well-defined. If the access rules are not well defined or too many summary tables need to be maintained, use PQO. Having too many summary tables will have an adverse effect on OLTP user response times if the application has to continually maintain them. If you do decide that it is more efficient to create summary tables using PQO, make use of the UNRECOVERABLE clause.

Deciding on Whether to Use an Index or PQO

Parallel query processing has introduced a new dimension in determining whether it is best to use an index or a full table scan to retrieve data. The parallel query option will scan the table significantly faster than a full table scan running serially if the table is striped across disks and the query is running on a multi CPU computer.

Gone is the old rule of thumb that said if more than 15 percent of the rows in a table are to be retrieved, perform a full table scan rather than use an index. The exact percentage depends on the length of the row and the total length of the columns in the index. With PQO, the correct figure now may be nearer to 5 percent because full table scans can be performed significantly faster using PQO, while index search response times remain the same.

If the entire set of columns being retrieved in the SELECT statement is being stored in the index, however, the figure may be closer to 30 percent or 40 percent. This is because all of the required data can be retrieved from the index without having to access the table. Obtaining data from the index will often require significantly less physical reads, depending on the length of the index columns compared to the length of the rows in the table. The index will be much more efficient if the statement includes an ORDER BY and the index columns in the index are in the same sequence as those in the ORDER BY. The index can be used to retrieve the rows in the correct order without having to sort the rows in temporary segments. You should use the index in preference to using PQO under these circumstances.

Assume that a table named ELECTORAL_ROLL has an index ER_INDX1 on the columns (STATE_CODE, SURNAME).

SELECT state_code, surname
   FROM electoral_roll
  WHERE state_code in ('VIC', 'NSW')
  ORDER BY state_code, surname;

This can be replaced by: 
SELECT /*+ INDEX_ASC (electoral_roll er_indx1) */
           state_code, surname
   FROM electoral_roll
  WHERE state_code in ('VIC', 'NSW');

Or you could use the following: 
SELECT /*+ INDEX_ONLY (electoral_roll er_indx1) */
           state_code, surname
   FROM electoral_roll
  WHERE state_code in ('VIC', 'NSW');

Notice that the ORDER BY command has been removed from the statement and we are relying on the index to provide the rows in the desired sequence. No sorting is required, nor is any access to the table.

If you run the EXPLAIN PLAN and confirm that an index only search is being performed, we recommend leaving the ORDER BY in the statement. This will make your statement understandable to people who have not read this article. It also returns the rows in the correct order, even if a new index was created. You may have a new index on STATE_CODE, SURNAME and a new column FIRST_INITIAL, and decide to remove the original index. The latest Oracle (7.1.6 and later) optimizers are smart enough to realize that the index satisfies your sorting requirements without having to go to the table. If the rows had to be retrieved in descending order, the following hint can be used:

SELECT /*+ INDEX_DESC (electoral_roll er_indx1) */
           state_code, surname
    FROM electoral_roll
  WHERE state_code in ('VIC', 'NSW')
   ORDER BY state_code desc surname desc;

If you seek all the rows, not just those where the state_code in ('VIC', 'NSW'), simply remove the WHERE clause and the rows will still be retrieved in sequence. The following statement will retrieve the indexed rows in ascending sequence without having to access the table. It is probably best to use hints, however, unless you are still using Oracle Version 6. 
SELECT state_code, surname
    FROM electoral_roll
  WHERE state_code > chr(1)
        AND surname > chr(1);

We recommend using the INDEX_ASC hint and the ORDER BY clause because Oracle continually warns us that the way that indexes present the data in ascending order may not be the default behaviour in the future. As a word of warning, be careful not to rely too much in hints. Oracle will ignore the hint if it is faulty in any way. It is important that you run your statements through Explain Plan to ensure that the statement will be processed as you would expect.

There is some concern amongst DBAs that creating indexes using PQO could potentially store the entries in a fashion that does assist selection of data in ascending order. Keep your minds at rest. The resulting index has all of the properties of a serially created one.

Other Design Considerations

Purging strategies are often overlooked until an application enters production. Sites soon realize their larger tables are becoming difficult to manage due to the size and its associate indexes. A large percentage of the data in the tables is often not required and can be purged. Deleting rows from large tables is very inefficient will take a huge amount of time and the tables overall size will not be reduced unless you rebuild the table and its indexes. PQO provides huge runtime reductions when combined with the UNRECOVERABLE option of the CREATE TABLE and CREATE INDEX and the COMPATIBLE and SORT_DIRECT_WRITES INIT.ora parameters.

Another excellent feature that will assist greatly with the purging task is partition views, which were introduced with Oracle7.3. You simply re-create the view to remove the oldest partition oldest table and to include a new table. You can then backup and drop the oldest table. And all this can be done with little interruption to your users. Partition views are created by putting check constraints on a table:

ALTER TABLE fin_transactions_94 ADD CONSTRINT C_FIN_YR_94
       CHECK (transaction_date between '01-jul-1994' and '30-jun-1995');

ALTER TABLE fin_transactions_95 ADD CONSTRINT C_FIN_YR_95
       CHECK (transaction_date between '01-jul-1995' and
 '30-jun-1996');

ALTER TABLE fin_transactions_96 ADD CONSTRINT C_FIN_YR_96
       CHECK (transaction_date between '01-jul-1996' and '30-jun-1997');

You can now create a view:

CREATE_VIEW all_fin_transactions AS
      SELECT * 
        FROM fin_transactions_94 
      UNION ALL
      SELECT * 
        FROM fin_transactions_95 
      UNION ALL
      SELECT * 
        FROM fin_transactions_96
/

If you enter the query:

SELECT SUM(transaction_amount)
  FROM all_fin_transactions
WHERE transaction_date between '01-aug-1995' and '02-aug-1996'
/

The query will scan the tables fin_transactions_95 and fin_transactions_96, thus avoiding a much larger table scan if the fin_transactions_94 had been included in the same table. Another design consideration relevant to PQO is when you have a NESTED LOOP or MERGE JOIN occurring with one of your tables being accessed using a FULL TABLE SCAN and PQO and the other table(s) being accessed using an index. The indexed table will potentially cause a severe bottleneck if each row returned from the driving table using the PQO servers has to then have an index scan followed by a disk I/O to retrieve that data from the table. Attempt to store all of the columns required by the statement from the indexed table into the index to avoid the disk I/O to the table.

One other issue to consider is the skew of your data. If you are using Oracle's striping facility, and having your data entered via OLTP transactions, it is possible the OLTP query servers may have an unbalanced workload, particularly if a sort or table join is involved. If your table stores financial transactions and you are using PQO to retrieve all of the rows for the current financial year, one datafile is likely to return all of the required dates that satisfies your WHERE clause. You should ideally use operating system striping under these circumstances.

When the tables are huge, some sites split them by financial year to improve manageability. It also provides the opportunity for you to use Oracle striping and achieve load sharing when using PQO.

Constructing Your Database to use PQO Effectively

The major aim when constructing your database is to share disk I/O load across disks to avoid I/O bottlenecks. Before deciding how to configure your database to use PQO effectively, keep in mind that accessing data in memory is considerably faster than accessing data from disk. A single processor demanding data from a disk will invariably have to wait because the disk will not be able to provide data quickly enough to keep the processor occupied. That is, unless the processor is required to perform resource consuming tasks with the data such as sorting or joining to other tables. Having multiple PQO query processes accessing the disk will worsen the disk bottleneck. You MUST stripe your tables to use PQO effectively.

This section lists a set of rules to consider when creating your database for PQO. Some of the rules are obvious, such as the need to stripe Others are rules that we use as a result of our PQO benchmark findings.

From our observations, we should be able to form some common sense rules that will assist you with your database layout.

Tip 1. Stripe your table or index being accessed using PQO across multiple disks using either Oracle's striping facility or operating system striping. Use operating system striping if your machine provides it and your tables or indexes are used by OLTP users as well as for PQO processing. Use Oracle striping if your machine does not offer operating system striping or your table is used exclusively for PQO operations, Oracle striping usually provides better performance than operating system striping for PQO operations.

If your machine does not provide operating system striping, use Oracle striping. Of course Oracle and operating system striping are not mutually exclusive. Oracle7.2 and later provides round robin datafile allocation of query servers and it may pay for your site to have multi-dimensional striping using both Oracle and operating system striping for absolute performance flexibility.

What is Oracle striping? Oracle stripes are created using the CREATE TABLESPACE command with the tablespace containing several datafiles, each located on a different disk. The table or index will be created with an INITIAL and NEXT sizing that will fit nicely into each file and a MINEXTENTS clause equal to the number of files. Oracle7.2 and later PQO uses Oracle striping very effectively. It allocates extents (datafiles) to each query process in a round robin fashion. Oracle striping is also standard Oracle and is generic across all machine types.

Tip 2. Ensure that your disk controllers are not creating a bottleneck. Be aware of which disks are on which controllers and stripe your tables/indexes across disks that are on different controllers

Tip 3. If you are using Unix operating system striping, avoid using cooked files unless you are using asynchronous or direct I/Os. If you are not using asynchronous or direct I/Os, use raw devices. If you find raw devices difficult, and are striping cooked files, monitor your disk I/Os to ensure they are spread evenly across your disks. If they aren't either use raw devices or consider Oracle striping.

We have found that on certain Unix machines, using cooked files for striping has resulted in the first disk's I/Os exceeding the remaining disks I/Os. This is caused by the inode having its modified date continually updated. If this is the case on your machine, you should use raw devices.

Tip 4. Stripe your indexes being created using PQO across multiple disks using either Oracle's striping facility or operating system striping. Ideally have the same number of stripes on the index as you have on the table that it is created from.

Tip 5. If you are creating a table as selecting from another using the Oracle7.2 PQO functionality, it is important the table being inserted into as well as the table being selected from are striped to avoid potential bottlenecks.

Tip 6. If you are performing a sort that requires writing to the temporary segments on disk (the entire sort cannot be performed in memory), make sure that you have your temporary segments striped across several disks. The number of disks striped across should equal the largest degree of parallelism that you use for the tables that you are sorting.

It is nice to make your extent sizes is a multiple of the SORT_AREA_SIZE. This does not provide a huge performance benefit however, because the sort data is written from memory to disk is often in variable sized chunks, particularly if you are using a large SORT_AREA_SIZE. The size of the chunks depends on many factors, including internal memory fragmentation. The extent size should be large however, for example, 100 megabytes, to avoid contention for the ST00 lock which can effectively make a parallel sort perform like a serial sort if your extents are too small prior to Oracle7.2.

If you have a mix of OLTP users and parallel query users, you should ideally have a separate temporary tablespace that is specifically set up for parallel query sort requirements and another with significantly smaller DEFAULT settings for the OLTP users. Use the ALTER USER TEMPORARY TABLESPACE temp_large to point the users to the appropriate temporary tablespace.

Tip 7. Oracle offers some new features that you should use to provide considerable performance improvements. If you are using Oracle7.3, create a tablespace specifically for temporary segments, that is CREATE TABLESPACE temp TEMPORARY. The temporary tablespace cannot contain permanent objects (tables, indexes, rollback segments etc). Oracle maintains a separate cache of extents which it allocates to temporary segments and hash joins. This effectively avoids contention on the ST00 lock mentioned in Tip 6. Don't forget to set the parameters COMPATIBLE=7.1.5 (or 7.2.1 or 7.3.1 depending on the version of the Oracle7 that you are running) and if you are using Oracle7.2 or later, set SORT_DIRECT_WRITES=TRUE to gain considerable performance gains by bypassing the buffer cache for sorting.

Tip 8. Consider placing each large table being accessed by PQO and each index being created by PQO into its own tablespace. This gives you greater flexibility in striping and moving the datafile across disks. It also allows you to more easily identify the I/O on the table in situations where you have multiple jobs performing many operations your database simultaneously. When you monitor I/O in SQL*DBA and via the v$filestat, you can get a better gauge of the I/Os on the table or index.

Tip 9. If you decide to use operating system striping, be aware that Oracle will read the DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_ READ_COUNT and you should set your stripe size to that size or a multiple of the size. For example, if you have a DB_BLOCK_SIZE of 4k and a DB_FILE_MULTIBLOCK_READ_COUNT of 16, set the stripe size to 64k. It is interesting to note that the largest multiblock I/O that Oracle is able to read on most platforms is 64k. Another factor that you should consider is the proposed usage of the object. It is best to set the stripe size (e.g. 64k) larger for tables and temporary segments used exclusively for long running batch work, including PQO processing. Indexes and tables used for OLTP processing can be set a smaller stripe size (e.g. 8k) .

Tip 10. Size your extent sizes in your table or index to ensure that the number of extents is minimised for example CREATE TABLE..... STORAGE (INITIAL 500M NEXT 500M PCTINCREASE 0 MINEXTENTS 10). This will provide improved performance because it avoids Oracle having to continually modify the UET$ and FET$ tables used for recording extent information. Oracle7.3 allows unlimited number of extents per database object, but this does not imply that there is not a negative performance impact of throwing large numbers of extents.

Tip 11. If you are loading data into the table using SQL*Loader, the first extent of the table is not used. Set your INITIAL extent small in your CREATE TABLE statement for example, STORAGE (INITIAL 1k NEXT 500M). If you are using Oracle's striping facility, make sure that you size your initial datafile accordingly, that is (the INITIAL extent size + 1 * NEXT extent size).

Tip 12. If you use Massively Parallel Processors, and utilise Oracle's striping facility, each file in the tablespace should ideally be local to a different node in the MPP.

Tip 13. If your table is scanned multiple times and you have enough memory, consider storing your entire table in cache using the ALTER TABLE tname CACHE. Caching a table provides an alternative to PQO which you should consider when benchmarking. If your statement contains joins to other tables, caching the table may provide disappointing performance improvements. This is because the bottlenecks will be in the obtaining the joined rows, not the initial data reading from the driving table. If you are joining and using Oracle7.3 or later, it may pay to dedicate more memory to hash joins instead.

Tip 14. Oracle7.3 has a new command which we recommend you use to create an index from an existing index (ALTER INDEX index_name REBUILD) which can be built in parallel. It can also be built using the UNRECOVERABLE clause which avoids writing to the redo logs and speeds up the index build considerably. Interestingly, UNRECOVERABLE is the default if your redo logs are not being archived.

Tip 15. Oracle7.3 also introduced bit mapped indexing which speeds up the index creation and reduces storage requirements considerably. Bit mapped indexing is most effective for single column low cardinality data, for example (YES,NO) or (ON, OFF) or (MALE, FEMALE). If your table has low cardinality, use bitmapped indexes.

Tip 16. If you are using the Cost Based Optimizer, it is essential to get a large enough sample to share the PQO sort load sharing. For PQO index creation and sorting, the base table is randomly sampled to find sort keys, which are then evenly divided and handed to sort processes. Poor sampling will lead to one of the sort processes causing a bottleneck. Pre 7.1.6, Oracle recommends that you use the ANALYZE statement to sample around 20% of the rows, or use the Rule Base Optimizer and hints. For Oracle 7.1.6 and later, you should sample as many rows as possible using the compute option with the ANALYZE statement. Generally analyzing 5% or more of the rows provides superior performance.

Oracle7.3 introduces histograms that you should take advantage of for superior distribution of workload. An interesting characteristic of histograms is that to make them work effectively, you should pass constant values to them, rather than bind variables. This implies that each statement will be stored separately in the shared pool area. The performance gains provided by histograms heavily outweighs the disadvantage of storing multiple statements in the shared pool area.

Tip 17. Always set your PCTINCREASE to 0 for tables, indexes and temporary segments used by PQO.

Tip 18. Ensure that you have enough space in your database to fit your tables, indexes and temporary extents. Some parallel operations use the INITIAL extent size for all query servers, other use the NEXT extent size. To avoid confusion, make the INITIAL and NEXT extents the same size. Be aware that if you have 10 query servers and an extent size of 100Megabytes, you will need 1 Gig of disk space free, even though the object eventual size may be somewhat smaller after Oracle performs trimming.

PQO and other relevant INIT.ora Parameters

Oracle provides several INIT.ora parameters that you must set correctly to use PQO to it's full potential. The parameters that will have the largest effect on PQO performance are PARALLEL_MAX_SERVERS, the maximum number of parallel query servers available on your instance and the COMPATIBLE and SORT_DIRECT_WRITES parameters, which avoid reading and writing through the buffer cache.

The Oracle buffer cache bottleneck has been a huge headache when multiple large batch jobs run simultaneously or when OLTP users run at the same time as large batch jobs. Avoiding the buffer cache for sort data as well as data being read by PQO offers a substantial performance improvement. TAKE ADVANTAGE OF IT! SORT_AREA_SIZE can also have a significant effect on PQO performance.

As a point of interest, Oracle7.3 has attacked the major multi processor contention points by providing multiple LRU lists and multiple SQL latches.

PARALLEL_MAX_SERVERS is the maximum number of servers allowed to exist concurrently. If you set your maximum servers to 20 and you have 5 SQL statements requesting a degree of parallelism of 5 each, the 5th statement will be forced to run the Full Table Scan using a single server. Set the value to (Maximum number of PQO Users * their maximum degree of parallelism * 2).

PARALLEL_MIN_SERVERS specifies the number of servers created when an instance starts. Oracle will spawn more server processors as required, which does provide a small overhead on performance. The performance overhead is most noticeable when you are processing a small table. The number of servers can be reduced as queries complete and the server is idle for the amount of time specified in the PARALLEL_SERVER_IDLE_TIME parameter. The number of servers running never falls below the value specified in PARALLEL_MIN_SERVERS.

PARALLEL_SERVER_IDLE_TIME is the number of minutes that a server remains idle before Oracle can terminate it.

PARALLEL_DEFAULT_MAX_SCANS is the maximum degree of parallelism available for any Full Table Scan.

PARALLEL_DEFAULT_SCANSIZE is used to calculate the degree of parallelism for a table that has been created or altered using the PARALLEL option without the degree of parallelism specified. We recommend for releases prior to Oracle7.3 you do not rely on Oracle calculating the degree of parallelism. You should specify the degree of parallelism using either a hint in the SQL statement or specify the degree of parallelism in the CREATE TABLE or ALTER TABLE statements, ie always put a number against the PARALLEL option (PARALLEL 4). This parameter is made obsolete in Oracle7.3. The new setting for the default degree of parallelism is the number of disks that the table is spread across of the number of CPUs on your machine, whichever is the minimum.

PARALLEL_MIN_PERCENT is a new parameter introduced in Oracle7.3. If a query has a degree of parallelism requirement, and is unable to obtain the percentage of query servers specified in this parameter, the query will terminate with an error. The default value for this parameter is 0 which allows your query to run with whatever resources are available. If you have long running jobs that run considerably faster if able to run with the full quota of query servers, it may pay to set this parameter to 50 or higher and re-start the query when the required number of servers becomes available.

COMPATIBLE is a parameter that should be set to the release of the Oracle RDBMS that you are currently running to take full advantage of any new features are provided. This inappropriately name parameter can provide you with huge performance improvements. Oracle 7.1.5 introduced the ability to bypass the buffer cache (direct reads) for table scans and sorts (COMPATIBLE=7.1.5). Oracle 7.3 introduced temporary tablespaces that improve the performance of sorts and hash joins (COMPATIBLE=7.3.1).

SORT_READ_FAC is a parameter that assists sort merges. The setting of the parameter will influence a sorts internal memory allocation and optimizer decisions. We recommend that you set it to 25%, 50% or equal to the value set in the INIT.ora parameter DB_BLOCK_MULTIBLOCK_READ_COUNT.

SORT_DIRECT_WRITES was introduced in Oracle7.2. It provides considerable performance improvements (often 5 times or more) through bypassing the buffer cache when writing sort data to the temporary tablespace. You must have adequate memory to take full advantage of this parameter. Each query process will have its own set of direct write buffers with the total memory requirements being (the number of query servers * SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE). To have sort direct writes enabled, set the parameter to TRUE (SORT_DIRECT_WRITES=TRUE).

SORT_AREA_RETAINED_SIZE is best set to the same size as the SORT_AREA_SIZE when using PQO. The SORT_AREA_RETAINED_SIZE is the amount of memory that each query server process is assigned when sorting takes place. Given the larger size of the sorts that are normally performed when using PQO, sort memory usage will quickly raise to the SORT_AREA_SIZE. One consideration is that if PQO queries run at the same time as OLTP, it may pay to keep the SORT_AREA_RETAINED_SIZE at perhaps 64k, because every single user will use the larger amount of memory. If you are running PQO out of OLTP hours, consider shutting down your database, adjusting the SORT_AREA_RETAINED_SIZE to the same size as the SORT_AREA_SIZE (1 or 2 Meg). If your database is exclusively PQO, with few users and plenty of memory, set the SORT_AREA_RETAINED_SIZE to 1 or 2 Meg.

SORT_AREA_SIZE is the maximum amount of memory that can be used by each user process to perform sorting. If the SORT_AREA_RETAINED_SIZE is sufficient, it will not rise above it. With PQO, it is also the amount of memory assigned to sorting for each parallel query server. When the area is filled with sorted data, it is written to the temporary tablespace. Because the SORT_AREA_SIZE is assigned on a per user basis, or in the case of PQO, on a per parallel server basis, it can quickly consume all of your machines memory, causing excessive paging or swapping. OLTP users usually require between 64K and 256K. PQO processes perform best when the SORT_AREA_SIZE is set to between 1MB and 2MB, although if the whole sort can be performed in memory without having to write to the temporary segments on disk, the best setting for the SORT_AREA_SIZE will be considerably larger. If PQO programs are run out of OLTP hours, consider shutting down and re-starting you instance with the larger SORT_AREA_SIZE.

SHARED_POOL_SIZE can provide a PQO performance bottleneck, if it is not set large enough. Parallel query servers use an area within the shared pool for query server to query server communication, that is, where one process is performing a table scan and the second process is sorting the data received from the first process. One parallel query user with a high degree of parallelism tends to use more shared pool area than several parallel query users with a degree of parallelism totalling the same as the single user.

DB_BLOCK_BUFFERS will provide a severe bottleneck if it is set too small. Generally anything below 10MB is inadequate. We recommend that you bypass the buffer cache as much as possible, particularly when PQO jobs are running at the same time as OLTP users. If you are using Oracle7.2 or later, set SORT_DIRECT_WRITES=TRUE. Also, don't forget to set the COMPATIBLE parameter to the version of Oracle you are currently running. If you are using 7.1.5 or later, setting COMPATIBLE will cause direct reads (bypassing the buffer cache) for all full table scans and sorts. With SMP it is essential that you set this parameter because of the added potential of performance degradation caused by latch contention in the buffer cache.

DB_BLOCK_SIZE is best set to a value such as 8K or 16K if your database is used exclusively for long running PQO style jobs. Our benchmarks indicate that a minimum of 4K should be used for an Oracle7.X database regardless of whether it is a mixed database, mixing OLTP users with batch reporting and updates, exclusively OLTP databases or decision support style database with many long running jobs and few OLTP users. Note: the default block size is 2K on many Unix machines.

HASH_JOIN_ENABLED is a parameter that was introduced in Oracle7.3 to enable hash joins. The hash joins are a new innovation that provides an alternative to sort merge joins. They perform considerably faster when the number of rows being joined for the two sides of the join are significantly different. Use HASH_JOIN_ENABLED=TRUE. HASH_AREA_SIZE was introduced in Oracle7.3 to provide memory for each process performing a hash join. Oracle recommends that it can be increased considerably in size and continue to provide excellent performance improvements. Remember that it is used on a per process basis and there is potential for damaging paging and swapping if set too high.

ALWAYS_ANTI_JOIN is a new parameter introduced in Oracle7.3. You should set the parameter =HASH to allow parallel processing of our "old resource hog", the NOT IN clause. Setting the parameter allows a parallel hash anti join.

Selecting the Optimal Degree of Parallelism

To set the optimal degree of parallelism, you must have a thorough understanding of the effect of changing it's value. Increasing the value by one will create an additional query server process or two query server processes if the statement requires a sort. One query process will read from the table and the other will be passed the data to perform the sort. Some basic facts to consider include disks are always going to provide a bottleneck for today's high-speed processors. It must be noted, however, that disk caching is advancing at a rapid rate. If multiple query processes are attacking the one disk, the disk will be running red hot, that is unless the process is required to perform a sort or join to other tables. Each query process will be assigned to a CPU and it must also be noted that if each process is performing resource intensive tasks (CPU intensive), the CPU can become the bottleneck.

We can conclude for a full table scan with no sorting or joins, it is best to set the degree of parallelism to the number of disks that the table is spread over. If a sort or join is involved, our benchmarks indicate that performance improvements often occur having the number of query processes up to twice the number of CPUs available on your system. We have found that it if PQO statements hit the same tables/indexes/temporary segments it is best to run the PQO statements one after the next.

From our benchmarks, PQO provides the best performance improvements on SMP machines where the datafiles and hence the tables being scanned are scattered across many disks. Our figures indicate that the number of disks that the table striped across should be no greater than twice the number of CPUs. Ideally, the CPUs are under utilised and there is adequate memory to dedicate to sorting. File striping can be done either automatically using the operating system or using the Oracle striping facility.

If you are performing a sort in your PQO processing, you must be careful to ensure that there is free memory on your system to accommodate the multiple sort areas as well as your SGA. There will be one sort area assigned in memory per query process, that is, if the degree of parallelism is 40 and the SORT_AREA_SIZE is 100 MB, you will need 4 GB of sort area. If there is not adequate memory, your response times will be degraded markedly caused by damaging paging and or swapping. It is usually best to set your sort area size to around 2 Megabytes unless you can fit your whole table in memory.

Setting the correct value to the degree of parallelism is best done by benchmarking the query on the configuration and using the actual table it is intended to run against. This is not always possible because the query may be a once off or the environment may be unable to be used for testing. Oracle provides the EXPLAIN PLAN to assist with checking the use that Oracle will make of PQO. You should always run it against your query to ensure that it will utilise PQO in the manner that you would expect. you should use hints in your SQL statements.

The information that you need to take into account to select the most appropriate degree of parallelism for your SQL statement include:

Ongoing Monitoring and Tuning of the Query Servers

The ideal way to monitor and tune the query servers is to check the elapsed run time of the queries using PQO. The problem is that other factors may be influencing the response time such as other jobs running at the same time as the PQO job was running. Oracle provides two tools that may be used, namely the V$PQ_SYSSTAT table and the EXPLAIN PLAN facility.

You should also be familiar with your operating system commands to monitor your disk I/Os, CPU usage, memory usage and paging and swapping rates and be capable of identifying your query server processes. On Dynix the query servers have the format ora_Pnnn_DEV where nnn is the digits that identify the query server number and DEV is the database instance.

Using the V$PQ_SYSSTAT Table The V$PQ_SYSSTAT virtual table assists with the setting of the INIT.ora parameters PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS. As mentioned elsewhere, the MAX servers is the largest number of servers that may exist in your instance and the MIN is the number of servers that are started for use when the instance is started. When extra servers are required, they are allocated, but the total number of servers cannot exceed the value specified in PARALLEL_MAX_SERVERS. After the query has completed, the query servers will remain active for the number of minutes specified in the PARALLEL_SERVER_IDLE_TIME. The number of servers will never fall below the INIT.ora PARALLEL_MIN_SERVERS setting. To view the current status of the Oracle parallel query servers, run the following query during peak PQO usage.

SELECT statistic, value 
 FROM v$pq_sysstat
/

STATISTIC       	VALUE          
Servers Busy       	0          
Servers Idle       	5          
Servers Highwater      	11          
Server Sessions      	33          
Servers Started       	6          
Servers Shutdown      	6          
Servers Cleaned Up      	0          
Queries Initiated      	3          
DFO Trees        	3          
Local Msgs Sent      	304          
Distr Msgs Sent       	0          
Local Msgs Recv'd      	288          
Distr Msgs Recv'd      	0          

13 rows selected.

If the number of Servers Busy is continually equal to the value assigned to PARALLEL_MAX_SERVERS, increase the PARALLEL_MAX_SERVERS, provided the system has sufficient CPU resources. This prevents Oracle having to continually start up new servers. It also means that some jobs may be missing out on the full set of servers that they require to run optimally.

If the Servers Busy is continually higher than the PARALLEL_ MIN_SERVERS, consider increasing the PARALLEL_MIN_SERVERS parameter up to the Servers Busy value. Starting up query servers can take time. As a point of interest, I have noticed that starting up a database with a high number of PARALLEL_MIN_SERVERS lengthens the start up time considerably.

If the number of servers started and shutdown is occurring at a rapid rate, consider increasing the value assigned to PARALLEL_SERVER_ IDLE_TIME.

An interesting observation is with PRO*C and other PRO languages. Unless you specifically say HOLD_CURSOR=NO and RELEASE_CURSOR=YES, Oracle will keep the query servers assigned to your program. Make sure that you don't let this occur.

Other views that will be of interest to you are V$PQ_SESSTAT and V$PQ_SLAVE. V$PQ_SESSTAT gives you details on the PQO usage of your current session. V$PQ_SLAVE provides you with statistics on the amount of time that various PQO sessions have been running.

SQL> desc v$pq_sesstat

  Name       	Null? 	Type
  STATISTIC        		VARCHAR2(30)
  LAST_QUERY        		NUMBER
  SESSION_TOTAL       		NUMBER

SQL> desc v$pq_slave

  Name       	Null? 	Type
  SLAVE_NAME        		VARCHAR2(4)
  STATUS         		VARCHAR2(4)
  SESSIONS         		NUMBER
  IDLE_TIME_CUR       		NUMBER
  BUSY_TIME_CUR       		NUMBER
  CPU_SECS_CUR        		NUMBER
  MSGS_SENT_CUR       		NUMBER
  MSGS_RCVD_CUR       		NUMBER
  IDLE_TIME_TOTAL       		NUMBER
  BUSY_TIME_TOTAL       		NUMBER
  CPU_SECS_TOTAL       		NUMBER
  MSGS_SENT_TOTAL       		NUMBER
  MSGS_RCVD_TOTAL       		NUMBER

Using Explain Plan to Tune Your PQO Queries

If you are using Oracle7.1 or Oracle7.2, examine the PLAN_TABLE using the following query: 
select substr(lpad(' ',2*level-1) ||
           decode(id, 0, statement_id, operation) ||
           ' ' || options || ' ' || object_node , 1, 79) 
                             "Parallel Query Plan Step"
  from plan_table
  start with id = 0
connect by prior id = parent_id
         and prior nvl(statement_id, 'X') = nvl(statement_id, 'X')
/

The columns to pay attention to are the OBJECT_NODE and OTHER columns. The OBJECT_NODE column shows you the order that output from the operations is performed and the OTHER column shows you the SQL text or parallel operation that the query server is performing for each step. The problem with the other column is that it is defined as long. To get the SQL test, enter the following query. Excuse the untidy layout of the query's output. 
select substr(lpad(' ',2*level-1) ||
           decode(id, 0, statement_id, operation) ||
           ' ' || options || ' ' || object_node , 1, 60) , other
  from plan_table
  start with id = 0
connect by prior id = parent_id
        and prior nvl(statement_id, 'X') = nvl(statement_id, 'X')
/

The two parallel operations that we have observed in the OTHER column are:

null the table scan will be performed serially using a single process
serial_to_parallel the table is scanned using a single processor but the data is passed to multiple processes running in parallel
parallel_to_parallel the full table scan is performed by a number of query processes running in parallel and the data is passed to a second set of processes which are also running in parallel
parallel_to_serial the table is scanned using multiple processes but the data is passed to a single process
parallel_combined_with_parent the table is scanned in parallel and the next step is handled by the same parallel process
parallel_combined_with_child same as parallel combined with child

Full table scans of substantial amounts of data should ideally be using parallel_to_parallel, parallel_to_serial, parallel_combined_with_parent or parallel_combined_with_child. If you have a sort of large amounts of data and high cardinality (many distinct values), the ideal is likely to be parallel_to_parallel.

select substr(lpad(' ',2*(level-1)) ||
           decode(id, 0, statement_id, operation) ||
           ' ' || options || ' ' || object_name || other_tag, 1, 79) 
                             "Plan Step with Parallel Path"
  from plan_table
  start with id = 0
connect by prior id = parent_id
         and prior nvl(statement_id, 'X') = nvl(statement_id, 'X')
/

If you are using TKPROF with Oracle7.1 to retrieve parallel query information, you're going to be very disappointed. Unfortunately parallel query usage information is totally missing. Oracle7.2 and later TKPROF provides the information that you'll need.

Creating Indexes in Parallel

PQO can significantly speed up the creation of indexes. The larger the table, the greater the performance improvement. PQO will also speed up the creation or enabling of PRIMARY KEY and UNIQUE KEY constraints when you use the USING INDEX clause of the ALTER TABLE command.

To tune the index creation effectively, it is important to understand the steps PQO uses to create the index.

1. The base table is randomly sampled in parallel to find index keys that will evenly divide into N sub ranges where N is the degree of parallelism

2. Each producer process scans 1/N of the base table and sends (key, rowid) pairs to each of N consumer processes using dividing keys found in step 1.

3. Each of the N consumers sort and build its part of the index. The sort and the index are stored in temporary segments.

4. The N consumers pass the temporary segment database information to the co-ordinator which assembles the B*tree index structure using the data passed to it from the other servers.

Your resulting index has all of the properties of a serially created index. Relying on the index to return rows in sequence works the same as it always has. Make sure that you use the INDEX_ASC or INDEX_ONLY hints.

The parallel index creates extents into one temporary extent per process. The extents are then merged into one final permanent segment. Each index MUST have at least N extents preferably of the same size with each stored on a separate disk.

To create an index in parallel mode use the CREATE INDEX statement as shown in the statement following:

CREATE INDEX electoral_search_ndx1 
                   ON electoral_roll (electoral_zone, surname, initial)
         STORAGE (INITIAL 200m NEXT 100m)

    PARALLEL (DEGREE 6);

To create the index without using PQO you enter the following command: 
CREATE INDEX electoral_search_ndx1 
                   ON electoral_roll (electoral_zone, surname, initial)
         STORAGE (INITIAL 200m NEXT 100m)

    NOPARALLEL;

If you do not specify a degree of parallelism in the CREATE INDEX command the table's definition will be used.

Other factors that will significantly improve the performance of index creation are the UNRECOVERABLE option on the CREATE INDEX command and the ORACLE7.3 option that allows you to create an index from a current index as opposed to having to read from the table. Index creation requires sorting, so don't forget to tune your SORT_AREA_SIZE, and Temporary Tablespaces as well as setting the COMPATIBLE (Oracle7.1.5 and later) and SORT_DIRECT_WRITE (Oracle7.2 and later) parameters. One interesting point that you should be made aware of is that enabling a UNIQUE or PRIMARY KEY CONSTRAINT to create an index will not run in parallel. You must create the index first and then enable or add the constraint.

Using PQO to Speed up Loading Data Into a Table

SQL*Loader will speed up the data loading task if you specify DIRECT=TRUE which bypasses the redo logs. PQO can further speed up data loading if used correctly and your data load lends itself to parallel loading. PQO will benefit the loading of larger tables if the table is spread across a number of disk devices using either Oracle or operating system striping facilities. If the table is on a single disk drive, PQO will invariably lead to a disk I/O bottleneck.

To use PQO for data loading issue the following SQL*Loader syntax:

sqlload userid=gurrym/mark control=accload1.ctl direct=true parallel=true
sqlload userid=gurrym/mark control=accload2.ctl direct=true parallel=true

Notice that multiple SQL*Loader sessions are required to force the parallel processing. Each control file, accload1.ctl and accload2.ctl will contain the file statement, which will have a different FILE name assigned to it. The file name specified is the database files from which the temporary segments are allocated. The files specified in the file clause should be on different disk devices to maximise throughput. 
LOAD DATA
INFILE 'accload.dat'
FILE = '/tmp/acctemp1.dat'
INSERT INTO TABLE accounts ...........

The mechanics used by the PQO from the parallel data load is to use temporary segments to load the data (as specified in the FILE parameter above). The temporary segments are merged and then inserted into the table directly above the high water mark.

Indexes do not get created during the parallel load. You must create them manually after the data load completes. You can speed up the creation of your index using the PARALLEL option. You will then have to enable your primary and unique key indexes, because there is no parallel option on the create constraint commands.

One issue that you should be aware of is the use of the high water mark, which is the furthest point that the table has been expanded to. If a high number of deletions have taken place, you may be wasting a significant amount of storage in your table. If you are deleting a lot of rows from your table, seriously consider re-building your table using the ORACLE7.2 function UNRECOVERABLE. The ability to re-create indexes using UNRECOVERABLE and create the indexes in PARALLEL will also assist. ORACLE7.3 also introduced the CREATE TABLE tname AS SELECT... which can alo be run in parallel and assist your cause.

Use the parallel clause as follows if you do not wish to use the parallel option.

sqlload userid=gurrym/mark control=accload1.ctl direct=true parallel=FALSE

Performing Parallel Recovery

Parallel recovery can assist you in speeding up the instance or media recovery of your database. Oracle provides an INIT.ora parameter RECOVERY_PARALLELISM which specifies the number of recovery processes. The value of the parameter must be greater than 1 and must not exceed the value assigned to the INIT.ora parameter PARALLEL_MAX_SERVERS. The number of recovery processes can also be specified in the RECOVER command (see following examples). The RECOVER clause setting takes precedence over the RECOVERY_PARALLELISM setting.

Use the commands 
RECOVER DATABASE PARALLEL (DEGREE 10); 	

# for database recovery using 10 parallel recovery processes

RECOVER TABLESPACE tablespace name PARALLEL (DEGREE 10); # for tablespace recovery

# for tablespace recovery using 10 parallel recovery processes

RECOVER DATAFILE 'datafile name' PARALLEL (DEGREE 10); 	# for datafile recovery

# for datafile recovery using 10 parallel recovery processes

You can also specify a RECOVER clause setting of PARALLEL (DEGREE DEFAULT) which sets the number of recovery processes to twice the number of datafiles being recovered. 

RECOVER DATABASE PARALLEL (DEGREE DEFAULT);

To perform a recovery in a serial mode (single recovery process), do not specify the RECOVERY_PARALLELISM option in the INIT.ora file and do not specify the PARALLEL option in the RECOVER clause. If the RECOVERY_PARALLELISM clause is set to true, you can force the recovery to operate in serial mode using the command 

RECOVER DATABASE NOPARALLEL;

Data is read from the redo logs sequentially and several recovery processes are started to perform the recovery to the various datafiles. To get the full benefits of parallel recovery, it is best to have your database spread across many disks and controllers. The benchmarks we have run indicate that the databases that receive the greatest performance improvements are on machines that do not support asynchronous I/O. The greater the number of disks requiring recovery, the more significant the performance improvements.
About the Author

Mark Gurry of New Age Consultants Pty. Ltd., Australia is an independent Oracle Performance consultant. Mark is the co-author, with Peter Corrigan, of "The Oracle Performance Tuning Book," O'Reilly & Associates.
Address: 52 Rowena Parade
Richmond, Victoria
Australia 3121
Phone: +61 3 9428 8454
Email: [email protected]


This is a copy of an article published @ http://www.ioug.org/