BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Using Parallel Query Hints

Oracle Tips by Burleson Consulting



The pq_distribute Hint

The pq_distribute hint is used in data warehouses to improve parallel join operation performance when using partitioned tables. The pq_distribute hint allows you to specify how rows of joined tables should be distributed among producer and consumer parallel query servers. The pq_distribute hint accepts three parameters: the table name, the outer distribution, and the inner distribution.

As I discussed in Chapter 10, we always want to avoid the PARALLEL_TO_PARALLEL execution plan when performing a parallel query join. Performing a PARALLEL_TO_PARALLEL operation means the incoming and outgoing data streams are parallelized, resulting in slow join performance. On the other hand, invoking the PARALLEL_COMBINED_WITH_PARENT operation means that sort and merge operations are combined into one operation.

Prior to the use of the pq_distribute hint, Oracle Remote DBAs would often fake out the SQL optimizer by deleting the CBO statistics on the inner table to force the PARALLEL_COMBINED_WITH_PARENT operation. This is because the SQL optimizer evaluates the size of candidate broadcast tables according to the CBO statistics. If a table is above a threshold value, the table will be joined via the PARALLEL_TO_PARALLEL execution mode, resulting in very slow execution times.

There are six acceptable combinations for table distribution with the pq_distribute hint. We use the emp table in these examples. Remember that the order of the parameters is outer distribution followed by inner distribution.

  •   pq_distribute(emp, hash, hash)  This maps the rows of each table to consumer parallel query servers using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash join or sort merge join.

  • pq_distribute(emp, broadcast, none)  This ensures that all rows of the outer table are broadcast to each parallel query server, while the inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. A rule of thumb is to use the Broadcast/None hint if the size of the inner table times the number of parallel query servers is greater than the size of the outer table.

  • pq_distribute(emp, none, broadcast)  This forces all rows of the inner table to be broadcast to each consumer parallel query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. A rule of thumb is to use the None/Broadcast hint if the size of the inner table times the number of parallel query servers is less than the size of the outer table.

  • pq_distribute(emp, partition, none)  This maps the rows of the outer table using the partitioning of the inner table. The inner table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of parallel query servers.

  • pq_distribute(emp, none, partition)  This combination maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers.

  • pq_distribute(emp, none, none)  Each parallel query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equi-partitioned on the join keys.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter