Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 276

Partition-wise Joins
A partition-wise join is a join optimization that is used when joining two tables that are both partitioned along the
join column(s). That is, if the tables and/or indexes have the same partition columns, the same number of partitions,
and the same range values for range partitions, then the optimizer is able to perform joins partition by partition. By
working with smaller sets, the number of rows that need to be joined is smaller, resulting in faster processing.
With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in
parallel. Partition-wise joins minimize the amount of data exchanged among parallel slaves during the execution
of parallel joins by taking into account data distribution. It divides a large join into smaller joins between a pair of
partitions from the two joined tables. To use this feature, you must equi-partition both tables on their join keys.

Sequence Numbers
Sequences (numbers) are used in many areas every day, from construction projects to kitchen projects to high-tech
computer applications. The construction worker has to follow a sequence when he constructs a house or a building.
In high-tech computer applications, sequence numbers are used to keep the transactional sequence. Oracle's
architecture uses the system change number (SCN) to keep the transactional order and uses them during instance/
database recovery operation.
Applications use sequences to generate a primary key for business entities. Applications that generate their own
sequence (custom sequences) internally, and store the values in tables, can cause serious performance issues in a
highly insert-intensive application. This is because each time a new number is required, the table has to be queried,
the number changed, and the new value updated. In doing this, the database generates lot of log I/O and contention
on the same block while the application updates the next sequence value. In a RAC environment, these high
concurrency hot block issues will be extrapolated, causing severe performance issues.
Common problems that can be encountered when the application-generated custom sequences are used include
the following:

• Row level locking causing high contention. Getting and maintaining sequences can cause

enqueue contention on the sequence enqueue and latches.

INSERT intensive applications,

• Due to the non-recursive nature of the transaction, in high

updates to the table can be queued.

• There could be serious concurrency issues when this method is used.

• Additional GES traffic due to Global TX and TM enqueues for both transaction and the table

involved in the operation.

The Oracle sequence generator reduces serialization where the statements of two transactions must generate
sequential numbers at the same time. By avoiding the serialization when multiple users wait for each other to
generate and use a sequence number, the sequence generator improves transaction throughput and, consequently,
a user's wait will be considerably shorter. A sequence generator is useful for generating transaction-safe numbers for
database transactions.

Sequences and Index Contention
Indexes, with key values generated using sequences, is subject to leaf block contention when the insert rate is high.
This is because the index leaf block holding the highest key value is changed for every row inserted, as the values
are monotonically ascending. In a RAC environment, this may lead to a high rate of current and CR blocks being
transferred between nodes.