endpoint_value and endpoint_actual_value

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 16 2006 - 12:06:15 CST