Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Decoding block dump using UTL_RAW

with 6 comments

See following block dump.

block_row_dump:
tab 0, row 0, @0x1f3d
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02  <-- Look at this!
col  1: [ 2]  58 31 <-- Look at this!
tab 0, row 1, @0x1f46
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 2]  58 32
...
tab 0, row 9, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 0b
col  1: [ 3]  58 31 30
end_of_block_dump

If you were like me, you would be very annoyed with the encoded raw value of the column. The most convenient way to decode the raw value into the readible real value is using UTL_RAW package.

With ths package, I can automate the conversion for the whole block like following.

UKJA@ukja102> create table t1(c1 number, c2 varchar2(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> insert into t1
  2  select level, 'X'||level
  3  from dual
  4  connect by level <= 10
  5  ;

10 rows created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> col f# new_value fno
UKJA@ukja102> col b# new_value bno
UKJA@ukja102> 
UKJA@ukja102> select dbms_rowid.rowid_relative_fno(rowid) as f#,
  2  			dbms_rowid.rowid_block_number(rowid) as b#
  3  from t1
  4  ;

        F#         B#                                                           
---------- ----------                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           
         6        836                                                           

10 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja102> 
UKJA@ukja102> alter system dump datafile &fno block &bno;
old   1: alter system dump datafile &fno block &bno
new   1: alter system dump datafile          6 block        836

UKJA@ukja102> @decode_block_dump T1    <-- Automate it!

block_row_dump:                                                                 
tab 0, row 0, @0x1f3d                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 02 means C1 = 1                                                
col  1: [ 2]  58 31 means C2 = X1                                               
tab 0, row 1, @0x1f46                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 03 means C1 = 2                                                
col  1: [ 2]  58 32 means C2 = X2                                               
tab 0, row 2, @0x1f4f                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 04 means C1 = 3                                                
col  1: [ 2]  58 33 means C2 = X3                                               
tab 0, row 3, @0x1f58                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 05 means C1 = 4                                                
col  1: [ 2]  58 34 means C2 = X4                                               
tab 0, row 4, @0x1f61                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 06 means C1 = 5                                                
col  1: [ 2]  58 35 means C2 = X5                                               
tab 0, row 5, @0x1f6a                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 07 means C1 = 6                                                
col  1: [ 2]  58 36 means C2 = X6                                               
tab 0, row 6, @0x1f73                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 08 means C1 = 7                                                
col  1: [ 2]  58 37 means C2 = X7                                               
tab 0, row 7, @0x1f7c                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 09 means C1 = 8                                                
col  1: [ 2]  58 38 means C2 = X8                                               
tab 0, row 8, @0x1f85                                                           
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2                                               
col  0: [ 2]  c1 0a means C1 = 9                                                
col  1: [ 2]  58 39 means C2 = X9                                               
tab 0, row 9, @0x1f8e                                                           
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2                                              
col  0: [ 2]  c1 0b means C1 = 10                                               
col  1: [ 3]  58 31 30 means C2 = X10                                           
end_of_block_dump                                                               

where decode_block_dump.sql is as following(it’s not a perfect version!)


define __TABLE_NAME = &1

set serveroutput on

declare
	v_varchar2		varchar2(4000);
	v_number			number;
	col_idx				number;
	col_type			varchar2(200);
	col_name			varchar2(100);
	col_value			varchar2(4000);

begin
	for r in (select column_value as txt from table(get_trace_file1)) loop
		dbms_output.put(r.txt);
		if regexp_like(r.txt, 'col[[:space:]]+[[:digit:]]+:') then 
			
			col_idx := regexp_replace(r.txt, 'col[[:space:]]+([[:digit:]])+: [[:print:]]+', '\1');
			
			select column_name, data_type into col_name, col_type
			from user_tab_cols
			where table_name = upper('&__TABLE_NAME') 
						and column_id = col_idx+1
			;
			
			col_value := replace(regexp_replace(r.txt, 'col[[:space:]]+[[:digit:]]+:[[:space:]]+\[[[:space:]]+[[:digit:]]\][[:space:]]+([[:print:]]+)', '\1'), ' ', '');
			if col_type = 'NUMBER' then
				--dbms_stats.convert_raw_value(col_value, v_number);
				v_number := utl_raw.cast_to_number(col_value);
				dbms_output.put(' means ' || col_name || ' = ' || v_number);
			elsif col_type = 'VARCHAR2' then
				--dbms_stats.convert_raw_value(col_value, v_varchar2);
				v_varchar2 := utl_raw.cast_to_varchar2(col_value);
				dbms_output.put(' means ' || col_name || ' = ' || v_varchar2);
			end if;
		end if;
		
		dbms_output.new_line;
	end loop;
end;
/

set serveroutput off

(the definition of get_trace_file1 is here)

DBMS_STATS.CONVERT_RAW_VALUE procedure provides similar functionality to UTL_RAW.CAST_TO_XXX functions. This procedure is used to convert the raw value in the column statistics into the real value, but there are no such things like DBMS_STATS.CONVERT_VARCHAR2_TO_RAW_VALUE, whereas UTL_RAW package provides the whole functionality.

Now I can read the block dump with a comfort!

About these ads

Written by Dion Cho

July 14, 2009 at 6:20 am

Posted in Misc.

Tagged with

6 Responses

Subscribe to comments with RSS.

  1. BTW, do all of you know that you can use sourcecode tag in the main post and even in the comments, like following?

    select 
    	prefix||
    	type || 
    	' max_rows=' || nrow ||', '||
    	'cur_rows=' || rrow ||', '||
    	'dump=alter system dump datafile ' ||
    			dbms_utility.data_block_address_file(to_dec(dba)) || ' block ' ||
    			dbms_utility.data_block_address_block(to_dec(dba))
    from (
    	select 
    		regexp_substr(column_value, '^[[:space:]]+') as prefix,
    		regexp_substr(column_value, '(branch|leaf)') as type,
    		regexp_replace(regexp_substr(column_value, '(branch:|leaf:) [^ ]+'), 
    							'(branch:|leaf:) 0x', '') as dba,
    		substr(regexp_substr(column_value, 'nrow: [[:digit:]]+'), 7) as nrow,
    		substr(regexp_substr(column_value, 'rrow: [[:digit:]]+'), 7) as rrow
    	from table(get_trace_file1)
    	where regexp_like(column_value, '(branch:|leaf:)')
    )
    ;
    

    http://support.wordpress.com/code/

    What a shame I found it out just a couple of days ago! :(

    Dion Cho

    July 14, 2009 at 6:23 am

  2. you can also use “pre” “/pre” tag for better readability of execution plans. (by the way it took my 1 year to find source code. you are not alone :))

    coskan

    July 14, 2009 at 1:49 pm

  3. I have amended it a bit to use it with index_tree_dumps

    What I changed was
    1- scripts asks 3 parameter OBJECT_NAME OWNER I/D(depending on the dump type)
    2- include index_tree_dump conversion
    3- Changed the regexp_replace search string to cover the needs on index dump and columns with 2 digit length (doesnt work for columns with lenght over 70)
    4- changed to extract only interpreted stack.

    http://docs.google.com/View?id=dgz6znfn_398hfkfhr

    not a nice programming style (never been good with programming) but still works :)

    coskan

    July 16, 2009 at 12:02 pm

  4. Hi Dion,

    Nice post. I have used dbms_stats.convert_raw_value and ended up creating a function called display_raw which takes a value and a type. I created it to display high and low values from column stats, but have found it useful in many other situations.

    It’s nice to be able to just select display_raw(X,Y) from table. I just used it again this week when pulling bind variables out of other_xml column of v$sql_plan.

    Code is very simple (using the sourcecode tag – hope this works):

    create or replace function display_raw (rawval raw, type varchar2)
    return varchar2
    is
       cn     number;
       cv     varchar2(32);
       cd     date;
       cnv    nvarchar2(32);
       cr     rowid;
       cc     char(32);
    begin
       if (type = 'NUMBER') then
          dbms_stats.convert_raw_value(rawval, cn);
          return to_char(cn);
       elsif (type = 'VARCHAR2') then
          dbms_stats.convert_raw_value(rawval, cv);
          return to_char(cv);
       elsif (type = 'DATE') then
          dbms_stats.convert_raw_value(rawval, cd);
          return to_char(cd,'dd-mon-yyyy');
       elsif (type = 'NVARCHAR2') then
          dbms_stats.convert_raw_value(rawval, cnv);
          return to_char(cnv);
       elsif (type = 'ROWID') then
          dbms_stats.convert_raw_value(rawval, cr);
          return to_char(cnv);
       elsif (type = 'CHAR') then
          dbms_stats.convert_raw_value(rawval, cc);
          return to_char(cc);
       else
          return 'UNKNOWN DATATYPE';
       end if;
    end;
    /
    
    select column_name, 
    display_raw(low_value,data_type) low_value, display_raw(high_value,data_type) high_value
    from dba_tab_cols;
    
    

    Kerry

    Kerry Osborne

    July 16, 2009 at 1:41 pm

  5. Guys, these extensions and customizations are what we really like in the area of Oracle – the only DBMS which gives us the maximum freedom.

    @Kerry, the sourcetag-ed SQL statements look pretty in the comment!

    Dion Cho

    July 17, 2009 at 5:10 am

  6. [...] Dion Cho -Decoding block dump using UTL_RAW [...]


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 60 other followers

%d bloggers like this: