Oracle Index Reorg 대상 추출 스크립트

Oracle Index Reorg 대상 추출 스크립트

---

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",

        max(o.mtime) "Last.DDL.Time",

        max(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.analyzetime,ip.obj#,ip.analyzetime,i.analyzetime)) "Last.Analyzed",

        max(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,'n/a',ip.obj#,decode(bitand(ip.flags,1024),0,'NO',1024,'YES','n/a')

        ,decode(bitand(i.flags,32),0,'NO',32,'YES','n/a'))) "Compress"

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#)

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

;