REM ------------------------------------------------------------------------------------------------
REM #DESC      : Show fragemented indexes and space savings if rebuilt
REM Usage      : Input parameter: none
REM              Run as SYS
REM Description: This script gets info from statistics generated by "analyze statistics" so the 
REM              quality of the result set depends largely on the currency of the CBO statistics.
REM -----------------------------------------------------------------------------------------------

@plusenv

col idxname 	format a30 	head "Owner.Index"
col tabname 	format a30 	head "Owner.Index"
col owner 	format a8 	head "Owner.Index" trunc
col uniq	format a01	head "U"
col tsname	format a15	head "Tablespace" trunc
col sampsz 	format 9999999 head "Sample|Size"
col xtrblk	format 9999999	head "Extra|Blocks"
col lfcnt	format 9999999 head "Leaf|Blocks"
col blk		format 9999999 	head "Curr|Blocks"
col blvl	format 9	head "BL"
col currmb	format 999999	head "Curr|MB"
col newmb	format 99999	head "New|MB"
col pfitbl	format a06	head "PF-I-L"
col it		format 99	head "IT"
col lanal	format a06	head "LastAnal"
col density	format a04	head "Dens%" trunc

break on owner on tabname

select /*+ ordered */ 
  	 u.name  				owner
  	 ,t.table_name  				tabname
  	 ,o.name  				idxname
  	,decode(bitand(i.property, 1), 0,' ', 1, 'x','?') 	uniq
  	,ts.name 						tsname
  	,to_char(i.analyzetime,'YYMMDD') 			lanal
  	,i.samplesize 						sampsz 
  	,rpad(substr(to_char(100 * i.rowcnt * (sum(h.avgcln) + 11) / (i.leafcnt * (p.value - 66 - i.initrans * 24)), '999.00'),2),6,' ') 	density
  	,lpad(i.pctfree$,2,' ')||'-'||rpad(i.initrans,1,' ')||'-'||rpad(i.blevel,1,' ') 							pfitbl
  	,seg.blocks 						blk
  	,i.leafcnt 						lfcnt
  	,floor((1 - i.pctfree$/100) * i.leafcnt - i.rowcnt * (sum(h.avgcln) + 11) / (p.value - 66 - i.initrans * 24)  ) 			xtrblk
  	,round(seg.bytes/(1024*1024)) 				currmb
  	,(1 + i.pctfree$/100) * (i.rowcnt * (sum(h.avgcln) + 11) / (i.leafcnt * (p.value - 66 - i.initrans * 24)) * seg.bytes/(1024*1204)) 	newmb
from 	 sys.ind$  		i
  	,sys.icol$  		ic
  	,sys.hist_head$  	h
  	,(select value  value from v$parameter where name = 'db_block_size' )  p -- block size 
  	,sys.obj$  		o
  	,dba_indexes  		t
  	,sys.user$  		u
  	,sys.ts$ 		ts
  	,dba_segments 		seg
where 	 i.leafcnt 		> 1 
and	 i.type# 		in (1,4,6)                 -- exclude special types 
and	 ic.obj# 		= i.obj# 
and	 h.obj# 		= i.bo# 
and  	 h.intcol# 		= ic.intcol# 
and	 o.obj# 		= i.obj# 
and	 t.index_name 		= o.name     
and	 t.owner 		= u.name     
and	 o.owner# 		!= 0 
and	 u.user# 		= o.owner# 
and	 i.ts# 			= ts.ts# 
and	 u.name 		= seg.owner 
and	 o.name 		= seg.segment_name 
and 	 seg.blocks 		> i.leafcnt		-- if i.leafcnt > seg.blocks then statistics are not up-to-date
group by 
  	 u.name
  	 ,t.table_name
  	,decode(bitand(i.property, 1), 0,' ', 1, 'x','?')
  	,ts.name
  	,to_char(i.analyzetime,'YYMMDD')
  	,i.samplesize
  	,o.name
  	,i.rowcnt
  	,i.leafcnt
  	,i.initrans
  	,i.pctfree$
  	,p.value
  	,i.blevel
  	,i.leafcnt
  	,seg.bytes
  	,i.pctfree$
  	,i.initrans
  	,seg.blocks
having 	 50 * i.rowcnt * (sum(h.avgcln) + 11) < (i.leafcnt * (p.value - 66 - i.initrans * 24)) * (50 - i.pctfree$) 
and 	 floor((1 - i.pctfree$/100) * i.leafcnt - i.rowcnt * (sum(h.avgcln) + 11) / (p.value - 66 - i.initrans * 24)  ) > 0 
order by 1,2,12;