table stats using table TRACE_INFO
–tab_all_stats
— mdw 30/11/04
— show the stats for a table, it’s columns, it’s indexes…
set veri off pages 64 pause off lines 100
col owner        form A8
col table_name   form A14 word wrap
col avg_sp       form 9,999
col avg_l        form 9999
col avg_sp       form 9,999
col n_buck       form 9,999
col chain        form 9,999
col clustf        form 999,999
col bl           form 999
col blocks       form 999,999
col low_v        form a10 trunc
col hi_v         form a10 trunc
col num_rows     form 99999,999
col dist_keys    form 99999,999
col l_blks       form 99999,999
col num_nulls    form 99,999
col column_name  form a20 word wrap
col index_name   form a15 word wrap
col buckno       form 99,999,999,999
col end_val      form 9,999,999,999,999
col end_act_val  form A10
select owner
, table_name
, num_rows
, blocks
, avg_space  avg_sp
, chain_cnt  chain
, avg_row_len avg_l
, global_stats gls
, user_stats   uls
, to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
, sample_size  samp_size
from dba_tables
where table_name = ‘TRACE_INFO’
/
select index_name
,substr(index_type,1,3)Â Â typ
,substr(uniqueness,1,3)Â Â unq
,blevel                  bl
,leaf_blocks             l_blks
,distinct_keys           dist_keys
,clustering_factor       clustf
,avg_leaf_blocks_per_key lb_key
,avg_data_blocks_per_key db_key
,to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
from dba_indexes
where table_name = ‘TRACE_INFO’
/
—
select– owner
–,table_name
column_name
,num_distinct
,low_value   low_v
,high_value  hi_v
,num_nulls   n_nulls
,num_buckets n_buck
,avg_col_len avg_l
from dba_tab_columns
where table_name = ‘TRACE_INFO’
order by column_id
/
select
column_name
,endpoint_value end_val
,endpoint_number buckno
,endpoint_actual_value end_act_val
from all_tab_histograms
where table_name = ‘TRACE_INFO’
order by table_name,column_name,endpoint_number
/
—
clear colu
Discussion ¬