Transaction internals

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] Redo Layer

[편집] Database Block Address (DBA)

오라클에서 제공하는 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
/