Oracle/Oracleの状態を確認する

SQL実行状態

ビュー名用途
V$SESSIONSID、SQL_ID、PREV_SQL_ID の確認
V$PROCESSSPID(OSのPID)の確認
V$SQL子カーソル(実行計画)毎の統計情報、SQLが終了した時点で更新される。実行中は5秒毎に更新
V$SQLAREA親カーソル(SQL文)毎の統計情報。メモリー内にあり、解析済で、実行準備のできているSQL文に関する統計情報
V$SQLSTATSSQL文毎にSQLカーソルに関する基本的なパフォーマンス統計情報を表示。
V$SQL、V$SQLAREAでも確認できるが統計確認に最適化されている(カーソルが無効になった後も統計表示できる)
SQL_FULLTEXT列はCBOL型で複数行で出力されるので注意
V$SQL_SHARED_CURSOR子カーソルが共有されない理由を確認する

SPID/SIDの確認

select p.spid PID, s.sid, s.serial#, s.username, s.program, s.machine, s.osuser
from v$session s, v$process p
where s.paddr=p.addr
--    and s.sid=(select distinct sid from v$mystat) -- SQL*Plus接続してる自分自身のSPIDを確認するにはこの条件を追加する
order by 1,2,3;
-- SPIDから、SQLの中身を確認する [#v01537d1]
select * from v$sql where sql_id in
( select sql_id from v$session s, v$process p where s.paddr=p.addr and p.spid='[SPID]');

SQL文の取得

  • SQL文から、SQL_IDを確認する
set long 65535
select sql_id, sql_fulltext from V$SQL where sql_text like '%<検索SQL>%';
-- 1000byte以上のSQL文を検索したい場合
select sql_id, sql_fulltext from V$SQL where dbms_lob.substr(sql_fulltext, 4000, 1) like '%<検索SQL>%';

※ CLOB型のsql_fulltextを検索すれば良いが、VARCHAR2型として取得する為、最大4000byte までしか検索できない(上記例は、1byte〜4000byte目の文字を検索してる)
※ 4000byte以上検索したい場合は、or dbms_lob.substr(sql_fulltext, 4000, 4001) のように or 条件で繋げるしかない
-- 共有プールに残ってない場合
select
    a.begin_interval_time,
    b.sql_id,
    c.sql_text
from DBA_HIST_SNAPSHOT a, DBA_HIST_SQLSTAT st, DBA_HIST_SQLTEXT c
where a.dbid=b.dbid and a.instance_number=b.instance_number and a.snap_id=b.snap_id
      and b.dbid=c.dbid and b.sql_id=c.sql_id
      and c.sql_text like '%<検索SQL>%';
  • SQL_IDからSQL文を取得する
select sql_text from v$sql where sql_id = '<SQL_ID>';
-- 共有プールに残ってない又は、1000byte以上のSQL文を確認する場合は
set long 65535
select sql_text from DBA_HIST_SQLTEXT where sql_id = '<SQL_ID>';
  • SQL_IDからSQL実行時間、読み込みブロック数、CPU時間を表示
select executions, disk_reads, buffer_gets, rows_processed, cpu_time, elapsed_time from V$SQLSTATS where sql_id = '<SQL_ID>';


待機イベント

ビュー説明
V$MUTEX_SLEEPミューテックスタイプ毎の待機情報
V$MUTEX_SLEEP_HISTORYミューテックスタイプ毎の待機履歴で、ブロッキングセッションの確認も可能
V$LATCHすべてのラッチ(親ラッチと、子ラッチ)
V$LATCH_CHILDREN子ラッチに関する統計情報
V$LATCH_PARENT親ラッチに関する統計情報
V$LATCH_MISSES取得できなかったラッチに関する統計情報(ラッチを取得使用とした場所など)
V$LOCKロックまたはラッチに対する情報
V$LOCKED_OBJECTトランザクションが獲得した全てのロック情報

待機イベントの確認

select
    to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') TIME,
    w.inst_id,
    w.sid,
    s.status,                  -- ACTIVE(SQL文実行中)か、INACTIVE(SQL文が届くのを待っている)
    s.sql_id,
    s.blocking_session,        -- 当該SIDをブロックしてるSID
    w.event,
    w.p1, w.p2, w.p3, w.p1text, w.p2text, w.p3text,
    w.wait_time,
    w.state,
    w.wait_time_micro,
    w.time_remaining_micro,
    w.time_since_last_wait_micro
from GV$SESSION_WAIT w, GV$SESSION s
where w.inst_id = s.inst_id and w.sid = s.sid and w.wait_class <> 'Idle';
カラム説明
SECONDS_IN_WAIT現在セッションの待機が開始されるまでに費やした時間(秒)、待機中でない場合は前回の時間
※ WAIT_TIME_MICRO、TIME_SINCE_LAST_WAIT_MICRO列の参照を推奨
WAIT_TIME0:待機中 1以上:前回の待機時間(1/100秒) -1:前回の待機が1/100秒未満
-2:時間取得できない(TIMED_STATISTICSパラメータがfalseになっている)
※ WAIT_TIME_MICRO、STATE列 の参照を推奨
STATEWAITING      :セッションは現在待機中
WAITED UNKNOWN TIME:TIMED_STATISTICSパラメータがfalseになっている
WAITED SHORT TIME :前回の待機は1/100秒未満だった
WAITED KNOWN TIME :前回の待機時間は、WAIT_TIME列で指定される
WAIT_TIME_MICRO現在セッションの待機時間(マイクロ秒)、待機中でない場合は、前回の待機時間(マイクロ秒)
TIME_REMAINING_MICRO1以上 :現在の待機の残り時間(マイクロ秒)
0   :現在の待機はタイムアウトした
-1  :セッションは現在の待機内で無期限に待機できる
NULL :待機していない
TIME_SINCE_LAST_WAIT_MICRO前回の待機終了後の経過時間(マイクロ秒)。セッションが現在待機中の場合、値は0
P1〜P3待機イベントの引数。詳細はOracle Database リファレンス11g リリース2(11.2)で確認可能
PREV_SQL_IDDML実行しCOMITTしてないSQLは、SQL_ID が空となり、PREV_SQL_ID に 埋め込まれる
 PIDも同時に取得する場合
  • BLOCKING_SESSION の SQL_ID を取得
select inst_id, sid, serial#, sql_id, sql_exec_start, prev_sql_id
from GV$SESSION
where sid in (select blocking_session from gv$session where blocking_session_status='VALID');

ロック

select
    l.inst_id,
    d.object_name,
    o.oracle_username,
    o.process PID,
    l.type,
    l.lmode,
    l.request,
    l.ctime,
    l.sid
from GV$LOCK L, GV$LOCKED_OBJECT O ,DBA_OBJECTS D
where  L.sid=O.session_id and O.object_id = D.object_id and l.inst_id = o.inst_id
order by 1,2
  • LMODE、REQUESTのロックモード
    LMODE/REQUESTSQLの例
    1:NULLselect 〜 from table
    2:SS 行共有select 〜 for update
    3:SX 行排他insert / update / delete
    4:S  共有alter index 〜 rebuild online
    5:SSX 共有/行排他
    6:X  排他drop table / alter table
    共有ロック:データが表定義が変更されると困る操作の時のみかけるロック。読み取りが終了するまで更新しないようフラグを立てる
    排他ロック:更新を行う際にかけるロック。1つの行や表に対して1つのみロックできる
  • TYPE列
    TM(DMLエンキュー)データの一貫性を保証するためのロック
    TX(トランザクションエンキュー)データ構造の整合性を保証するためのロック
    REQUEST列のデータが「0:なし」でない場合、そのセッションは現在更新要求を出しているが、ロックがかかって「待ち」が発生していることを示す

     

メモリ

SGA情報

ビュー名説明
V$SGAINFOSGAサイズに関する情報(現在のサイズ、グラニュル、割り当てられてない空き容量)
V$SGASTATSGAコンポーネントに関する詳細情報
V$SGA_DYNAMIC_COMPONENTS動的SGAにて調整されたコンポーネントの現在のサイズに関する情報
V$SGA_RESIZE_OPS過去800回分のSGAサイズ変更操作(変更前後でのパラメータサイズ)に関する情報
V$SGA_TARGET_ADVICESGA_TARGET初期化パラメータに関する情報
カラム名説明
OPER_TYPE:操作タイプSTATIC(変更なし)、INITIALIZING(初期化中)、DISABLED(遅延)、GROW(拡張)、SHRINK(縮小)、SHRINK_CANCEL(縮小中止)
OPER_MODE:操作モードIMMEDIATE(エラー回避の即時実行)、DEFERRED(バランスを取る為の遅延実行)、MANUAL(手動実行)
STATUS  :完了状態COMPLETE(完了)、INACTIVE(停止中)、PENDING(保留)、CANCELLED(中止)

SGAの統計情報

-- 現在の使用量
show sga
select * from V$SGAINFO;
※ free memoryの容量も含んだ値で計算されるっぽい
-- 履歴の使用量
select
    g.instance_number,
    to_char(s.begin_interval_time,'yyyy/mm/dd hh24:mi') "TIME",
    g.pool,
    round(sum(g.bytes)/1024/1024,0)
from DBA_HIST_SNAPSHOT s join DBA_HIST_SGASTAT g on s.instance_number=g.instance_number
    and s.snap_id=g.snap_id and name != 'free memory'
group by g.instance_number, s.begin_interval_time, g.pool
order by g.instance_number, g.pool, s.begin_interval_time;

―――――――――――――――――――――――――
履歴の使用量 SNAP_IDの差分で集計(インスタンス毎)
―――――――――――――――――――――――――
select a.instance_number
      ,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI') "TIME"
      ,a.pool
      , round(sum(a.bytes)/1024/1024,0) - bytes_aft
from DBA_HIST_SGASTAT a,
    ( select instance_number, snap_id, round(sum(bytes)/1024/1024,0) bytes_aft
      from DBA_HIST_SGASTAT
      where name != 'free memory' and pool='shared pool' group by instance_number,snap_id, pool ) b,
     DBA_HIST_SNAPSHOT c
where a.snap_id=b.snap_id+1
      and a.instance_number=b.instance_number
      and a.name != 'free memory'
      and a.pool='shared pool'
      and a.snap_id=c.snap_id
      and a.instance_number=c.instance_number
group by a.instance_number,c.begin_interval_time,a.pool, bytes_aft
order by 1,2;
-- コンポーネントサイズ変更履歴
select * from V$SGA_RESIZE_OPS where COMPONENT in ('shared pool','large pool','java pool','streams pool','DEFAULT buffer cache');

共有プール内で確保済プールおよび領域の統計情報

select * from V$SHARED_POOL_RESERVED;
カラム説明カラム説明
FREE_SPACE予約領域内の空き領域の合計サイズUSED_SPACE予約領域内で使用領域の合計サイズ
AVG_FREE_SIZE予約領域内の空き領域の平均サイズAVG_USED_SIZE予約領域内で使用領域の平均サイズ
FREE_COUNT予約領域内の空き領域の数USED_COUNT予約領域内で使用領域の数
MAX_FREE_SIZE予約領域内の最大空き領域サイズMAX_USED_SIZE予約領域内の最大使用領域サイズ
REQUESTS空き領域を探す為に予約領域が検索された数
REQUEST_MISSES予約領域に空きがない為、共有プールのフラッシュが発生した回数
LAST_MISS_SIZE共有プールフラッシュが発生した時の最後の要求サイズ
MAX_MISS_SIZE共有プールフラッシュが発生した時の最大要求サイズ
REQUEST_FAILURES共有プールに空きがない為、ORA-04031が発生した回数
LAST_FAILURE_SIZE共有プールに空きがない為、ORA-04031が発生した時のサイズ
ABORTED_REQUEST_THRESHOLD共有プールをフラッシュせずにORA-04031を発生させるしきい値のサイズ
ABORTED_REQUESTS共有プールをフラッシュせずにORA-04031が発生した回数
LAST_ABORTED_SIZE共有プールをフラッシュせずにORA-04031が発生した時の最後の要求サイズ

割り当て余力を確認
component が「DEFAULT buffer cache」の行において、current_size から user_specified_size を引いた値(diff)は、共有プールへの割り当て拡張可能な余力になる

select component, user_specified_size,current_size, current_size - user_specified_size diff from v$sga_dynamic_components;

分割された共有プールの各プールごとの使用状況を確認する

select
    ksmdsidx subpool#,
    decode(ksmssnam, 'free memory', 'free memory', 'used memory') type,
    round(sum(ksmsslen)/1024/1024,1) MB
from x$ksmss
group by ksmdsidx,decode(ksmssnam, 'free memory', 'free memory', 'used memory')
order by ksmdsidx,decode(ksmssnam, 'free memory', 'free memory', 'used memory');
-- subpool# 0 は Reserved Granule領域で各プールに割り当てていき最終的に 0 になる
-- 詳細
select 'sga heap('||ksmchidx||','||(ksmchdur-1)||')' "subpool" ,
       ksmchcls class,
       count(ksmchcls) count,
       sum(ksmchsiz) sum,
       max(ksmchsiz) max,
       avg(ksmchsiz) average
from x$ksmsp
group by 'sga heap('||ksmchidx||','||(ksmchdur-1)||')',
      ksmchcls
order by 1;
  • Class列の意味
    CLASS列説明
    freeフリー・メモリ
    freeablFreeable チャンク
    recrRecreatable チャンク
    permPermanent チャンク
    先頭にRが付いているのは予約済み領域に属するチャンク

共有プールの断片化状況を確認

select
    round(sum(ksmchsiz)/1024,0) FREE_KB,
    count(ksmchsiz) Count,
    round((sum(ksmchsiz)/count(ksmchsiz))/1024,0) AVG_KB,
    round(max(ksmchsiz)/1024,0) MAX_KB
from x$ksmsp where ksmchcls='free';

アドレス毎の断片化状況

select ksmchptr "ADDRESS", ksmchsiz "SIZE" from x$ksmsp
where ksmchcls = 'free' order by 2;

ライブラリキャッシュ内にキャッシュされてるオブジェクト情報(メモリ使用量等)取得

select * from V$DB_OBJECT_CACHE;


PGA情報

ビュー名説明
V$PGA_STATPGAメモリーの統計情報
V$SQL_WORKAREA各SQLカーソルが使用している作業領域情報。使用されてるパス(1PASS、Multi-Pass)の実行回数やメモリーサイズの見積もり
V$SQL_WORKAREA_ACTIVE現在使用されてる各作業領域情報。パスやサイズ、ディスク使用してる場合は一時セグメントのサイズ
V$SQL_WORKAREA_HISTOGRAM作業領域サイズ別の分布情報。最大33に分類された2の累乗サイズ毎のパス別の実行回数
V$PGA_TARGET_ADVICEPGAターゲットサイズを変更した場合にヒット率(最適パスで完了できるか)と過剰割当ての変化
V$PGA_TARGET_ADVICE_HISTOGRAMPGAターゲットサイズを変更した場合に、作業領域サイズ別の分布がどのように変化するか
V$PROCESS_MEMORYプロセス毎のPGA情報。カテゴリ(SQL、PL/SQL、Javaなど)毎に割り当てられたサイズ、使用中サイズ、最大サイズの確認
PGA統計名説明
aggregate PGA target parameterPGA_AGGREGATE_TARGET初期化パラメータの現在値
aggregate PGA auto target作業領域に使用されているPGAメモリーサイズ
global memory boundグローバルメモリー境界(個々の作業領域に割当て可能な最大サイズ)
total PGA inuse現在作業領域として消費されている合計サイズ
total PGA allocated現在割り当てられているPGAターゲットサイズ
maximum PGA allocatedインスタンス起動後に割り当てられたPGA最大サイズ
over allocation countインスタンス起動後に過剰割当てが発生した回数
cache hit percentage現在のヒット率(最適パスで実行できた比率)
システム統計名説明
workarea memory allocated作業領域として現在割り当てられているサイズ
workarea excutions - optimal最適パスで実行された回数
workarea excutions - onepass1パスで実行された回数
workarea excutions - multipassマルチパスで実行された回数

PGA履歴情報

select
    S.begin_interval_time,
    P.INSTANCE_NUMBER,
    P.NAME,
    round(P.VALUE/1024/1024,0)
from DBA_HIST_SNAPSHOT S join DBA_HIST_PGASTAT P on S.INSTANCE_NUMBER= P.INSTANCE_NUMBER and S.SNAP_ID=P.SNAP_ID
and P.NAME in ('total PGA allocated','total PGA inuse','aggregate PGA auto target')
order by S.INSTANCE_NUMBER,P.NAME,S.begin_interval_time ;

プロセス単位

select
    d.spid,
    a.sid,
    d.pid,
    a.program,
    a.machine,
    a.username,b.name,
    trunc(c.value/1024/1024) size_mb,
    a.sql_id, a.prev_sql_id
from v$session a, v$statname b, v$sesstat c, v$process d
where a.sid = c.sid and b.name like 'session%ga%'
      and b.statistic# = c.statistic#
      and a.paddr = d.addr
order by name, size_mb desc;

SQL毎の作業領域使用状況確認

select
    w.inst_id,
    s.sql_fulltext,
    w.operation_type,
    w.estimated_optimal_size,
    w.last_memory_used,
    w.optimal_executions,
    w.onepass_executions,
    w.multipasses_executions
from GV$SQL_WORKAREA w, GV$SQL s
where w.address=s.address and w.child_number = s.child_number and w.inst_id=s.inst_id;


 

ジョブ

ジョブの定義を確認する

column program_name format a30
column schedule_name format a30

select job_name, program_name, schedule_name, stop_on_window_close from dba_scheduler_jobs;
  • schedule_name
    ジョブの実行時間を管理しているスケジュールのグループ名。実際に定義されている時間は以下のsql文で確認。
    column repeat_interval format a80
    column duration format a15
    
    select window_name, repeat_interval, duration from dba_scheduler_windows
    where window_name in ( select window_name from dba_scheduler_wingroup_members where window_group_name = '<スケジュール名>' );
  • stop_on_window_close
    ジョブの実行時間内に処理が終わらない場合、強制的にジョブを終了するか、しないか

実行されるプログラムを確認

column program_action format a60

select program_name, program_type, program_action from dba_scheduler_programs;

ジョブの実行時間を確認

column job_name format a30
column actual_start_date format a45
column run_duration format a15

select job_name, actual_start_date, run_duration from dba_scheduler_job_run_details;


 

表領域

表領域のサイズ

column tablespace_name format a20

select 
    d.tablespace_name, 
    MAXSIZE "MAXSIZE[MB]", 
    NOWSIZE "NOWSIZE[MB]", 
    round(NOWSIZE - FREE) "USED[MB]",
    case when MAXSIZE=0 then 0 else round(((NOWSIZE - FREE)/MAXSIZE)*100,1) end "USED(%)",
    FREE "FREE[MB]"
from
    ( SELECT tablespace_name, round(SUM(MAXBYTES)/(1024*1024)) MAXSIZE ,round(SUM(bytes)/(1024*1024)) NOWSIZE
      FROM dba_data_files GROUP BY tablespace_name) d,
    ( SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) FREE
      FROM dba_free_space GROUP BY tablespace_name) f
where d.tablespace_name=f.tablespace_name
order by tablespace_name;

UNDO表領域のサイズ

select
    to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "TIME",
    A.TABLESPACE_NAME,
    round(A.MAXBYTES/1024/1024,2) "TBS_MAX(MB)",
    round(max(A.BYTES/1024/1024),2) "TBS_SIZE(MB)",
    round(sum(B.BYTES)/1024/1024,2) "USED_SIZE(MB)"
from DBA_DATA_FILES A join DBA_UNDO_EXTENTS B on A.TABLESPACE_NAME = B.TABLESPACE_NAME
where
    A.TABLESPACE_NAME like 'UNDOTBS%' and
    B.STATUS in ('ACTIVE', 'UNEXPIRED')
group by A.TABLESPACE_NAME, A.MAXBYTES;
-- UNDO表領域のステータス確認
select tablespace_name,
       status,
       round(sum(bytes)/1024/1024,3) MB
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name

-- ACTIVE  :使用中
-- UNEXPIRED:解放不可
-- EXPIRED :解放可能(undo_retention 超過)
-- 未使用領域
select
    tablespace_name,
    round(sum(bytes)/1024/1024,3) "FREE(MB)"
from  dba_free_space
where tablespace_name like 'UNDO%'
group by tablespace_name
  • UNDO領域の解放
    • SMONによりDB起動後、12時間毎に実施
    • 各セグメント(トランザクション毎)のExpiredとなったエクステントを解放する(unExpiredが出てくるまでExpired領域を解放)
    • 残存しているExpiredを以下のセグメント数分、オンラインにし、残りは全てオフラインにする
      MAXCONCURRENCY+1 または UNDO表領域を作成した時のUNDOセグメントの初期個数 の大きい数値
      ※MAXCONCURRENCY は v$undostat にて確認(最大トランザクション数)
    • オフラインのUNDOセグメントの解放は、UNDO表領域が圧迫した時のみ実行される(12時間毎の解放はされない)
    • オフラインのUNDOセグメントの確認は DBA_ROLLBACK_SEGS ビュー

  • UNDO獲得
    • カレントのUNDOセグメントの空きブロックを確保
    • カレントのUNDOセグメントのEXPIREDを確保
    • 他の OFFLINE の UNDO セグメントのEXPIREを確保
    • 他の ONLINE の UNDO セグメントのEXPIREを確保

一時表領域のサイズ

ビュー名説明
V$TEMPSEG_USAGE現在使用している一時セグメントに関する情報(アクティブに使用中のみ情報を確認できる)
V$SORT_SEGMENT作成されている一時セグメントに関する情報(一部の列はアクティブに使用中でなくても確認可能)
V$TEMPSTAT一時ファイルに対する読み書きの情報
V$TEMP_EXTENT_POOLキャッシュまたは使用されてる一時領域の容量確認
V$TEMPFILE
DBA_TEMP_FILES
一時表領域で使用しているファイルの確認
DBA_HIST_TEMPSTATXS一時ファイルの読み書きの統計情報
DBA_TEMP_FREE_SPACE一時表領域の割り当てられている容量と、再利用可能な領域を含む空き容量の確認
V$SQL_WORKAREA各SQLカーソルが使用している作業領域情報。使用されてるパス(1PASS、Multi-Pass)の実行回数やメモリーサイズの見積もり
V$SQL_WORKAREA_ACTIVE現在使用されてる各作業領域情報。パスやサイズ、ディスク使用してる場合は一時セグメントのサイズ
select d.tablespace_name
    ,round(a.maxbytes/1024/1024,0) "MAX_SIZE(MB)"
    ,round(a.bytes/1024/1024,0)    "TBL_SIZE(MB)"
    ,round(t.bytes/1024/1024,0)    "USED_SIZE(MB)"
    ,round((a.bytes - t.bytes)/1024/1024, 0) "FREE(MB)"
from DBA_TABLESPACES d,
    (select sum(maxbytes) maxbytes, tablespace_name, sum(bytes) bytes from DBA_TEMP_FILES group by tablespace_name) a,
    (select tablespace_name, sum(bytes_used) bytes from GV$TEMP_EXTENT_POOL group by tablespace_name) t
where d.tablespace_name = a.tablespace_name
    and d.tablespace_name = t.tablespace_name
    and d.contents like 'TEMPORARY';

MAX_SIZE(MB)は、maxbytes が 0(無制限)がある場合は考慮してないので注意

データファイルの使用量

一時表領域で使用しているデータファイルの容量は、DBA_TEMP_FILESビューにて確認する必要がある。

column tablespace_name format a15
column file_name format a60

select b.tablespace_name
    ,b.status
    ,a.file_name
    ,a.online_status
    ,round(a.bytes/(1024*1024),2)        "SIZE[MB]"
    ,a.autoextensible
    ,round(a.increment_by/(1024*1024),3) "INC[MB]"
    ,round(a.maxbytes/(1024*1024),2)     "MAXSIZE[MB]"
from DBA_DATA_FILES a, DBA_TABLESPACES b
where a.tablespace_name=b.tablespace_name
order by 1, 2;
-- SNAP_ID毎のTEMPファイルの書き込みブロック数の確認(DB全体)
select a.snap_id, sum(a.phyblkwrt) - phyblkwrt_aft
from DBA_HIST_TEMPSTATXS a,
    (select snap_id, sum(phyblkwrt) phyblkwrt_aft from DBA_HIST_TEMPSTATXS where tsname='TEMP' group by snap_id) b
where a.tsname='TEMP'
    and a.snap_id=b.snap_id+1
group by a.snap_id,phyblkwrt_aft
order by 1;

断片化確認

表の断片化

select b.owner
    ,b.table_name
    ,round(a.seg_bytes/1024/1024,1) "ALLOCATED_SIZE(MB)"
    ,round(b.tab_bytes/1024/1024,1) "USE_SIZE(MB)"
    ,round((a.seg_bytes - b.tab_bytes)/1024/1024,1) "FREE(MB)"
from
(
    select owner,segment_name,sum(bytes) SEG_BYTES from DBA_SEGMENTS group by owner,segment_name
) A,
(
    select owner,table_name,sum(num_rows*avg_row_len) TAB_BYTES from dba_tables group by owner,table_name
) B
where a.segment_name = b.table_name and b.owner not in ('SYS','SYSTEM')
order by 5 desc;

B*Tree索引断片化確認

-- 階層数が4階層以上になってる索引を抽出
select owner, index_name, blevel from dba_indexes where blevel > 3


 

キャッシュヒット

DBバッファキャッシュヒット率

SELECT
  ROUND(100- 100 * SUM(DECODE(NAME,'physical reads',VALUE,0)) /
   (SUM(DECODE(NAME,'db block gets from cache',VALUE,0)) + 
     SUM(DECODE(NAME,'consistent gets from cache',VALUE,0))), 2)
"DB CACHE HIT RATE(%)"
FROM 
   V$SYSSTAT
WHERE 
   NAME IN
   (
   'physical reads', 'db block gets from cache',
   'consistent gets from cache'
   );

ディクショナリ・キャッシュヒット率(共有プール)

SELECT ROUND(100 * SUM(GETS-GETMISSES) / SUM(GETS), 2) "DICTIONARY HIT RATE(%)"  FROM V$ROWCACHE;

ハードパース・ソフトパース確認

累計値

select vse.sid, vst.name, vse.value
from v$sesstat vse join v$statname vst on vse.statistic# = vst.statistic#
where vst.name in ('parse count (total)', 'parse count (hard)')
    and sid in (select sid from V$SESSION where sql_id is not null)
order by vse.sid;

SQL毎のハードパース

select inst_id, sql_id, sql_text,
   last_load_time,     -- ライブラリキャッシュにロードされた時間(ハードパースされた時間)
   last_active_time,   -- 問い合わせプランが最後にアクティブになった時間
   executions,         -- ライブラリキャッシュにロードされた後に、実行されたSQLの回数(つまり 0 はハードパース)
   cpu_time,           -- CPU処理時間(マイクロ秒)
   elapsed_time        -- 経過時間(マイクロ秒)
from gv$sql
order by sql_id, last_load_time;


ASMディスク

ASMインスタンス接続

su - grid
. oraenv
ORACLE_SID = [grid] ? +ASM1 ※ノード2 の場合は +ASM2

sqlplus / as sysdba
select instance_name from v$instance;

DISKGROUP毎の総容量と空き容量と、OFFLINEになってるDISK総数確認

select
    group_number,  name, state, type, offline_disks
    ,round(total_mb/1024,2) "total(gb)"
    ,round(free_mb/1024,2) "free(gb)"
    ,round(((total_mb-free_mb)/total_mb*100),2) "used(%)"
    ,round(usable_file_mb/1024,2) "usable_file(gb)"
from V$ASM_DISKGROUP;

DISK毎のステータスと、容量の確認

select group_number
    ,disk_number
    ,state,mount_status
    ,mode_status
    ,header_status
    ,failgroup,name
    ,path
    ,total_mb
    ,free_mb
    ,round((total_mb - free_mb)/total_mb*100,2) "userd(%)"
from V$ASM_DISK;

ASMで作成されたファイルと容量確認

-- TYPE毎
select group_number
    ,type
    ,sum(round(bytes/1024/1024,0)) "SIZE[MB]"
    ,sum(round(space/1024/1024,0)) "SPACE[MB]"
from v$ASM_FILE
group by group_number,type
order by group_number,type;
-- FILE毎
select type
      ,a.name                   "FILE_NAME"
      ,trunc(b.space/1024/1024) "ALLOCATE[MB]"
      ,trunc(b.bytes/1024/1024) "USED[MB]"
from V$ASM_ALIAS a, V$ASM_FILE b
where a.group_number = b.group_number
  and a.file_number = b.file_number
  and a.file_incarnation = b.incarnation
order by type;


 

統計情報取得

システム統計(SYSSTAT)

――――――――――――――――――――――――――――――――――――――――――――――――――――――――
SNAP_ID毎の差分集計
※ そのまま実行すると大量のレコードが出力されるので注意、統計名を絞る。副問い合わせはSNAP_IDで範囲を絞る
※ BEGIN_INTERVAL_TIME が 01:00 は 01:00 〜 次のSNAP_IDの時間との差分になる
――――――――――――――――――――――――――――――――――――――――――――――――――――――――
set line 10000 pages 50000 trim on colsep ',' num 30
select a.instance_number
      ,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI') "BEGIN_INTERVAL_TIME"
      ,a.stat_name
      ,a.value - b.value
from DBA_HIST_SYSSTAT a,
     ( select instance_number, snap_id,stat_name, value from DBA_HIST_SYSSTAT
       where stat_name in ('physical reads cache','physical writes')
--         and snap_id > XXXX
     ) b,
     DBA_HIST_SNAPSHOT c
where a.snap_id=b.snap_id+1
      and a.instance_number=b.instance_number
      and a.stat_name = b.stat_name
      and a.snap_id=c.snap_id
      and a.instance_number=c.instance_number
      and a.stat_name in ('physical reads cache','physical writes')
--    and a.snap_id > XXXX
order by 1,3,2;
――――――――――――――
インスタンス毎に集計
――――――――――――――
select to_char(b.begin_interval_time,'YYYY/MM/DD HH24:MI')
     ,a.stat_name
     ,max(case when a.instance_number='1' then value else null end) as ins1
     ,max(case when a.instance_number='2' then value else null end) as ins2
     ,max(case when a.instance_number='3' then value else null end) as ins3
from DBA_HIST_SYSSTAT a, DBA_HIST_SNAPSHOT b
where a.snap_id=b.snap_id
    and a.instance_number=b.instance_number
    and a.stat_name in ('physical reads cache','physical writes')
group by to_char(b.begin_interval_time,'YYYY/MM/DD HH24:MI'), stat_name
order by 2,1
―――――――――――――――――――――――――――――――
統計値毎の集計(キャッシュフュージョンに関する統計値の取得例)
―――――――――――――――――――――――――――――――
select a.instance_number
    ,to_char(b.begin_interval_time,'YYYY/MM/DD HH24:MI') as "TIME"
    ,max(case when a.stat_name='gc cr blocks received' then value else null end)
     + max(case when a.stat_name='gc current blocks received' then value else null end) as "gc XXXX blocks received"
    ,max(case when a.stat_name='gc cr blocks served' then value else null end)
     + max(case when a.stat_name='gc current blocks served' then value else null end) as "gc XXXX blocks served"
    ,max(case when a.stat_name='gcs messages sent' then value else null end) as "gcs messages sent"
from DBA_HIST_SYSSTAT a, DBA_HIST_SNAPSHOT b
where a.snap_id=b.snap_id
    and a.instance_number=b.instance_number
    and a.stat_name in ('gc cr blocks received'
        ,'gc cr blocks served'
        ,'gc current blocks received'
        ,'gc current blocks served'
        ,'gcs messages sent')
group by to_char(b.begin_interval_time,'YYYY/MM/DD HH24:MI'), a.instance_number
order by a.instance_number, to_char(b.begin_interval_time,'YYYY/MM/DD HH24:MI');

―――――――――――――――――――――――――――――――
リアルタイム集計の場合(キャッシュフュージョンに関する統計値)
―――――――――――――――――――――――――――――――
select inst_id
    ,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')
    ,max(case when name='gc cr blocks received' then value else null end)
     + max(case when name='gc current blocks received' then value else null end) as "gc XXXX blocks received"
    ,max(case when name='gc cr blocks served' then value else null end)
     + max(case when name='gc current blocks served' then value else null end) as "gc XXXX blocks served"
    ,max(case when name='gcs messages sent' then value else null end) as "gcs messages sent"
from GV$SYSSTAT
where  name in ('gc cr blocks received','gc cr blocks served','gc current blocks received','gc current blocks served','gcs messages sent')
group by inst_id;
―――――――――――――――――――――――――――――――
トランザクション数の取得(インスタンス毎)
―――――――――――――――――――――――――――――――
SELECT systimestamp
     ,sum(case when INST_ID='1' then VALUE else null end) as ins1
     ,sum(case when INST_ID='2' then VALUE else null end) as ins2
     ,sum(case when INST_ID='3' then VALUE else null end) as ins3
FROM GV$SYSSTAT WHERE NAME in ('user commits','user rollbacks')
group by systimestamp;

―――――――――――――――――――――――――――――――
REDO に関する統計(統計毎) or 条件でSUMしてるので。
―――――――――――――――――――――――――――――――
select systimestamp,inst_id,
    case when name = 'user commits' or name = 'user rollbacks' then 'transaction'
         when name = 'user calls' then 'user calls'
         when name = 'redo writes' then 'redo writes'
         when name = 'redo blocks written' then 'redo blocks written'
    else null end,
    sum(value)
from gv$sysstat
where name in ('user commits','user rollbacks','user calls','redo writes','redo blocks written')
group by systimestamp,inst_id,
    case when name = 'user commits' or name = 'user rollbacks' then 'transaction'
         when name = 'user calls' then 'user calls'
         when name = 'redo writes' then 'redo writes'
         when name = 'redo blocks written' then 'redo blocks written'
    else null end;


待機イベント

――――――――――――――――――――――――――――――――――――――――――――――――
SNAP_ID毎の差分。(END_INTERVAL_TIME が 01:00 は 01:00 より1つ前のSNAPと、01:00 の 差分になる)
――――――――――――――――――――――――――――――――――――――――――――――――
set line 10000 pages 50000 trim on colsep ',' num 30
select
    a.instance_number
    ,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI') "BEGIN_INTERVAL_TIME"
    ,a.event_name
    ,a.total_waits- b.total_waits                    "TOTAL_WAITS"
    ,a.total_timeouts - b.total_timeouts             "TOTAL_TIMEOUTS"
    ,a.time_waited_micro - b.time_waited_micro       "TIME_WAITED_MICRO"
    ,a.total_waits_fg - b.total_waits_fg             "TOTAL_WAITS_FG"
    ,a.total_timeouts_fg - b.total_timeouts_fg       "TOTAL_TIMEOUTS_FG"
    ,a.time_waited_micro_fg - b.time_waited_micro_fg "TIME_WAITED_MICRO_FG"
from DBA_HIST_SYSTEM_EVENT a,
    (select
        instance_number
        ,snap_id
        ,event_name
        ,total_waits
        ,total_timeouts
        ,time_waited_micro
        ,total_waits_fg
        ,total_timeouts_fg
        ,time_waited_micro_fg 
    from DBA_HIST_SYSTEM_EVENT
    where event_name in ('db file sequential read')
--    and snap_id > XXXX
    ) b, DBA_HIST_SNAPSHOT c
where a.snap_id=b.snap_id+1
    and a.snap_id=c.snap_id
    and a.instance_number=c.instance_number
    and a.instance_number=b.instance_number
    and a.event_name = b.event_name
    and a.event_name in ('db file sequential read')
--    and a.snap_id > XXXX
order by 1,2;
――――――――――――
SNAP毎のヒストグラム集計
――――――――――――
select
    a.instance_number
    ,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI') "BEGIN_INTERVAL_TIME"
    ,a.event_name
    ,a.wait_time_milli
    ,a.wait_count - b.wait_count "TOTAL_WAIT_COUNT"
    ,a.wait_time_milli * (a.wait_count - b.wait_count) "TOTAL_WAIT_TIME_MSEC"
from DBA_HIST_EVENT_HISTOGRAM a,
    (select
        instance_number
        ,snap_id
        ,event_name
        ,wait_time_milli
        ,wait_count
      from dba_hist_event_histogram
      where event_name in ('db file sequential read')
--        and snap_id > XXXX
    ) b 
, DBA_HIST_SNAPSHOT c
where a.snap_id=b.snap_id+1 and a.instance_number=b.instance_number
    and a.wait_time_milli=b.wait_time_milli
    and a.snap_id=c.snap_id and a.instance_number=c.instance_number
    and a.event_name = b.event_name
    and a.event_name in ('db file sequential read')
--    and a.snap_id > XXXX
order by 1,2,4;
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
SNAP毎の平均待機時間(ヒストグラムの WAIT_TIME_MILLI * WAIT_COUNT の合計値を WAIT_COUNTの合計値で割ることで平均を求めている)
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
select
    a.instance_number
    ,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI') "BEGIN_INTERVAL_TIME"
    ,a.event_name
    ,sum(a.wait_count - b.wait_count) "TOTAL_WAIT_COUNT"
    ,sum(a.wait_time_milli * (a.wait_count - b.wait_count)) "TOTAL_WAIT_TIME_MSEC"
    ,CASE sum(a.wait_count - b.wait_count)
       WHEN 0 THEN 0
       ELSE round(sum(a.wait_time_milli * (a.wait_count - b.wait_count)) / sum(a.wait_count - b.wait_count),3) 
     END "AVERAGE_WAIT_TIME_MSEC"
from DBA_HIST_EVENT_HISTOGRAM a,
    (select
        instance_number
        ,snap_id
        ,event_name
        ,wait_time_milli
        ,wait_count
      from dba_hist_event_histogram
      where event_name in ('db file sequential read')
--        and snap_id > XXXX
    ) b 
, DBA_HIST_SNAPSHOT c
where a.snap_id=b.snap_id+1 and a.instance_number=b.instance_number
    and a.wait_time_milli=b.wait_time_milli
    and a.snap_id=c.snap_id and a.instance_number=c.instance_number
    and a.event_name = b.event_name
    and a.event_name in ('db file sequential read')
--    and a.snap_id > XXXX
group by a.instance_number,to_char(c.begin_interval_time,'YYYY/MM/DD HH24:MI'),a.event_name
order by 1,2;


I/O統計

IOSTAT_FILE、IOSTAT_FUNCTION、SESS_IOなど。

――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
プログラム毎のDISK I/O、ブロック変更数などを確認する(同じプログラムでもSIDが変化する為、統計はクリアされるタイミングがある?)
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
select
  sysdate,
  case when SUBSTRB(b.PROGRAM,0,6) = 'oracle' then SUBSTRB(b.PROGRAM,0,6)
  else b.PROGRAM end,
  sum(a.BLOCK_GETS),
  sum(a.CONSISTENT_GETS),
  sum(a.PHYSICAL_READS),
  sum(a.BLOCK_CHANGES),
  sum(a.CONSISTENT_CHANGES)
from V$SESS_IO a,V$SESSION b
where a.sid=b.sid
group by case when SUBSTRB(b.PROGRAM,0,6) = 'oracle' then SUBSTRB(b.PROGRAM,0,6)
else b.PROGRAM end;


 

その他

トップN分析

select * from
   (select 〜  order by ソートカラム desc)
where rownum <=5;


 

トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-01-11 (水) 23:59:38 (74d)