/* ckobjectstat.sql
	check stats info on objects of interet
	SDR-Oracle 7/5/02
*/
set verify off
col objtype format a8
col stats_status format a15
col owner format a15 
col pctanal format 999 head PctAnalyzed
col objorder noprint
col table_name format a30
accept objname char default NONE prompt 'What is the table name <none> : '
accept objowner char default ALL prompt 'Who is the table owner <all> : '
select 'Table' objtype, owner, table_name , table_name objorder,
	decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
        nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_tables
where table_name like upper('&objname%') and upper('&objname') != 'NONE'
  and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
union
select ' Index' objtype,owner, index_name, table_name objorder,
        decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
        nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_indexes
where (owner = upper('&objowner') or upper('&objowner') = 'ALL')
  and (table_name like upper('&objname%') and upper('&objname') != 'NONE')
union
select '  Hist' objtype, owner, column_name, table_name objorder, 
	'Buckets' status_status,
	count(*) pctanal
from dba_tab_histograms
where (table_name like upper('&objname%') and upper('&objname') != 'NONE')
  and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
  and endpoint_number > 1 
group by owner, column_name, table_name
union
select ' PartInd' objtype, p.index_owner, i.index_name, i.table_name objorder,
    decode(p.last_analyzed,null,'No Stats',to_char(p.last_analyzed,'mm/dd/yy hh24:mi')),
    nvl(p.sample_size,0)/decode(nvl(p.num_rows,1),0,1,nvl(p.num_rows,1)) * 100 pctanal
from dba_ind_partitions p, dba_indexes i
where p.index_name = i.index_name
and p.index_owner = i.owner
and (i.table_name like upper('&objname%') and upper('&objname') != 'NONE')
and p.partition_position = 1
order by 4, 1 desc,3
/