다른 명령
오라클 메모리 확인
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;
미연결된 세션 요청 메모리 추정치
아래 쿼리는 사용자 세션에 의해 할당 된 최대 메모리 입니다. 이를 사용하여 연결되지 않은 프로세스의 남은 메모리 요구 사항을 계산합니다.
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) 메모리 변경 -- 메모리 자동 관리하도록 확인/변경
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;
사용자별 사용하는 메모리
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;
사용자별 사용하는 메모리 상세 조회
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 ;
현재 사용중인 메모리
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 ;