Oracle Performance tuning-Histogram

The maximum and minimum values of Histogram are oracle internal. The values queried by SQL select are encrypted.

For example, all_tab_cols.low_value and all_tab_cols.high_value store the maximum and minimum values of a field in a table. However, if you select the field directly, it is a password and cannot be read.

Oracle provides two methods to solve the problem of reading the maximum and minimum values:

1. utl_raw package

2. dbms_stats package

The following is an application of the utl_raw package. dbms_stats must be called using pl/SQL, so we haven't studied it yet:

Select *
From
(Select owner, table_name, column_name, data_type,
Low_value,
Decode (DATA_TYPE,
'Clob', UTL_RAW.CAST_TO_VARCHAR2 (LOW_VALUE ),
'Blob ', UTL_RAW.CAST_TO_VARCHAR2 (LOW_VALUE ),
'Number', utl_raw.cast_to_number (low_value ),
'Char ', utl_raw.cast_to_varchar2 (low_value ),
'Varchar2', utl_raw.cast_to_varchar2 (low_value ),
'Date', to_date ('01-may-11', 'dd-MON-YYYY '),
Utl_raw.cast_to_varchar2 (low_value) as low_value_text,
High_value,
Decode (data_type,
'Clob', utl_raw.cast_to_varchar2 (high_value ),
'Blob ', UTL_RAW.CAST_TO_VARCHAR2 (high_value ),
'Number', UTL_RAW.CAST_TO_NUMBER (high_value ),
'Char ', UTL_RAW.CAST_TO_VARCHAR2 (high_value ),
'Varchar2', UTL_RAW.CAST_TO_VARCHAR2 (high_value ),
'Date', TO_DATE ('01-JUN-11 ', 'dd-MON-YYYY '),
UTL_RAW.CAST_TO_VARCHAR2 (high_value) AS high_VALUE_TEXT
From all_tab_cols
Where owner = 'SC' and (table_name = 'activitym1 'or table_name = 'propsummarya2' or table_name = 'propsummarym1 ')
)

The Owner and table_name In the instance are all in the self-developed database, not required