A Developer’s Guide to Oracle Real Application Clusters (RAC)

Are you a developer who is using Oracle Database and is interested in learning more about Oracle Real Application Clusters (RAC)? At Quest’s INSYNC conference, Anil Nair, prod­uct manager at Oracle, presented “A Developer’s Guide to Oracle RAC.” The session covered tips and tricks to ensure that develop­ers can benefit from features such as session failover and load balancing with minimal effort.

Common Issues and Workarounds

Nair walked attendees through high-level issues that developers may encounter as well as work­arounds to remedy them.

Avoiding Hot Spots

The first issue Nair discussed was avoiding hot spots in applications. This is not an RAC-specific issue but is a generic scalability issue. Frequent transactional changes to the same data block in all instances may result in “write hot spots.” Write hot spots occur on indexes in 99% of OLTP (online trans­action processing) performance issues. A block with pending changes may be pinged by other instances. A pending redo must be written to a log before the block can be transferred.

Non-Ordered and Cached Sequences

It’s also important to use non-ordered and cached sequences if sequences are used to generate the primary key. If not cached, symptoms may include EQ or SQ contention. Ordered sequences do not scale well, so the solution is to only use them in one instance in active-passive configuration and create multiple per application.

Scalable Sequences in 18c

Since Oracle Database 18c, there has been a new option in Cre­ate/Alter Sequence SCALE {Extend | NoExtend | NoScale}. When scale is specified, an internal algorithm uses the instance ID and other session-specific information to generate an offset. DBAs need to alter existing sequences. DBA/User/ALL_ sequences will reflect the additional attributes.

Reduce Brownout by Avoiding Hot Spots

You can reduce brownout by doing frequent commits and setting MTTR targets. Set FAST_ START_MTTR_TARGET after evaluating the TARGET_MTTR and ESTIMATED_MTTR from V$INSTANCE_RECOVERY. Optimize log flush by placing redo logs on fast storage if performance-critical (e.g., SSDs) and separating disks for logs from other IO busy disks. Exadata has a smart logging feature, and ODA by default uses SSDs. Schema tuning only involves minimal modifi­cation and is the preferred option.

Indexes

You also want to ensure that the index choice that you have is conducive to scale. Global hash partitioned indexes and locally partitioned indexes both achieve better cache local­ity. If you’re using Exadata, some of the unused indexes can be dropped.

Controlling Concurrent Sessions

It’s important to control the number of concurrent sessions. Scheduling delays on high context switch rates on busy systems may result in performance problems. More processes result in higher memory utilization, higher risk of paging, and higher sys­tem CPU time. To control concurrent sessions, use connection pooling and avoid connection storms (pool and process limits). In addition, ensure that the load is well-balanced over nodes.