Oracle パフォーマンスチューニング用小道具

更新サイズ把握

UNDOセグメント（対象セッションが現在使っているUNDO領域サイズ）確認

select s.INST_ID,s.SID,s.SERIAL#,s.USERNAME,used_ublk,t.USED_UREC,t.STATUS,t.START_DATE
from gv$transaction t inner join gv$session s
on t.ADDR = s.TADDR
;

REDOログサイズ（累積値なので2点で観測必要）確認

select s.STATISTIC# -- 統計番号
,decode(s.CLASS,1,'ユーザ',2,'REDO',4,'エンキュー',8,'キャッシュ',16,'OS',32,'RAC',64,'SQL',128,'デバッグ','???') class_dec
,s.NAME  --統計名
,s.VALUE  -- 統計値
from v$sysstat s where name = 'redo size'
;

待機イベント発生確認

select 
 t.INST_ID,t.SAMPLE_TIME,t.SESSION_ID,t.SESSION_SERIAL#,u.USERNAME
,t.SQL_ID,t.SQL_CHILD_NUMBER,t.SQL_OPNAME,t.SQL_PLAN_OPERATION
,t.SQL_EXEC_START,t.EVENT,t.WAIT_CLASS,t.WAIT_TIME
,t.SESSION_state,t.TIME_WAITED
,t.PROGRAM,t.MODULE,t.ACTION
from GV$ACTIVE_SESSION_HISTORY t left outer join DBA_USERS u
on t.USER_ID = u.USER_ID
where t.SQL_ID = '93txugju8rtkn'
order by t.INST_ID,t.SQL_ID,t.SQL_CHILD_NUMBER,t.SAMPLE_TIME desc
;

キャッシュクリア

データベース・バッファ・キャッシュクリア

ALTER SYSTEM FLUSH BUFFER_CACHE 
;

共有プールクリア

ALTER SYSTEM FLUSH SHARED_POOL
;

「SQL_ID指定で共有プールクリア」を行うSQL出力

select s.INST_ID,s.SQL_ID,s.CHILD_NUMBER
,case when s.CHILD_NUMBER = 0
 then
 'exec sys.dbms_shared_pool.purge('''||s.ADDRESS||','||s.HASH_VALUE||''',''C'');' 
 else
 'exec sys.dbms_shared_pool.purge('''||s.ADDRESS||','||s.HASH_VALUE||''',''C'''||','||s.CHILD_NUMBER||');'
end "プロシージャ" 
from gv$sql s
where s.SQL_ID='ct2g3h4c98fp4'
order by s.INST_ID,s.SQL_ID,s.CHILD_NUMBER
;

上記クリアされた事及びその後を確認する

select s.INST_ID,s.SQL_ID,s.CHILD_NUMBER,s.PLAN_HASH_VALUE,s.LAST_LOAD_TIME,s.LAST_ACTIVE_TIME
,s.EXECUTIONS,s.ELAPSED_TIME,s.CPU_TIME,s.ROWS_PROCESSED,s.FETCHES,s.SORTS
,s.USERS_OPENING,s.USERS_EXECUTING
from GV$SQL s
 where s.SQL_ID = 'ct2g3h4c98fp4'
;

実効プランの確認

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('dw5a4prnbfuw5', 0,format=>'ALL LAST PEEKED_BINDS'))
;