Locks On Hot Object With RAC 12.2 X vs.11.2.0.4 Current SQL: select spare6 from user$ where user#=:1
(Doc ID 2564792.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database Appliance Software - Version 12.2.1.1.0 to 12.2.1.4.0 Bare Metal [Release 12.2]Information in this document applies to any platform.
Symptoms
An upgrade of a previously working application no longer works resulting in locking.
Evidence pointed to the following query as occurring at the same time as the Hang in the Application query.
Other Applications or load testing on the same configuration did not reproduce the problem.
The problem query seen in AWR report included:
- current sql: select spare6 from user$ where user#=:1
Other waits reported including
- 'gc cr request'
- 'gc buffer busy acquire'
- 'library cache lock'
or as seen in hang analyze outputs
- 'gc cr request'<='gc buffer busy acquire'<='library cache lock'
Altering the database from RAC ( two node or more cluster) to single-instance avoided the problem all other things being equal.
The application queries, data set were identical in pre-upgrade and after going to single instance from RAC.
Changes
In this case the following changes occurred:
- Change in Hardware. ODA V1 to ODA X
- Change in OS. OL5 to OL 6.7 uek4
- Change in database version. 11.2.0.4 to 12.2.1.2
- Change from single instance to RAC
The upgrade in this case was to a new OS, New HW, RAC from single-instance, new Database version and no testing.
Determining the source of the problem with this many changes was not trivial.
Of these changes ONLY the following is directly related to the problem.
Change in OS.
Originally The change in the database version could not be totally ruled out.
However, the RDBMS version is actually tied to the OS version which happened to be updated at the same time
If the upgrade was only the OS and not the RDBMS then we would not have encountered the problem
Another factor which was significant was from single-instance to RAC (without testing or design considerations).
The reason this was significant is that the problem ONLY reproduced for this configuration if using RAC:
The problem did not occur using Single-Instance.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |