Sort Merge Joins and Parallel Query Oracle Tips by Burleson Consulting

The sort merge operation is the most ideal for parallel query because a merge join always performs full-table scans against the tables. Sort merge joins are generally best for queries that produce very large result sets such as daily reports and table detail summary queries. Here we see a simple query that has been formed to perform a sort merge using parallel query against both tables.

select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the output of the execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
OTHER_TAG
----------------------------------------------------------------------
SELECT STATEMENT
                                                                     5
  MERGE JOIN
                                                                     1
PARALLEL_TO_SERIAL
    SORT
JOIN                                                                 1
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           EMP                                   1
PARALLEL_TO_PARALLEL
    SORT
JOIN                                                                 2
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           BONUS                                 1
PARALLEL_TO_PARALLEL

Again, please note that a sort merge join does not use indexes to join the tables. In most cases, index access is faster, but a sort merge join may be appropriate for a large tale join without a where clause, or in queries that do not  have available indexes to join the tables.

Hash Joins in Parallel

Oracle hash joins are notoriously hard to set up within Oracle, and the Oracle Metalink archives are full of problem reports regarding successful hash join invocation. I will discuss hash joins in detail in Chapter 16, but for now, just be aware that Oracle reads the driving table into a RAM array of hash_area_size and uses a special hashing technique to join the memory array with the larger table.

For equi-join operations, hash joins can outperform nested loop joins, especially in cases where the driving table is small enough to fit entirely into the hash_area_size. If the driving table is too large, the hash join will write temporary segments into the TEMP tablespace, slowing down the query. Since the reading of the table rows for a hash join is the most time-consuming operation in a hash join, setting parallelism on the table can dramatically improve the performance and throughput of the query.

Here is an example of a query that forces a parallel hash join. Note that the emp table is set as the driving table:

select /*+ use_hash(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the execution plan for the hash join. Note that both tables in this join are using parallel query to obtain their rows:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
OTHER_TAG
----------------------------------------------------------------------
SELECT STATEMENT
                                                                     3
  HASH JOIN
                                                                     1
PARALLEL_TO_SERIAL
    TABLE ACCESS
FULL                           EMP                                   1
PARALLEL_TO_PARALLEL
    TABLE ACCESS
FULL                           BONUS                                 2

For equi-join SQL, hash joins are often faster than nested loop joins, especially in cases where the driving table is filtered into a small number of rows in the query’s where clause. Here are some tips for ensuring that a hash join is enabled and optimized for your query.

• Check initialization parameters   Make sure that you have the proper settings for optimizer_index_cost_adj, hash_multiblock_io_count, optimizer_max_permutations, and hash_area_size. You can see Chapter 16 for details on setting these parameters.

• Verify driving table   Make sure that the smaller table is the driving table (the first table in the from clause). This is because a hash joins builds the memory array using the driving table.

• Analyze CBO statistics   Check that tables and/or columns of the join tables are appropriately analyzed.

• Check for skewed columns   Column histograms statistics are recommended only for non-uniform column distributions in low-cardinality indexes. If needed, you can override the join order chosen by the cost-based optimizer using the ORDERED hint.

• Check RAM region   Ensure that hash_area_size is large enough to hold the smaller table in memory. Otherwise, Oracle must write to the TEMP tablespace, slowing down the hash join.

• Monitor parallel query slave rows   For parallel hash joins, make sure there is no skew in the slave processes' workloads by monitoring CPU usage, and reviewing the v$pq_tqstat view (see the pq_server.sql script in the following section). Monitoring parallel slave row statistics during the elapsed time of the hash join will show whether there is slave workload skew. Also note that skews could also occur because there are very few values in the column being equi-joined.

To summarize, Oracle parallel query will often improve the performance of large-table joins, and you must carefully experiment with the different join methods to determine the best join plan. Now let’s move on to look at how the Oracle Remote DBA can monitor the behavior of parallel query slave processes.

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