Re: endpoint_value and endpoint_actual_value
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 23 Mar 2006 20:27:46 -0700
Jerry,
No suggestion on the first question. But let me turn the 2nd question around:
o Any thoughts on when/why endpoint_actual_value is not null?
My conjecture would be - if the values in
endpoint_value are not unique between different
endpoints. Unless I'm mistaken, that can only
happen for character columns. Here is a testcase:
drop table h1;
create table h1 (
c1 varchar2(8)
, c2 varchar2(8)
, c3 varchar2(8)
, c4 varchar2(8)
, c5 varchar2(8)
);
insert into h1
select to_char(trunc(dbms_random.value(100,999)),'fm000')
, to_char(trunc(dbms_random.value(100,999)),'fm0000')
, to_char(trunc(dbms_random.value(100,999)),'fm00000')
, to_char(trunc(dbms_random.value(100,999)),'fm0000000')
, to_char(trunc(dbms_random.value(100,999)),'fm00000000')
from dba_objects where rownum <= 1000
/
exec
dbms_stats.gather_table_stats(user,'H1',method_opt=>'for
all columns size 254');
break on "table" skip 1 on "column" skip 0
select table_name "table"
, column_name "column"
, endpoint_number "EP"
, endpoint_value "value"
, endpoint_actual_value "actual value"
from dba_histograms h
where owner = sys_context('USERENV','CURRENT_SCHEMA')
and table_name = 'H1'
order by table_name, column_name, endpoint_number
/
You'll see (at least I did on a 10.1 database)
that columns c1 through c3 do have
endpoint_actual_values of NULL, but c4 and c5 do
have not_null endpoint_actual_values:
column EP
value actual value
--------------------- ---------
------------------------------------- -------------
C1 0 255399904680983000000000000000000000
1 255400142365470000000000000000000000
2 255400617734445000000000000000000000
3 255420187090586000000000000000000000
...
column EP
value actual value
--------------------- ---------
------------------------------------- --------------
C2 0 250227905087332000000000000000000000
1 250227905396817000000000000000000000
2 250227906944242000000000000000000000
3 250227984624979000000000000000000000
...
column EP
value actual value
--------------------- ---------
------------------------------------- --------------
C3 0 250207701963919000000000000000000000
1 250207701967546000000000000000000000
2 250207701971173000000000000000000000
3 250207701974799000000000000000000000
...
column EP
value actual value
--------------------- ---------
------------------------------------- --------------
C4 0 250207622737193000000000000000000000 0000100
1 250207622737193000000000000000000000 0000104
2 250207622737193000000000000000000000 0000107
3 250207622737193000000000000000000000 0000108
4 250207622737193000000000000000000000 0000109
5 250207622737198000000000000000000000 0000114
6 250207622737198000000000000000000000 0000119
...
column EP
value actual value
--------------------- ---------
------------------------------------- ---------------
C5 0 250207622737193000000000000000000000 0000100
1 250207622737193000000000000000000000 0000101
2 250207622737193000000000000000000000 0000105
3 250207622737198000000000000000000000 0000110
4 250207622737198000000000000000000000 0000116
5 250207622737198000000000000000000000 0000119
As you can see, the endpoint_values of c4 and c5
can be identical even though the actual values
are different. That is because Oracle does not
use the full value of the character column to
encode an endpoint_value, but only the first n (n seems to be 6).
At 11:06 AM 3/16/2006, you wrote:
We have a tool in our product that displays the
database statistics for all of the tables in the
schema in order to help with remote debugging of
performance problems. Right now, we simply list
out the contents of a histogram in an html
table, but we are trying to get to the point of
including a graphical representation of the
histograms that includes the end point
values. I?m trying to figure out how to
properly decode the values in
endpoint_value. Here?s what I?ve come up with so far:
[snip]
where hexstr() comes from asktom. The real
query will restrict on a single histogram, but
my test query gets the info from all histograms
because I am trying to figure out the decoding
and why endpoint_actual_value can be null for
some varchar2 columns. There?s an old thread
started by Karen Morton about
endpoint_actual_value being null. (I didn?t
include the link because I?m getting
inconsistent results on the search and can?t get
a good link right now.) Based on the response
from Lex (?? the endpoint_actual_value is only
derived and stored in cases where Oracle might
need it ??), I thought that sys.col_usage$ might
have some useful information, so I tried this query:
[snip]
Unfortunately, I can?t make any generalizations
from the results. There are rows for varchar2
columns that have the same values in
sys.col_usage$ and that show differing behavior for endpoint_actual_value.
So, my questions are:
o Are there any suggestions for how to improve the decoding?
o Any additional thoughts on why endpoint_actual_value is null?
Thanks,
Jerry
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
Other related posts: