select /*+ ALL_ROWS NO_MERGE(A) NO_MERGE(B) USE_HASH(A B) */a.OWNER "Owner",
a.INDEX_NAME "Index Name",
a.INDEX_TYPE "Index Type",
a.TABLE_OWNER "Table Owner",
a.TABLE_NAME "Table Name",
a.UNIQUENESS "Uniqueness",
DECODE(a.COMPRESSION, 'DISABLED', NULL, 'COMPRESS ' || TO_CHAR(a.PREFIX_LENGTH) ) "Compress",
B.SEG_SIZE "Index Size(KB)",
A.LEAF_BLOCKS "Index Leaf Blocks",
EXT_BLOCKS "Frag Blocks",
"Density",
-- INDEX_INFO "Index Statistics",
a.INI_TRANS "INITRANS",
a.PCT_FREE "PCTFREE",
a.DEGREE "DEGREE",
a.PARTITIONED "Partitioned",
a.NUM_ROWS "NUM Rows",
a.BLEVEL "B-Leval",
a.LEAF_BLOCKS "Leaf Blocks",
a.AVG_LEAF_BLOCKS_PER_KEY "Avg Leaf Blocks/Key",
a.AVG_DATA_BLOCKS_PER_KEY "Avg Data Blocks/Key",
a.CLUSTERING_FACTOR "Clustering Factors",
TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') "Last Analyzed"
FROM (select a.OWNER,
a.INDEX_NAME,
a.INDEX_TYPE,
a.TABLE_OWNER,
a.TABLE_NAME,
TABLE_TYPE,
UNIQUENESS,
COMPRESSION,
PREFIX_LENGTH,
INI_TRANS,
PCT_FREE,
DEGREE,
PARTITIONED,
a.NUM_ROWS,
a.BLEVEL,
a.LEAF_BLOCKS,
a.AVG_LEAF_BLOCKS_PER_KEY,
a.AVG_DATA_BLOCKS_PER_KEY,
a.CLUSTERING_FACTOR,
--iRPM.INDEX_COL_LIST2(a.owner, a.INDEX_NAME, a.TABLE_OWNER, a.TABLE_NAME) INDEX_INFO,
LAST_ANALYZED
from dba_indexes a
where a.owner NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'BI',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'HR',
'IX',
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'OE',
'OLAPSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'OWBSYS',
'OWBSYS_AUDIT',
'PM',
'SCOTT',
'SH',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SYS',
'SYSMAN',
'SYSTEM',
'WMSYS',
'XDB',
'XS',
'DVF',
'DVO',
'DVSYS',
'LBACSYS',
'TSMSYS',
'ORANGE',
'QUEST',
'STRMADMIN',
'MIG',
'RMAN')
and a.index_type not like 'LOB%'
and a.index_name not like 'BIN$%' --AND_OWNER_TABNAME_SEARCH1
) A,
(select owner,
segment_name,
SEG_SIZE
from (select /*+ LEEADING(V1) NO_MERGE(V1) USE_HASH(V1 A) */owner,
segment_name,
snaptime,
sum(bytes)/1024 SEG_SIZE ,
RANK() OVER (PARTITION BY owner, segment_name
ORDER BY owner, segment_name, snaptime desc) RNK
from RPMMGR.ILM_DBA_SEGMENTS A
where --
A.SYSTEM_NAME = (select name SYSTEM_NAME from v$database)
AND SNAPTIME = (select max(SNAPTIME) from RPMMGR.ILM_DBA_SEGMENTS B)
and segment_type like 'INDEX%'
and segment_name not like 'BIN$%'
group by owner,
segment_name,
snaptime )
where RNK = 1) B,
(select OWNER,
INDEX_NAME,
ROUND(AVG(
CASE
WHEN DENSITY < 0 THEN 0
WHEN DENSITY > 1 THEN 1
ELSE DENSITY
END) , 2) "Density",
SUM(
CASE
WHEN EXT_BLOCKS < 0 THEN 0
ELSE EXT_BLOCKS
END) EXT_BLOCKS
FROM (select /* ordered */u.name OWNER,
o.name INDEX_NAME,
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))) EXT_BLOCKS
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 )
GROUP BY OWNER,
INDEX_NAME) C
WHERE A.OWNER = B.OWNER(+)
AND A.INDEX_NAME = B.SEGMENT_NAME(+)
AND A.OWNER = C.OWNER(+)
AND A.INDEX_NAME = C.INDEX_NAME(+)
and "Density" <=0.8
ORDER BY 8 DESC NULLS LAST,
1,
2,
3,
4
;
select /*+ ALL_ROWS NO_MERGE(A) NO_MERGE(B) USE_HASH(A B) */a.OWNER "Owner",
a.INDEX_NAME "Index Name",
a.INDEX_TYPE "Index Type",
a.TABLE_OWNER "Table Owner",
a.TABLE_NAME "Table Name",
a.UNIQUENESS "Uniqueness",
DECODE(a.COMPRESSION, 'DISABLED', NULL, 'COMPRESS ' || TO_CHAR(a.PREFIX_LENGTH) ) "Compress",
B.SEG_SIZE "Index Size(KB)",
A.LEAF_BLOCKS "Index Leaf Blocks",
EXT_BLOCKS "Frag Blocks",
"Density",
-- INDEX_INFO "Index Statistics",
a.INI_TRANS "INITRANS",
a.PCT_FREE "PCTFREE",
a.DEGREE "DEGREE",
a.PARTITIONED "Partitioned",
a.NUM_ROWS "NUM Rows",
a.BLEVEL "B-Leval",
a.LEAF_BLOCKS "Leaf Blocks",
a.AVG_LEAF_BLOCKS_PER_KEY "Avg Leaf Blocks/Key",
a.AVG_DATA_BLOCKS_PER_KEY "Avg Data Blocks/Key",
a.CLUSTERING_FACTOR "Clustering Factors",
TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') "Last Analyzed"
FROM (select a.OWNER,
a.INDEX_NAME,
a.INDEX_TYPE,
a.TABLE_OWNER,
a.TABLE_NAME,
TABLE_TYPE,
UNIQUENESS,
COMPRESSION,
PREFIX_LENGTH,
INI_TRANS,
PCT_FREE,
DEGREE,
PARTITIONED,
a.NUM_ROWS,
a.BLEVEL,
a.LEAF_BLOCKS,
a.AVG_LEAF_BLOCKS_PER_KEY,
a.AVG_DATA_BLOCKS_PER_KEY,
a.CLUSTERING_FACTOR,
--iRPM.INDEX_COL_LIST2(a.owner, a.INDEX_NAME, a.TABLE_OWNER, a.TABLE_NAME) INDEX_INFO,
LAST_ANALYZED
from dba_indexes a
where a.owner NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'BI',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'HR',
'IX',
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'OE',
'OLAPSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'OWBSYS',
'OWBSYS_AUDIT',
'PM',
'SCOTT',
'SH',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SYS',
'SYSMAN',
'SYSTEM',
'WMSYS',
'XDB',
'XS',
'DVF',
'DVO',
'DVSYS',
'LBACSYS',
'TSMSYS',
'ORANGE',
'QUEST',
'STRMADMIN',
'MIG',
'RMAN')
and a.index_type not like 'LOB%'
and a.index_name not like 'BIN$%' --AND_OWNER_TABNAME_SEARCH1
) A,
(select owner,
segment_name,
SEG_SIZE
from (select /*+ LEEADING(V1) NO_MERGE(V1) USE_HASH(V1 A) */owner,
segment_name,
SYSDATE,
sum(bytes)/1024 SEG_SIZE ,
RANK() OVER (PARTITION BY owner, segment_name
ORDER BY owner, segment_name, SYSDATE desc) RNK
from SYS.DBA_SEGMENTS A
where segment_type like 'INDEX%'
and segment_name not like 'BIN$%'
group by owner,
segment_name,
SYSDATE )
where RNK = 1) B,
(select OWNER,
INDEX_NAME,
ROUND(AVG(
CASE
WHEN DENSITY < 0 THEN 0
WHEN DENSITY > 1 THEN 1
ELSE DENSITY
END) , 2) "Density",
SUM(
CASE
WHEN EXT_BLOCKS < 0 THEN 0
ELSE EXT_BLOCKS
END) EXT_BLOCKS
FROM (select /* ordered */u.name OWNER,
o.name INDEX_NAME,
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))) EXT_BLOCKS
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 )
GROUP BY OWNER,
INDEX_NAME) C
WHERE A.OWNER = B.OWNER(+)
AND A.INDEX_NAME = B.SEGMENT_NAME(+)
AND A.OWNER = C.OWNER(+)
AND A.INDEX_NAME = C.INDEX_NAME(+)
and "Density" <=0.8
ORDER BY 8 DESC NULLS LAST,
1,
2,
3,
4
'Oracle > index' 카테고리의 다른 글
| 인덱스 분포도 (0) | 2022.01.24 |
|---|---|
| partition global index 추출 (0) | 2022.01.24 |
| index invisible (0) | 2022.01.24 |
| index monitoring (0) | 2022.01.24 |
| index(인덱스) (0) | 2022.01.24 |