형태

오라클 계정 -> 5분 단위로 해서 -> 아래의 테이블 생성후 -> Crontab 으로 조회하여 입력하는 형태

자세한 것은 Shell, Crontab 정보를 찾으면서 하면됨

-- 임의로 실행하는 쿼리목록 조회하기
select      to_char(sysdate,'yyyymmdd'),
            to_char(sysdate, 'HH24'),
            to_char(sysdate, 'MI'),
            A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외
   
   
CREATE TABLE MStorage.USER_SQL_LOG
(
      VIEW_YMD   VARCHAR2(8) DEFAULT to_char(sysdate,'yyyymmdd'),
      VIEW_HH    VARCHAR2(2) DEFAULT to_char(sysdate, 'HH24'),
      VIEW_MI    VARCHAR2(2) DEFAULT to_char(sysdate, 'MI'),
      INST_ID    NUMBER,
      SID      NUMBER,
      SERIAL#    NUMBER,
      USERNAME   VARCHAR2(30),
      TYPE     VARCHAR2(10),
      STATUS    VARCHAR2(8),
      STATE     VARCHAR2(19),
      EVENT     VARCHAR2(64),
      OSUSER    VARCHAR2(30),
      PROGRAM    VARCHAR2(48),
      MACHINE    VARCHAR2(64),
      SQL_ADDRESS RAW(8),
      SQL_ID   VARCHAR2(13),
      SHARABLE_MEM NUMBER,
      PERSISTENT_MEM NUMBER,
      RUNTIME_MEM NUMBER,
      FIRST_LOAD_TIME VARCHAR2(38),
      OPTIMIZER_MODE VARCHAR2(10),
      OPTIMIZER_COST NUMBER,
      MODULE     VARCHAR2(64),
      SQL_FULLTEXT  CLOB
) TABLESPACE MStorage;

COMMENT ON TABLE MStorage.USER_SQL_LOG IS '로그 모니터링 테이블(GV$SESSION) 참조';

 

-- 인덱스 생성
CREATE INDEX MStorage.IX_USER_SQL_LOG_01 ON MStorage.USER_SQL_LOG
(
  VIEW_YMD,VIEW_HH, VIEW_MI
)
TABLESPACE MStorage;

 

-- 실제 입력 구문

INSERT INTO MStorage.USER_SQL_LOG
       (
       inst_id,
            SID,
            SERIAL#,
            USERNAME,
            TYPE,
            STATUS,
            STATE,
            EVENT,
            OSUSER,
            PROGRAM,
            MACHINE,
            SQL_ADDRESS,
            SQL_ID,
            sharable_mem,
            persistent_mem,
            runtime_mem,
            first_load_time,
            OPTIMIZER_MODE,
            OPTIMIZER_COST,
            MODULE,
            SQL_FULLTEXT               
            )
select      A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     1=1-- A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외

무료로... WAS를 모니터링 가능하게 하는 소프트웨어 이다

...

http://code.google.com/p/psi-probe/

 

출처 : http://visualvm.java.net/eclipse-launcher.html

1. 설치방법
   - 위 사이트에서 Visualvm 파일을 다운받아서 이클립스 설치 디렉토리에 압축을 푼다.

2. 환경구성
    - 그림을 보면서 하면됨.

    - JDK 홈 디렉토리의 VisualVM을 사용함(JRE는 아님!, 반드시 JDK가 필요)
    - 위의 경로의 VIsualVM이 실행가능해야 함.

3. 사용법
    - 위의 Application Configuration 설정을 해줌
    - 실행 런처를 VisualVM Launcher로 변경함.
    - VIsualVM 자동 시작되는 구성으로 어플리케이션이 실행과 디버그가 생성된다.

추가로...

Default Launch를 변경하면

모두 모니터링 가능하다..

설정화면 !

참고로 visual VM을 (위의 지정된 경로에있는)

실행하면 바로 모니터링 됨 !

  SELECT   ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
           ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
           s.INST_ID,
           s.SADDR,
           s.SID,
           s.SERIAL#,
           s.AUDSID,
           s.PADDR,
           s.USER#,
           s.USERNAME,
           s.COMMAND,
           s.OWNERID,
           s.TADDR,
           s.LOCKWAIT,
           s.STATUS,
           s.SERVER,
           s.SCHEMA#,
           s.SCHEMANAME,
           s.OSUSER,
           s.PROCESS,
           s.MACHINE,
           s.PORT,
           s.TERMINAL,
           UPPER (s.PROGRAM) PROGRAM,
           s.TYPE,
           s.SQL_ADDRESS,
           s.SQL_HASH_VALUE,
           s.SQL_ID,
           s.SQL_CHILD_NUMBER,
           s.PREV_SQL_ADDR,
           s.PREV_HASH_VALUE,
           s.PREV_SQL_ID,
           s.PREV_CHILD_NUMBER,
           s.PLSQL_ENTRY_OBJECT_ID,
           s.PLSQL_ENTRY_SUBPROGRAM_ID,
           s.PLSQL_OBJECT_ID,
           s.PLSQL_SUBPROGRAM_ID,
           s.MODULE,
           s.MODULE_HASH,
           s.ACTION,
           s.ACTION_HASH,
           s.CLIENT_INFO,
           s.FIXED_TABLE_SEQUENCE,
           s.ROW_WAIT_OBJ#,
           s.ROW_WAIT_FILE#,
           s.ROW_WAIT_BLOCK#,
           s.ROW_WAIT_ROW#,
           s.LOGON_TIME,
           s.LAST_CALL_ET,
           s.PDML_ENABLED,
           s.FAILOVER_TYPE,
           s.FAILOVER_METHOD,
           s.FAILED_OVER,
           s.RESOURCE_CONSUMER_GROUP,
           s.PDML_STATUS,
           s.PDDL_STATUS,
           s.PQ_STATUS,
           s.CURRENT_QUEUE_DURATION,
           s.CLIENT_IDENTIFIER,
           s.BLOCKING_SESSION_STATUS,
           s.BLOCKING_INSTANCE,
           s.BLOCKING_SESSION,
           s.SEQ#,
           s.EVENT#,
           s.EVENT,
           s.P1TEXT,
           s.P1,
           s.P1RAW,
           s.P2TEXT,
           s.P2,
           s.P2RAW,
           s.P3TEXT,
           s.P3,
           s.P3RAW,
           s.WAIT_CLASS_ID,
           s.WAIT_CLASS#,
           s.WAIT_CLASS,
           s.WAIT_TIME,
           s.SECONDS_IN_WAIT,
           s.STATE,
           s.SERVICE_NAME,
           s.SQL_TRACE,
           s.SQL_TRACE_WAITS,
           s.SQL_TRACE_BINDS,
           s.ECID,
           stat.cpu - stat.CPU_this_call_start cpu_this_call,
           stat.CPU,
           stat.UGA_memory,
           stat.PGA_memory,
           stat.Commits,
           stat.Rollbacks
    FROM   GV$SESSION S,
           (  SELECT   ss.inst_id stat_inst_id,
                       ss.sid stat_sid,
                       SUM(DECODE (sn.name,
                                   'CPU used when call started', ss.VALUE,
                                   0))
                          CPU_this_call_start,
                       SUM(DECODE (sn.name,
                                   'CPU used by this session', ss.VALUE,
                                   0))
                          CPU,
                       SUM (DECODE (sn.name, 'session uga memory', ss.VALUE, 0))
                          uga_memory,
                       SUM (DECODE (sn.name, 'session pga memory', ss.VALUE, 0))
                          pga_memory,
                       SUM (DECODE (sn.name, 'user commits', ss.VALUE, 0))
                          commits,
                       SUM (DECODE (sn.name, 'user rollbacks', ss.VALUE, 0))
                          rollbacks
                FROM   GV$sesstat ss, GV$statname sn
               WHERE   ss.STATISTIC# = sn.STATISTIC# AND ss.inst_id = sn.inst_id
                       AND (   sn.name = 'CPU used when call started'
                            OR sn.name = 'CPU used by this session'
                            OR sn.name = 'session uga memory'
                            OR sn.name = 'session pga memory'
                            OR sn.name = 'user commits'
                            OR sn.name = 'user rollbacks')
            GROUP BY   ss.inst_id, ss.sid) stat
   WHERE   (    (s.USERNAME IS NOT NULL)
            AND (NVL (s.osuser, 'x') <> 'SYSTEM')
            AND (s.TYPE <> 'BACKGROUND'))
           AND ( (stat.stat_inst_id = s.inst_id) AND (stat.stat_sid = s.sid))
ORDER BY   "PROGRAM", OWNERID

시스템 유지보수(SM)에는

디스크의 증가량을 주별 또는 월별로 모니터링하여 미리 할당할 수도 있고

보고용으로도 사용할 수 있도록 DB에 관리용 테이블을 만들어

자동으로 저장할 수 있도록 해 놓으면 시간이 많이 지난 다음에도

추이를 알 수 있어 유용합니다.

 

테이블스페이스 사용율(exec_tbs_usage.sh)을

매일 야간에 Crontab에서 실행할 수 있도록 등록하여 사용하는 샘플입니다.

 

% vi exec_tbs_usage.sh
#---------------------------------------------------------
# ORACLE 환경변수
#---------------------------------------------------------
export ORACLE_SID=TESTDB

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=$ORACLE_BASE/product/10.2

      :

#-------------------------------------------------
# 실행할 쉘이 존재하는 절대경로
#-------------------------------------------------
cd /oracle/app/product/10.2/DBA

echo "DB Tablespace Usage"        > ./log/exec_tbs_usage.log
date                             >> ./log/exec_tbs_usage.log
TABLESPACE_USAGE.sh              >> ./log/exec_tbs_usage.log

 

% vi TABLESPACE_USAGE.sh

#!/usr/bin/ksh

###############################################################################
#
# 일별 테이블스페이스(TABLESPACE) 사용량 저장
#
# *---------------------------------------------------------------------------
# *                 일별  테이블 건수
# *---------------------------------------------------------------------------
# *  Program              : TABLESPACE_USAGE.sh
# *  Object Name          : 없음
# *  Object Type          : PL/SQL
# *  OutPut Table         : DBMANAGER.TABLESPACE_USAGE
# *  Create Date          : 2010-06-10
# *-------------------------------------------------------------------------*/
# *
# *  Execution Sample     : exec_tablespace_usage.sh (cron job)
# *                         TABLESPACE_USAGE.sh
# *                         SQL Exec. : sqlplus / as sysdba
# *
# *-------------------------------------------------------------------------*/
#
###############################################################################

#----------------------------------------------
#-- DB수집정보를 저장할 오라클 계정 생성 및 Grant
#----------------------------------------------
#-- DROP USER DBMANAGER CASCADE;
#-- CREATE USER DBMANAGER IDENTIFIED BY DBMANAGER

#--              DEFAULT TABLESPACE TOOLS TEMPORARY TABLESPACE TEMP;
#--
#-- GRANT SELECT_CATALOG_ROLE, SELECT ANY TABLE, SELECT ANY DICTIONARY TO DBMANAGER;
#-- GRANT UNLIMITED TABLESPACE   TO DBMANAGER;
#-- GRANT BECOME USER            TO DBMANAGER;
#-- GRANT CREATE VIEW            TO DBMANAGER;
#-- GRANT CREATE TABLE           TO DBMANAGER;
#-- GRANT ALTER  SESSION         TO DBMANAGER;
#-- GRANT CREATE SESSION         TO DBMANAGER;
#-- GRANT CREATE SYNONYM         TO DBMANAGER;
#-- GRANT SELECT ANY TABLE       TO DBMANAGER;
#-- GRANT SELECT ANY SEQUENCE    TO DBMANAGER;
#-- GRANT SELECT ANY DICTIONARY  TO DBMANAGER;
#--
#--
#----------------------------------------------
#-- DB수집정보를 저장할TABLE 생성 및 Grant
#----------------------------------------------
#--
#-- DROP TABLE DBMANAGER.TABLESPACE_USAGE PURGE;
#-- CREATE TABLE DBMANAGER.TABLESPACE_USAGE
#--      ( GATHER_DATE     CHAR(8)      DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') NOT NULL,
#--        TABLESPACE_NAME VARCHAR2(30)           NOT NULL,
#--        ALLOC_KBYTES    NUMBER       DEFAULT 0 NOT NULL,
#--        USED_KBYTES     NUMBER       DEFAULT 0 NOT NULL,
#--        EXEC_DATETIME   DATE         DEFAULT SYSDATE NOT NULL
#--      ) TABLESPACE TOOLS;
#--
#-- CREATE UNIQUE INDEX
#--        DBMANAGER.UK_TABLESPACE_USAGE_01 ON DBMANAGER.TABLESPACE_USAGE(GATHER_DATE, TABLESPACE_NAME)
#--        TABLESPACE TOOLS;

#-- COMMENT ON TABLE DBMANAGER.TABLESPACE_USAGE IS '테이블스페이스 크기 및 사용량';
#--
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.GATHER_DATE     IS '수집 일시';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.TABLESPACE_NAME IS '테이블스페이스 명';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.ALLOC_KBYTES    IS '할당된 크기(KB)';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.USED_KBYTES     IS '사용된 크기(KB)';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.EXEC_DATETIME   IS '수행 일시';

#---------------------------------------------------------
# ORACLE 환경변수
#---------------------------------------------------------
export ORACLE_SID=TESTDB

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=$ORACLE_BASE/product/10.2

      :

#-------------------------------------------------
# 실행할 쉘이 존재하는 절대경로
#-------------------------------------------------
cd /sw/oracle/app/product/10.2/DBA

if [ $# != 0 ]
then

    echo "\n-----------------------------------------"
    echo "\n인수(Argument) 갯수가 틀립니다."
    echo "\n실행 예) : TABLESPACE_USAGE.sh"
    echo "\n-----------------------------------------"
    exit
fi

#---------------------------------------------------------
# 입력 값 없음
#---------------------------------------------------------

##################################
##### [ 오라클 PL/SQL Start] #####
##################################

#--------------------
#-- SQL*PLUS 접속
#--------------------
sqlplus -s /nolog<<EOF

SET SERVEROUTPUT ON
SET PAGESIZE 0 SQLPROMPT "" ECHO OFF HEADING OFF FEEDBACK OFF TIMING OFF VERIFY OFF TERMOUT ON TRIMSPOOL ON

---------------------
-- SYS 계정으로 접속
---------------------
connect / as sysdba

DECLARE

-------------------------------------------------------
-- 커서 변수 선언
-------------------------------------------------------
    cursor_insert    INTEGER;
    cursor_delete    INTEGER;

-------------------------------------------------------
-- 데이터 값을 저장할 변수 선언
-------------------------------------------------------
    v_dummy          NUMBER;
    v_gather_date    VARCHAR2(08) := TO_CHAR(SYSDATE,'YYYYMMDD');

-------------------------------------------------------
-- SQL 문장을 저장할 변수 선언
-------------------------------------------------------
    insertStmt       VARCHAR2(2000);
    deleteStmt       VARCHAR2(2000);

    BEGIN

-------------------------------------------------------
-- CURSOR 열기
-------------------------------------------------------
        cursor_insert := DBMS_SQL.OPEN_CURSOR;
        cursor_delete := DBMS_SQL.OPEN_CURSOR;

-----------------------------------------------------------------
-- 중복 데이터에 의한 오류를 방지하기 위해
-- DBMANAGER.TABLESPACE_USAGE에 있는 해당일자(TODAY)의 정보 삭제
-----------------------------------------------------------------
        deleteStmt := 'DELETE /*+ parallel(A 8) */ FROM DBMANAGER.TABLESPACE_USAGE A WHERE A.GATHER_DATE = :gather_date';

        DBMS_SQL.PARSE( cursor_delete, deleteStmt, DBMS_SQL.NATIVE );

        DBMS_SQL.BIND_VARIABLE( cursor_delete, 'gather_date', v_gather_date );

        v_dummy := DBMS_SQL.EXECUTE( cursor_delete );

-------------------------------------------------------
-- DBMANAGER.TABLESPACE_USAGE에 입력(INSERT)
-------------------------------------------------------
        insertStmt := ' INSERT INTO DBMANAGER.TABLESPACE_USAGE                           '||
                      ' ( GATHER_DATE, TABLESPACE_NAME,  ALLOC_KBYTES, USED_KBYTES )     '||
                      ' SELECT :gather_date, C.TABLESPACE_NAME,                          '||
                      '        NVL(A.TOTAL,0)                   ALLOC_KBYTES,            '||
                      '        NVL(A.TOTAL,0) - NVL(B.FREE,0)   USED_KBYTES              '||
                      '   FROM (SELECT TABLESPACE_NAME,                                  '||
                      '                NVL(ROUND((SUM(BYTES)/1024),2),0) TOTAL           '||
                      '           FROM SYS.DBA_DATA_FILES                                '||
                      '          GROUP BY TABLESPACE_NAME) A,                            '||
                      '        (SELECT TABLESPACE_NAME,                                  '||
                      '                NVL(ROUND((SUM(BYTES)/1024),2),0) FREE            '||
                      '           FROM SYS.DBA_FREE_SPACE                                '||
                      '          GROUP BY TABLESPACE_NAME) B,                            '||
                      '        DBA_TABLESPACES             C                             '||
                      '  WHERE C.TABLESPACE_NAME = A.TABLESPACE_NAME(+)                  '||
                      '    AND C.TABLESPACE_NAME = B.TABLESPACE_NAME(+)                  '||
                      '    AND C.TABLESPACE_NAME NOT LIKE '||'''TEMP%'''                   ;
        DBMS_SQL.PARSE( cursor_insert, insertStmt, DBMS_SQL.NATIVE);

        DBMS_SQL.BIND_VARIABLE( cursor_insert, 'gather_date', v_gather_date );

        v_dummy := DBMS_SQL.EXECUTE( cursor_insert );

-------------------------------------------------------
-- CURSOR 닫기
-------------------------------------------------------
        DBMS_SQL.CLOSE_CURSOR( cursor_insert );
        DBMS_SQL.CLOSE_CURSOR( cursor_delete );
        COMMIT;

         EXCEPTION
--            WHEN DUP_VAL_ON_INDEX THEN
--                 DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러-중복되는 데이터가 존재');
--            WHEN TOO_MANY_ROWS    THEN
--                 DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러-리턴해야 하는 SELECT문이 하나 이상의 행을 반환');
--            WHEN NO_DATA_FOUND    THEN
--                 DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러-SELECT문이 아무런 데이터 행을 반환하지 못함');
--            WHEN INVALID_CURSOR   THEN
--                 DBMS_OUTPUT.PUT_LINE('INVALID_CURSOR 에러-잘못된 커서 연산');
              WHEN OTHERS           THEN
                   DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE));
                   DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
 END;
/
exit
EOF

 

환경 : Unix, Aix5L, Oracle10gR2