Two-node 19c RAC on Linux
We have two-node RAC, however the application vendor recommended to avoid using RAC in active-active mode. So we use it in active-passive:
There are 3 schemas/users: U1, U2, and U3
The are 3 services: S1, S2, and S3
Normally S1 runs on node#1, U1 connects to S1
S2 and S3 run on node#2, U2 connects to S2, U3 connects to S3
However last week all 3 services were running on Node#2.
Once a day U1 runs large INSERT AS SELECT, it takes 1 hour 30 min and consumes approx 250 GB out of 800 GB temporary tablespace TEMP.
When this INSERT started running we saw around 200 sessions waiting on enq: SS - contention, most were from U1 and didn't have SQL_ID in V$SESSION. Final blocking session corresponded to DBW0. We created new temp tablespace TEMP2 and re-assigned users to use it instead of TEMP. This reduced the waits but didn't eliminate them completely. Then we moved the services to node#1 and restarted the instance on node#2, this finally fixed the issue.
MOS 2601825.1 explains SS waits as a Race condition between SMON and the foreground processes, why does it occur only in RAC? We have many standalone instances, they don't have SS contention waits. We'll create dedicated temp tablespaces for each user, not sure whether it will help.