[Oracle] Process / Session 모니터링

몇일 전 프로세스 이슈인 줄 알고 찾아보면서,

하루에 한번씩 프로세스 현황을 각 DB 마다 수집 하여 

통계가 필요할 것 같아 스크립트를 작성해 보았다.

대충은 아니지만 후다닥 만든 부분 이라 앞으로 테스트로 수집하면서

필요하거나 부족한 부분을 보충하면서 완성해야 될 것 같다.

대략 흐름은 아래와 같다.

1. 모니터링을 관장할 DB에서 각 수집을 해야할 DB들의 Link를 생성

2. DB Link를 통해 매일 오전 7시에 각 DB 의 session과 Process 를 수집

  (Scheduler 를 이용)

3. 수집 대상은 session / process / instance / host 등이 된다.

  (RAC 장비들이 다수이기 때문에 gv$를 이용하여 진행)

스크립트는 아래와 같다

--DB Link생성

CREATE PUBLIC DATABASE LINK DBA_LINK

CONNECT TO SYSTEM

IDENTIFIED BY 비밀번호

USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBIP)(PORT = 포트)))(CONNECT_DATA = (SERVICE_NAME = DB서비스명)))';

--정보를 저장할 Table 생성  

create table chk_session

(

l_date date default sysdate not null,

l_instance nvarchar2(20),

l_hostname nvarchar2(20),

ss_max number default 0,

ss_current number default 0,

ss_limit number default 0,

ss_active number default 0,

ss_inactive number default 0,

ps_max number default 0,

ps_current number default 0,

ps_limit number default 0

);

--procedure 생성

create or replace procedure SYS.proc_chk_session

is

begin

  insert into chk_session

 select 

    sysdate as rs_date,

    b.instance_name AS RS_INSTANCE,

    b.host_name AS RS_HOSTNAME,

    MAX(case when  resource_name='sessions' then MAX_UTILIZATION end) as SESSION_MAX,

    MAX(case when  resource_name='sessions' then CURRENT_UTILIZATION end) as SESSION_CURRENT,

    MAX(case when  resource_name='sessions' then LIMIT_VALUE end) as SESSION_LIMIT,

    MAX((SELECT  COUNT(*) FROM GV$SESSION@DBA_LINK C WHERE C.STATUS='ACTIVE' and b.inst_id=c.inst_id)) AS SESSION_CURRENT_ACTIVE,

    MAX((SELECT  COUNT(*) FROM GV$SESSION@DBA_LINK C WHERE C.STATUS='INACTIVE' and b.inst_id=c.inst_id)) AS SESSION_CURRENT_INACTIVE,

    MAX(case when  resource_name='processes' then MAX_UTILIZATION end) as PROCESS_MAX,

    MAX(case when  resource_name='processes' then CURRENT_UTILIZATION end) as PROCESS_CURRENT,

    MAX(case when  resource_name='processes' then LIMIT_VALUE end) as PROCESS_LIMIT

  from gv$resource_limit@DBA_LINK a, gv$instance@DBA_LINK b

  where a.resource_name in ('processes', 'sessions')

    and a.inst_id = b.inst_id

  GROUP BY sysdate, b.instance_name, b.host_name;

end;

/

--scheduler 생성

begin 

SYS.dbms_scheduler.create_job

  (job_name => 'check_session_db'

  ,start_date      => TO_TIMESTAMP_TZ('2016/01/16 07:00:00','yyyy/mm/dd hh24:mi:ss')

  ,job_class  => 'DEFAULT_JOB_CLASS'

  ,JOB_TYPE => 'PLSQL_BLOCK'

  ,repeat_interval => 'FREQ=DAILY; INTERVAL=1'

  ,job_action => 'BEGIN PROC_CHK_SESSION; END;'

  );

end;

/

--scheduler 활성화 및 테스트 실행

exec sys.dbms_scheduler.enable('check_session_db');

exec sys.dbms_scheduler.run_job('check_session_db');

 

정말 허접해 보이기는 하지만 나는 이것도 내 경험이라 생각해 본다.

수정 보완점은 아래와 같다.

1. system 계정으로 진행했기 때문에 보안상 문제가 될 소지가 있다. 그래서 이 부분을 각 권한을 따로 부여하여 진행

2. 성능 부분을 체크 안했기 때문에 플랜을 확인하여 최소한의 리소스를 사용

이렇게 하나하나 쿼리를 작성하는 것도 좋을 듯 하다.