Database Reference
In-Depth Information
In a RAC database, excessive logical reads can lead to increased global cache activity. Numerous blocks might need
to be transferred back and forth between the instances in a busy database. This increased global cache activity will
slow down the processes, executing inefficient statements, and also cause performance issues with other application
components. SQL statements performing nested loops join with millions of rows in the driving table usually suffer
from this type of performance issue. Due to incorrect cardinality estimates, the optimizer might choose nested loops
join instead of hash join, leading to numerous lookups of other tables.
Again, RAC acts as a problem magnifier and magnifies performance issues associated with inefficient execution
plans. A reliable method of statistics collection is an important part of an application lifecycle in a RAC database.
A few customer sites collect statistics on a cloned copy of the production database and import the statistics to a
production database, avoiding downtime due to invalid statistics.
Excessive Parallel Scans
Excessive inter-instance parallel scanning can overload the interconnect. In an inter-instance parallel operation,
messages between parallel servers are transmitted over the interconnect. If the interconnect is not sized properly,
then it is possible to induce global cache latency in the database due to overloaded interconnect.
Our recommendation is that if your application is designed to use parallelism aggressively, measure the
private network traffic for PX traffic carefully and size the interconnect hardware to match the workload. For further
discussion about parallelism in a RAC database, see Chapter 12.
Also, most parallel scans read blocks directly from disk to PGA. These direct reads trigger object-level
checkpoints, causing performance issues. For smaller read-mostly tables, caching an entire table in a KEEP buffer
cache and performing full scans might be more efficient.
Further, you can reduce the interconnect traffic for PX messages by localizing all PX servers to the local node
using the parallel_force_local parameter. If the parameter is set to TRUE, then all parallel executions initiated from the
local node will allocate PX servers from the local node only, avoiding PX interconnect traffic.
Full Table Scans
Full table scans can utilize direct path or conventional mode reads. With conventional mode read, blocks are read
into the buffer cache from the disk. Reading the block into buffer cache requires global cache locks.
7
This additional
workload affects the application performance in a RAC database. However, blocks read for a full table scan operation
are considered to be cold blocks and can be paged out of the buffer cache quickly. Hence, gains from the shared buffer
cache are minimal, even if many concurrent processes are scanning the table. To reread the block into the buffer
cache, global cache locks must be acquired again. Concurrent sessions from multiple instances compete for the same
buffers, increasing waits for global cache events and global cache block transfers. This problem is more acute if the
buffer cache is undersized.
A table can be scanned using the direct path read access path too, even if SQL executes serially. This feature is
known as
serial direct read
. However, a direct path read access path requires an object-level checkpoint similar to the
object checkpoint triggered for parallel SQL statements. Thus, excessive serial direct reads can be cost prohibitive
if the buffer cache is very active and large. Events such as write complete waits and DFS lock handle can be seen as
major wait events in the database.
With an effective table partitioning scheme, you can reduce the number of blocks to be read from the disk.
For example, if the table is accessed using a time component, then partition the table along the natural time order.
However, a SQL statement should be written matching the partitioning method. With partition pruning, the full table
scan access path will scan only a smaller set of partitions, reducing disk reads and global cache locks.
7
The dynamic remastering feature is useful. If the table is remastered to a local instance, then the need for additional global cache
locks is reduced significantly.
