oracle monitoring

SET ECHO OFF

SET FEEDBACK OFF
CLEAR COLUMNS;

ALTER SESSION SET OPTIMIZER_GOAL = RULE;

SPOOL $PWD/monitor.out

!hostname
!uname -a

show parameter db_name;
show parameter instance_name;
show sga;

select * from v$version;

PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *               LIBRARY CACHE TUNING                  *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

SELECT TO_CHAR(TRUNC(SUM(reloads)/SUM(pins)*100, 5),99.99999)||
       '% (LESS THAN 1%)' "LIBRARY CACHE MISS RATIO"
  FROM V$LIBRARYCACHE;

PROMPT
PROMPT # libary cache miss ratio is good if it is less than 1 -2 %
PROMPT # ratio를 늘리려면 shared pool size의 값을 증가 시켜야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            DICTIONARY CACHE TUNING                  *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN Parameter Heading "INIT.ORA PARAMETER"
COLUMN Parameter FORMAT A22
COLUMN "HIT %"   FORMAT 99990.0

SELECT UPPER(Parameter) Parameter, Gets, Getmisses,
       (DECODE(Gets,0,1,Gets)-Getmisses)*100/DECODE(Gets,0,1,Gets) "HIT %",
       Count, Usage
  FROM V$ROWCACHE;

SELECT TRUNC(SUM(getmisses)/SUM(gets)*100, 5)||'%  (LESS THAN 9.8%)'
       "DATA DICTIONARY MISS RATIO "
  FROM V$ROWCACHE;

PROMPT
PROMPT # data dictionary miss ratio is good if it is less than 9.8%
PROMPT # ratio를 늘리려면 shared pool size의 값을 증가 시켜야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            Shared Pool의 사용 상태                  *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

SET SERVEROUTPUT ON
COLUMN misses FORMAT 999,999
COLUMN gets   FORMAT 999,999,999

DECLARE
   object_mem     number;
   shared_sql     number;
   cursor_mem     number;
   used_pool_size number;
   free_mem       number;
   pool_size      varchar2(512);
BEGIN  

SELECT SUM(sharable_mem)
  INTO object_mem
  FROM V$DB_OBJECT_CACHE
WHERE owner is not null;

SELECT SUM(sharable_mem)
  INTO shared_sql
  FROM V$SQLAREA;

SELECT SUM(250*users_opening)
  INTO cursor_mem
  FROM V$SQLAREA;

SELECT bytes
  INTO free_mem
  FROM V$SGASTAT
 WHERE name='free memory'
   AND pool = 'shared pool';

used_pool_size := ROUND(1.2*(object_mem+shared_sql+cursor_mem));

SELECT value
  INTO pool_size
  FROM V$PARAMETER
 WHERE name='shared_pool_size';

dbms_output.put_line('Object mem                     :  '||TO_CHAR(object_mem,'999,999,999')||' bytes');

dbms_output.put_line('Shared SQL                     :  '||TO_CHAR(shared_sql,'999,999,999')||' bytes');

dbms_output.put_line('Cursors                        :  '||TO_CHAR(cursor_mem,'999,999,999')||' bytes');

dbms_output.put_line('Free memory                    :  '||TO_CHAR(free_mem,'999,999,999')||' bytes' ||'('||TO_CHAR(ROUND(free_mem/1024/1000,1))||'MB)');

dbms_output.put_line('Shared pool utilization(total) :  '||TO_CHAR(used_pool_size, '999,999,999')||' bytes'||'('||TO_CHAR(ROUND(used_pool_size/1024/1000,1))||'MB)');

dbms_output.put_line('Shared pool allocation(actual) :  '||'     '||pool_size||' bytes'||'('||TO_CHAR(ROUND(pool_size/1024/1000,1))     ||'MB)');

dbms_output.put_line('Percentage Utilized            :  '||'          '||TO_CHAR(ROUND(used_pool_size/pool_size*100))   ||' %');

END;
/

PROMPT

REM 계산의 편의를 위한 temporary TABLE buffer_cache 생성

DROP TABLE buffer_cache;

CREATE TABLE buffer_cache
( aa   NUMBER(10),
  bb   NUMBER(10),
  cc   NUMBER(10)
);

INSERT INTO buffer_cache (aa) SELECT value FROM V$SYSSTAT
  WHERE name = 'db block gets';
UPDATE buffer_cache SET bb = (SELECT value FROM V$SYSSTAT
  WHERE name = 'consistent gets');
UPDATE buffer_cache SET cc = (SELECT value FROM V$SYSSTAT
  WHERE name = 'physical reads');

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *              BUFFER CACHE TUNING                    *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN hit FORMAT A40          HEADING 'BUFFER CACHE HIT RATIO'
COLUMN lr FORMAT 99,999,999,999  HEADING 'LOGICAL READS'
COLUMN pr FORMAT 99,999,999,999  HEADING 'PHYSICAL READS'

-- SELECT TRUNC((1 - (cc/(aa+bb)))*100, 5)||'%  (MORE THAN 60-70%)' hit,
--       (aa+bb) lr , cc pr
--  FROM buffer_cache;

select trunc((a.value+b.value-c.value)/(a.value+b.value)*100,2)
       "Buffer Cache hit ratio"
  from sys.v_$sysstat a, sys.v_$sysstat b, sys.v_$sysstat c
 where a.name='db block gets'
   and b.name='consistent gets'
   and c.name='physical reads';

PROMPT
PROMPT # buffer_cache hit ratio is good if it is more than 60-70%
PROMPT # ratio를 늘리려면 parameter file의 db_buffer_blocks의 값을
PROMPT # 증가 시켜야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        ROLLBACK SEGMENT'S WAIT RATIO                *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN miss_ratio FORMAT A20

SELECT name "ROLLBACK SEGMENT NAME",
       TRUNC(waits/gets*100, 5)||'%' miss_ratio
  FROM V$ROLLSTAT, V$ROLLNAME
 WHERE V$ROLLSTAT.usn = V$ROLLNAME.usn;

PROMPT
PROMPT # MISS RATIO가 1~2% 이하 이어야 한다.
PROMPT # 1~2%보다 크면 ROLLBACK SEGMENT의 갯수를 늘려주어야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            REDO LOG FILE'S WAIT RATIO               *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

SELECT value "REDO LOG REQUEST"
  FROM V$SYSSTAT
 WHERE name = 'redo log space requests';

PROMPT
PROMPT # 0에 가까워야 한다.
PROMPT # LOG_bUFFER를 늘려주어야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *           Internal latch Contention                 *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN 'LATCH NAME' FORMAT A20
COLUMN gets         FORMAT 999,999,999
COLUMN misses       FORMAT 999,999
COLUMN sleeps       FORMAT 999,999
COLUMN 'I_GETS'     FORMAT 999,999,999
COLUMN 'I_MISSES'   FORMAT 999,999

SELECT C.name "LATCH NAME", A.gets, A.misses, A.sleeps,
       A.immediate_gets "I_GETS", A.immediate_misses "I_MISSES"
  FROM V$LATCH A, V$LATCHHOLDER B, V$LATCHNAME C
 WHERE A.addr = B.laddr(+)
   AND A.latch# = C.latch#
   AND C.name = 'cache buffers chains'
 ORDER BY A.latch#;

PROMPT
PROMPT # I_GETS equals to immediate_gets
PROMPT # I_MISSES equals to immediate_misses
PROMPT # DB_BLOCK_BUFFERS를 조정한다.
PROMPT

SELECT C.name "LATCH NAME", A.gets, A.misses, A.sleeps,
       A.immediate_gets "I_GETS", A.immediate_misses "I_MISSES"
  FROM V$LATCH A, V$LATCHHOLDER B, V$LATCHNAME C
 WHERE A.addr = B.laddr(+)
   and A.latch# = C.latch#
   and C.name = 'cache buffers lru chain'
 ORDER BY A.latch#;

PROMPT
PROMPT # wait 시간을 줄이려면,
PROMPT # DB_BLOCK_BUFFERS와 DB_BLOCK_WRITE_BATCH를 증가시킨다.
PROMPT

SELECT C.name "LATCH NAME", A.gets, A.misses,
       A.immediate_gets "I_GETS", A.immediate_misses "I_MISSES"
  FROM V$LATCH A, V$LATCHHOLDER B, V$LATCHNAME C
 WHERE A.addr = B.laddr(+)
   AND A.latch# = C.latch#
   AND C.name in ('redo allocation','redo copy')
 ORDER BY A.latch#;

PROMPT

SELECT C.name "LATCH NAME",
       NVL(misses/DECODE(gets,0,1),0) "MISSES_GETS RATIO",
       NVL(immediate_misses/DECODE(immediate_gets,0,1),0)
       "IMMEDIATE MISSES_GETS RATIO"
  FROM V$LATCH A, V$LATCHHOLDER B, V$LATCHNAME C
 WHERE A.addr = B.laddr(+)
   AND A.latch# = C.latch#
   AND C.name in ('redo allocation','redo copy')
 ORDER BY A.latch#;

PROMPT
PROMPT # misses_gets ratio나 immediate misses_gets ratio가
PROMPT # 1%를 초과하면 Latch 경합 해소가 필요하다.
PROMPT # Redo allocation는 LOG_SMALL_ENTRY_MAX_SIZE(on multi-cpu)를
PROMPT # 줄임으로써 해결하고,
PROMPT # Redo copy는 LOG_SIMULTANEOUS_COPIES나
PROMPT # LOG_ENTRY_PREBUILD_THRESHHOLD를 늘린다.
PROMPT # 단, multi-cpu인 경우에 효과가 있다.
PROMPT

SELECT C.name "LATCH NAME", A.gets, A.misses, A.sleeps,
       A.immediate_gets "I_GETS", A.immediate_misses "I_MISSES"
  FROM V$LATCH A, V$LATCHHOLDER B, V$LATCHNAME C
 WHERE  A.addr = B.laddr(+)
   AND A.latch# = C.latch#
   AND C.name = 'row cache objects'
 ORDER BY A.latch#;

PROMPT
PROMPT # 경합 해소를 위해 SHARED_POOL_SIZE를 증가시킨다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        INTERNAL SORT AND EXTERNAL SORT              *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN name FORMAT A20

SELECT name, value
  FROM V$SYSSTAT
 WHERE name IN ('sorts (memory)', 'sorts (disk)');

PROMPT
PROMPT # MEMORY SORT에 비해 DISK SORT가 상대적으로 많으면
PROMPT # SORT_AREA_SIZE의 크기를 늘려 주어야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *                      free space확보                 *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT
PROMPT
PROMPT

set pagesize 66
column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"
column  bytes   format 9,999,999,999,999    heading "Total Bytes"
column  used    format 99,999,999,999   heading "Used"
column  free    format 999,999,999,999  heading "Free"

select a.tablespace_name                                              name,
           sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )      bytes,
           sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
           sum(a.bytes)/count( distinct b.file_id )                       used,
           sum(a.bytes)/count( distinct b.file_id )                       free,
           100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
                           (sum(a.bytes)/count( distinct b.file_id ) )) /
           (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *  DATA FILE'S PHYSICAL READ, PHYSICAL WRITE NUMBER   *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN tablespace_name FORMAT A20
COLUMN name            FORMAT A30

SELECT tablespace_name, name, phyrds, phywrts
  FROM V$DATAFILE df, V$FILESTAT fs, dba_data_files fl
 WHERE df.file# = fs.file#
   AND df.file# = fl.file_id;

PROMPT
PROMPT # 상대적으로 Physical Read / Write가 많은 Data File의 Object를
PROMPT # 분산하여 특정 Disk로 I/O 가 집중되는 것을 줄여 주도록 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        Shared Server/Dispatcher Contention          *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

PROMPT *        Session Data Memory 확인                     *

REM SELECT SUM(value) "SESSION MEMORY"
REM   FROM V$SESSTAT
REM  WHERE statistic# IN (15, 20);

REM SELECT SUM(value) "MAX SESSION MEMORY"
REM  FROM V$SESSTAT
REM  WHERE statistic# IN (16, 21);

PROMPT
PROMPT # 멀티쓰레드 서버로 연결할 사용자당 1k의 shared_pool_size 증가
PROMPT

PROMPT
PROMPT *        Dispatcher 사용률 확인                       *
PROMPT

COLUMN Protocol FORMAT a15

REM SELECT network "PROTOCOL",
REM        TRUNC(SUM(busy)/(SUM(busy)+SUM(idle))*100, 5)||'%' "TOTAL BUSY RATE"
REM   FROM V$DISPATCHER
REM  GROUP BY network;

PROMPT
PROMPT # 50% 이상이면 Dispatcher 프로세스를 증가시킨다.
PROMPT

PROMPT
PROMPT *        멀티쓰레드 서버의 튜닝                       *
PROMPT

REM SELECT network "PROTOCOL",
REM        DECODE(SUM(totalq),0,'NO RESPONSE',SUM(wait)/SUM(totalq)||'(1/100sec)')
REM   FROM V$QUEUE Q, V$DISPATCHER D
REM  WHERE Q.type = 'DISPATCHER'
REM    AND Q.paddr = D.paddr
REM  GROUP BY network;

PROMPT
PROMPT # Dispatcher 응답큐에 답을 기다리는 평균시간
PROMPT # 지속적으로 시간이 증가하면 Dispatcher 프로세스를 증가시킨다.
PROMPT

REM SELECT DECODE(totalq,0,'NO REQUEST',wait/totalq||'(1/100sec)')
REM        "AVERAGE WAIT TIME"
REM   FROM V$QUEUe
REM  WHERE type='COMMON';

PROMPT
PROMPT # 기다린 시간이 크면 MTS_MAX_SERVERS를 증가시킨다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            BACKUP STATUS CHEC                       *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

archive log list;

select * from v$backup;

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *                      Disk Space                     *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

!df -k

PROMPT
PROMPT # background_dump_dest
PROMPT # core_dump_dest
PROMPT # user_dump_dest
PROMPT # log_archive_dest 등
PROMPT # DataBase의 정상적인 운용을 위한 Physical Disk Area의
PROMPT # 안정적 확보가 항시 가능하도록 주지 시킨다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            TABLE/INDEX의 extents확인                *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COL OWNER FORMAT A15
COL "OBJECT NAME" FORMAT A20
COL "USED(extents)" FORMAT 99,999
COL "USED(bytes)" FORMAT 999,999,999,999

SELECT owner "OWNER", segment_name "OBJECT NAME",
       COUNT(extent_id) "USED(extents)", SUM(bytes) "USED(bytes)"
  FROM dba_extents
 GROUP BY owner, segment_name
HAVING COUNT(extent_id) > 20;

PROMPT
PROMPT # 7.3 이하의 Version에서의 Max Extent Limit 방지를 위하여
PROMPT # Extent가 많은 Table/Index들에 대하여 Next Extent Size를 조정해주거나
PROMPT # 적정한 Storage로 재생성을 해야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *             Object Size Check                       *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

select segment_type, sum(bytes)/1024/1024 from dba_segments
group by segment_type;

PROMPT
PROMPT  * Total Object Size
PROMPT

select sum(bytes)/1024/1024 from dba_segments;

 

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *             Total Free Space (Tablespace)           *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

select sum(bytes)/1024/1024 from dba_free_space;

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        Resource Usage Check(process, session)       *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT
set linesize 120
col resource_name format a10;

select * from v$resource_limit
where rownum < 3;

clear columns

PROMPT # current_utilization : 현재 접속 수

PROMPT # max_utilization : 오라클 시작한 이래로 최대 접속 피크수

PROMPT # initial_allocation : init.ora 파라이터에서 설정한 수치

PROMPT # max_utilization 이 limit_value에 근접하다면 processes를 늘려줘야함.

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *         Datafile Error Check                        *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

select * from v$recover_file;

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            LISTENER STATUS CHECK                    *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

!lsnrctl status

SET FEEDBACK ON
CLEAR COLUMNS

SPOOL OFF

!df -k >> monitor.out