행위

오라클 메모리

DB CAFE

thumb_up 추천메뉴 바로가기

1 오라클 메모리 확인[편집]

1.1 DB 메모리 (연결된 프로세스가 사용중인 추정치)[편집]

  • SGA + PGA = EST MEMORY REQUIREMENT FOR CURRENT CONNECTIONS

1) SGA 

SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" 
  FROM v$sga;

2) PGA 

select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)" 
  from v$process;

2-1) PGA 상세 

SELECT spid, program
     , pga_max_mem      max
     , pga_alloc_mem    alloc
     , pga_used_mem     used
     , pga_freeable_mem free
FROM V$PROCESS;

1.2 미연결된 세션 요청 메모리 추정치[편집]

아래 쿼리는 사용자 세션에 의해 할당 된 최대 메모리 입니다. 이를 사용하여 연결되지 않은 프로세스의 남은 메모리 요구 사항을 계산합니다. 

select max(p.pga_max_mem)/1024/1024  PGA_MAX_USER_SESS -- " PGA MAX MEMORY OF USER SESSION(MB)"
  from v$process p
     , v$session s
 where P.ADDR = S.paddr 
   and s.username is not null;

1.3 메모리 변경 후 작업 절차[편집]

1) 메모리 변경 -- 메모리 자동 관리하도록 확인/변경 

SQL> show parameter target;

ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;

2) DB 재시작 

SQL> shutdown immediate;
SQL> startup;

2 사용자별 사용하는 메모리[편집]

select sess.username  as username
      ,sess.sid       as session_id
      ,sess.serial#   as session_serial
      ,sess.program   as session_program
      ,sess.server    as session_mode
      ,round(stat.value/1024/1024, 2) as "current_UGA_memory (in MB)"
  from v$session    sess
      ,v$sesstat    stat
      ,v$statname   name
  where sess.sid        = stat.sid
    and stat.statistic# = name.statistic#
    and name.name       = 'session uga memory'
    -- and sess.username   = 'MY_USERNAME' -- your user/schema name
    -- and stat.value      >= 10485760   -- (All Session Usage > 10MB)
order by   value;

2.1 사용자별 사용하는 메모리 상세 조회[편집]

SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory')        session_pga_memory
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max')    session_pga_memory_max
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory')        session_uga_memory
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max')    session_uga_memory_max
FROM
    v$session  s
WHERE s.username = 'MY_USERNAME' -- your user/schema name
ORDER BY session_pga_memory DESC
;

3 현재 사용중인 메모리[편집]

select
    to_char(ssn.sid, '9999')                             as session_id,
    ssn.serial#                                          as session_serial,
    nvl(ssn.username, nvl(bgp.name, 'background'))
    || '::'
    || nvl(lower(ssn.machine), ins.host_name)            as process_name,
    to_char(prc.spid, '999999999')                       as pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') as current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') as maximum_size_mb
from
    v$statname    stat1,
    v$statname    stat2,
    v$session     ssn,
    v$sesstat     se1,
    v$sesstat     se2,
    v$bgprocess   bgp,
    v$process     prc,
    v$instance    ins
where
    stat1.name         = 'session pga memory'
    and stat2.name     = 'session pga memory max'
    and se1.sid        = ssn.sid
    and se2.sid        = ssn.sid
    and se2.statistic# = stat2.statistic#
    and se1.statistic# = stat1.statistic#
    and ssn.paddr      = bgp.paddr (+)
    and ssn.paddr      = prc.addr  (+)
    and ssn.sid in (
        select sid
        from v$session
        where username = 'MY_USERNAME' -- your user/schema name
    )
order by
    maximum_size_mb
    ;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_메모리&oldid=5878"