*[[Oracle]]/Oracleの状態を確認する [#j506e99e] #contents **SQL実行状態 [#n91385e2] |BGCOLOR(#f0f8ff):''ビュー名''|BGCOLOR(#f0f8ff):''用途''| |[[V$SESSION>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3016.htm]]|SID、SQL_ID、PREV_SQL_ID の確認| |[[V$PROCESS>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2101.htm]]|SPID(OSのPID)の確認| |[[V$SQL>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3043.htm]]|子カーソル(実行計画)毎の統計情報、SQLが終了した時点で更新される。実行中は5秒毎に更新| |[[V$SQLAREA>http://docs.oracle.com/cd/a.snap_idE16338_01/server.112/b56311/dynviews_3064.htm]]|親カーソル(SQL文)毎の統計情報。メモリー内にあり、解析済で、実行準備のできているSQL文に関する統計情報| |[[V$SQLSTATS>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3069.htm]]|SQL文毎にSQLカーソルに関する基本的なパフォーマンス統計情報を表示。&br;V$SQL、V$SQLAREAでも確認できるが統計確認に最適化されている(カーソルが無効になった後も統計表示できる)&br;SQL_FULLTEXT列はCBOL型で複数行で出力されるので注意| |[[V$SQL_SHARED_CURSOR>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3059.htm]]|子カーソルが共有されない理由を確認する| *** SPID/SIDの確認 [#y91e26e8] 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文の取得 [#l54075cf] -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>'; ~ **待機イベント [#c39015ba] |BGCOLOR(#f0f8ff):''ビュー''|BGCOLOR(#f0f8ff):''説明''| |[[V$MUTEX_SLEEP>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2068.htm]]|ミューテックスタイプ毎の待機情報| |[[V$MUTEX_SLEEP_HISTORY>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2069.htm]]|ミューテックスタイプ毎の待機履歴で、ブロッキングセッションの確認も可能| |[[V$LATCH>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2015.htm]]|すべてのラッチ(親ラッチと、子ラッチ)| |[[V$LATCH_CHILDREN>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2016.htm]]|子ラッチに関する統計情報| |V$LATCH_PARENT|親ラッチに関する統計情報| |[[V$LATCH_MISSES>http://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn30110.htm]]|取得できなかったラッチに関する統計情報(ラッチを取得使用とした場所など)| |[[V$LOCK>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2027.htm]]|ロックまたはラッチに対する情報| |[[V$LOCKED_OBJECT>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2030.htm]]|トランザクションが獲得した全てのロック情報| ***待機イベントの確認 [#aca3d09f] 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'; |BGCOLOR(#f0f8ff):''カラム''|BGCOLOR(#f0f8ff):''説明''| |SECONDS_IN_WAIT|現在セッションの待機が開始されるまでに費やした時間(秒)、待機中でない場合は前回の時間&br;※ WAIT_TIME_MICRO、TIME_SINCE_LAST_WAIT_MICRO列の参照を推奨| |WAIT_TIME|0:待機中 1以上:前回の待機時間(1/100秒) -1:前回の待機が1/100秒未満&br;-2:時間取得できない(TIMED_STATISTICSパラメータがfalseになっている)&br;※ WAIT_TIME_MICRO、STATE列 の参照を推奨| |STATE|WAITING :セッションは現在待機中&br;WAITED UNKNOWN TIME:TIMED_STATISTICSパラメータがfalseになっている&br;WAITED SHORT TIME :前回の待機は1/100秒未満だった&br;WAITED KNOWN TIME :前回の待機時間は、WAIT_TIME列で指定される| |WAIT_TIME_MICRO|現在セッションの待機時間(マイクロ秒)、待機中でない場合は、前回の待機時間(マイクロ秒)| |TIME_REMAINING_MICRO|1以上 :現在の待機の残り時間(マイクロ秒)&br;0 :現在の待機はタイムアウトした&br;-1 :セッションは現在の待機内で無期限に待機できる&br;NULL :待機していない| |TIME_SINCE_LAST_WAIT_MICRO|前回の待機終了後の経過時間(マイクロ秒)。セッションが現在待機中の場合、値は0| |P1〜P3|待機イベントの引数。詳細は[[Oracle Database リファレンス11g リリース2(11.2)>http://docs.oracle.com/cd/E16338_01/server.112/b56311/waitevents003.htm]]で確認可能| |PREV_SQL_ID|DML実行しCOMITTしてないSQLは、SQL_ID が空となり、PREV_SQL_ID に 埋め込まれる| #region(PIDも同時に取得する場合) PIDも同時に取得する場合 select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') TIME, s.inst_id, s.status, s.sql_id, p.spid PID, s.sid, s.serial#, s.blocking_session, s.username, s.osuser, s.program, s.machine, to_char(s.logon_time,'YYYY/MM/DD HH24:MI:SS') LOGIN, to_char(s.sql_exec_start,'YYYY/MM/DD HH24:MI:SS') SQL_START, w.wait_class, 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 s, GV$PROCESS p, GV$SESSION_WAIT w where s.paddr = p.addr and s.inst_id = p.inst_id and s.username is not null and s.inst_id = w.inst_id and s.sid = w.sid order by s.inst_id, s.sid; #endregion -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'); ***ロック [#ze5502a5] 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のロックモード |BGCOLOR(#f0f8ff):''LMODE/REQUEST''|BGCOLOR(#f0f8ff):''SQLの例''| |1:NULL|select 〜 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:なし」でない場合、そのセッションは現在更新要求を出しているが、ロックがかかって「待ち」が発生していることを示す~ ~ #br **[[メモリ>Oracle/アーキテクチャ#bf2f4f8c]] [#fd294212] ***[[SGA情報>Oracle/アーキテクチャ#pa00969a]] [#y470ddf4] |BGCOLOR(#f0f8ff):''ビュー名''|BGCOLOR(#f0f8ff):''説明''| |[[V$SGAINFO>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3035.htm]]|SGAサイズに関する情報(現在のサイズ、グラニュル、割り当てられてない空き容量)| |[[V$SGASTAT>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3036.htm]]|SGAコンポーネントに関する詳細情報| |[[V$SGA_DYNAMIC_COMPONENTS>https://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3031.htm]]|動的SGAにて調整されたコンポーネントの現在のサイズに関する情報| |[[V$SGA_RESIZE_OPS>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3033.htm]]|過去800回分のSGAサイズ変更操作(変更前後でのパラメータサイズ)に関する情報| |[[V$SGA_TARGET_ADVICE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3034.htm]]|SGA_TARGET初期化パラメータに関する情報| |BGCOLOR(#f0f8ff):''カラム名''|BGCOLOR(#f0f8ff):''説明''| |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; |BGCOLOR(#f0f8ff):''カラム''|BGCOLOR(#f0f8ff):''説明''|BGCOLOR(#f0f8ff):''カラム''|BGCOLOR(#f0f8ff):''説明''| |&color(#0000ff){FREE_SPACE};|予約領域内の空き領域の合計サイズ|&color(#0000ff){USED_SPACE};|予約領域内で使用領域の合計サイズ| |&color(#0000ff){AVG_FREE_SIZE};|予約領域内の空き領域の平均サイズ|&color(#0000ff){AVG_USED_SIZE};|予約領域内で使用領域の平均サイズ| |&color(#0000ff){FREE_COUNT};|予約領域内の空き領域の数|&color(#0000ff){USED_COUNT};|予約領域内で使用領域の数| |&color(#0000ff){MAX_FREE_SIZE};|予約領域内の最大空き領域サイズ|&color(#0000ff){MAX_USED_SIZE};|予約領域内の最大使用領域サイズ| |&color(#0000ff){REQUESTS};|>|>|空き領域を探す為に予約領域が検索された数| |&color(#0000ff){REQUEST_MISSES};|>|>|予約領域に空きがない為、共有プールのフラッシュが発生した回数| |&color(#0000ff){LAST_MISS_SIZE};|>|>|共有プールフラッシュが発生した時の最後の要求サイズ| |&color(#0000ff){MAX_MISS_SIZE};|>|>|共有プールフラッシュが発生した時の最大要求サイズ| |&color(#0000ff){REQUEST_FAILURES};|>|>|共有プールに空きがない為、ORA-04031が発生した回数| |&color(#0000ff){LAST_FAILURE_SIZE};|>|>|共有プールに空きがない為、ORA-04031が発生した時のサイズ| |&color(#0000ff){ABORTED_REQUEST_THRESHOLD};|>|>|共有プールをフラッシュせずにORA-04031を発生させるしきい値のサイズ| |&color(#0000ff){ABORTED_REQUESTS};|>|>|共有プールをフラッシュせずにORA-04031が発生した回数| |&color(#0000ff){LAST_ABORTED_SIZE};|>|>|共有プールをフラッシュせずにORA-04031が発生した時の最後の要求サイズ| |BGCOLOR(#f0f8ff):''予約領域''|BGCOLOR(#f0f8ff):''条件''|BGCOLOR(#f0f8ff):''REQUEST_MISSES''|BGCOLOR(#f0f8ff):''REQUEST_FAILURES''|BGCOLOR(#f0f8ff):''FREE_SPACE''|BGCOLOR(#f0f8ff):''対処方法''| |未使用|要求サイズが4KB以下|0|0|予約領域サイズの50%以上|予約領域を小さくする| |~|~|増加中|増加中|~|共有プールを大きくする| |使用する|要求サイズが4KB以上|増加中|増加中|-|予約領域を大きくする| ''割り当て余力を確認''~ 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列の意味 |BGCOLOR(#f0f8ff):''CLASS列''|BGCOLOR(#f0f8ff):''説明''| |free|フリー・メモリ| |freeabl|Freeable チャンク| |recr|Recreatable チャンク| |perm|Permanent チャンク| 先頭に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情報>Oracle/アーキテクチャ#h36d1ef9]] [#e1bd07ed] |BGCOLOR(#f0f8ff):''ビュー名''|BGCOLOR(#f0f8ff):''説明''| |[[V$PGASTAT>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2096.htm]]|PGAメモリーの統計情報| |[[V$SQL_WORKAREA>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3061.htm]]|各SQLカーソルが使用している作業領域情報。使用されてるパス(1PASS、Multi-Pass)の実行回数やメモリーサイズの見積もり| |[[V$SQL_WORKAREA_ACTIVE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3062.htm]]|現在使用されてる各作業領域情報。パスやサイズ、ディスク使用してる場合は一時セグメントのサイズ| |[[V$SQL_WORKAREA_HISTOGRAM>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3063.htm]]|作業領域サイズ別の分布情報。最大33に分類された2の累乗サイズ毎のパス別の実行回数| |[[V$PGA_TARGET_ADVICE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2094.htm]]|PGAターゲットサイズを変更した場合にヒット率(最適パスで完了できるか)と過剰割当ての変化| |[[V$PGA_TARGET_ADVICE_HISTOGRAM>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2095.htm]]|PGAターゲットサイズを変更した場合に、作業領域サイズ別の分布がどのように変化するか| |[[V$PROCESS_MEMORY>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2102.htm]]|プロセス毎のPGA情報。カテゴリ(SQL、PL/SQL、Javaなど)毎に割り当てられたサイズ、使用中サイズ、最大サイズの確認| |BGCOLOR(#f0f8ff):''PGA統計名''|BGCOLOR(#f0f8ff):''説明''| |aggregate PGA target parameter|PGA_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|現在のヒット率(最適パスで実行できた比率)| |BGCOLOR(#f0f8ff):''システム統計名''|BGCOLOR(#f0f8ff):''説明''| |workarea memory allocated|作業領域として現在割り当てられているサイズ| |workarea excutions - optimal|最適パスで実行された回数| |workarea excutions - onepass|1パスで実行された回数| |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 in ('session uga memory','session pga memory') -- 使用した最大のメモリ容量も確認したい場合は、session XXX memory max の統計を出力する 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; ~ #br **ジョブ [#rb6b774f] ***ジョブの定義を確認する [#cc2d3736] 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~ ジョブの実行時間内に処理が終わらない場合、強制的にジョブを終了するか、しないか~ ***実行されるプログラムを確認 [#wb2945c7] column program_action format a60 select program_name, program_type, program_action from dba_scheduler_programs; ***ジョブの実行時間を確認 [#j761cc70] 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; ~ #br **表領域 [#v2080111] ***表領域のサイズ [#o6d3bf9f] -- 表領域の最大サイズを元に使用率を計算している -- 現在の表領域のサイズを元に計算する場合は、case文を削除し、round(((NOWSIZE - FREE)/NOWSIZE)*100,1) "USED(%)", に変更すること 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 round(((NOWSIZE - FREE)/NOWSIZE)*100,1) 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表領域のサイズ [#mde6f4eb] 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>https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_4220.htm]] ビュー~ ~ -UNDO獲得 --カレントのUNDOセグメントの空きブロックを確保~ --カレントのUNDOセグメントのEXPIREDを確保~ --他の OFFLINE の UNDO セグメントのEXPIREを確保~ --他の ONLINE の UNDO セグメントのEXPIREを確保~ ***一時表領域のサイズ [#ad4d1411] |BGCOLOR(#f0f8ff):''ビュー名''|BGCOLOR(#f0f8ff):''説明''| |[[V$TEMPSEG_USAGE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3107.htm]]|現在使用している一時セグメントに関する情報(アクティブに使用中のみ情報を確認できる)| |[[V$SORT_SEGMENT>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3041.htm]]|作成されている一時セグメントに関する情報(一部の列はアクティブに使用中でなくても確認可能)| |[[V$TEMPSTAT>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3108.htm]]|一時ファイルに対する読み書きの情報| |[[V$TEMP_EXTENT_POOL>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3103.htm]]|キャッシュまたは使用されてる一時領域の容量確認| |[[V$TEMPFILE>https://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3105.htm]]&br;[[DBA_TEMP_FILES>http://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_5061.htm]]|一時表領域で使用しているファイルの確認| |[[DBA_HIST_TEMPSTATXS>https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_4066.htm]]|一時ファイルの読み書きの統計情報| |[[DBA_TEMP_FREE_SPACE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_5062.htm]]|一時表領域の割り当てられている容量と、再利用可能な領域を含む空き容量の確認| |[[V$SQL_WORKAREA>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3061.htm]]|各SQLカーソルが使用している作業領域情報。使用されてるパス(1PASS、Multi-Pass)の実行回数やメモリーサイズの見積もり| |[[V$SQL_WORKAREA_ACTIVE>http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_3062.htm]]|現在使用されてる各作業領域情報。パスやサイズ、ディスク使用してる場合は一時セグメントのサイズ| 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(無制限)がある場合は考慮してないので注意~ ~ ***データファイルの使用量 [#f5a40d1d] 一時表領域で使用しているデータファイルの容量は、[[DBA_TEMP_FILES>http://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_5061.htm]]ビューにて確認する必要がある。~ 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; ***HWMの確認(ハイウォーターマーク) [#i67cf9e0] select file_name , ceil( (nvl(dehwm.HWM,1)*bsz.BLOCK_SIZE)/1024/1024 ) SMALLEST , ceil( ddf.BLOCKS*bsz.BLOCK_SIZE/1024/1024 ) CURRENT_SIZE , ceil( ddf.BLOCKS*bsz.BLOCK_SIZE/1024/1024 ) - ceil( (nvl(dehwm.HWM,1)*bsz.BLOCK_SIZE)/1024/1024 ) SAVINGS from DBA_DATA_FILES ddf , (select FILE_ID , max(BLOCK_ID + BLOCKS - 1) HWM from DBA_EXTENTS group by FILE_ID ) dehwm , (select to_number(VALUE) block_size from V$PARAMETER where NAME = 'db_block_size') bsz where ddf.FILE_ID = dehwm.FILE_ID(+) order by SAVINGS desc; ※ 元情報:https://techlab.sixsquare.co.jp/archives/1598 ***SYSAUX表領域で使用されてるコンポーネントのサイズ確認 [#q6497667] SELECT SCHEMA_NAME,OCCUPANT_NAME,ROUND(SPACE_USAGE_KBYTES/1024,1) "SPACE_USAGE_MBYTES" FROM V$SYSAUX_OCCUPANTS order by SPACE_USAGE_MBYTES desc; ***断片化確認 [#m2c25bc5] ''表の断片化''~ 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 ~ #br **キャッシュヒット [#z5462c77] ***DBバッファキャッシュヒット率 [#ebbcac09] 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' ); ***ディクショナリ・キャッシュヒット率(共有プール) [#j64f329a] SELECT ROUND(100 * SUM(GETS-GETMISSES) / SUM(GETS), 2) "DICTIONARY HIT RATE(%)" FROM V$ROWCACHE; ***ハードパース・ソフトパース確認 [#m6318562] ''累計値'' 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ディスク [#u154d809] ''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; ~ #br **統計情報取得 [#r4545259] ***システム統計(SYSSTAT) [#gb449c97] ―――――――――――――――――――――――――――――――――――――――――――――――――――――――― 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 に関する統計(統計毎) ――――――――――――――――――――――――――――――― 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; ~ ***待機イベント [#v8d6430c] ―――――――――――――――――――――――――――――――――――――――――――――――― 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統計 [#i8581afe] 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; ~ #br **その他 [#i31486fc] ***トップN分析 [#ie54fd89] select * from (select 〜 order by ソートカラム desc) where rownum <=5; ~ #br