2018.01.10
Transaction internals
目次
Redo Layer
Database Block Address(DBA)
Oracleが提供するDBMS_UTILITYパッケージを利用して、16進数で表示されているDBAを簡単に相対ファイル番号とブロック番号を確認する方法
SQL> set serveroutput on DECLARE l_dba NUMBER := TO_NUMBER ('00C00012','XXXXXXXX'); l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba); l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba); BEGIN DBMS_OUTPUT.PUT_LINE ('File : '||l_file); DBMS_OUTPUT.PUT_LINE ('Block : '||l_block); END;
Appendix of Redo Layer
1.check_redo_scn.sql
col member for a40 set linesize 140 select a.first_change#, a.status, b.member from v$log a, v$logfile b where a.group#=b.group# /
2.dba2_fb.sql
set serveroutput on DECLARE l_dba NUMBER := TO_NUMBER ('&dba','XXXXXXXX'); l_file NUMBER := DBMS_UTILITY. DATA_BLOCK_ADDRESS_FILE (l_dba); l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba); BEGIN DBMS_OUTPUT.PUT_LINE ('File : '||l_file); DBMS_OUTPUT.PUT_LINE ('Block : '||l_block); END; /
3.param.sql(SYS Userで実行)
set pages 0 set heading off set linesize 120 col name for a40 col value for a60 SELECT ksppinm as name, ksppstvl as value FROM sys.x$ksppi x , sys.x$ksppcv y WHERE ( x.indx = y.indx ) AND ksppinm like '%&1%' order by ksppinm /
4.check_flush.sql
select name, value from v$sysstat where name in ('IMU Flushes','IMU commits','redo size','IMU undo allocation size', 'user commits') order by name /
Undo Layer
Appendix of Undo Layer
1) rowid2fb.sql
var v_rowid_type number; var v_object_number number; var v_relative_fno number; var v_block_number number; var v_row_number number; set serveroutput on exec dbms_rowid.rowid_info ('&1', :v_rowid_type, :v_object_number, :v_relative_fno, :v_block_number,:v_row_number); /
2) print.sql
print v_relative_fno print v_block_number /
3) chk_undostat.sql
set linesize 140 select to_char(a.begin_time,'HH24:MI:SS') begin, to_char(a.end_time, 'HH24:MI:SS') end, a.maxquerylen max_q_len, a.maxqueryid max_q_id, a.tuned_undoretention tuned_ur, substr(b.sql_text,1,15) sql_text from v$undostat a, v$sql b where a.maxqueryid=b.sql_id(+) and rownum<=4 /
Enqueue Layer
Appendix of Enqueue Layer
1)v$ lockビュー定義
------------------------------------------------------- - GV$ LOCK定義 ------------------------------------------------------- SELECT s.inst_id、 l.laddr"ADDR"、 l.kaddr"KADDR"、 s.ksusenum「SID」、 r.ksqrsidt"TYPE"、 r.ksqrsid1"ID1"、 r.ksqrsid2「ID2」、 l.lmode"LMODE"、 l.request「REQUEST」、 l.ctime、「CTIME"、 decode(l.lmode、0、0、l.block)"BLOCK" FROM v$_lock l、 x$ ksuse s、 x$ ksqrs r WHERE l.saddr= s.addr AND l.raddr= r.addr
-------------------------------------------------- ----- - GV $ _LOCK定義 -------------------------------------------------- ----- SELECT USERENV( 'Instance')、 laddr、 kaddr、 saddr、 raddr、 lmode、 request、 ctime、 BLOCK FROM v $ _lock1 UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktadm WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktatrfil WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktatrfsl WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktatl WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktstusc WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktstuss WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 addr、 ksqlkadr、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktstusg WHERE bitand(kssobflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0) UNION ALL SELECT inst_id、 ktcxbxba、 ktcxblkp、 ksqlkses、 ksqlkres、 ksqlkmod、 ksqlkreq、 ksqlkctim、 ksqlklblk FROM x $ ktcxb WHERE bitand(ksspaflg、1)!= 0 AND(ksqlkmod!= 0 OR ksqlkreq!= 0)
2) chk_lock.sql
select a.sid, b.object_name, a.type, a.id1, a.id2, a.lmode, a.request, a.block from v$lock a, dba_objects b where a.sid in (&sid.....) and a.type='TM' and a.id1=b.object_id(+) order by sid /
Block Layer
Appendix of Block Layer
1) dba2fb.sql
set feedback off set serveroutput on DECLARE l_dba NUMBER := TO_NUMBER ('&dba','XXXXXXXX'); l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba); l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba); BEGIN DBMS_OUTPUT.PUT_LINE ('alter system dump datafile '||l_file||' block '||l_block||';'); END; / set feedback on
2) hex2chr.sql
select chr(to_number('&1', 'XXXXXXXX')) from dual;
PGA Layer
Appendix of PGA Layer
1. getworarea.sql script
col sql format a13 col est_opt_sz heading "est|opt_sz" format 999.9 col est_one_sz heading "est|one_sz" format 999.9 col last_mem_used heading "last|mem_used" format 999.9 col total_exe heading "total|exe" format 999 col opt_exe heading "opt|exe" format 999 col onepass_exe heading "onepass|exe" format 999 col multipass_exe heading "multipass|exe" format 999 col active_time heading "active|time" format 999.9 col last_tmp_sz heading "last|tmp_sz" format 999 SELECT --SUBSTR( sql_text , 57 , 11 ) AS SQL , -- for sort test SUBSTR( sql_text , 103 , 10 ) AS SQL, -- for hash test ROUND( estimated_optimal_size/1024/1024 , 1 ) AS est_opt_sz , ROUND( estimated_onepass_size/1024/1024 , 1 ) AS est_one_sz , ROUND( last_memory_used/1024/1024 , 1 ) AS last_mem_used , optimal_executions AS opt_exe, onepass_executions AS onepass_exe, multipasses_executions AS multipass_exe, ROUND( active_time/1000000 , 1) AS active_time, ROUND( last_tempseg_size/1024/1024 , 1 ) AS last_tmp_sz FROM v$sql_workarea swa , v$sql sq WHERE swa.address = sq.address AND swa.hash_value = sq.hash_value AND sql_text LIKE 'select count(*) from (select %' order by 4,1 /