Testing for Scalability - Expert Oracle RAC Performance Diagnostics and Tuning - page 103

Table 4-7. RAP Phase VII—Load Test on One Server

Test # Date & Time # of Users Test

Duration

# of Db
Servers

# of App
Servers

Description Status

2 10-Aug-xxxx

1 pm

40 60 minutes 4 6 Db server shows high

CPU usage with 10
users. Identified a
poorly performing
SQL statement. Overall
response times poor.

FAILED

Once again, the test illustrated in Table 4-7 failed. Similar to the previous tests, the failure of this test was due to
lack of sufficient resources to complete the operation. However, the potential cause of the problem was focused on a
bad SQL statement. The statement performed well in a single instance superdome environment. However, in a RAC
environment, it had issues.

Step 6
It's important to tune the SQL statement to get the databases to scale.
Once the query was tuned and deployed back into the test environment, the test was repeated. This time the test
succeeded and the response time expected was obtained. Although this could be improved, we looked at the internal
RAC-related statistics and noticed high sequence number related waits.
Looking further identified high waits on sequences— PCT_SUCC_GETS < 50% and sequence UPDATES frequency high
on Instance 1 and Instance 2:

INT PARAMETER GETS MISSES PCT_SUCC_GETS UPDATES
---- -------------- -------- -------- ------------- ----------
1 dc_sequences 24238 12205 49.6 24,238
2 dc_sequences 20913 12203 41.6 20,911
3 dc_sequences 2926 869 70.3 2,926
4 dc_sequences 2621 886 66.2 2,621

All sequences were set to the default cache value of 20. In a RAC environment, sequences maybe requested from
more than one session and from multiple instances in the cluster. Cache size of 20 may be minimal, and sequences
that are frequently accessed should be set to a higher cache size.

Step 7
With the aforementioned fixes and repeating the tests, the overall response times for the database was normal: it was
ready for testing beyond 4 nodes. We increased the DB servers to six instances (see Table 4-8 ).

Table 4-8. RAP Phase VII—Load Test on Six Servers

Test # Date & Time # of Users Test Duration # of Db Servers # of App Servers Description Status

3 — — — — — — SUCCESS

4 12-Aug-xxxx 80 60 minutes 6 6 — SUCCESS

With response times meeting the SLA requirements, the database servers are on the right track; they are scaling
as expected.