Contention on internal database structures

Latches, mutexes and buffers protect structures in database memory: while one session works on one of them, the others that need it wait their turn. The cause is usually too much parsing, a hot block or an object changed while in use.

Symptoms

In the Diagnosis, the finding Contention on internal database structures (CONCORRENCIA) appears. The text says how much of the total time went to the Concurrency wait class, as a percentage and in seconds, lists the events of the class that weighed the most and ends with View profile by class.

The finding appears when the Concurrency class weighs on the total time. The severity is medium and becomes high when the class weighs more. These events belong to the class, by the Oracle 19c wait_class:

read by other session belongs to User I/O and latch free to Other: they do not count here. An event that the report does not classify appears in the profile as Unclassified.

Cause

The traced session contended for a structure with other sessions running at the same time. The trace shows only this session's wait; whoever contended with it is not in it. Common causes, from the most to the least frequent:

• Too much parsing: SQL with literals instead of bind variables, or a cursor opened and closed on every execution. It weighs on the shared pool and library cache latches and on the cursor mutexes.
• Hot block from logical reads: many sessions reading the same blocks, usually through a plan that rereads the same blocks (latch: cache buffers chains).
• Concurrent inserts into the same block: an index with an increasing key (sequence, date) receiving inserts from several sessions (buffer busy waits, enq: TX - index contention).
• Sequence without cache or with a small cache (row cache lock).
• DDL, compilation or statistics gathering on an object in use, and cursors invalidated often (library cache lock, library cache pin, cursor: pin S wait on X).
• Parallel query starting the parallel execution processes (os thread startup).

Resolution

Start with the event the finding lists first. The cases go from the most to the least common.

Too much parsing

Check the PARSE row in Call types: Misses close to Count means a hard parse on almost every PARSE. If the Diagnosis also shows one of these findings, start with it:

• Many hard parses.
• Literals instead of bind variables.
• One PARSE per execution.

Hot block from logical reads

With latch: cache buffers chains, reduce the logical reads of the statements that waited. Find these statements in the Concurrency group of Top statements by component and by class and, in the plan of each one, the operation with high Self LIO (operation that dominates the plan). The typical case is a NESTED LOOPS that rereads the same blocks thousands of times.

Concurrent inserts into the same block

In Objects with the most wait time, find the index or table of the waits. For an index with an increasing key, the usual ways out are a reverse key index and a global hash-partitioned index:

alter index exemplo.pedido_pk rebuild reverse;

create index exemplo.pedido_data_ix on exemplo.pedido (data_pedido)
  global partition by hash (data_pedido) partitions 16;

A reverse key index no longer serves range searches on the key (between, >), and the partitioned index requires the Partitioning option. For a table with buffer busy waits on insert, check whether the segment is in a tablespace with automatic segment space management (ASSM).

Sequence without cache

For row cache lock in statements that use NEXTVAL, look at the cache of the schema's sequences and increase it for the most used ones:

select sequence_owner, sequence_name, cache_size, order_flag
  from dba_sequences
 where sequence_owner = 'EXEMPLO'
 order by cache_size;

alter sequence exemplo.pedido_seq cache 1000;

With or without cache, a sequence does not guarantee numbers without gaps: rollback also skips numbers. The cache only makes the gap larger when the instance goes down or the sequence is aged out of the shared pool.

Object changed while in use

DDL, package compilation and statistics gathering invalidate the cursors that use the object, and the sessions stop to parse again. Move these tasks outside business hours. If the statement has too many cursor versions, v$sql_shared_cursor gives the reason for each version:

select sql_id, version_count, executions, parse_calls
  from v$sqlarea
 order by version_count desc
 fetch first 20 rows only;

Parallel query processes

os thread startup is the time to start processes, usually the parallel execution ones. Check the degree of parallelism of the statements that waited: in a short statement executed many times, parallelism costs more than it gains.

How to confirm

1. Apply one fix at a time.
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 Concurrency row in By wait class and the event rows in the profile.

Result: the Concurrency class no longer weighs on the total time and the finding disappears from the Diagnosis. When the cause was parsing, the Misses column of the PARSE row in Call types also drops.

In the report

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

• Diagnosis: the finding, with the View profile by class link.
• By wait class: the Concurrency row, with the events in Top components (wait classes).
• Response time profile: each event, with Occurrences and Avg (s). Click the event name to open the card with the typical cause and where to look (profile).
• Top statements by component and by class: the statements that contended (statements by component).
• Call types: Count and Misses of the PARSE row (call types).
• Objects with the most wait time: the contended index or table (objects).
• Shareable texts: statements that differ only in literals. The section appears only when the trace has such statements (shareable texts).