endpoint_value and endpoint_actual_value
- From: "Jerry Brenner" <jbrenner@xxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 16 Mar 2006 10:06:15 -0800
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:
SELECT h.table_name, h.column_name, data_type, histogram,
endpoint_number, endpoint_value, endpoint_actual_value,
case when (data_type like 'NUMBER%') THEN
to_char(endpoint_value)
WHEN (endpoint_actual_value IS NOT NULL) THEN
endpoint_actual_value ELSE
substr( hexstr( endpoint_value ), 1, 30 )
END
decoded_endpoint_value
FROM user_tab_columns c, user_tab_histograms h
WHERE c.table_name = h.table_name and c.column_name = h.column_name AND
histogram <> 'NONE'
--WHERE table_name = '<tableName>' AND column_name = '<columnName>'
ORDER BY h.table_name, h.column_name, endpoint_number;
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:
SELECT h.table_name, h.column_name, data_type, histogram,
endpoint_number, endpoint_value, endpoint_actual_value,
case when (data_type like 'NUMBER%') THEN
to_char(endpoint_value)
WHEN (endpoint_actual_value IS NOT NULL) THEN
endpoint_actual_value ELSE
substr( hexstr( endpoint_value ), 1, 30 )
END
decoded_endpoint_value, u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds, u.timestamp
FROM user_tab_columns c, user_tab_histograms h, sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
WHERE c.table_name = h.table_name and c.column_name = h.column_name AND
histogram <> 'NONE' AND o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol# and o.name = h.table_name and c.name =
h.column_name
and data_type = 'VARCHAR2'
--WHERE table_name = '<tableName>' AND column_name = '<columnName>'
ORDER BY h.table_name, h.column_name, endpoint_number;
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
Other related posts:
