| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: endpoint_value and endpoint_actual_value
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"
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 23 2006 - 21:27:46 CST
 |
 |