oracle 水平透视,oracle高水平位测试

高水平位需要引起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;