Dtabhist (dtabhist.sql)

What This Script Does

This script provides detailed histogram statistics for table columns, essential for understanding data distribution and optimizer behavior. Histograms help the optimizer make better cardinality estimates for skewed data.

The Script

set pagesize 999
set lines 500

col OWNER                  format a20
col TABLE_NAME             format a30
col COLUMN_NAME            format a30
col ENDPOINT_NUMBER        format 99999999999
col ENDPOINT_VALUE         format 999999999999999999999999999999999999
col ENDPOINT_ACTUAL_VALUE  format a100
col num_distinct format 9999999999 head "Nbr|Distinct";
col density format 99.9999999 head "Density";
col num_nulls format 9999999999 head "Nbr|Nulls";
col avg_col_len format 9999999 head "Avg|Col_Len";
col low_value format a15 head 'Low|Value';
col high_value format a15 head 'High|Value';
col sample_size format 999999999 head 'Sample|Size';
col global_stats format a6 head 'Global|Stats';
col user_stats format a5 head 'User|Stats';

select th.owner
      ,th.table_name
      ,th.column_name
      ,tc.histogram
      ,tc.num_distinct
      ,tc.num_buckets
      ,tc.last_analyzed
      ,th.endpoint_number
      ,th.endpoint_value
      ,th.endpoint_actual_value
      ,tc.density
      ,tc.num_nulls
      ,tc.avg_col_len
      ,substr(tc.low_value,1,15) low_value
      ,substr(tc.high_value,1,15) high_value
      ,tc.sample_size
      ,tc.global_stats
      ,tc.user_stats
  from dba_tab_histograms th
      ,dba_tab_cols tc
 where nvl(th.owner,'null') like nvl(upper('&owner'),'%')
   and nvl(th.table_name,'null') like nvl(upper('&table_nm'),'%')
   and nvl(th.column_name,'null') like nvl(upper('&column_nm'),'%')
   and th.owner = tc.owner
   and th.table_name = tc.table_name
   and th.column_name = tc.column_name
order by th.owner, th.table_name, th.column_name, th.endpoint_value
;

Parameters

The script prompts for:

&Schema - owner to filter (use % for all)
&Table - name pattern to filter (use % for all)
&Column - name pattern to filter (use % for all)

Usage

-- Basic usage
@dtabhist.sql

-- When prompted, enter:
-- owner: Schema owner to filter (use % for all)
-- table_nm: Table name pattern to filter (use % for all)
-- column_nm: Column name pattern to filter (use % for all)

Sample Output

                                                                                           Nbr              Nbr                                                 Low             High     Sample Global User
OWNER                TABLE_NAME                     COLUMN_NAME                    HISTOGRAM         Distinct NUM_BUCKETS LAST_ANALYZED ENDPOINT_NUMBER                         ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE      Density       Nulls Col_Len Value           Value             Size Stats  Stats
-------------------- ------------------------------ ------------------------------ --------------- ---------- ----------- ------------- --------------- -------------------------------------- -------------------- ----------- ----------- ------- --------------- --------------- ---------- ------ -----
HR                   EMPLOYEES                      DEPARTMENT_ID                  FREQUENCY               12          12 05-JAN-25               1                                     10 10                     0.0000893           1       3 C10B            C152                 107 YES    NO
                                                                                                                                                         2                                     20 20
                                                                                                                                                         9                                     30 30
                                                                                                                                                        15                                     40 40
                                                                                                                                                        16                                     50 50
                                                                                                                                                        61                                     60 60
                                                                                                                                                        66                                     70 70
                                                                                                                                                       101                                     80 80
                                                                                                                                                       103                                     90 90
                                                                                                                                                       106                                    100 100
                                                                                                                                                       107                                    110 110