ORACLE/TUNING2008. 11. 11. 10:50

Transaction internals

반응형

Transaction internals

 

 

목차

  • 1 Redo Layer
    • 1.1 Database Block Address (DBA)
    • 1.2 Appendix of Redo Layer
  • 2 Undo Layer
    • 2.1 Appendix of Undo Layer
  • 3 Enqueue Layer
    • 3.1 Appendix of Enqueue Layer
  • 4 Block Layer
    • 4.1 Appendix of Block Layer
  • 5 PGA Layer
    • 5.1 Appendix of PGA Layer

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

/

 

 

제공 : DB포탈사이트 DBguide.net

반응형

'ORACLE > TUNING' 카테고리의 다른 글

Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing  (0) 2009.02.03
오라클 hint 사용법  (2) 2008.11.24
옵티마이저의 비용계산 방법과 실행원리  (0) 2008.11.07
접속 부하가 있을때 Multi listener구성하기  (0) 2008.06.12
자동화 통계수집 ＆ AWR ＆ ASH  (0) 2008.02.01
Posted by [PineTree]

티스토리툴바