Oracle Code Tips Collection

（１）カレントトレースファイル（TraceFile）ファイル名を取得する

SELECT d.VALUE
     || '/'
     || LOWER (RTRIM (i.INSTANCE, CHR (0)))
     || '_ora_'
     || p.spid
     || '.trc' trace_file_name
FROM 
(SELECT p.spid
 FROM v$mystat m, v$session s, v$process p
 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
 FROM v$thread t, v$parameter v
 WHERE v.NAME = 'thread'
 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
 FROM v$parameter
 WHERE NAME = 'user_dump_dest') d;

（２）起動してからの累計キャッシュのヒット率（90%以上が理想）

SELECT round((1-(phy.value/(cur.value+con.value)))*100,2)||'%' AS "cache hit ratio"
 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';

おまけ：[ヒット率]
1 - ( physical_reads / ( db_block_gets + consistent_gets ) )

（３）ライブラリキャッシュ ヒット率（99%以上が理想）

SELECT SUM(PINS) AS "hit ratio",
            SUM(RELOADS) AS "missed ratio",
            ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2)
                  || '%' AS "library cache hit ratio"
     FROM V$LIBRARYCACHE;

（４）ディクショナリキャッシュ ヒット率（90%以上が理想）

SELECT SUM(GETS) AS "hit ratio",
            SUM(GETMISSES) AS "missed ratio",
            ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) || '%' AS "Dictionary hit ratio"
     FROM V$ROWCACHE;

（５）x$bhからバッファ・キャッシュの空き比率を調べる（90%以上が理想）

SET SERVEROUTPUT ON;
     DECLARE
       cnt_total NUMBER;
       cnt_free NUMBER;
       result NUMBER;
       message VARCHAR2(256);
     BEGIN
       SELECT COUNT(*) INTO cnt_total FROM X$BH;
       SELECT COUNT(*) INTO cnt_free FROM X$BH 
         WHERE STATE = 0;
       result := ROUND((cnt_free / cnt_total), 3) * 100;
       SELECT DECODE(SIGN(result), 1,
           'OK, There are enough buffer caches',
           'NG, Buffer size is NOT enough') INTO message
         FROM DUAL;
       DBMS_OUTPUT.PUT_LINE('Buff empty ratio->' || 
           result || '%');
       DBMS_OUTPUT.PUT_LINE(message);
     END;
/

（６）ソート状況を判断し、SORT_AREA_SIZEを設定する。
sorts (disk)が多い場合、変更を検討する。
メモリソートの割合はが効率的に95%以上であることが望ましい。
95%未満であれば、SORT_AREA_SIZEの値を大きくする

SELECT name,value FROM v$sysstat WHERE name LIKE '%sort%';
SELECT A.RATIO "MEMORY SORT RATIO",
           DECODE(SIGN(A.RATIO - 0.95),1,
                  'SORT_AREA_SIZE is enough.',
                  'SORT_AREA_SIZE is NOT enough.') "NOTES"
      FROM (SELECT M.VALUE / (M.VALUE + D.VALUE) RATIO
              FROM V$SYSSTAT M, V$SYSSTAT D
             WHERE M.NAME = 'sorts (memory)'
               AND D.NAME = 'sorts (disk)') A;

（７）REDOログバッファの待機割合（１％以下が理想）

SELECT round(A.RATIO*100,2)||'%' "REDO SPACE WAIT RATIO",
           DECODE(SIGN(A.RATIO - 0.01),1,
           'Set LOG_BUFFER to a bigger value.',
           'LOG_BUFFER is enough.') "notes"
      FROM (SELECT R.VALUE / W.VALUE RATIO
              FROM V$SYSSTAT R, V$SYSSTAT W
             WHERE R.NAME = 'redo log space requests'
               AND W.NAME = 'redo writes') A;

（８）表領域の容量チェック

select 
  a.TABLESPACE_NAME,
  min(a.BYTES)/1024/1024 "TOTAL(MB)", 
  round(min(a.BYTES)/(1024*1024) - sum(b.BYTES)/
  (1024*1024),2) "USED(MB)", 
  round((min(a.BYTES)/(1024*1024) - sum(b.BYTES)/(1024*1024))/
  (min(a.BYTES)/1024/1024)*100,2) "USED RATIO(%)",
  round(sum(b.BYTES)/(1024*1024),2) "FREE(MB)"
from 
  dba_data_files a, dba_free_space b
where 
  a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME;

（９）該当表領域の個々テーブルの占有容量チェック

-- EXAMPLE表領域の場合
select 
  TABLESPACE_NAME, 
  SEGMENT_NAME AS TABLENAME,
  sum(BYTES/1024) AS "SIZE(KB)" 
from DBA_EXTENTS 
where TABLESPACE_NAME = 'EXAMPLE' AND SEGMENT_TYPE='TABLE' 
group by TABLESPACE_NAME, SEGMENT_NAME;

（１０）時系列アーカイブログの件数統計

set pages 9999;
set lines 2000;
select to_char(first_time,'RR/MM/DD') "DATE",
    to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
    to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
    to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
    to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
    to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
    to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
    to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
    to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
    to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
    to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
    to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
    to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
    to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
    to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
    to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
    to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
    to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
    to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
    to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
    to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
    to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
    to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
    to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
    to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" 
from v$archived_log 
group by to_char(first_time,'RR/MM/DD')
order by first_time;