Issue

In environments with a large number of literal queries, an OUT_OF_SHP error may occur, causing query execution to fail.

trace.log records messages like the following.

THROW. ec=ERROR_OUT_OF_SHP(-3002)
       [ Out of memory (unable to allocate 1874648 bytes of shared pool memory). ]
       (csr_id:720599)
       [shp_alloc.c:538:shp_malloc_dbginfo]

The log shows an attempt to allocate approximately 1.7MB, but the allocation fails because there is no contiguous 1.7MB SHARED POOL space available, resulting in an error.

Cause

A large number of literal queries cause many PPC allocations in the SHARED POOL and repeatedly occupy and release memory, leading to memory fragmentation.

Even if the ALTER SYSTEM FLUSH PPC; command is executed, if the life cycle of the PPC to be released has not ended, the memory occupation will not be freed.

As a result, although there may be sufficient total free space in the SHARED POOL, it may not be possible to secure a contiguous memory space of the required size, which can cause the OUT_OF_SHP error.

Solutions

You can check for fragmentation of the SHARED POOL by following the steps below.

1. Create a Shell Script

You can adjust the unit of the max_m variable for convenience.

#!/bin/bash
if [ $# != 2 ]; then
    echo "usage: max_chunk.sh [CHUNK] [DUMP]"
    exit
fi

max=0
for i in `grep $1 $2 | awk '{print $2}'`
do
    if [[ "$i" -gt "$max" ]]; then
        max="$i"
    fi
done

max_m=`expr $max / 1024 / 1024`
echo "the largest $1 chunk size in $2 is $max_m Mbytes"

2. Perform SHARED POOL Dump

ALTER SYSTEM DUMP SHARED POOL;

3. Check the Dump File

By default, the dump file is created in the following location.

$TB_HOME/instance/$TB_SID/dump/tracedump

Alternatively, it is created in the location specified by the TRACE_DUMP_DEST parameter.

4. Run the Shell Script

sh max_chunk.sh FREE tb_dump_3256_38_1404859.trc

the largest FREE chunk size in tb_dump_3256_38_1404859.trc is 1 Mbytes

If the shell script result shows that the largest contiguous available SHARED POOL space is 1MB, it can be inferred that memory fragmentation has occurred.

Use Case

At a site where OUT_OF_SHP errors occur once every 2 to 3 weeks, a method was sought to predict fragmentation before the error occurs and perform a planned database restart. There is a case where fragmentation was predicted using this shell script monitoring method.