Decoding block dump using UTL_RAW
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!
BTW, do all of you know that you can use sourcecode tag in the main post and even in the comments, like following?
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
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
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
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
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
[...] Dion Cho -Decoding block dump using UTL_RAW [...]
Blogroll Report 10/07/2009 – 17/07/2009 « Coskan’s Approach to Oracle
July 17, 2009 at 3:09 pm