Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Object name from file# and block#

with 13 comments

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.

About these ads

Written by Dion Cho

July 6, 2009 at 3:52 am

Posted in Misc., Troubleshooting

Tagged with ,

13 Responses

Subscribe to comments with RSS.

  1. Dion,

    When I tried to simulate the above script in one of my development database I ran into following errors:

    @which_object 5 571401
    old 1: alter system dump datafile &__FILE block &__BLOCK
    new 1: alter system dump datafile 5 block 571401

    System altered.

    for r in (select column_value as t from table(get_trace_file1)) loop
    *
    ERROR at line 7:
    ORA-06550: line 7, column 47:
    PL/SQL: ORA-00904: “GET_TRACE_FILE1″: invalid identifier
    ORA-06550: line 7, column 11:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 8, column 18:
    PLS-00364: loop index variable ‘R’ use is invalid
    ORA-06550: line 8, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 14, column 18:
    PLS-00364: loop index variable ‘R’ use is invalid
    ORA-06550: line 14, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 19, column 18:
    PLS-00364: loop index variable ‘R’ use is invalid
    ORA-06550: line 19, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 28, column 18:
    PLS-00364: loop index variable ‘R’ use is invalid
    ORA-06550: line 28, column 3:
    PL/SQL: Statement ignored

    Syed Jaffar Hussain

    July 6, 2009 at 7:00 am

  2. @Syed

    It is the story of another post

    http://dioncho.wordpress.com/2009/03/19/another-way-to-use-trace-file/

    @Dion

    Thank you for this super fast idea.

    coskan

    July 6, 2009 at 7:22 am

  3. @Coskan.

    Yes, it’s fast but should be enhanced to work on every kind of blocks. As you know already, Oracle has many kinds(classes) of blocks and each class has different formats.

    Dion Cho

    July 6, 2009 at 7:26 am

  4. @Dion
    You are absolutelly right about the block type.

    Another issue I did not try before I wrote the comment now I need to ask you the definition of to_dec function you used

    coskan

    July 6, 2009 at 3:08 pm

  5. @Coskan

    It’s one of the inter-base conversion functions invented by Tom Kyte.

    http://www.jlcomp.demon.co.uk/faq/base_convert.html

    Dion Cho

    July 6, 2009 at 11:19 pm

  6. [...] Cho, the Oracle Performance Storyteller, looked at attempting to get an object name from file# and block#, and, “ . . . met with a big disappointment on the performance.” [...]

  7. [...] Dion Cho – Object Name from File and Block [...]

  8. Hi Dion,

    When i am running the script on one of my 9.2.0.8 db, its giving the below error

    05:15:08 TEST_17 >@sw 18
    old 34: sid IN (&1)
    new 34: sid IN (18)

    SID STATE EVENT SEQ# SEC_IN_WAIT P1 P2 P3 P1TRAN
    ——- ——————————– —————————————- ———- ———– ———- ———- ———- ——
    18 WAITING db file sequential read 12788 0 19 14477 1

    Elapsed: 00:00:00.00
    05:15:30 TEST_17 >@which_obj 19 14477
    old 1: alter system dump datafile &__FILE block &__BLOCK
    new 1: alter system dump datafile 19 block 14477

    System altered.

    Elapsed: 00:00:01.04
    if regexp_like(r.t, ‘buffer tsn:’) then
    *
    ERROR at line 8:
    ORA-06550: line 8, column 6:
    PLS-00201: identifier ‘REGEXP_LIKE’ must be declared
    ORA-06550: line 8, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 14, column 6:
    PLS-00201: identifier ‘REGEXP_LIKE’ must be declared
    ORA-06550: line 14, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 19, column 6:
    PLS-00201: identifier ‘REGEXP_LIKE’ must be declared
    ORA-06550: line 19, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 28, column 6:
    PLS-00201: identifier ‘REGEXP_LIKE’ must be declared
    ORA-06550: line 28, column 3:
    PL/SQL: Statement ignored

    Anand

    July 12, 2009 at 11:54 pm

  9. @Dion,

    Also what does this “TYPE” shows?

    Regards,
    Anand

    Anand

    July 12, 2009 at 11:59 pm

  10. @Anand

    Unfortuantely, regular expression is avaiable >= 10g.

    TYPE in my demo, is not a feature of the segment, but it’s a feature of the block. FIRST LEVEL BITMAP BLOCK means that this block is the 1st level block of the automatic segment space management.

    Dion Cho

    July 13, 2009 at 3:51 am

  11. if the block is till in memory you can try query:

    col blocks format 99999
    col file_id format 99999
    col file_id format 99999
    col ext_nr format 99999
    col tablespace format a20
    col sub_name format a20
    col name format a20
    col owner format a16
    with subv as ( select u.name owner, o.name SEGMENT_NAME,
           o.subname partition_name, so.object_type,
           s.ts#,
           s.file# relative_fno,
           s.block# header_block
    from sys.user$ u, sys.obj$ o,  sys.sys_objects so, sys.seg$ s
    where s.file# = so.header_file
      and s.block# = so.header_block
      and s.ts# = so.ts_number
      and o.obj# = so.object_id
      and o.owner# = u.user#
      and s.type# = so.segment_type_id
      and o.type# = so.object_type_id
      and s.file# = &&FILE_ID
    order by s.block#
    )
    select * from (
          select ds.owner, ds.SEGMENT_NAME Name, ds.partition_name sub_name,
                ts.name tablespace,
                e.ktfbueblks blocks, e.ktfbuefno file_id,
                e.ktfbueextno ext_nr, e.ktfbuebno block_id
          from
               subv ds, sys.x$ktfbue e, sys.ts$ ts
          where
                 e.ktfbuesegfno = ds.relative_fno
             and e.ktfbuesegbno = ds.header_block
             and e.ktfbuesegtsn = ds.ts#
             and e.ktfbuefno = &&FILE_ID
             and e.KTFBUEBNO > (&BLOCK_ID - ktfbueblks)
             and ts.ts# = ds.ts#
          order by KTFBUEBNO
    ) where rownum =1
    

    Bernard Polarski

    July 13, 2009 at 8:42 am

  12. @Bernad

    With the block existent in memory, there would be no problem, but in most cases, blocks are coming in and going out of the cache. That’s why so many people have tried to solve this problem. The worst I’ve ever seen was to make daily summarized table for DBA_EXTENTS view. I think that my approach is the best performant ever existed. :)

    Anyway, use pre tag when posting code, or you can use the special sourcecode tag as documented here.

    The effectof sourcecode tag is as following:

    select * from dual;
    

    Dion Cho

    July 13, 2009 at 8:59 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: