Oracle/Oracleの状態を確認する †SQL実行状態 †
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文の取得 †
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>%';
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>';
select executions, disk_reads, buffer_gets, rows_processed, cpu_time, elapsed_time from V$SQLSTATS where sql_id = '<SQL_ID>'; 待機イベント †
待機イベントの確認 †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';
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
メモリ †SGA情報 †
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;
割り当て余力を確認 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;
共有プールの断片化状況を確認 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情報 †
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')
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;
実行されるプログラムを確認 †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; 表領域 †表領域のサイズ †-- 表領域の最大サイズを元に使用率を計算している -- 現在の表領域のサイズを元に計算する場合は、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表領域のサイズ †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
一時表領域のサイズ †
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; HWMの確認(ハイウォーターマーク) †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表領域で使用されてるコンポーネントのサイズ確認 †SELECT SCHEMA_NAME,OCCUPANT_NAME,ROUND(SPACE_USAGE_KBYTES/1024,1) "SPACE_USAGE_MBYTES" FROM V$SYSAUX_OCCUPANTS order by SPACE_USAGE_MBYTES desc; 断片化確認 †表の断片化 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 に関する統計(統計毎) ――――――――――――――――――――――――――――――― 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; |