Skip to content

dba_tab_columns low value and high value – advanced performance tuning article





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.

view source

print?

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 /

view source

print?

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.

view source

print?

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

view source

print?

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
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.