This is an advanced performance tuning article for the new oracle dba explaining about the low value and high value columns found in the dba_tab_columns table/view.
The oracle optimizer uses these columns to guess the number of values that a given predicate/where condition could potentially process.
select column_name, to_char(utl_raw.cast_to_number(low_value)) low, to_char(utl_raw.cast_to_number(high_value)) high
from dba_tab_columns
where table_name = ‘UDNDINNT’
and (column_name = ‘IDND’)
Here is some code to display the HIGH_VALUE/LOW_VALUE columns from USER_TAB_COL_STATISTICS which are stored as RAW datatypes.
01 |
create or replace function display_raw (rawval raw, type varchar2) |
|
02 |
return varchar2 |
|
03 |
is |
|
04 |
cn number; |
|
05 |
cv varchar2(32); |
|
06 |
cd date; |
|
07 |
cnv nvarchar2(32); |
|
08 |
cr rowid; |
|
09 |
cc char(32); |
|
10 |
begin |
|
11 |
if (type = 'NUMBER') then |
|
12 |
dbms_stats.convert_raw_value(rawval, cn); |
|
13 |
return to_char(cn); |
|
14 |
elsif (type = ‘VARCHAR2’) then |
|
15 |
dbms_stats.convert_raw_value(rawval, cv); |
|
16 |
return to_char(cv); |
|
17 |
elsif (type = 'DATE') then |
|
18 |
dbms_stats.convert_raw_value(rawval, cd); |
|
19 |
return to_char(cd); |
|
20 |
elsif (type = ‘NVARCHAR2’) then |
|
21 |
dbms_stats.convert_raw_value(rawval, cnv); |
|
22 |
return to_char(cnv); |
|
23 |
elsif (type = 'ROWID') then |
|
24 |
dbms_stats.convert_raw_value(rawval, cr); |
|
25 |
return to_char(cnv); |
|
26 |
elsif (type = ‘CHAR’) then |
|
27 |
dbms_stats.convert_raw_value(rawval, cc); |
|
28 |
return to_char(cc); |
|
29 |
else |
|
30 |
return ‘UNKNOWN DATATYPE’; |
|
31 |
end if; |
|
32 |
end; |
|
33 |
/ |
01 |
select |
|
02 |
a.column_name, |
|
03 |
display_raw(a.low_value,b.data_type) as low_val, |
|
04 |
display_raw(a.high_value,b.data_type) as high_val, |
|
05 |
b.data_type |
|
06 |
from |
|
07 |
user_tab_col_statistics a, user_tab_cols b |
|
08 |
where |
|
09 |
a.table_name=’FOO’ and |
|
10 |
a.table_name=b.table_name and |
|
11 |
a.column_name=b.column_name |
|
12 |
/ | |
COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------------- ---------------- ---------
ORGANIZATION_ID 00D000000000062 00D300000000tgk CHAR
UG_ID 00500000000008U 00GD0000000mBda CHAR
USERS_ID 005000000000063 00G30000000mBcq CHAR
IS_TRANSITIVE 0 1 CHAR
SUPPRESS_RULES 0 1 CHAR
Decoding high_value and low_value January 3, 2010
Posted by mwidlake in internals, performance.
Tags: data dictionary, performance, SQL
trackback
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.
01 |
-- col_stats |
|
02 |
-- Martin Widlake mdw 21/03/2003 |
|
03 |
-- MDW 11/12/09 enhanced to include more translations of low_value/high_value |
|
04 |
-- pilfered from Gary Myers blog |
|
05 |
col owner form a6 word wrap |
|
06 |
col table_name form a15 word wrap | |
07 |
col column_name form a22 word wrap |
|
08 |
col data_type form a12 | |
09 |
col M form a1 |
|
10 |
col num_vals form 99999,999 | |
11 |
col dnsty form 0.9999 |
|
12 |
col num_nulls form 99999,999 | |
13 |
col low_v form a18 |
14 |
col hi_v form a18 |
15 |
col data_type form a10 |
|
16 |
set lines 110 |
|
17 |
break on owner nodup on table_name nodup |
|
18 |
spool col_stats.lst | |
19 |
select --owner |
|
20 |
-- ,table_name |
|
21 |
column_name |
|
22 |
,data_type | |
23 |
,decode (nullable,'N','Y','N') M |
|
24 |
,num_distinct num_vals | |
25 |
,num_nulls |
|
26 |
,density dnsty | |
27 |
,decode(data_type |
|
28 |
,’NUMBER’ ,to_char(utl_raw.cast_to_number(low_value)) | |
29 |
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value)) |
|
30 |
,’NVARCHAR2′ ,to_char(utl_raw.cast_to_nvarchar2(low_value)) | |
31 |
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value)) |
|
32 |
,’BINARY_FLOAT’ ,to_char(utl_raw.cast_to_binary_float(low_value)) | |
33 |
,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX') |
|
34 |
+to_number(substr(low_value,3,2),’XX’))||’-‘ | |
35 |
||to_number(substr(low_value,5,2),'XX')||'-' |
36 |
||to_number(substr(low_value,7,2),’XX’)||’ ‘ |
37 |
||(to_number(substr(low_value,9,2),'XX')-1)||':' |
|
38 |
||(to_number(substr(low_value,11,2),’XX’)-1)||’:’ | |
39 |
||(to_number(substr(low_value,13,2),'XX')-1) |
|
40 |
, low_value | |
41 |
) low_v |
|
42 |
,decode(data_type | |
43 |
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value)) |
|
44 |
,’VARCHAR2′ ,to_char(utl_raw.cast_to_varchar2(high_value)) | |
45 |
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value)) |
|
46 |
,’BINARY_DOUBLE’,to_char(utl_raw.cast_to_binary_double(high_value)) | |
47 |
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value)) |
|
48 |
,’DATE’,to_char(1780+to_number(substr(high_value,1,2),’XX’) | |
49 |
+to_number(substr(high_value,3,2),'XX'))||'-' |
|
50 |
||to_number(substr(high_value,5,2),’XX’)||’-‘ | |
51 |
||to_number(substr(high_value,7,2),'XX')||' ' |
|
52 |
||(to_number(substr(high_value,9,2),’XX’)-1)||’:’ | |
53 |
||(to_number(substr(high_value,11,2),'XX')-1)||':' |
|
54 |
||(to_number(substr(high_value,13,2),’XX’)-1) | |
55 |
, high_value |
56 |
) hi_v |
57 |
from dba_tab_columns |
|
58 |
where owner like upper(‘&tab_own’) |
|
59 |
and table_name like upper(nvl('&tab_name','WHOOPS')||'%') |
|
60 |
ORDER BY owner,table_name,COLUMN_ID |
|
61 |
/ |
|
62 |
clear colu | |
63 |
spool off |
|
64 |
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
01 |
@col_stats | |
02 |
Enter value for tab_own: dwperf |
|
03 |
old 40: where owner like upper(‘&tab_own’) |
|
04 |
new 40: where owner like upper(‘dwperf’) |
|
05 |
Enter value for tab_name: ad_sql_exec_p |
|
06 |
old 41: and table_name like upper(nvl(‘&tab_name’,’WHOOPS’)||’%’) | |
07 |
new 41: and table_name like upper(nvl(‘ad_sql_exec_p’,’WHOOPS’)||’%’) | |
08 |
any key> |
|
09 |
||
10 |
COLUMN_NAME DATA_TYPE M NUM_VALS NUM_NULLS DNSTY LOW_V HI_V | |
11 |
---------------------- ---------- - ---------- ---------- ------- ------------------ --------------- |
12 |
INST_ID NUMBER Y 4 0 0.2500 1 4 |
13 |
SQL_ID VARCHAR2 Y 87,104 0 0.0000 008d71chrks14 gzw309ttkx862 |
14 |
PLAN_HASH_VALUE NUMBER Y 1,884 0 0.0005 2646229 4294043874 |
15 |
SPC_ID NUMBER N 83 0 0.0120 1076 2269 |
16 |
ADDRESS RAW N 118,198 0 0.0000 00000003E33123A0 0000001342FF3FF8 |
17 |
HASH_VALUE NUMBER N 87,104 0 0.0000 2758710 4294676643 |
18 |
CREATED_DATE DATE N 120 0 0.0083 2009-10-23 8:19:10 2009-12-10 9:19:13 |
19 |
LATEST_FIRST_LOAD_TIME DATE N 11,791 0 0.0001 2009-9-14 11:55:59 2009-12-13 9:33:24 |
20 |
TOTAL_LOADS NUMBER N 98 0 0.0102 1 55047 |
21 |
TOTAL_PARSE_CALLS NUMBER N 92 0 0.0109 0 2972 |
22 |
TOTAL_EXECUTIONS NUMBER N 235 0 0.0043 0 834624 |
23 |
TOTAL_ROWS_PROCESSED NUMBER N 809 0 0.0012 0 26946123 |
24 |
TOTAL_FETCHES NUMBER N 313 0 0.0032 0 834624 |
25 |
TOTAL_BUFFER_GETS NUMBER N 3,016 0 0.0003 0 3355576809 |
26 |
TOTAL_DISK_READS NUMBER N 985 0 0.0010 0 28189240 |
27 |
TOTAL_DIRECT_WRITES NUMBER N 98 0 0.0102 0 751289 |
28 |
TOTAL_SORTS NUMBER N 106 0 0.0094 0 5283 |
29 |
TOTAL_CPU_TIME NUMBER N 94,401 0 0.0000 1337 12183936207 |
30 |
TOTAL_ELAPSED_TIME NUMBER N 115,203 0 0.0000 1337 139692482086 |
31 |
TOTAL_OPTIMIZER_COST NUMBER N 1,467 0 0.0007 0 369740902209315000 | |
32 |
0000000 | |
