Shared pool이 계속 커지는 현상 - 메모리 누수 문제?

 

Oralce DB 메모리 중 Shared pool 영역이 점점 커지는 현상이 발생했다. SGA_TARGET = 0으로 자동 공유 메모리 관리는 꺼져 있는 상태이다.

 

메모리 누수(Memory Leak)

메모리 누수는 메모리가 계속해서 사용되면서 해제되지 않는 상태를 의미한다.

Oracle 데이터베이스에서는 여러 메모리 구성 요소가 있으며, 그 중에서도 Shared Pool은 SQL 및 PL/SQL 문의 파싱, 실행 계획, 프로시저 등을 캐시하는 데 사용된다. 만약 이러한 캐시에서 메모리가 해제되지 않으면 Shared Pool의 크기가 계속해서 늘어날 수 있다.

 

메모리 누수 현상 분석

메모리 누수를 해결하려면 해당 메모리를 사용하는 SQL 쿼리, 프로시저, 트리거 등을 검토하고 최적화해야 한다.

메모리 누수를 확인하려면 다음을 수행할 수 있다.

• V$SGASTAT 뷰를 사용하여 메모리 사용량 모니터링
• AWR 리포트에서 과거 메모리 사용 트렌드 확인
• V$SESSTAT 를 사용하여 각 세션의 메모리 사용 확인

 

>>

위 가이드를 따라 v$sgastat 뷰로 Shared pool을 며칠간 모니터링 한 결과 pga accounting 영역이 점차적으로 늘어나는 것 확인하였다.

 

 

---

pga accounting 영역 증가 현상

PGA Accounting 영역이란?

shared pool의 pga accounting 영역은 사용자 세션에서 수행된 SQL 문의 PGA 메모리 사용에 대한 통계를 저장한다. 이 영역은 PGA 메모리 사용에 대한 통계 정보를 캡처하고 세션 간의 PGA 메모리 사용을 추적하는 데 사용된다. 이 통계 정보는 성능 모니터링 및 튜닝에 활용된다.

 

PGA Accounting 영역의 크기

shared pool의 pga accounting 영역은 보통 세션들이 수행하는 작업에 따라 동적으로 변한다. 즉, 정렬 작업, 해시 조인, 해시 그룹의 수행에 따라 pga accounting 크기가 변한다.

 

PGA Accounting 영역이 계속해서 늘어나는 원인

PGA Accounting 영역이 계속해서 늘어나는 것은 비정상적인 현상이며 가능한 원인은 아래로 추정할 수 있다.

1. PGA 메모리 사용 증가:
   • pga accounting 영역이 증가하는 주된 이유는 세션에서 사용되는 PGA 메모리가 늘어나는 경우입니다. 이는 대량의 데이터를 정렬하거나 해시 조인을 수행하는 쿼리, 또는 다른 메모리 집약적인 작업이 많은 경우 발생할 수 있습니다.
2. 잘못된 쿼리 실행 계획:
   • 쿼리 실행 계획이 예상치 못하게 비효율적이라면, 많은 PGA 메모리를 사용할 수 있습니다. 쿼리 최적화를 통해 실행 계획을 향상시키는 것이 도움이 될 수 있습니다.
3. PGA 메모리 누수:
   • 세션에서 할당한 PGA 메모리를 제대로 해제하지 않는 경우 메모리 누수가 발생할 수 있습니다. 이로 인해 pga accounting 영역이 계속해서 커질 수 있습니다.
4. Bug 또는 버전 문제:
   • Oracle 데이터베이스의 특정 버전이나 패치에서 발생하는 버그로 인해 pga accounting 영역이 비정상적으로 커지는 경우가 있을 수 있습니다. 최신 패치를 적용하거나, Oracle 지원팀과 상담하여 문제 해결을 검토할 수 있습니다.

PGA Accounting 영역이 계속해서 늘어나는 경우, 수행 중인 작업과 관련된 SQL 쿼리 및 프로세스를 확인해보는 것이 중요하다. v$session 및 v$sql 뷰 등을 사용하여 현재 실행 중인 세션 및 SQL 쿼리를 확인하고, AWR 리포트를 생성하여 성능 이슈를 분석할 수 있다.

또한 v$process, v$session, v$sesstat, v$statname 등의 뷰를 적절하게 사용하여 PGA 사용에 대한 통계 정보를 확인할 수 있다.

 

 

PGA를 많이 사용하는 세션 식별 SQL

아래 쿼리는 PGA를 많이 사용하는 세션 순으로 각 세션에 대한 정보를 표시한다. 이 정보를 토대로 어떤 세션이 문제를 일으키고 있는지를 확인하고, 필요한 조치를 취할 수 있다.

SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.MACHINE,
    s.OSUSER,
    s.PROGRAM,
    s.SQL_ID,
    ss.NAME,
    ss.VALUE
FROM
    V$SESSION s
JOIN
    V$SESSTAT ss ON s.SID = ss.SID
JOIN
    V$STATNAME st ON ss.STATISTIC# = st.STATISTIC#
WHERE
    st.NAME LIKE 'session pga memory%'
ORDER BY
    ss.VALUE DESC;
    
    
result >>
+-----+---------+----------+-----------+-------+-----------------------------+----------+-------------------------+-------+
| SID | SERIAL# | USERNAME | MACHINE   | OSUSER| PROGRAM                     | SQL_ID   | NAME                    | VALUE |
+-----+---------+----------+-----------+-------+-----------------------------+----------+-------------------------+-------+
| 123 | 456     | USER1    | CLIENT01  | user1 | application.exe             | abcd1234 | session pga memory max  | 1048576 |
| 123 | 456     | USER1    | CLIENT01  | user1 | application.exe             | abcd1234 | session pga memory used | 524288  |
| 789 | 321     | USER2    | CLIENT02  | user2 | another_application.exe     | efgh5678 | session pga memory max  | 786432  |
| 789 | 321     | USER2    | CLIENT02  | user2 | another_application.exe     | efgh5678 | session pga memory used | 262144  |
+-----+---------+----------+-----------+-------+-----------------------------+----------+-------------------------+-------+