我们在10046生产的trace 文件里经常看到下面的信息. 表示系统在等待散列读取某个文件号的某个块开始的8个块.
WAIT #6: nam=‘db file scattered read‘ ela= 438472 file#=6 block#=2641 blocks=8 WAIT #6: nam=‘db file scattered read‘ ela= 1039 file#=6 block#=833 blocks=8 obj#=90054 tim=878243950382 WAIT #6: nam=‘db file scattered read‘ ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=878243957168 WAIT #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_N1
Elapsed: 00:02:43.84
Statistics
----------------------------------------------------------
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_dba varchar2(100);
v_type varchar2(100);
v_obj_id number;
v_obj_name varchar2(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.