행위

"오라클 메모리"의 두 판 사이의 차이

DB CAFE

(새 문서: == 오라클 메모리 확인 == === DB 메모리 (연결된 프로세스가 사용중인 추정치) === # SGA + PGA = EST MEMORY REQUIREMENT FOR CURRENT CONNECTIONS # SGA <source lan...)
 
 
(같은 사용자의 중간 판 10개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 
== 오라클 메모리 확인 ==
 
== 오라클 메모리 확인 ==
 
=== DB 메모리 (연결된 프로세스가 사용중인 추정치) ===
 
=== DB 메모리 (연결된 프로세스가 사용중인 추정치) ===
−
# SGA + PGA = EST MEMORY REQUIREMENT FOR CURRENT CONNECTIONS
+
* SGA + PGA = EST MEMORY REQUIREMENT FOR CURRENT CONNECTIONS
−
# SGA
+
1) SGA
 
<source lang=sql>
 
<source lang=sql>
 
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)"  
 
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)"  
 
   FROM v$sga;
 
   FROM v$sga;
 
</source>
 
</source>
−
# PGA
+
2) PGA
 
<source lang=sql>
 
<source lang=sql>
 
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)"  
 
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)"  
 
   from v$process;
 
   from v$process;
 
</source>
 
</source>
−
# PGA 상세  
+
2-1) PGA 상세  
 
<source lang=sql>
 
<source lang=sql>
 
SELECT spid, program
 
SELECT spid, program
22번째 줄: 22번째 줄:
 
</source>
 
</source>
  
−
=== DB 메모리 + 미연결된 세션 요청 추정치 ===  
+
=== 미연결된 세션 요청 메모리 추정치 ===  
  
 
아래 쿼리는 사용자 세션에 의해 할당 된 최대 메모리 입니다.
 
아래 쿼리는 사용자 세션에 의해 할당 된 최대 메모리 입니다.
31번째 줄: 31번째 줄:
 
   from v$process p
 
   from v$process p
 
     , v$session s
 
     , v$session s
−
  where P.ADDR = S.paddr and s.username is not null;
+
  where P.ADDR = S.paddr  
 +
  and s.username is not null;
 
</source>
 
</source>
  
 +
=== 메모리 변경 후 작업 절차 ===
 +
1) 메모리 변경
 +
-- 메모리 자동 관리하도록 확인/변경
 +
<source lang=sql>
 +
SQL> show parameter target;
  
−
Get the maximum number of processes that an Oracle instance can handle.
+
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
 +
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
 +
</source>
 +
2) DB 재시작
 +
<source lang=sql>
 +
SQL> shutdown immediate;
 +
SQL> startup;
 +
</source>
  
 +
== 사용자별 사용하는 메모리 ==
 +
<source lang=sql>
 +
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;
  
 +
</source>
  
−
select name, value from v$parameter
+
=== 사용자별 사용하는 메모리 상세 조회 ===
 +
<source lang=sql>
 +
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
 +
;
 +
</source>
  
−
where name = 'processes';
+
== 현재 사용중인 메모리 ==
−
 
+
<source lang=sql>
−
 
+
select
−
 
+
    to_char(ssn.sid, '9999')                            as session_id,
−
Let's count the number of connected sessions:
+
    ssn.serial#                                          as session_serial,
−
 
+
    nvl(ssn.username, nvl(bgp.name, 'background'))
−
 
+
    || '::'
−
 
+
    || nvl(lower(ssn.machine), ins.host_name)            as process_name,
−
select count(1) from v$session
+
    to_char(prc.spid, '999999999')                      as pid_thread,
−
 
+
    to_char((se1.value / 1024) / 1024, '999g999g990d00') as current_size_mb,
−
where username is not null;
+
    to_char((se2.value / 1024) / 1024, '999g999g990d00') as maximum_size_mb
−
 
+
from
−
 
+
    v$statname    stat1,
−
 
+
    v$statname    stat2,
−
Get the total available connections by subtracting the connected sessions count from the processes parameter value.
+
    v$session     ssn,
−
 
+
    v$sesstat    se1,
−
The resulting value shall be multiplied by the resulting max allocated memory by a session done by the previous query.
+
    v$sesstat    se2,
−
 
+
    v$bgprocess  bgp,
−
This would then give you the estimated amount of reserve memory needed to accommodate additional connections.
+
    v$process    prc,
−
 
+
    v$instance    ins
−
 
+
where
−
 
+
    stat1.name        = 'session pga memory'
−
 
+
    and stat2.name    = 'session pga memory max'
−
 
+
    and se1.sid        = ssn.sid
−
ESTIMATED TOTAL MEMORY REQUIREMENT
+
    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 (
−
SGA + PGA + UNCONNECTED SESSIONS = EST MEMORY REQUIREMENT AT MAXIMUM PROCESS UTILIZATION
+
        select sid
−
 
+
        from v$session
−
 
+
        where username = 'MY_USERNAME' -- your user/schema name
−
where:
+
    )
−
 
+
order by
−
Unconnected Sessions (MB) = (processes - connected sessions) * pga max memory of user session
+
    maximum_size_mb
 +
    ;
 +
</source>
 +
[[category:oracle]]

2021년 5월 10일 (월) 17:39 기준 최신판

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"