Avoiding physical contention in parallel join queries

Figure 1-1 illustrates a join of two tables, orders_tbl and stock_tbl. There are 10 worker process available: orders_tbl has 10 partitions on 10 different physical devices and is the outer table in the join; stock_tbl is nonpartitioned. The worker processes have a problem with access contention on orders_tbl, but each worker process must scan stock_tbl. There may be physical I/O contention if the entire table does not fit into cache. In the worst case, 10 worker processes attempt to access the physical device on which stock_tbl resides. Avoid physical I/O contention by creating a named cache that contains the entire table stock_tbl.

Another way to reduce or eliminate physical I/O contention is to partition both orders_tbl and stock_tbl and distribute those partitions on different physical devices.

Figure 1-1: Joining tables on different physical devices

Image shows the orders_tbl (on one device) joining with the stock_tb on another table.