ORA-04031: KTSL subheap, ktsl_load_disp-2

LOB inserts have been occasionally failing on both 19c and 21c databases:

ORA-04031: unable to allocate 52832 bytes of shared memory (shared pool,unknown object,KTSL subheap,ktsl_load_disp-2)

We’ve observed following:

• The affected LOBs were securefiles.
• The affected databases had 32k block size.
• The size of shared pool allocation requests was around 52k.
• The allocation attempts were on KTSL subheap with the comment ktsl_load_disp-2.

Free space

Surprisingly, there was enough free space in the shared pool (our shared pool constists of only one subpool):

==============================================
Memory Utilization of Subpool 1 (SGA HEAP)
==============================================
      Allocation Name                 Size         Max Size   Chunks
___________________________       _____________   __________  ______
"free memory                    "     550418056    588099904  186502

There was enough free space, but there weren’t any free sufficiently large chunks in the duration 1 to fulfill the memory allocation request:

select KSMCHDUR,KSMCHCOM,KSMCHCLS, count(*)
  from X$KSMSP 
  where 
	KSMCHSIZ >= 52832 
	and KSMCHCLS in ('free', 'R-free')
  group by KSMCHDUR,KSMCHCOM,KSMCHCLS ;

  KSMCHDUR KSMCHCOM         KSMCHCLS   COUNT(*)
---------- ---------------- -------- ----------
         4 free memory      free             73
         4 free memory      R-free           21
         1 free memory      R-free           23

Interestingly, reserved shared pool had 23 chunks in the duration 1 that could have satisfied the allocation request, but the allocation rather failed instead of taking a chunk from the reserved shared pool.

Tracing memory allocations with debugger

The Oracle server process allocates ktsl dispenser chunks (“ktsl_load_disp”) when creating a new LOB extent. These allocations can be tracked with the following gdb breakpoint:

b kghalo if $_regex((char *)(*(uint64_t *)($rsp+8+8+8)),"^ktsl_load_disp-2$")

commands 1
printf "kghalo - size: %d  comment: %s \n", $rdx, (char *)(*(uint64_t *)($rsp+8+8+8))
end

A short description of the elements in the breakpoint command above:

• kghalo is one of Oracle C functions that allocate memory.
• kghalo receives the allocation size through the 4th argument. According to x86 calling conventions, the 4th parameter is passed through the CPU register rdx.
• The comment for the chunk, a.k.a allocation reason, is passed on the stack. Consequently, it is dereferenced via the stack pointer rsp.

For a longer explanation on how to trace function arguments with debugger read this .

The test table contains a CLOB column:

create user c##u no authentication default tablespace users;
grant create table to c##u ;
alter user c##u quota unlimited on users ;

create table c##u.t (c clob);

Attach with gdb to the Oracle server process before running insert:

attach PID

Run insert in the debugged session:

insert into c##u.t values ( rpad('a',32767,'a') );

You should get the following output on breakpoint on a database with the 32k block size:

kghalo - size: 52800  comment: ktsl_load_disp-2

Notice that the allocation request size of 52800 bytes is similar to the value we’ve got in the ORA-04031 message.

I’m displaying free chunks larger than 52800 bytes in another SQLPlus session:

  KSMCHDUR KSMCHCOM         KSMCHCLS   COUNT(*)
---------- ---------------- -------- ----------
         4 free memory      R-free            7
         4 free memory      free              1
         1 free memory      R-free           20
         1 free memory      free              2

On kghalo start, we have two free chunks in the duration 1 (excluding the reserved shared pool).

Now go to debugger and continue the execution until exiting kghalo:

fin

One free chunk in the duration 1 is gone after kghalo ended:

  KSMCHDUR KSMCHCOM         KSMCHCLS   COUNT(*)
---------- ---------------- -------- ----------
         4 free memory      R-free            7
         4 free memory      free              1
         1 free memory      R-free           20
         1 free memory      free              1

The same allocation in the database with 8k block size is exactly 4 times smaller (13200 bytes):

kghalo - size: 13200  comment: ktsl_load_disp-2

In conclusion, the allocation size is proportional to the block size. Therefore, databases with larger block sizes are more likely to hit the problem.

Tracing LOB inserts with an undocumented trace event

In the course of this troubleshooting, I discovered the undocumented trace event 45050 for tracing LOB inserts. I set all the flags in the bitmap to get the maximum verbosity:

ALTER SESSION SET EVENTS='45050 trace name context forever, level 4294967295' ;

The trace reveals the reason for increased memory allocation for 32k block size – memory per dispenser partition is proportional to the block size:

grep Memory DB8K_ora_2594545_0002.trc
Memory per disp partition is 150

grep Memory DB32K_ora_2580289_0001.trc
Memory per disp partition is 600

Summary

In summary, LOB insert allocates chunks for LOB dispensers when creating a new extent. The session gets ORA-04031 on failed memory allocation. The memory allocation can fail even when there’s enough free memory in the shared pool – in particular, when there’s a lack of contiguous space large enough to fulfill the allocation request. Since the requested allocation size is proportional to the block size, the error is more likely on databases with larger block sizes.

In most cases, you should use the default 8k block size anyway – it’s mostly used and tested. A non-default block size is a road less travelled and might lead to a problem unknown to Oracle. For example, here’s another side effect with larger block sizes.