Oracle查询索引碎片及数据表空间使用情况

--检查索引碎片情况，只能对单个表进行分析。
--需要注意块的大小、索引的pctfree的值的大小、rowid的长度的不同，根据不同的情况修改相应的值

select index_name, c.NMB "应有大小", d.SMB "现大小"
  from (select index_name,
               round((select num_rows numrows
                        from dba_tables
                       where table_name = upper('tableName')
                         AND owner = upper('ownerName')) /
                     ((8192 - 819.2 - 4 - 20 - 72 - 32) /
                     ((sum(AVG_COL_LEN)) + 2 + 18)) * 8192 / 1024 / 1024) NMB
          from (SELECT b.index_name  index_name,
                       a.column_name,
                       a.AVG_COL_LEN AVG_COL_LEN
                  FROM dba_tab_columns a,
                       (select b.index_name, b.column_name, b.index_owner
                          from dba_ind_columns b
                         where b.table_name = upper('tableName')
                           and B.INDEX_OWNER = upper('ownerName')
                         order by b.index_name) b
                 WHERE a.TABLE_NAME = upper('tableName')
                   AND A.OWNER = upper('ownerName')
                   and a.column_name = b.column_name
                 order by b.index_name)
         group by index_name) c,
       (SELECT segment_name, round(sum(bytes) / 1024 / 1024) SMB
          FROM dba_segments
         WHERE OWNER = upper('ownerName')
         group by segment_name) d
 where c.index_name = d.segment_name;

索引碎片比较严重的话，重建索引：

--ALTER INDEX indexName rebuild online nologging;

DECLARE 
    CURSOR myCur IS
        select INDEX_NAME from user_indexes WHERE TABLE_NAME=upper('tableName') AND INDEX_TYPE='NORMAL';
    v_cname myCur% rowtype;
    vsSql varchar2(256);
begin
open myCur;   
    loop      
       fetch myCur into v_cname;     
       exit when myCur% notfound;                        
       vsSql:='ALTER INDEX ' || v_cname.INDEX_NAME  || ' rebuild online nologging';
       EXECUTE IMMEDIATE vsSql;
    end loop;  
 close myCur;
end;

 

查看指定数据表的空间分配及实际使用情况：

select ta.Used_Blocks, tt.*
from (
       SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) || DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) as Used_Blocks
       FROM tableName
     ) ta
     cross join (
       select t.*
       from user_segments t
       where t.segment_type = 'TABLE'
             and t.segment_name = upper('tableName')
       order by t.blocks desc
     ) tt;

大量删除数据后的高水位线，造成表扫描性能问题，释放数据空间

--删除数据后，释放数据空间
alter table tableName enable row movement;
alter table tableName shrink space cascade;
alter table tableName disable row movement;

  http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html

  http://blog.csdn.net/wyzxg/article/details/5631721

 

附，更新统计信息、清空缓存

--analyze table 更新统计信息：
analyze table my_table compute statistics; 
--OR
EXEC DBMS_STATS.gather_table_stats(ownname => 'socct', tabname =>'tableName', estimate_percent =>100, cascade =>true, method_opt => 'for all columns size auto');

--清空缓存(共享池和数据缓存)，Command window执行：
alter system flush shared_pool;
alter system flush buffer_cache;