|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
prompt Execessive Split Index List (block > 1000) prompt _______________________________________________________ prompt select /*+ ordered */ u.name "Owner", o.name "Index", op.subname "Partition", decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,o.subname,'') "SubPartition", (1-floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ) *(sum(h.avgcln)+10) /((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)/100)) )/decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)) "Density", floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ) *(sum(h.avgcln) + 10) /((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)/100))) "Extra.Block" from sys.ind$ i, sys.icol$ ic, ( select obj#,part#,bo#,ts#,rowcnt,leafcnt,initrans,pctfree$,analyzetime,flags from sys.indpart$ union all select 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 decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) > 1 and i.type# in (1) /* exclude special types */ and i.pctthres$ is null /* exclude IOT secondary indexes */ and decode(nvl(isp.obj#,nvl(ip.obj#,i.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# = nvl(isp.obj#,nvl(ip.obj#,i.obj#)) and o.owner# != 0 and u.user# = o.owner# and op.obj# = nvl(ip.obj#,i.obj#) and u.name not in ('SYS','SYSTEM','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','SYSAUX','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL') group by u.name, o.name, op.subname, decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,o.subname,''), decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ), decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt), decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans), decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$), p.value having (1-floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ) *(sum(h.avgcln)+10) /((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)/100)) )/decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) ) <= 0.75 and floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt,ip.obj#,ip.rowcnt,i.rowcnt) *(sum(h.avgcln) + 10) /((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)/100)) ) > 1000 order by 6 desc,5 / |