索引分析
select
idx.owner owner,
idx.table_name tablename,
idx.index_name index_name,
idx.blocks idx_blocks,
tbl.blocks tbl_blocks,
trunc(idx.blocks/tbl.blocks*100)/100 pct
from
(select i.owner owner ,i.index_name index_name,
SUM(S1.blocks) blocks,i.table_owner table_owner,
i.table_name table_name
from dba_segments s1,dba_indexes i
where
s1.owner=i.owner and s1.segment_name=i.index_name and
i.owner not in('SYS','SYSTEM')
GROUP BY i.owner ,i.index_name ,i.table_owner , i.table_name ) idx,
(select t.owner owner ,t.table_name table_name,SUM(s2.blocks) blocks from dba_segments s2,dba_tables t where
s2.owner=t.owner and s2.segment_name=t.table_name and
t.owner not in ('SYS','SYSTEM')
GROUP BY T.OWNER,T.TABLE_NAME
) tbl
where
idx.table_owner=tbl.owner and
idx.table_name=tbl.table_name and
(idx.blocks/tbl.blocks)>0.5 and
idx.blocks>200
order by 4
/
select
idx.owner owner,
idx.table_name tablename,
idx.index_name index_name,
idx.blocks idx_blocks,
tbl.blocks tbl_blocks,
trunc(idx.blocks/tbl.blocks*100)/100 pct
from
(select i.owner owner ,i.index_name index_name,
SUM(S1.blocks) blocks,i.table_owner table_owner,
i.table_name table_name
from dba_segments s1,dba_indexes i
where
s1.owner=i.owner and s1.segment_name=i.index_name and
i.owner not in('SYS','SYSTEM')
GROUP BY i.owner ,i.index_name ,i.table_owner , i.table_name ) idx,
(select t.owner owner ,t.table_name table_name,SUM(s2.blocks) blocks from dba_segments s2,dba_tables t where
s2.owner=t.owner and s2.segment_name=t.table_name and
t.owner not in ('SYS','SYSTEM')
GROUP BY T.OWNER,T.TABLE_NAME
) tbl
where
idx.table_owner=tbl.owner and
idx.table_name=tbl.table_name and
(idx.blocks/tbl.blocks)>0.5 and
idx.blocks>200
order by 4
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25462274/viewspace-2124953/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
rman的备份查看的脚本
请登录后发表评论
登录
全部评论
