태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

File#, Block#로부터 Object(Segment) 정보 얻어내기

오라클 2009.07.09 13:29

Oracle을 사용하다보면 정말 쉬울 것 같은데, 막상 해보면 아주 어려운 작업들이 종종 있다. 대표적인 예가 File#와 Block#로부터 Segment 정보를 얻는 것이다. 아래와 같은 Wait Event 정보가 있다.

   1: WAIT #6: nam='db file scattered read' ela= 438472 file#=6 block#=2641 blocks=8
   2: WAIT #6: nam='db file scattered read' ela= 1039 file#=6 block#=833 blocks=8 obj#=90054 tim=878243950382
   3: WAIT #6: nam='db file scattered read' ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=878243957168
   4: WAIT #6: nam='db file scattered read' ela= 815 file#=11 block#=7409 blocks=8 obj#=90054 tim=878243966696
   5: ...

File#(p1), Block#(p2) 값을 이용해 어떤 Segment에 대한 Disk I/O가 발생하는지 추적하고 싶을 것이다. 대부분의 문서에서는 DBA_EXTENTS 뷰를 이용하는 방법(너무나 당연하게도!)을 제시하고 있다.

하지만 실제로 사용해 보면 아연질색해진다.

   1: UKJA@ukja102> ed which_obj
   2:  
   3: /*
   4: define __FILE = &1
   5: define __BLOCK = &2
   6: 
   7: select segment_name
   8: from dba_extents
   9: where file_id = &__FILE
  10:   and &__BLOCK between block_id and block_id + blocks - 1
  11:             and rownum = 1
  12: ;
  13: 
  14: set echo on
  15: 
  16: */
  17:  
  18: UKJA@ukja102> @which_obj 6 2641
  19:  
  20: SEGMENT_NAME
  21: --------------------
  22: T1_N1
  23:  
  24: Elapsed: 00:02:43.84
  25:  
  26: Statistics
  27: ----------------------------------------------------------
  28:        4676  recursive calls
  29:           2  db block gets
  30:     4077424  consistent gets
  31:        6492  physical reads
  32:           0  redo size
  33:         418  bytes sent via SQL*Net to client
  34:         400  bytes received via SQL*Net from client
  35:           2  SQL*Net roundtrips to/from client
  36:           5  sorts (memory)
  37:           0  sorts (disk)
  38:           1  rows processed

엄청난 Logical Reads와 Physical Reads 값, 그리고 2분 40초에 달하는 수행시간!

이 문제를 해결하기 위해 다양한 편법이 사용되고 있지만 어느 하나 나를 만족시키는 것은 없었다. 그래서 나만의 방법이 고안되었다. 1) File#, Block#을 이용해 Block Dump를 수행하고, 2) Trace 파일을 읽어서 Object ID 정보를 얻은 다음 2) 그 값을 dba_object 뷰와 조인한다.

이 작업을 다음과 같이 완전히 자동화할 수 있다.

   1: UKJA@ukja102> ed which_obj2
   2:  
   3: /*
   4: define __FILE = &1
   5: define __BLOCK = &2
   6: 
   7: alter system dump datafile &__FILE block &__BLOCK;
   8: 
   9: set serveroutput on
  10: 
  11: declare
  12:     v_dba        varchar2(100);
  13:     v_type    varchar2(100);
  14:     v_obj_id        number;
  15:     v_obj_name    varchar2(100);
  16: begin
  17:     for r in (select column_value as t from table(get_trace_file1)) loop
  18:         if regexp_like(r.t, 'buffer tsn:') then
  19:             dbms_output.put_line('------------------------------------------------');
  20:             v_dba := regexp_substr(r.t, '[[:digit:]]+/[[:digit:]]+');
  21:             dbms_output.put_line(rpad('dba = ',20)|| v_dba);
  22:         end if;
  23: 
  24:         if regexp_like(r.t, 'type: 0x([[:xdigit:]]+)=([[:print:]]+)') then
  25:             v_type := substr(regexp_substr(r.t, '=[[:print:]]+'), 2);
  26:             dbms_output.put_line(rpad('type = ',20)|| v_type);
  27:         end if;
  28: 
  29:         if regexp_like(r.t, 'seg/obj:') then
  30:             v_obj_id := to_dec(substr(regexp_substr(r.t,
  31:                             'seg/obj: 0x[[:xdigit:]]+'), 12));
  32:             select object_name into v_obj_name from all_objects
  33:                 where data_object_id = v_obj_id;
  34:             dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
  35:             dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
  36:         end if;
  37: 
  38:         if regexp_like(r.t, 'Objd: [[:digit:]]+') then
  39:             v_obj_id := substr(regexp_substr(r.t, 'Objd: [[:digit:]]+'), 7);
  40:             select object_name into v_obj_name from all_objects
  41:                 where data_object_id = v_obj_id;
  42:             dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
  43:             dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
  44:         end if;
  45: 
  46:     end loop;
  47: 
  48:     dbms_output.put_line('------------------------------------------------');
  49: 
  50: end;
  51: /
  52: 
  53: */
  54:  
  55: UKJA@ukja102> @which_obj2 6 2641
  56: old   1: alter system dump datafile &__FILE block &__BLOCK
  57: new   1: alter system dump datafile 6 block 2641
  58:  
  59: System altered.
  60:  
  61: Elapsed: 00:00:00.01
  62: ------------------------------------------------
  63: dba =               6/2641
  64: type =              FIRST LEVEL BITMAP BLOCK
  65: object_id =         90055
  66: object_name =       T1_N1
  67: ------------------------------------------------
  68: PL/SQL procedure successfully completed.
  69:  
  70: Elapsed: 00:00:00.04

2분 40초의 느리고 짜증나는 작업이 0.05초의 효율적이고 아름다운 작업로 바뀐다.


신고
Trackback 0 : Comments 4
  1. 그와함께 2009.07.10 13:49 신고 Modify/Delete Reply

    흥미롭고 참신한 방법을 알려주셔서 감사합니다. ^^

  2. roka1002 2009.07.20 10:06 신고 Modify/Delete Reply

    아주 편리한 방법이네요. 감사합니다.^^;

  3. 왕만두 2009.07.29 21:37 신고 Modify/Delete Reply

    넘버원이십니다.

  4. Ejql 2010.12.09 13:54 신고 Modify/Delete Reply

    아주 좋은 방법입니다.
    감사합니다.

Write a comment

티스토리 툴바