I did. On a customers database we saw massive waits for “enq: CF – contention”. The sessions waiting for that event where all executing simple INSERT statements. So why do they wait for controlfile operations?
My Oracle Support Note 1380939.1 pointed me to the root cause. The INSERTs are doing NOLOGGING operations. And hence the database updates the UNCRECOVERABLE_TIME and UNRECOVERABLE_SCN information inside the controlfile over and over again. This serialization leads to massive performance degradation.
But who is doing NOLOGGING operations? That is bad in terms of recoverability and in our special case really really bad from a performance perspective. We started investigating this. The database runs in NO FORCE LOGGING mode and all tablespaces are LOGGING too:
SQL> select force_logging from v$database; FOR --- NO SQL> select distinct logging from dba_tablespaces where contents='PERMANENT'; LOGGING --------- LOGGING
Let’s check the logging attribute of the table:
SQL> select logging from dba_tables where table_name='TABLE_IN_QUESTION'; LOG --- SQL> select partitioned,logging from dba_tables where table_name='TABLE_IN_QUESTION'; PAR LOG --- --- YES
Obviously the table is partitioned so I needed to check the partitions itself:
SQL> select distinct logging from dba_tab_partitions where table_name='TABLE_IN_QUESTION'; LOGGING ------- YES
So why are there nologging operations? The logging attribute is for segments, so I checked for LOB columns in the table.
SQL> select column_name, logging, partitioned from dba_lobs where table_name='TABLE_IN_QUESTION'; COLUMN_NAME LOGGING PAR -------------------- ------- --- VALUE NONE YES SQL> select distinct column_name, logging from dba_lob_partitions where table_name='TABLE_IN_QUESTION'; COLUMN_NAME LOGGING -------------------- ------- VALUE NO
That’s it. The LOB partitions are defined to do NOLOGGING operations. But we were not able to change that:
SQL> alter table TABLE_IN_QUESTION modify lob (value) (nocache logging); alter table TABLE_IN_QUESTION modify lob (value) (nocache logging) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
And of cause the application owner, who was complaining most about bad performance by the way, did not commit to switch the data loading off for a short period of time. You know, availability, etc bla bla… So we just enabled the FORCE LOGGING mode for the database which immediately removed the “enq: CF – contention”.
SQL> alter database force logging; Database altered.
That is what Cloud Control looked like…
But we need to keep in mind that some time when we are allowed to, we must change the LOB parameters.