Decoding high_value and low_value January 3, 2010
Posted by mwidlake in internals, performance.Tags: data dictionary, performance, SQL
16 comments
There is an update to this post here>
The table DBA_TAB_COLUMNS holds the LOW_VALUE and HIGH_VALUE for columns. This information is potentially very useful to us, especially as when the CBO looks at a WHERE clause on a column for a given value, the High and Low values for that column are considered. If the value in the WHERE clause is outside the known range, the expected number of values to be found is decreased in proportion to the distance outside the range.
What is not so helpful is that Oracle stores, and displays, the information in an internal raw format. Which is utterly unhelpful to us of course. Why can’t they at least expose the information in a view in a human-readable format? Tsch.
So here is some code on how to decode low_value and high_value. I pulled most of this together a few years back but always struggled to decode the low and high values for dates, until I found this post by Gary Myers.
-- col_stats -- Martin Widlake mdw 21/03/2003 -- MDW 11/12/09 enhanced to include more translations of low_value/high_value -- pilfered from Gary Myers blog col owner form a6 word wrap col table_name form a15 word wrap col column_name form a22 word wrap col data_type form a12 col M form a1 col num_vals form 99999,999 col dnsty form 0.9999 col num_nulls form 99999,999 col low_v form a18 col hi_v form a18 col data_type form a10 set lines 110 break on owner nodup on table_name nodup spool col_stats.lst select --owner -- ,table_name column_name ,data_type ,decode (nullable,'N','Y','N') M ,num_distinct num_vals ,num_nulls ,density dnsty ,decode(data_type ,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value)) ,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value)) ,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value)) ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value)) ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value)) ,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX') +to_number(substr(low_value,3,2),'XX'))||'-' ||to_number(substr(low_value,5,2),'XX')||'-' ||to_number(substr(low_value,7,2),'XX')||' ' ||(to_number(substr(low_value,9,2),'XX')-1)||':' ||(to_number(substr(low_value,11,2),'XX')-1)||':' ||(to_number(substr(low_value,13,2),'XX')-1) , low_value ) low_v ,decode(data_type ,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value)) ,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value)) ,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value)) ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value)) ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value)) ,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX') +to_number(substr(high_value,3,2),'XX'))||'-' ||to_number(substr(high_value,5,2),'XX')||'-' ||to_number(substr(high_value,7,2),'XX')||' ' ||(to_number(substr(high_value,9,2),'XX')-1)||':' ||(to_number(substr(high_value,11,2),'XX')-1)||':' ||(to_number(substr(high_value,13,2),'XX')-1) , high_value ) hi_v from dba_tab_columns where owner like upper('&tab_own') and table_name like upper(nvl('&tab_name','WHOOPS')||'%') ORDER BY owner,table_name,COLUMN_ID / clear colu spool off clear breaks
Most of the translation is done via the utl_raw.cast_to_datatype functions but no such function is provided for dates, which is where most of us come unstuck. Gary recognised that the data was being stored in hex format, with an offset for the year.
I hope the script helps some of you.
{There are also functions under DBMS_STATS, DBMS_STATS.CONVERT_RAW_VALUES, that can also be called to translate many datatypes}
If anyone knows how to decode timestamps, I’d love to know as it would save me trying to work it out/find it on the Net somewhere. I’ll add it to the post so that there is one place to find all translatations.
Here is a quick output for a test table
@col_stats Enter value for tab_own: dwperf old 40: where owner like upper('&tab_own') new 40: where owner like upper('dwperf') Enter value for tab_name: ad_sql_exec_p old 41: and table_name like upper(nvl('&tab_name','WHOOPS')||'%') new 41: and table_name like upper(nvl('ad_sql_exec_p','WHOOPS')||'%') any key> COLUMN_NAME DATA_TYPE M NUM_VALS NUM_NULLS DNSTY LOW_V HI_V ---------------------- ---------- - ---------- ---------- ------- ------------------ --------------- INST_ID NUMBER Y 4 0 0.2500 1 4 SQL_ID VARCHAR2 Y 87,104 0 0.0000 008d71chrks14 gzw309ttkx862 PLAN_HASH_VALUE NUMBER Y 1,884 0 0.0005 2646229 4294043874 SPC_ID NUMBER N 83 0 0.0120 1076 2269 ADDRESS RAW N 118,198 0 0.0000 00000003E33123A0 0000001342FF3FF8 HASH_VALUE NUMBER N 87,104 0 0.0000 2758710 4294676643 CREATED_DATE DATE N 120 0 0.0083 2009-10-23 8:19:10 2009-12-10 9:19:13 LATEST_FIRST_LOAD_TIME DATE N 11,791 0 0.0001 2009-9-14 11:55:59 2009-12-13 9:33:24 TOTAL_LOADS NUMBER N 98 0 0.0102 1 55047 TOTAL_PARSE_CALLS NUMBER N 92 0 0.0109 0 2972 TOTAL_EXECUTIONS NUMBER N 235 0 0.0043 0 834624 TOTAL_ROWS_PROCESSED NUMBER N 809 0 0.0012 0 26946123 TOTAL_FETCHES NUMBER N 313 0 0.0032 0 834624 TOTAL_BUFFER_GETS NUMBER N 3,016 0 0.0003 0 3355576809 TOTAL_DISK_READS NUMBER N 985 0 0.0010 0 28189240 TOTAL_DIRECT_WRITES NUMBER N 98 0 0.0102 0 751289 TOTAL_SORTS NUMBER N 106 0 0.0094 0 5283 TOTAL_CPU_TIME NUMBER N 94,401 0 0.0000 1337 12183936207 TOTAL_ELAPSED_TIME NUMBER N 115,203 0 0.0000 1337 139692482086 TOTAL_OPTIMIZER_COST NUMBER N 1,467 0 0.0007 0 369740902209315000 0000000