[오라클] [스크립트] INSTANCE별 USER,PROGRAM,MACHINE PROCESS 개수, 메모리

-- 다중 인스턴스에서 USER, PROGRAM, MACHINE, PROGRAM, PROCESS 확인
select inst_id,
       count(osuser),
       count(username),
       count(program),
       count(machine),
       count(process)
  from gv$session
group by inst_id;

-- SGA(Fixed Size, Variable Size, Database Buffers, Redo Buffers) 전체 크기 
select INST_ID, NAME, TRUNC(SUM(VALUE)/1024/1024) SUM from gv$sga group by ROLLUP(INST_ID, NAME);

-- SGA 구성 요소 별 크기 
select inst_id, name, trunc(bytes/1024/1024) Mbytes from gv$sgainfo order by inst_id, bytes desc;

-- SGA에 대한 디테일 정보 확인 
select inst_id, pool, trunc(sum(bytes)/1024/1024) mb from gv$sgastat  
group by inst_id, pool
order by inst_id
;

-- 동적 할당된 메모리 크기
select inst_id,
       component,
       CURRENT_SIZE/1024/1024 curr_mb,
       MIN_SIZE/1024/1024 min_mb,
       MAX_SIZE/1024/1024 max_mb,
       USER_SPECIFIED_SIZE/1024/1024 user_spec_mb,
       OPER_COUNT,
       LAST_OPER_TYPE,
       LAST_OPER_MODE,
       LAST_OPER_TIME,
       GRANULE_SIZE/1024/1024 granule_mb
  from gv$memory_dynamic_components
  where CURRENT_SIZE <> 0
  order by inst_id, curr_mb desc;

 

메모리 사이즈 변동 결과 보기

select 
  component, oper_type, oper_mode, 
  parameter, round(initial_size/1024/1024,1) initial_size, 
  round(target_size/1024/1024,1) target_size, round(final_size/1024/1024,1) final_size, 
  status, start_time, end_time 
from V$SGA_RESIZE_OPS;