oracle表碎片的整理分析

本篇文章给大家分享的是有关oracle表碎片的整理分析,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。

数据库在日常使用过程中,不断的insert,delete,update操作,导致表和索引出现碎片是在所难免的事情,碎片多了,sql的执行效率自然就差了,道理很简单,高水位线(HWL)下的许多数据块都是无数据的,但全表扫描的时候要扫描到高水位线的数据块!

表的碎片和文件系统的碎片化的是不一样的,当随着在一个表上的DML的上操作越来越多时,HWM之前可能有很多空闲空间,而在读取表时HWM以下的块都会被读进来,这样会产生更多的IO,从而影响性能.只有在DDL操作才会进表的收缩.

对表进行碎片整理,碎片整理方法有:1,使用alter MOVE 表,然后索引rebuild;2.使用alter table enable row movement;然后alter table shrink space cascade(shrink使用有限制,需注意);3,通过 create table XXX as select * from abb; 4,使用导出和导入表 ;

实验如下:
SQL>  create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
———-
     86956

SQL> insert into t1 select * from t1;

86956 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> create index idx_t1_id on t1(object_id);

Index created.

–先查询表大小及统计信息:
SQL> set lines 200
SQL> COL TABLE_NAME FOR A15
SQL> COL TABLESPACE_NAME FOR A15
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                      TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM

SQL> COL SEGMENT_NAME FOR A15
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME                     BYTES     BLOCKS    EXTENTS
—————————— ————— —————————— ———- ———- ———-
SYS                            T1              SYSTEM                           20971520       2560         35
SYS                            IDX_T1_ID       SYSTEM                            4194304        512         19

可以看到dba_segment中已经可以记录表大小,而dba_tables则没有。

–使用dbms_stats手机统计信息
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM              173912       2476            0          0          98 2017-10-26 05:35:37

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM            20971520       2560         35
SYS                            IDX_T1_ID       SYSTEM             4194304        512         19

发现dba_table中已有记录BLOCKS块大小记录,但是没有empty_blocks空块记录和AVG_SPACE值。

–需要使用analyze子句收集表t1的空块信息。
DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM              173912       2476           83        863         101 2017-10-26 05:38:18

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM            20971520       2560         35
SYS                            IDX_T1_ID       SYSTEM             4194304        512         19

发现dba_tables中的 EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN字段有值了,且AVG_ROW_LEN的值发生了变化。

–计算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少,如下:
SQL> select table_name, (blocks * 8192 / 1024 / 1024) – (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables  where table_name = 'T1';

TABLE_NAME      data lower than hwm in mb
————— ————————-
T1                             2.59235382

查看执行计划,全表扫描大概需要消耗CPU 675
SQL> explain plan for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |   173K|    16M|   675   (1)| 00:00:09 |
|   1 |  TABLE ACCESS FULL| T1   |   173K|    16M|   675   (1)| 00:00:09 |
————————————————————————–

8 rows selected.

–删除大部分数据,收集统计信息,全表扫描依然耗cpu 673,如下:
SQL>  select count(*) from t1;

  COUNT(*)
———-
    173912

SQL> delete t1 where rownum <170000;

169999 rows deleted.

SQL> select count(*) from t1;

  COUNT(*)
———-
      3913

–使用dbms_stats分析表
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

–再次查询dba_segments和dba_tables视图
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM                3913       2476           83        863         101 2017-10-26 05:50:29

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM            20971520       2560         35
SYS                            IDX_T1_ID       SYSTEM             4194304        512         19

发现dba_tables中的num_rows字段已经更新了,其他字段没有更新;而dba_segments视图相关字段也没有变化。这说明DML操作的删除行操作,即使进行了统计信息的更新,但是因为表里存在碎片,所以表大小没有变化。

–使用analyze分析表:
SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM                3913       2476           83       7761         104 2017-10-26 05:52:00

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM            20971520       2560         35
SYS                            IDX_T1_ID       SYSTEM             4194304        512         19

还是没有变化,结论如上。。。。。。。。。。。。。。。。。

–查看执行计划,cpu cost 673几乎没变化
SQL> explain plan for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————–
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  3913 |   397K|   673   (1)| 00:00:09 |
|   1 |  TABLE ACCESS FULL| T1   |  3913 |   397K|   673   (1)| 00:00:09 |
————————————————————————–

8 rows selected.

–再次估算表在高水位线下还有多少空间是无数据的,但在全表扫描时又需要做无用功的数据块,如下:
SQL> select table_name, (blocks * 8192 / 1024 / 1024) – (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables  where table_name = 'T1';

TABLE_NAME      data lower than hwm in mb
————— ————————-
T1                             18.9556503

发现表中碎片增长很多。。。。。。。。。。。。

–对表进行碎片整理,重新收集统计信息,如下:
注:碎片整理方法有:1,使用alter MOVE 表,然后索引rebuild;2.使用alter table enable row movement;然后alter table shrink space cascade(shrink使用有限制,需注意);3,通过 create table XXX as select * from abb; 4,使用导出和导入表 ;

SQL> alter table t1 disable  row movement;

Table altered.

SQL> alter  table t1 move;

Table altered.

SQL> select INDEX_NAME,STATUS from dba_indexes where index_name ='IDX_T1_ID';

INDEX_NAME                     STATUS
—————————— ——–
IDX_T1_ID                      UNUSABLE

SQL> alter index IDX_T1_ID rebuild online;

Index altered.

–先查询dba_tables/dba_segments:
SQL>  select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM                3913       2476           83       7761         104 2017-10-26 05:52:00

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM              524288         64          8
SYS                            IDX_T1_ID       SYSTEM              131072         16          2

发现经过碎片整理后且在没有收集统计信息的情况下dba_segments的块大小已经自动更新了,而dba_tables各字段没有更新

–再次查询碎片情况:
SQL> select table_name, (blocks * 8192 / 1024 / 1024) – (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables  where table_name = 'T1';

TABLE_NAME      data lower than hwm in mb
————— ————————-
T1                             18.9556503

没有变化。

–收集统计信息,使用dbms_stat包:
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM                3913         58           83       7761         101 2017-10-26 06:07:17

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM              524288         64          8
SYS                            IDX_T1_ID       SYSTEM              131072         16          2

SQL> select table_name, (blocks * 8192 / 1024 / 1024) – (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables  where table_name = 'T1';

TABLE_NAME      data lower than hwm in mb
————— ————————-
T1                             .076220512

发现经过dbms_stat包收集统计信息后dba_tables的blocks、AVG_ROW_LEN字段已经更新,且高水位下的碎片已经回收了,但是EMPTY_BLOCKS、AVG_SPACE字段没有更新

–使用analyze子句收集EMPTY_BLOCKS字段统计信息,如下;
SQL>  analyze table t1 compute statistics;

Table analyzed.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';

OWNER                          TABLE_NAME      TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
—————————— ————— ————— ———- ———- ———— ———- ———– ——————-
SYS                            T1              SYSTEM                3913         58            5        887         104 2017-10-26 06:10:06

SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');

OWNER                          SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
—————————— ————— ————— ———- ———- ———-
SYS                            T1              SYSTEM              524288         64          8
SYS                            IDX_T1_ID       SYSTEM              131072         16          2

SQL> select table_name, (blocks * 8192 / 1024 / 1024) – (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables  where table_name = 'T1';

TABLE_NAME      data lower than hwm in mb
————— ————————-
T1                              .06502533

发现经过analyze子句收集统计信息后dba_tables的EMPTY_BLOCKS、AVG_SPACE字段更新了

–再次执行sql,发现CPU cost只有17,如下:
SQL> explain plan for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————-
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  3913 |   397K|    17   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  3913 |   397K|    17   (0)| 00:00:01 |
————————————————————————–

8 rows selected.

以上就是oracle表碎片的整理分析,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。

原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/203637.html

(0)
上一篇 2021年11月28日
下一篇 2021年11月28日

相关推荐

发表回复

登录后才能评论