monitoring index space

上一篇 / 下一篇 2008-05-13 17:50:07 / 个人分类：oracle ocp 033性能调优

SQL> analyze index pk_test_tab validate structure;
索引被分析
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME                              WASTAGE
------------------------------ ----------
PK_test_TAB         16.6666666
SQL> alter index pk_test_tab rebuild;
索引被改变
SQL> analyze index pk_test_tab validate structure;
索引被分析
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME                              WASTAGE
------------------------------ ----------
PK_test_TAB                  0

===============================================================
SQL> delete bcoms_flag_pool_tab where rownum<2;
1 行已删除
SQL> commit;
提交完成
SQL> analyze index pk_test_tab validate structure;
索引被分析
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME                              WASTAGE
------------------------------ ----------
PK_test_TAB                 20
SQL> alter index pk_test_tab coalesce;
索引被改变
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME                              WASTAGE
------------------------------ ----------
PK_test_TAB                 20
SQL> analyze index pk_test_tab validate structure;
索引被分析
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME                              WASTAGE
------------------------------ ----------
PK_test_TAB                  0
SQL>
======================

SQL> update test_tab set serial_no=
2 'asdf'
3 where order_serial_no='234234';
1 行已更新
SQL> commit;
提交完成
SQL> analyze index pk_test_tab validate structure;
索引被分析
SQL> select name,(del_lf_rows_len/lf_rows_len)*100 as wastage from index_stats;
NAME WASTAGE
------------------------------ ----------
PK_test_TAB 20