Hello, my oracle listeners :-),
This is an Oracle database RAC 12.2.0.1.201020 (31741641, DBRU) with two nodes, the issue had happened on Oct 14th, 2024 18:00 - 20:00, firstly aplication person found out the business has been stuck, exactly, from 18:13:25, database has generated a huge amount of "gc buffer busy acquire" on node 2, from 18:14:45, too much “enq: TX – index contention” and “gc current request” on node 1.
Afterwards, DBA killed all of the application sessions (no enough time to find the real blocker) connected to the database, the issues has disappeared. Nevertheless the next day (Oct 15th) during the same period of 18:00 - 20:00 we also captured the AWR then discovered that it hasn't been occurred the same problem again, so a weird phenomenon!
By the way when the issue has been happend oracle database's OS workload pressure is very slow and the CPU resource is enough, sessions has more than 200.
The blocker is doing an insert operation, this table has a column that has been concatenating a portion by another table's column, in other words, the business logic (using oracle PL/SQL procedure) is the similar to oracle sequence.
DBA told us the table is a partition table with 13 number of indexes but these indexes are all global (not local).
The following are AWR reports in 18:00 - 19:00 and 19:00 - 20:00 with two nodes.
AWR_1_59679_59680.pdf
AWR_1_59680_59681.pdf
AWR_2_59679_59680.pdf
AWR_2_59680_59681.pdf
Very appreciated you if any better suggestion!
Best Regards
Quanwen Zhao