웹 개발과 관련된 모든 것들

형태

오라클 계정 -> 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$ 로 시작하는 뷰를 보는 경우는 제외

시스템 유지보수(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

서버 프로세스

** 서버 프로세스 상태

SELECT sid, serial#, username, status "Status of Session",
       server "ServerType", osuser, machine, program, process
FROM   v$session;

** PGA 상태(전체 세션)

SELECT MEM.value "PGA", DISK.value "Temporary 테이블스페이스",
       (disk.value/mem.value) * 100 "Ratio"
FROM   v$sysstat MEM, v$sysstat DISK
WHERE  MEM.name = 'sorts (memory)'
   AND DISK.name = 'sorts (disk)';

**  PGA 상태 (세션 별)

SELECT  A.process, A.username, B.block_gets, B.consistent_gets
        , B.physical_reads
FROM    v$sess_io B, v$session A
WHERE   B.sid=A.sid

**  UGA 상태 (Shared_Pool)

SELECT SUM(value) "Total Session Memory"
FROM   v$mystat A, v$statname B
WHERE  name = 'session uga memory'
   AND A.statistic# = B.statistic#;

데이터 버퍼 캐쉬

** 데이터 버퍼 캐쉬 구조

SELECT id, name, block_size, buffers
FROM   v$buffer_pool;

**  히트율

SELECT phy.value "Physical Read",cur.value+con.value
   "Buffer Cache Read",
   (1-((phy.value) / (cur.value+con.value)))*100 "히트율"
FROM   v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE  cur.name = 'db block gets'
   and con.name = 'consistent gets'
   and phy.name = 'physical reads';

** 대기상태

SELECT (SELECT total_waits FROM   v$system_event WHERE  event = 'free buffer waits') "Free Buffer Waits",
       (SELECT total_waits FROM   v$system_event WHERE  event = 'buffer busy waits') "Buffer Busy Waits"
FROM dual;

** 메모리 사용상태(세션 별)

SELECT A.process, A.username, B.block_gets, B.consistent_gets, B.physical_reads
FROM   v$sess_io B, v$session A
WHERE  B.sid = A.sid;

로그 버퍼 (히트율)

SELECT S1.value "Redo Alloc Retries",
       S2.value "Redo Entries",
       S1.value / S2.value * 100 "히트율"
FROM   v$sysstat S1, v$sysstat S2
WHERE  S1.name = 'redo buffer alLocation retries'
   AND S2.name = 'redo entries';

공유 풀 영역 (Shared Pool Area)

** Reload 상태

SELECT Gets, Gethits, Gethitratio*100 "히트율"
FROM   v$Librarycache
WHERE  namespace = 'SQL AREA';

** 히트율

SELECT SUM(pins) "Executions(PINS)",
       SUM(reloads) "Cache Misses(RELOADS) " ,
       SUM(reloads) / SUM(pins)*100 "Reload율"
FROM   v$Librarycache;

** Reserved Pool(응답상태)

SELECT requests, request_misses, request_failures, free_space,
       avg_free_size, max_free_size, used_space,avg_used_size
FROM   v$shared_pool_reserved;

** 파싱된 SQL문의 상태

SELECT cpu_time, elapsed_time,executions, loads, invalidations, version_count,
       parse_calls, sorts,  sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%' AND command_type IN (2,3,6,7)
   AND rownum <= 200
ORDER BY sql_text DESC;

** 파싱된 SQL문의(메모리 순)

SELECT buffer_gets, sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%'
   AND command_type IN(2,3,6,7)
ORDER BY buffer_gets DESC;

** 파싱된 SQL문의(I/O 순)

SELECT disk_reads, sql_text
FROM   v$sqlarea
WHERE  sql_text NOT LIKE '%$%'
   AND command_type IN(2,3,6,7)
ORDER BY disk_reads DESC;

Large Pool (free memory 상태)

SELECT pool, name, bytes
FROM   v$sgastat
WHERE  pool = 'large pool'; 

DB Writer 정보 

** DBWR 정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%DB%';

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'free buffer waits';

CKPT 정보 

** CKPT  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%CKPT%';

** 대기상태

SELECT event, total_waits, time_waited, average_wait
FROM   v$system_event
WHERE  event LIKE 'log file switch (checkpoint%';

LGWR 정보 

** LGWR  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%LGWR%';

** 대기상태

SELECT sid, event, seconds_in_wait, state
FROM   v$session_wait
WHERE  event = 'log buffer space%'; 

PMON 정보 

** PMON  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%PMON%';

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'pmon timer';

SMON 정보 

** SMON  정보

SELECT spid, program, username, serial#, terminal
FROM   v$process
WHERE  program LIKE '%SMON%'; 

** 대기상태

SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'smon timer';

Data Files 

** Data-File 구조

SELECT B.file_id "File #", B.file_name,
       B.tablespace_name, B.bytes "Bytes",
       ((B.bytes - sum(nvl(A.bytes,0)))) "Used Space",
       (SUM(NVL(A.bytes,0)))  "Free Space",
       (SUM(NVL(A.bytes,0)) / (B.bytes)) * 100 "FreeSpace Ratio"
FROM   sys.dba_free_space A, sys.dba_data_files B
WHERE  A.file_id(+) = B.file_id
GROUP BY B.tablespace_name, B.file_id, B.file_name, B.bytes
ORDER BY B.file_id;

** Disk I/O 상태

select name, phyrds, phywrts
FROM   v$datafile DF, v$filestat FS
WHERE  DF.file# = FS.file#;

** Object 종류

SELECT A.owner, A.object_id, A.object_name, B.partition_name,
       B.tablespace_name, B.bytes, B.blocks, B.extents,
       B.initial_extent, B.next_extent, B.min_extents,
       B.max_extents, B.pct_increase, B.freelists,
       B.relative_fno, B.buffer_pool, A.created, A.status
FROM   dba_objects A, dba_segments B
WHERE  A.owner = 'SCOTT'
   AND A.object_type = 'TABLE'
ORDER BY A.object_name;

** 롤백 세그먼트(경합 상태)

SELECT SUM(waits) "Waits", SUM(gets) "Gets",
       100 * SUM(waits)/SUM(gets) "히트율"
FROM   v$rollstat;

** 롤백 세그먼트(대기 상태)

SELECT (SELECT count FROM   v$waitstat WHERE  class = 'undo header') "Undo Header",
       (SELECT count FROM   v$waitstat WHERE  class = 'undo block')  "Undo Block",
       (SELECT count FROM   v$waitstat WHERE  class = 'system undo header') "System Undo Header",
       (SELECT count FROM   v$waitstat WHERE  class = 'system undo block') "System Undo block"
FROM dual;

**  TEMP 세그먼트(경합 상태)

SELECT username, user, contents, segtype,
       extents, blocks
FROM   v$sort_usage;

** Lock 상태 (Holder & Waiter)

SELECT LPAD(' ',DECODE(request,0,0,1))||sid sess,
       id1, id2, lmode, request, type
FROM   v$lock
WHERE  id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0)
ORDER BY id1, request;

** Lock 상태 (Waiters)

SELECT LPAD(' ',DECODE(C.request,0,0,1))||C.sid sess, B.username,
       B.program, C.type, A.sql_text SQL
FROM   v$sqlarea A,v$session B, v$lock C, v$access D
WHERE  C.id1 IN (SELECT id1 FROM v$lock WHERE lmode = 0 OR lmode = 6)
   AND A.address(+) = B.sql_address
   AND A.hash_value(+) = B.sql_hash_value
   AND B.sid = D.sid
   AND B.sid = C.sid
   AND D.sid = C.sid
   AND D.owner != 'SYS'
ORDER BY C.id1, C.request;

** DB 사용자

SELECT username, default_tablespace, temporary_tablespace,
       account_status, expiry_date, lock_date, profile
FROM   dba_users;

 Control-file 구조

SELECT *
FROM   v$controlfile; 

 Redo log files

** Redo-Log Files 구조

SELECT A.group# col1, bytes / 1024 col2,
       members col3, member col4 ,
       A.status col5, sequence#
FROM   v$log A, v$logfile B
WHERE  A.group# = B.group#;

** I/O 대기상태

SELECT event, total_waits, time_waited, average_wait
FROM   v$system_event
WHERE  event LIKE 'log file switch completion%';

Archived log files

SELECT name, sequence#, first_change#, first_time, 
       next_change#, next_time, archived, status 
FROM   v$archived_log;

SELECT sid, serial#, osuser, process, machine, program, 
       to_char(logon_time, 'yyyy/mm/dd hh:mi:ss') as Logon_Time
FROM   v$session 
WHERE  (upper(server) <> 'PSEUDO' AND UPPER(program) NOT LIKE 'oracle.exe%')
       and serial# <> 1;