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
/

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다