Donald K. Burleson Oracle Tips

The dangers of Invoking Oracle parallel query

There are some global Oracle changes that can have a profound impact on your whole system, and turning on parallel query with the ALTER TABLE command can be very dangerous. Oracle parallel query can be turned on in several ways. You can turn it on permanently for a table, or you can isolate the parallel query to a single table.

The danger is setting parallel at the database level with the ALTER TABLE command like this:

The recommended approach for using Parallel query is to add a parallel hint to all SQL statements that perform a full-table scan and would benefit from parallel query.  If you cannot use hints and you are using Oracle8i, you can also use Optimizer Plan stability for this purpose.

Note the use of the double hints in the preceding query. Most Oracle DBAs always use the full hint with the parallel hint because they are both required to use Oracle parallel query.  Smart Oracle professionals always use hints to invoke parallel query.

As we stated, it is very dangerous to enable Oracle parallel query for a table with the alter table command. Once a table is marked for parallel query, the Cost-Based Optimizer (CBO) will change the execution plan for existing queries to use parallel full-table scans instead of index scans. This well-intentioned mistake has crippled many databases, since queries that used to run fast with indexes will now use a full-table scan.

This happens because the CBO will have a lower cost for a parallel full-table scan than a single-process full-table scan.  If you must set table parallelism at the database level, many DBAs will alter the optimizer_index_cost_adj parameter.

The default value for optimizer_index_cost_adj is 1,000, and any value less than 1,000 makes the CBO view indexes less expensive. If you do not like the propensity of the CBO first_rows mode to favor full-table scans, you can lower the value of optimizer_index_cost_adj to 10, thereby telling the CBO to always favor index scans over full-table scans. For complete details on adjusting parallelism for your Oracle database, see Chapter 14 of Oracle High Performance SQL Tuning, by Oracle Press.