현상

다량의 리터럴 쿼리 수행 환경에서 OUT_OF_SHP 오류가 발생하며 쿼리 수행에 실패할 수 있습니다.

trace.log에는 아래와 같은 메시지가 기록됩니다.

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]

로그상 약 1.7MB를 할당받으려고 시도하지만, 연속된 1.7MB의 SHARED POOL 공간이 없어 메모리 할당에 실패하여 오류가 발생합니다.

원인

다량의 리터럴 쿼리는 SHARED POOL에 다량의 PPC 할당과 메모리 점유·해지를 반복하며 메모리 단편화를 유발합니다.

ALTER SYSTEM FLUSH PPC; 명령을 수행하더라도 해지하려는 PPC의 Life Cycle이 종료되지 않은 경우에는 메모리 점유가 해제되지 않습니다.

이로 인해 SHARED POOL의 전체 여유 공간은 존재하더라도 필요한 크기의 연속된 메모리 공간을 확보하지 못하여 OUT_OF_SHP 오류가 발생할 수 있습니다.

해결

다음 절차를 통해 SHARED POOL의 단편화 여부를 확인할 수 있습니다.

1. 쉘 스크립트 작성

편의에 따라 max_m 변수의 단위를 조정하여 사용합니다.

#!/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. SHARED POOL Dump 수행

ALTER SYSTEM DUMP SHARED POOL;

3. Dump 파일 확인

Dump 파일은 기본적으로 다음 위치에 생성됩니다.

$TB_HOME/instance/$TB_SID/dump/tracedump

또는 TRACE_DUMP_DEST 파라미터에 지정된 위치에 생성됩니다.

4. 쉘 스크립트 실행

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

쉘 스크립트 수행 결과 사용 가능한 SHARED POOL의 연속된 공간이 1MB인 경우, 메모리 단편화가 발생한 것으로 추정할 수 있습니다.

사용 사례

2~3주에 1회 OUT_OF_SHP가 발생하는 사이트에서 에러 발생 전에 단편화를 예측하여 DB 재기동 PM을 수행하는 방안을 모색하였으며, 해당 쉘 스크립트를 이용한 모니터링 방법으로 단편화를 예측한 사례가 있습니다.