索引碎片:
由于index_stats只能存储最近一行数据,analyze index后查询index_stats必须在同一session内,
否则查询index_stats无记录,我们回到之前碎片很严重的索引ind_obj_id需要重新analyze一下:
anlyze index 锁定index
execute DBMS_STATS.GATHER_TABLE_STATS('cccc','SELL',CASCADE => TRUE);
analyze index UK_ORDER_TRADENO_GL validate structure;
select t.name, --索引名
t.lf_rows, --number of leaf rows (values in the index)
t.lf_blks,
t.del_lf_rows, --number of deleted leaf rows in the index
(t.del_lf_rows / t.lf_rows)*100 ratio --删除比例
from index_stats t
where t.name='UK_ORDER_TRADENO_GL';
select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;
alter index UK_ORDER_TRADENO_GL rebuild online parallel 4 compute statistics;
如果RATIO值大于30%以上就需要考虑索引重建了。
select index_name,PARTITIONED,status from user_indexes where PARTITIONED='YES' and table_name='CCCC_ORDER_PAR';
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME='CCCC_ORDER_PAR';
select 'alter index '||INDEX_NAME||' rebuild online parallel 16;' from user_indexes where TABLE_NAME='CCCC_ORDER_PAR';
查看pct_userd列,如果该值<=75%,就应该重建了;改列的默认值是90%
select PCT_USED from index_stats where NAME='IDX_CCCC_MPID';
由于index_stats只能存储最近一行数据,analyze index后查询index_stats必须在同一session内,
否则查询index_stats无记录,我们回到之前碎片很严重的索引ind_obj_id需要重新analyze一下:
anlyze index 锁定index
execute DBMS_STATS.GATHER_TABLE_STATS('cccc','SELL',CASCADE => TRUE);
analyze index UK_ORDER_TRADENO_GL validate structure;
select t.name, --索引名
t.lf_rows, --number of leaf rows (values in the index)
t.lf_blks,
t.del_lf_rows, --number of deleted leaf rows in the index
(t.del_lf_rows / t.lf_rows)*100 ratio --删除比例
from index_stats t
where t.name='UK_ORDER_TRADENO_GL';
select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;
alter index UK_ORDER_TRADENO_GL rebuild online parallel 4 compute statistics;
如果RATIO值大于30%以上就需要考虑索引重建了。
select index_name,PARTITIONED,status from user_indexes where PARTITIONED='YES' and table_name='CCCC_ORDER_PAR';
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME='CCCC_ORDER_PAR';
select 'alter index '||INDEX_NAME||' rebuild online parallel 16;' from user_indexes where TABLE_NAME='CCCC_ORDER_PAR';
查看pct_userd列,如果该值<=75%,就应该重建了;改列的默认值是90%
select PCT_USED from index_stats where NAME='IDX_CCCC_MPID';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1993583/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/91975/viewspace-1993583/