ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle高水平位测试

oracle高水平位测试

原创 Linux操作系统 作者:alsrt 时间:2011-08-11 14:46:03 0 删除 编辑

高水平位需要引起DBA足够的重视,尤其对于经常频繁修改的表,即使是小表,我们也需要经常性的去降低表的高水平位,测试如下

建表

create table testaa as select * from user_objects where rownum<10000;

多次插入数据

insert into testaa select * from testaa;

QL> select bytes,blocks from dba_extents where SEGMENT_NAME='TESTAA';

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
   1048576        128

收集统计信息

 exec dbms_stats.gather_table_stats('scott','TESTAA',CASCADE=>TRUE);

然后查看

SQL> exec dbms_stats.gather_table_stats('scott','TESTAA',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select bytes,blocks from dba_extents where SEGMENT_NAME='TESTAA';

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
   1048576        128

17 rows selected.

删除数据

SQL> delete testaa;

11232 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes,blocks from dba_extents where SEGMENT_NAME='TESTAA';

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
   1048576        128

17 rows selected.

SQL> exec dbms_stats.gather_table_stats('scott','TESTAA',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

 

QL>  select bytes,blocks from dba_extents where SEGMENT_NAME='TESTAA';

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8

     BYTES     BLOCKS
---------- ----------
     65536          8
     65536          8
     65536          8
     65536          8
     65536          8
   1048576        128

17 rows selected.


发现blocks没有任何变化,dbms_stat可以收集insert的统计信息,但是不能够收集

delete的统计信息,真是够怪的!!!!!!

一般去除oracle的高水平位,我们有很多方法,这里采用move的方法,但是要注意一点,完成以后需要rebuild索引,继续进行测试

SQL> alter table testaa move;

Table altered.

SQL> select bytes,blocks from dba_extents where SEGMENT_NAME='TESTAA';

     BYTES     BLOCKS
---------- ----------
     65536          8

对于需要批量降低高水平位的操作,我写了个pl/sql,供以后方便操作

建议该用户在某一个schema下做操作


declare
reduce_hwm varchar2(1000);
BEGIN
FOR REC IN (SELECT TABLE_NAME FROM USER_TABLES) loop
reduce_hwm:='ALTER TABLE '||REC.TABLE_NAME ||' MOVE';
EXECUTE IMMEDIATE reduce_hwm;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error: ' || reduce_hwm || '!');
END;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-732462/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    33668