Recommendation to rebuild:
==========================================================================
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Indexes that should be rebuilt - list of indexes with density below 75%</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #ffffff} --> -
</STYLE>" -
BODY "TEXT='#212121'"
--TABLE "WIDTH='70%' BORDER='1'"
set termout off;
set echo off;
set verify off
SET FEEDBACK OFF;
begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/
set echo on
column filename new_val filename
column filename2 new_val filename2
select 'sparse_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.html' filename from v$instance;
select 'rebuild_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.sql' filename2 from v$instance;
set echo off
set define &
spool &filename
column index_name format a40
column part_name format a20
column subpart_name format a20
column last_ddl_time format a13
column x noprint new_value x
column last_analyzed format a13
column compression format a11
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'
alter session set nls_date_format='DD-MON-YYYY';
SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON
select
instance_name as Database_Name,
(SELECT SYS_CONTEXT('USERENV','HOST') FROM dual) as Hostname,
(select sysdate from dual) as Report_created
from
v$instance;
SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON
select /*ORACONF*/ /*+ ordered */
u.name ||'.'|| o.name index_name,
op.subname part_name,
decode(&obj, isp.obj#, o.subname, '') subpart_name,
to_char(100*(1 - floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
)/&leafcnt),'999.00') ||'%' density,
floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
) extra_blocks,
max(o.mtime) last_ddl_time,
decode(max(&last_analyzed),to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',max(&last_analyzed)) last_analyzed,
max(&compress) compression,
(select index_type as index_type from dba_indexes where index_name=o.name and owner=u.name) index_type,
(select sum(bytes/1024/1024) as index_size from dba_segments where segment_name=o.name and owner=u.name) index_size_in_MB
from
sys.ind$ i,
sys.icol$ ic,
( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
union all
select /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
sys.indsubpart$ isp,
( select ts#, blocksize value
from sys.ts$
) p,
sys.hist_head$ h,
sys.obj$ o,
sys.user$ u,
sys.obj$ op
where
i.obj# = ip.bo#(+) and
ip.obj# = isp.pobj#(+) and
&leafcnt > 1 and
i.type# in (1) and -- exclude special types
i.pctthres$ is null and -- exclude IOT secondary indexes
decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
ic.obj# = i.obj# and
h.obj# = i.bo# and
h.intcol# = ic.intcol# and
o.obj# = &obj and
o.owner# != 0 and
u.user# = o.owner# and
op.obj# = &objp
group by
u.name,
o.name,
op.subname,
decode(&obj, isp.obj#, o.subname, ''),
&rowcnt,
&leafcnt,
&initr,
&pctf,
p.value
having
100*(1 - floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
)/&leafcnt) <= nvl('&density','75') and
floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
) > 0
order by
5 desc, 4
/
undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off
set markup html off;
set termout on;
--prompt ORACONF: sparse_tables.html
set termout off;
spool &filename2
SET SERVEROUTPUT OFF
set pages 0
set lines 200
column index_name format a200
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'
select /*ORACONF*/ /*+ ordered */
'alter index '||u.name ||'.'|| o.name||' rebuild online parallel 4;' index_name
from
sys.ind$ i,
sys.icol$ ic,
( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
union all
select /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
sys.indsubpart$ isp,
( select ts#, blocksize value
from sys.ts$
) p,
sys.hist_head$ h,
sys.obj$ o,
sys.user$ u,
sys.obj$ op
where
i.obj# = ip.bo#(+) and
ip.obj# = isp.pobj#(+) and
&leafcnt > 1 and
i.type# in (1) and -- exclude special types
i.pctthres$ is null and -- exclude IOT secondary indexes
decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
ic.obj# = i.obj# and
h.obj# = i.bo# and
h.intcol# = ic.intcol# and
o.obj# = &obj and
o.owner# != 0 and
u.user# = o.owner# and
op.obj# = &objp
group by
u.name,
o.name,
op.subname,
decode(&obj, isp.obj#, o.subname, ''),
&rowcnt,
&leafcnt,
&initr,
&pctf,
p.value
having
100*(1 - floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
)/&leafcnt) <= nvl('&density','75') and
floor( &leafcnt -
&rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
) > 0
/
undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off
begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/
============================================================================
Output : (If you run above sql scrip(any_name.sql). As an output are two files)
Note: please check from the report if all indexes are normal indexes (bit map indexes are not possible to rebuild online)
=> html report (sparse_indexes_<SID_<DATE>.html) -with all indexes with the density below 75%.
=> sql file (rebuild_indexes_<SID>_<DATE>.sql) - you can run it to rebuild indexes