Object name from file# and block#

Get object name from file# and block#

Above simple request has met with a big disappointment on the performance. For instance, let’s assume that I have following wait events.

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
...

How can I get the object(segment) name from the p1(file#) and p2(block#)? The general and natural solution is querying DBA_EXTENTS view, which turns out to be very under-performing.

UKJA@ukja102> ed which_obj

/*
define __FILE = &1
define __BLOCK = &2

select segment_name
from dba_extents
where file_id = &__FILE
			and &__BLOCK between block_id and block_id + blocks - 1
			and rownum = 1
;

set echo on

*/

UKJA@ukja102> @which_obj 6 2641

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

Bang!

So people started to implement alternatives like 1) making summarized table for DBA_EXTENTS view, or 2) querying X$BH view very quickly, or 3) doing block dump and find the object id from it.

The 3rd alternative seems promising to me, so I made an automized script to get object information from file# and block# like following.

UKJA@ukja102> ed 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;
/

*/

UKJA@ukja102> @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 =         90055
object_name =       T1_N1
------------------------------------------------
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

2 minutes of under-performing job has been replaced with 0.5 second of beautiful result.

Advertisement