Oracle中 根據 file# 和 block# 找到對象

標籤：des   style   color   strong   檔案   for   re   c   

我們在10046生產的trace 檔案裡經常看到下面的資訊. 表示系統在等待散列讀取某個檔案號的某個塊開始的8個塊.

WAIT #6: nam=‘db file scattered read‘ ela= 438472 file#=6 block#=2641 blocks=8WAIT #6: nam=‘db file scattered read‘ ela= 1039 file#=6 block#=833 blocks=8 obj#=90054 tim=878243950382WAIT #6: nam=‘db file scattered read‘ ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=878243957168WAIT #6: nam=‘db file scattered read‘ ela= 815 file#=11 block#=7409 blocks=8 obj#=90054 tim=878243966696...
因為我們想要根據file#，block#去找到系統正在讀取哪個object(segment). 

現在主要有三種方式

1. 查詢dba_extents 表,效率比較差.

       select segment_name from dba_extents where and rownum = 1 and file_id = 6 and 2641 between block_id and block_id + blocks - 1;

SEGMENT_NAME--------------------T1_N1Elapsed: 00:02:43.84Statistics----------------------------------------------------------       4676  recursive calls          2  db block gets    4077424  consistent gets       6492  physical reads          0  redo size        418  bytes sent via SQL*Net to client        400  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          5  sorts (memory)          0  sorts (disk)          1  rows processed

2. 查詢v$BH view, 查詢很快,

SQL> desc v$bh
名稱 是否為空白? 類型
----------------------------------------- -------- ----------------------------
FILE# NUMBER
BLOCK# NUMBER
CLASS# NUMBER
STATUS VARCHAR2(10)
XNC NUMBER
FORCED_READS NUMBER
FORCED_WRITES NUMBER
LOCK_ELEMENT_ADDR RAW(4)
LOCK_ELEMENT_NAME NUMBER
LOCK_ELEMENT_CLASS NUMBER
DIRTY VARCHAR2(1)
TEMP VARCHAR2(1)
PING VARCHAR2(1)
STALE VARCHAR2(1)
DIRECT VARCHAR2(1)
NEW CHAR(1)
OBJD NUMBER
TS# NUMBER
LOBID NUMBER
CACHEHINT NUMBER

SQL> select objd, file#,block#,class#,ts#,cachehint,status,dirty from v$bh where file#=2 and block#=59101;

OBJD FILE# BLOCK# CLASS# TS# CACHEHINT STATUS DI
---------- ---------- ---------- ---------- ---------- ---------- -------------------- --
6373 2 59101 1 1 15 xcur N

3. dump出那個block塊,尋找object id.

　alter system dump datafile 6 block 2641;

file which_obj2:

define __FILE = &1
define __BLOCK = &2
alter system dump datafile &__FILE block &__BLOCK;
set serveroutput on

declare
v_dbavarchar2(100);
v_typevarchar2(100);
v_obj_idnumber;
v_obj_namevarchar2(100);
begin
for r in (select column_value as t from table(get_trace_file1)) loop
if regexp_like(r.t, ‘buffer tsn:‘) then
dbms_output.put_line(‘------------------------------------------------‘);
v_dba := regexp_substr(r.t, ‘[[:digit:]]+/[[:digit:]]+‘);
dbms_output.put_line(rpad(‘dba = ‘,20)|| v_dba);
end if;

if regexp_like(r.t, ‘type: 0x([[:xdigit:]]+)=([[:print:]]+)‘) then
v_type := substr(regexp_substr(r.t, ‘=[[:print:]]+‘), 2);
dbms_output.put_line(rpad(‘type = ‘,20)|| v_type);
end if;

if regexp_like(r.t, ‘seg/obj:‘) then
v_obj_id := to_dec(substr(regexp_substr(r.t,
‘seg/obj: 0x[[:xdigit:]]+‘), 12));
select object_name into v_obj_name from all_objects
where data_object_id = v_obj_id;
dbms_output.put_line(rpad(‘object_id = ‘,20)|| v_obj_id);
dbms_output.put_line(rpad(‘object_name = ‘,20)|| v_obj_name);
end if;

if regexp_like(r.t, ‘Objd: [[:digit:]]+‘) then
v_obj_id := substr(regexp_substr(r.t, ‘Objd: [[:digit:]]+‘), 7);
select object_name into v_obj_name from all_objects
where data_object_id = v_obj_id;
dbms_output.put_line(rpad(‘object_id = ‘,20)|| v_obj_id);
dbms_output.put_line(rpad(‘object_name = ‘,20)|| v_obj_name);
end if;

end loop;

dbms_output.put_line(‘------------------------------------------------‘);

end;
/

@which_obj2 6 2641
old 1: alter system dump datafile &__FILE block &__BLOCK
new 1: alter system dump datafile 6 block 2641

System altered.

Elapsed: 00:00:00.01
------------------------------------------------
dba = 6/2641
type = FIRST LEVEL BITMAP BLOCK
object_id = 9005
Elapsed: 00:00:00.045
object_name = T1_N1
------------------------------------------------
PL/SQL procedure successfully completed.