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
/

Back to index