Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

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:


Alter table customer parallel degree 35;


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.


select /*+ FULL(emp) PARALLEL(emp, 35) */


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.






Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.