[오라클] INSTANCE별 USER,PROGRAM,MACHINE PROCESS 개수, 메모리 확인 쿼리

 -- 2. 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;
 
-- 동적 할당된 메모리 크기
select 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 v$memory_dynamic_components;
 
-- v$memory_dynamic_components

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

-- 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);


-- 오라클 프로세스에서 할당 및 사용하는 PGA 메모리
select pname, trunc(pga_used_mem/1024) pga_used_mem_kb, trunc(pga_alloc_mem/1024) pga_alloc_mem_kb, trunc(pga_max_mem/1024) pga_max_mem_kb from v$process order by PGA_USED_MEM_KB DESC;

-- 할당 및 사용 PGA 메모리 합계
select null, trunc(sum(pga_used_mem)/1024/1024) pga_used_mem, trunc(sum(pga_alloc_mem)/1024/1024) pga_alloc_mem, trunc(sum(pga_max_mem)/1024/1024) pga_max_mem from v$process;

-- 부하에 따라 버퍼 캐시 크기는 변경됨
SELECT INST_ID, NAME, trunc(BYTES/1024/1024) MBytes FROM GV$SGAINFO WHERE NAME='Buffer Cache Size' ORDER BY INST_ID;