cursor_sharing=FORCE를 영구 설정으로 둬도 되나?

급할 때 버티는 임시 처방으로 권한다. FORCE는 리터럴을 시스템 생성 바인드 변수로 치환해 하드 파싱을 줄여 주지만, 파싱 오버헤드가 붙고 히스토그램 기반 최적화가 흐트러져 일부 쿼리의 실행계획이 나빠질 수 있다. 근본 해결은 애플리케이션이 바인드 변수를 쓰도록 고치고 기본값 EXACT로 되돌리는 것이다.

ALTER SYSTEM FLUSH SHARED POOL을 하면 ORA-04031이 사라지나?

공유풀의 커서를 비워 당장은 숨통이 트인다. 하지만 리터럴 SQL이 계속 들어오면 공유풀이 다시 채워져 재발한다. 플러시 직후에는 모든 SQL이 다시 하드 파싱되므로 CPU가 잠깐 튈 수 있다. 응급 조치일 뿐 해결책이 아니다.

리터럴 SQL을 쓰는 애플리케이션을 어떻게 찾나?

v$sqlarea를 force_matching_signature로 묶어 보면 리터럴만 다른 SQL이 같은 시그니처로 묶인다. 한 시그니처에 SQL이 수십, 수백 개 달려 있으면 그 패턴의 원문을 조회해 어느 모듈이 값을 직접 박아 넣는지 확인한다. v$sysstat의 parse count (hard) 비율로 심각도를 교차 검증한다.

공유풀만 키우면 ORA-04031이 해결되나?

리터럴 남용이 원인이면 메모리를 키워도 시간만 벌 뿐 재발한다. v$sgastat에서 free memory는 바닥인데 sql area와 library cache가 공유풀을 거의 다 먹고 있다면 크기 문제가 아니라 커서 개수 문제다. 바인드 변수 적용이 먼저고 사이징은 보조다.

cursor_sharing의 SIMILAR 값은 왜 안 보이나?

SIMILAR은 과거 버전의 값으로, 현재 지원 버전에서는 제거됐다. 공식 문서 기준 유효한 값은 EXACT(기본값)와 FORCE 둘뿐이다. 옛 문서나 블로그를 보고 SIMILAR을 설정하려 하면 적용되지 않는다.

ORA-04031로 shared pool이 터질 때

운영 중인 Oracle에서 갑자기 세션들이 에러를 토하며 멈추는데, 로그를 열어 보면 ORA-04031이 찍혀 있는 경우가 있다. 제가 현장에서 이 에러를 만날 때 열에 아홉은 메모리를 더 줘서 해결될 문제가 아니라, 애플리케이션이 바인드 변수를 안 쓰고 값을 그대로 박아 넣은 SQL을 쏟아내는 게 원인이었다. 공유풀(shared pool)을 키워 봤자 며칠 더 버티다 또 터진다. 이번 글은 그 뿌리를 어떻게 찾아 막는지를 단계로 정리한다.

결론부터 말하자면, ORA-04031은 공유풀에 연속된 메모리 조각을 할당하지 못할 때 나는 에러이고, 가장 흔한 범인은 리터럴 SQL로 인한 하드 파싱 폭증이다. v$sqlarea에서 리터럴만 다른 쿼리가 수천 개로 번진 흔적을 찾아 확인하고, 급한 불은 cursor_sharing=FORCE로 끄되, 근본 해결은 애플리케이션의 바인드 변수 적용과 적정한 SGA/공유풀 사이징으로 간다.

1. 현상과 진단 범위

ORA-04031은 공유풀에서 요청한 크기의 메모리 덩어리를 확보하지 못했을 때 발생한다. 예를 들어 게시판 조회 화면이 WHERE board_id = 1001, WHERE board_id = 1002처럼 ID만 바꿔 가며 쿼리를 던지면, Oracle은 이들을 전부 다른 SQL로 보고 하나하나 파싱해 공유풀에 적재한다. 하루에 수십만 건이 쌓이면 공유풀이 자잘한 커서로 가득 차고, 결국 새 커서를 올릴 자리가 없어 ORA-04031로 끝날 수 있다.

아래 순서로 본다. 먼저 핵심 용어를 짚고, 에러 메시지를 읽은 뒤, v$sqlarea로 리터럴 SQL을 찾고, 임시 처방과 근본 해결, 마지막으로 공유풀 사이징을 다룬다.

2. 알아 둘 용어

공유풀(shared pool)은 SGA 안에서 파싱된 SQL과 실행계획, 데이터 딕셔너리 정보를 캐싱하는 영역이다. 같은 SQL을 다시 만나면 재파싱 없이 캐시된 커서를 재사용해 CPU와 래치 경합을 줄인다. 이 캐시가 있는 하위 영역을 라이브러리 캐시(library cache)라고 부른다.

하드 파싱(hard parse)은 캐시에 없는 새 SQL을 만나 구문 분석부터 최적화까지 전 과정을 수행하는 비싼 작업이다. 반대로 캐시된 커서를 재사용하면 소프트 파싱(soft parse)이다. 리터럴 SQL은 텍스트가 매번 달라 캐시에 걸리지 않으니 하드 파싱이 폭증한다.

바인드 변수(bind variable)는 SQL 본문에 값을 직접 쓰는 대신 :1, :id 같은 자리표시자를 두고 실행 시점에 값을 넘기는 방식이다. 이러면 텍스트가 고정돼 하나의 커서를 재사용한다.

3. 에러 메시지부터 읽는다

alert 로그나 세션 에러에 찍힌 메시지를 먼저 본다.

ORA-04031: unable to allocate 4160 bytes of shared memory
("shared pool","SELECT ...","SQLA","kglsim heap")

따옴표 안 첫 항목이 shared pool이면 공유풀 문제다(가끔 large pool, java pool로 찍히면 그쪽을 본다). 세 번째, 네 번째 항목은 어떤 하위 구조에서 막혔는지를 가리킨다. SQLA나 kgl 계열이 보이면 라이브러리 캐시, 즉 SQL 커서 쪽이 꽉 찼다는 신호다.

공유풀 내부가 어떻게 쓰이는지는 v$sgastat으로 본다.

SQL> SELECT pool, name, bytes
  2  FROM v$sgastat
  3  WHERE pool = 'shared pool'
  4  ORDER BY bytes DESC
  5  FETCH FIRST 8 ROWS ONLY;

POOL         NAME                         BYTES
------------ ----------------------- ----------
shared pool  sql area                 268435456
shared pool  library cache             94371840
shared pool  free memory               12582912
shared pool  kglsim object batch       20971520
shared pool  KGLH0                     41943040
...

sql area와 library cache가 공유풀 대부분을 먹고 free memory가 바닥을 긁고 있으면, 메모리가 작아서가 아니라 커서가 너무 많아서 터지는 패턴이다.

4. v$sqlarea로 리터럴 SQL을 찾는다

핵심 진단이다. v$sqlarea에는 공유풀에 올라온 SQL별 통계가 들어 있고, force_matching_signature 컬럼은 리터럴만 다른 SQL에 같은 값을 매긴다. 이 값으로 묶었을 때 한 시그니처에 수십, 수백 개의 SQL이 달려 있으면 리터럴 남용의 확증이다.

SQL> SELECT force_matching_signature,
  2         COUNT(*)        AS variants,
  3         SUM(executions) AS execs
  4  FROM   v$sqlarea
  5  WHERE  force_matching_signature <> 0
  6  GROUP  BY force_matching_signature
  7  HAVING COUNT(*) > 50
  8  ORDER  BY variants DESC
  9  FETCH FIRST 10 ROWS ONLY;

FORCE_MATCHING_SIGNATURE   VARIANTS      EXECS
------------------------ ---------- ----------
     14923847610293847562       3184       3184
      9381726354019283746       2271       2290
...

VARIANTS가 수천인데 EXECS가 거의 같다면, 사실상 매 실행마다 새 커서를 만들고 버린다는 뜻이다. 하드 파싱이 그만큼 돌았다고 보면 된다. 어떤 SQL인지 한 시그니처를 골라 원문을 확인한다.

SQL> SELECT sql_text
  2  FROM   v$sqlarea
  3  WHERE  force_matching_signature = 14923847610293847562
  4  FETCH FIRST 3 ROWS ONLY;

SQL_TEXT
------------------------------------------------------
SELECT * FROM orders WHERE customer_id = 48213
SELECT * FROM orders WHERE customer_id = 48214
SELECT * FROM orders WHERE customer_id = 48377

값만 다르고 구조가 같은 SQL이 줄줄이 나오면 범인을 잡은 것이다. 하드 파싱 비율 자체는 v$sysstat으로 교차 확인한다.

SQL> SELECT name, value FROM v$sysstat
  2  WHERE name IN ('parse count (total)','parse count (hard)');

NAME                           VALUE
------------------------- ----------
parse count (total)         48213765
parse count (hard)          11920438

하드 파싱이 전체 파싱에서 차지하는 비율이 높을수록 리터럴 문제가 심하다. 건강한 OLTP라면 하드 파싱은 한 자릿수 퍼센트에 머무는 편이고, 이게 수십 퍼센트로 올라가면 경고다.

5. 급한 불 끄기

지금 당장 운영이 멈춰 있다면 공유풀을 비워 숨통을 틔운다. 커서가 사라지니 직후 하드 파싱이 몰려 CPU가 잠깐 튈 수 있다는 점은 감안한다.

SQL> ALTER SYSTEM FLUSH SHARED POOL;

System altered.

이건 근본 해결이 아니라 재발한다. 코드를 바로 못 고치는 상황이면 cursor_sharing을 FORCE로 돌려 Oracle이 리터럴을 시스템 생성 바인드 변수로 치환하게 한다. 공식 문서 기준 이 파라미터의 기본값은 EXACT이고, 유효 값은 EXACT와 FORCE 둘뿐이다(과거의 SIMILAR은 제거됐다).

SQL> SHOW PARAMETER cursor_sharing

NAME              TYPE    VALUE
----------------- ------- ------
cursor_sharing    string  EXACT

SQL> ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

System altered.

전체가 부담되면 특정 세션이나 특정 모듈에만 적용하는 방법도 있다.

SQL> ALTER SESSION SET cursor_sharing = FORCE;

다만 FORCE는 공짜가 아니다. 파싱마다 치환 오버헤드가 붙고, 리터럴에 의존하던 히스토그램 기반 최적화가 흐트러져 특정 쿼리의 실행계획이 나빠질 수 있다. 그래서 FORCE는 애플리케이션이 바인드 변수를 제대로 쓸 때까지 버티는 다리로만 쓰고, 적용 후에는 주요 쿼리의 실행계획이 바뀌지 않았는지 확인한다.

6. 근본은 바인드 변수

제대로 고치는 길은 애플리케이션이 값을 바인드 변수로 넘기게 하는 것이다. JDBC라면 Statement에 문자열을 이어 붙이는 대신 PreparedStatement에 자리표시자를 쓴다.

// 하드 파싱을 유발하는 코드
String sql = "SELECT * FROM orders WHERE customer_id = " + id;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

// 커서를 재사용하는 코드
String sql = "SELECT * FROM orders WHERE customer_id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();

MyBatis라면 #{param}이 바인드 변수로 나가고 ${param}은 문자열 치환이라 리터럴이 된다. 동적 컬럼명처럼 꼭 필요한 곳이 아니면 ${}를 피한다. PL/SQL은 정적 SQL이면 자동으로 바인딩되지만, EXECUTE IMMEDIATE로 문자열을 조립할 때는 USING 절로 값을 넘겨야 바인딩된다.

바인드 변수로 바꾼 뒤 4절의 force_matching_signature 쿼리를 다시 돌려 보면, 같은 시그니처의 variants 수가 수천에서 한두 개로 줄어든 걸 확인할 수 있다.

7. 공유풀과 SGA 사이징

리터럴 문제를 잡은 다음에도 공유풀이 빠듯하면 사이징을 손본다. 요즘 운영에서는 자동 공유 메모리 관리(ASMM)를 쓰는 경우가 많고, 이때 sga_target이 전체 SGA를 자동 배분한다. 현재 설정을 먼저 본다.

SQL> SHOW PARAMETER sga_target
SQL> SHOW PARAMETER shared_pool_size
SQL> SHOW PARAMETER memory_target

ASMM(즉 sga_target > 0, memory_target = 0)에서는 shared_pool_size가 공유풀의 하한선으로 작동한다. 공유풀이 자꾸 쪼그라들어 ORA-04031이 난다면, 상한인 sga_target을 늘리거나 공유풀 하한을 명시해 최소 크기를 보장한다.

SQL> ALTER SYSTEM SET shared_pool_size = 1G SCOPE = BOTH;

System altered.

참고로 memory_target 하나로 SGA와 PGA를 함께 맡기는 자동 메모리 관리(AMM)도 있지만, 운영 DB에서는 SGA와 PGA를 분리해 다루기 쉬운 ASMM을 권한다. 적정 공유풀 크기가 궁금하면 v$shared_pool_advice로 크기별 파싱 비용 추정을 참고한다.

SQL> SELECT shared_pool_size_for_estimate AS mb,
  2         estd_lc_time_saved_factor     AS factor
  3  FROM   v$shared_pool_advice
  4  ORDER  BY mb;

크기를 키워도 factor가 더 안 좋아지지 않는 지점이 있다. 그 이상은 메모리만 낭비하니, 조언 뷰가 평평해지는 구간 근처에서 공유풀을 잡으면 된다. 메모리 증설은 어디까지나 보조 수단이고, 리터럴을 줄이지 않으면 아무리 키워도 시간만 벌 뿐이다.

8. 정리

ORA-04031은 메모리를 더 달라는 신호가 아니라, 대개 바인드 변수를 안 써서 공유풀이 쓰레기 커서로 가득 찼다는 신호다. v$sqlarea의 force_matching_signature로 리터럴 SQL을 특정하고, 급하면 cursor_sharing=FORCE로 버티되, 애플리케이션의 바인드 변수 적용으로 끝을 낸다. 공유풀 사이징은 그다음 미세 조정이다. 진단과 조치 뒤에는 하드 파싱 비율과 variants 수가 실제로 떨어졌는지 반드시 재확인한다.