OptimizingOracleRAC - 数据库编程

tribution of work across the cluster. Partitioning the segments with the highest levels of Global Cache activity. Hash partitioning can split up the hot blocks, hopefully reducing Global Cache contention for those blocks.Range or list partitioning the segments in conjunction with isolation of user populations can also be considered. For instance as well as creating services to support geographical regions (East Coast/West Coast for instance), we could also consider partitioning key tables and indexes on the same geographic basis. Partitions specific to a particular region will naturally tend to become cached (and mastered) on the instances to which those users connect, resulting in fewer Global Cache requests.Reverse key indexes can help relieve hot Global Cache index leaf and branch blocks. When a segment has an ascending numeric primary key, all new rows require an update to the leading edge of the index. Freelist management will distribute the new table rows across the cluster, but the leading leaf and branch blocks of the index will be in contention by all instances in the cluster. Reverse key indexes distribute new entries across all the leaf blocks, relieving the contention. However, be aware that reverse key indexes cannot be used for index range scans (though you don’t often need to perform range scand on primary keys).If you have legacy tablespaces that don’t use Automatic Segment Storage Management (ASSM), then you must make sure that you use multiple freelist groups on tables that have non-trivial insert rates. Otherwise all new rows will be directed to the same new blocks, creating Global Cache contention for that block.

Very high Global Cache request rates can limit RAC performance and scalability. Global Cache activity can be reduced by adjusting application design, isolating workloads or sessions using services, reverse key primary indexes and by partitioning segments.

Inter-instance parallel query can improve parallel SQL performance but can also increase Global Cache traffic. Restricting parallel execution to a single instance or smaller set of instances will often be indicated. The Oracle 10g INSTANCE_GROUP parameter or the Oracle 11g PARALLEL_INSTANCE_GROUP parameter can control the instances that are involved in a parallel SQL.

Summary

Most of the performance optimization techniques applicable to single instance databases are just as applicable to RAC databases. In particular, before moving to a RAC architecture make sure you have eliminated any “hot block” forms of contention such as buffer busy or cache buffers chains latch contention as these tend to magnify in RAC.

The most significant difference in a RAC database is the use of Global Cache requests to fetch blocks from other instances in the cluster rather than to read them from disk. RAC will scale and perform well, providing that:

Global Cache latency is much less than disk read latency. Achieving this involves both optimizing the interconnect network, and making sure that no instances get too busy to respond to Global Cache requests in a timely manner.The rate of Global Cache requests is reasonable. In particular, “hot” blocks that are in constant contention across the cluster should be minimized.The cluster is reasonably well balanced. In particular, no instance should be overloaded: an overloaded instance is likely to cause performance problems both for itself, and other instances in the cluster.

OptimizingOracleRAC(十六)

Summary