Waits tied to database configuration

Waits in the Configuration class come from a limit of the instance or of the object: small redo logs, buffer cache, sequence cache, transaction space in the block. The event tells which limit was reached, and the fix is usually configuration, not SQL.

Symptoms

In the Diagnosis, the finding Waits tied to database configuration (CONFIGURACAO) appears. The text says how much of the total time went to the Configuration wait class, as a percentage and in seconds, lists the events of the class that weighed the most, recalls what to review (redo logs, buffer cache, sequence cache) and ends with View profile by class.

The finding appears when the Configuration class weighs on the total time. The severity is always medium, whatever the percentage. An event that the report does not classify appears in the profile as Unclassified and does not count here.

Cause

Each event of the class points to a different limit. The redo log switch waits stop every session that generates redo, not just the traced one.

Resolution

Find the event that the finding lists first in the cause table. The cases go from the most to the least common.

Small redo logs or few groups

Look at the size and status of the groups, and how many switches there were per hour in the last 24 hours:

select thread#, group#, bytes / 1024 / 1024 as mb, members, status, archived
  from v$log
 order by thread#, group#;

select trunc(first_time, 'HH24') as hora, thread#, count(*) as trocas
  from v$log_history
 where first_time > sysdate - 1
 group by trunc(first_time, 'HH24'), thread#
 order by hora, thread#;

Many switches per hour call for larger redo logs; checkpoint incomplete also calls for more groups. Create the new groups and drop the small ones when they become INACTIVE, as the Oracle documentation on the redo log describes. With archiving needed, check the archiving destination before changing the groups.

Sequence and transaction space in the block

For enq: SQ - contention, increase the sequence cache; in RAC, also prefer noorder:

alter sequence exemplo.pedido_seq cache 1000;

For enq: TX - allocate ITL entry, increase the INITRANS of the object that appears in Objects with the most wait time. The new value applies to new blocks; existing blocks only change when the table is moved (alter table ... move; without update indexes, the indexes become UNUSABLE and must be rebuilt) or the index is rebuilt:

alter table exemplo.pedido initrans 10;
alter index exemplo.pedido_pk rebuild initrans 10;

Buffer cache, DBWR and log buffer

free buffer waits and write complete waits point to DBWR, which does not write the changed blocks fast enough: check the writes to storage and the buffer cache size. log buffer space points to a small log buffer or a slow LGWR facing a lot of redo. These are instance settings: take the profile and the trace Period to the DBA.

Space and undo

• enq: HW - contention: several sessions inserting into a growing segment. Allocating extents before the load helps (alter table exemplo.pedido allocate extent;).
• undo segment extension: undo grows during the operation. Check the size and autoextend of the undo tablespace.
• enq: ST - contention: appears with a dictionary-managed tablespace. Prefer locally managed tablespaces.

How to confirm

These events depend on the load of the whole instance, such as the redo that all sessions generate. Measure again at the same time of day and under a similar load.

1. Apply the setting for the event that weighed.
2. Turn on tracing for the same operation with waits (turn on tracing with DBMS_MONITOR).
3. Upload the new file and open the report.
4. Compare the Configuration row in By wait class and the event's row in the profile.

Result: the Configuration class no longer weighs on the total time and the finding disappears from the Diagnosis. In the profile, the adjusted event drops in Occurrences or in Avg (s).

In the report

The sample report has no Configuration class waits; the links open the same sections in it.

• Diagnosis: the finding, with the View profile by class link.
• By wait class: the Configuration row, with the events in Top components. The class card opens when you click its name (wait classes).
• Response time profile: each event, with Occurrences and Avg (s). The card of each event says when it is normal and when it is a concern (profile).
• Objects with the most wait time: the table or index of enq: TX - allocate ITL entry and of enq: HW - contention (objects).
• Top statements by component and by class: the statements that waited, such as those that generate redo or use the sequence (statements by component).
• Summary: the trace Period, to cross with the time of the redo log switches in v$log_history (summary).