-- E.Nossova, Product TuTool: www.tutool.de.
-- folgende Formeln wurden benutzt:
-- block header size = kcbh + ub4 + ktbbh + ((inittrans - 1)*ktbit) + kdbh,
-- available data space in block = ceil((block size - block header size)*(1 - pctfree/100)) - kdbt (oder ub4),


/* estimates and reports normal indexes that should be rebuilt,
input parameters : 
        index owner, default: all,
        index name, default: all,
        only_without_stats, (Y;N), default: Y,
        sample_percent, 0 - 100, default 10,
        density in %, default: 75.
Attention: a temporary table will be created and 
           finally droped,
           this script can be very expensive!*/
 
set pagesize 10000
set verify off
set feedback off
set linesize 1000
set trim on
set trimspool on
set arraysize 1
 
column index_name format a40
column last_ddl_time  format a13
column x noprint new_value x
column last_analyzed format a13
column compression format a11
column density format a8
column leaf_density format a13
column sample_per noprint new_value sample_per
column sample_p noprint new_value sample_p
 
set term off
 
select decode(user,'SYS','x','x_') x from sys.dual
/
 
alter session set nls_date_format='dd.mm.yyyy'
/

set term on

set serveroutput on

define index_owner='&index_owner'
define index_name='&index_name'
define only_without_stats='&only_without_stats'
define sample_percent='&sample_percent'
define density='&density'

define obj = 'i.obj#'
define objp = 'i.obj#'
define pctf = 'i.pctfree$'
define initr = 'i.initrans'
define last_analyzed = 'nvl(i.analyzetime,to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A'')'

whenever sqlerror exit failure

create global temporary table tutool_index_stats (obj# number, leafcnt number, rowcnt number, avgrln number, blcnt number)
/

whenever sqlerror continue

select decode(to_number('&sample_percent'),null,' sample block (10) ', 0, 'sample block (0.000001) ', 100,'',' sample block (&sample_percent) ') sample_per, decode(to_number('&sample_percent'),null,'10', '0', '0.000001', '&sample_percent') sample_p from sys.dual
/

declare

cursor cur_idx is 
select tau.name table_owner, tao.name table_name, u.name index_owner, o.name index_name, o.obj# index_obj_id
from
sys.ind$  i,
sys.tab$ t,
sys.obj$  o,
sys.user$  u,
sys.obj$ tao,
sys.user$ tau
where
i.type# in (1) and -- exclude special types
i.pctthres$ is null and -- exclude IOT secondary indexes
(i.analyzetime is null or nvl(upper('&only_without_stats'),'Y') = 'N') and
bitand(i.flags, 4096) = 0 and
bitand(o.flags, 128) = 0 and
t.obj# = i.bo# and
o.obj# = &obj and
o.name = nvl('&index_name',o.name) and
o.owner# != 0 and
u.user# = o.owner# and
u.name = nvl('&index_owner',u.name) and
t.obj# = tao.obj# and
tau.user# = tao.owner#;

cursor cur_idx_cols (p_index_owner in varchar2, p_index_name in varchar2) is
select '"'||column_name||'"'column_name
from sys.dba_ind_columns 
where
index_owner = p_index_owner and
index_name = p_index_name
order by column_position;

cursor cur_fidx_cols (p_index_owner in varchar2, p_index_name in varchar2) is
select column_expression column_name
from sys.dba_ind_expressions 
where
index_owner = p_index_owner and
index_name = p_index_name
order by column_position;

cursor cur_subpart (p_index_owner in varchar2, p_index_name in varchar2, p_index_part in varchar2) is
select subpartition_name 
from sys.dba_ind_subpartitions
where
index_owner = p_index_owner and
index_name = p_index_name and
partition_name = p_index_part;

cursor cur_part (p_index_owner in varchar2, p_index_name in varchar2) is
select partition_name 
from sys.dba_ind_partitions
where
index_owner = p_index_owner and
index_name = p_index_name;

v_fidx_flag boolean;
v_subpart_flag boolean;
v_part_flag boolean;
v_sqltext varchar2(32767);
v_sqltext_where varchar2(32767);
v_tbl number;
v_tby number;
v_ubl number;
v_uby number;
v_l1 number;
v_l2 number;
v_l3 number;
v_hwm number;

begin
	for rec_cur_idx in cur_idx loop
		v_hwm := 0;

		v_part_flag := false;

		begin
			for rec_cur_part in cur_part (rec_cur_idx.index_owner, rec_cur_idx.index_name) loop
				v_part_flag := true;
				v_subpart_flag := false;
				
				for rec_cur_subpart in cur_subpart (rec_cur_idx.index_owner, rec_cur_idx.index_name, rec_cur_part.partition_name) loop
					v_subpart_flag := true;
					sys.dbms_space.unused_space(rec_cur_idx.index_owner, rec_cur_idx.index_name, 'INDEX SUBPARTITION', v_tbl, v_tby, v_ubl, v_uby, v_l1, v_l2, v_l3, rec_cur_subpart.subpartition_name);
					v_hwm := v_hwm + (v_tbl - v_ubl);
				end loop;
	
				if not v_subpart_flag then
					sys.dbms_space.unused_space(rec_cur_idx.index_owner, rec_cur_idx.index_name, 'INDEX PARTITION', v_tbl, v_tby, v_ubl, v_uby, v_l1, v_l2, v_l3, rec_cur_part.partition_name);
					v_hwm := v_hwm + (v_tbl - v_ubl);
				end if;
	
			end loop;
	
			if not v_part_flag then
				sys.dbms_space.unused_space(rec_cur_idx.index_owner, rec_cur_idx.index_name, 'INDEX', v_tbl, v_tby, v_ubl, v_uby, v_l1, v_l2, v_l3);
				v_hwm := v_hwm + (v_tbl - v_ubl);
			end if;
		exception when others then
			v_hwm := null;
		end;
	
		v_sqltext := 'insert into tutool_index_stats (obj#, leafcnt, rowcnt, avgrln, blcnt) select /*+ cursor_sharing_exact no_monitoring no_expand ';
                v_sqltext_where := '(';
		v_fidx_flag := false;

		v_sqltext := v_sqltext || 'index_ffs ("'||rec_cur_idx.table_name||'" "'||rec_cur_idx.index_name||'") noparallel_index ("'||rec_cur_idx.table_name||'" "'||rec_cur_idx.index_name||'") */ ';
		v_sqltext := v_sqltext || rec_cur_idx.index_obj_id ||' obj#, ceil(count(distinct sys_op_lbid('||rec_cur_idx.index_obj_id||', ''L'',"'||rec_cur_idx.table_owner||'"."'||rec_cur_idx.table_name||'".ROWID)) * (100/&sample_p)) leafcnt, ceil(count(*) * (100/&sample_p)) rowcnt, avg( ';

		for rec_cur_fidx_cols in cur_fidx_cols(rec_cur_idx.index_owner, rec_cur_idx.index_name) loop
			v_fidx_flag := true;
			v_sqltext := v_sqltext || 'nvl(vsize('||rec_cur_fidx_cols.column_name||'),0) + 1 + ';
			v_sqltext_where := v_sqltext_where ||rec_cur_fidx_cols.column_name||' is not null or ';
		end loop;

		if not v_fidx_flag then
			for rec_cur_idx_cols in cur_idx_cols(rec_cur_idx.index_owner, rec_cur_idx.index_name) loop
				v_sqltext := v_sqltext || 'nvl(vsize('||rec_cur_idx_cols.column_name||'),0) + 1 + ';
				v_sqltext_where := v_sqltext_where ||rec_cur_idx_cols.column_name||' is not null or ';
			end loop;
		end if;

		v_sqltext := substr(v_sqltext, 1, length(v_sqltext) - 3) || ' ) avgrln, '||v_hwm||' blcnt from "'||rec_cur_idx.table_owner||'"."'||rec_cur_idx.table_name||'" &sample_per where '||substr(v_sqltext_where, 1, length(v_sqltext_where) - 4) || ')';

		begin
			execute immediate v_sqltext;
		exception when others then
			null;
		end;

	end loop;
end;
/

prompt Sparse Indexes:
prompt

select /*+ ordered */
  u.name ||'.'|| o.name  index_name,
  to_char(100*(1 - floor( tis.leafcnt -
    (tis.rowcnt * (tis.avgrln + rd.rowid_len + 1)) / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
  )/tis.leafcnt),'990.00') ||'%' density,
  floor( tis.leafcnt -
  (tis.rowcnt * (tis.avgrln + rd.rowid_len + 1))  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
  ) extra_leaf_blocks,
  to_char(100*(tis.leafcnt/tis.blcnt),'990.00') ||'%' leaf_density, 
  tis.leafcnt leaf_blocks,
  tis.blcnt blocks,
  p.value block_size,
  o.mtime last_ddl_time,
  decode(&last_analyzed,to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',&last_analyzed) last_analyzed,
  &compress compression
from
  (select vsize(rowid) rowid_len from sys.dual) rd,
  (select sum(s1) kcbh, sum(s2) ub4, sum(s3) ktbbh, sum(s4) ktbit, sum(s5)kdbh, sum(s6) ub1, sum(s7) sb2, sum(s8) kdbt from 
       ( select type_size s1,0 s2,0 s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KCBH'
         union all 
         select 0 s1,type_size s2,0 s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='UB4'
	 union all
         select 0 s1,0 s2,type_size s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KTBBH'
	 union all
         select 0 s1,0 s2,0 s3,type_size s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KTBIT'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,type_size s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KDBH'
	 union all 
         select 0 s1,0 s2,0 s3,0 s4,0 s5,type_size s6,0 s7,0 s8 from sys.v_$type_size where type='UB1'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,0 s5,0 s6,type_size s7,0 s8 from sys.v_$type_size where type='SB2'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,0 s5,0 s6,0 s7,type_size s8 from sys.v_$type_size where type='KDBT'
	)
  ) tl,
  tutool_index_stats tis,
  sys.ind$  i,
  (select ts#, blocksize value from sys.ts$) p,
  sys.obj$  o,
  sys.user$  u
where
  tis.leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  i.ts# = p.ts# and
  tis.obj# = &obj and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  100*(1 - floor( tis.leafcnt -
  (tis.rowcnt * (tis.avgrln + rd.rowid_len + 1))  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
  )/tis.leafcnt) <= nvl('&density','75') and
  floor( tis.leafcnt - (tis.rowcnt * (tis.avgrln + rd.rowid_len + 1))  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))) >= 0
order by
  3 desc, 2 
/

prompt
prompt Indexes odered by Leaf Density (to determine Indexes with a lot of empty Blocks):
prompt
 
select /*+ ordered */
  u.name ||'.'|| o.name  index_name,
  to_char(100*(tis.leafcnt/tis.blcnt),'990.00') ||'%' leaf_density, 
  tis.leafcnt leaf_blocks,
  tis.blcnt blocks,
  p.value block_size,
  o.mtime last_ddl_time,
  decode(&last_analyzed,to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',&last_analyzed) last_analyzed
from
  (select vsize(rowid) rowid_len from sys.dual) rd,
  tutool_index_stats tis,
  sys.ind$  i,
  (select ts#, blocksize value from sys.ts$) p,
  sys.obj$  o,
  sys.user$  u
where
  tis.leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  i.ts# = p.ts# and
  tis.obj# = &obj and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  100*(tis.leafcnt/tis.blcnt) <= nvl('&density','75')
order by
  2, 3 desc
/

drop table tutool_index_stats
/

set linesize 80
 
undefine rowcnt 
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
undefine index_owner
undefine index_name
undefine only_without_stats
undefine sample_percent
 
set serveroutput off