Skip to main content

Search

Welcome to Tibero GTS!

How to Resolve OUT_OF_SHP Error

Document Type | Troubleshooting

Category | Monitoring/Inspection

Document Number | TMOTS028

 

Issue

The query execution fails with messages like the following in the trace.log file.
  • The log shows an attempt to allocate 1.7MB, but the execution fails or an error occurs due to insufficient consecutive 1.7MB of Shared Pool (hereafter SHP) space.
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

 

Cause

A large number of literal queries repeatedly occupy and release memory for ppc allocations in the Shared Pool (hereafter SHP), causing memory fragmentation. Also, even if the ALTER SYSTEM FLUSH PPC command is applied, if the life cycle of the PP to be released has not ended, the memory occupation is not released, which can lead to an OUT_OF_SHP error.
 
 

Solutions

1. Create Shell Script

#!/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"

Note

Adjust the max_m variable unit for convenience as needed.

 

2. Perform SHARED DUMP

SQL> ALTER SYSTEM DUMP SHARED POOL;
System altered.
 

3. Confirm DUMP Occurrence

Default dump location: $TB_HOME/instance/$TB_SID/dump/tracedump or the location specified by the TRACE_DUMP_DEST parameter
 

4. Run 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

 

5. Check Results

Based on the shell script results, the largest consecutive available SHP space is 1MB, indicating that fragmentation has occurred.

 

 

 

Related to