oracle表碎片的整理分析

本篇文章給大家分享的是有關(guān)oracle表碎片的整理分析，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

數(shù)據(jù)庫在日常使用過程中，不斷的insert，delete，update操作，導(dǎo)致表和索引出現(xiàn)碎片是在所難免的事情，碎片多了，sql的執(zhí)行效率自然就差了，道理很簡單，高水位線（HWL）下的許多數(shù)據(jù)塊都是無數(shù)據(jù)的，但全表掃描的時(shí)候要掃描到高水位線的數(shù)據(jù)塊!

表的碎片和文件系統(tǒng)的碎片化的是不一樣的,當(dāng)隨著在一個(gè)表上的DML的上操作越來越多時(shí),HWM之前可能有很多空閑空間,而在讀取表時(shí)HWM以下的塊都會(huì)被讀進(jìn)來,這樣會(huì)產(chǎn)生更多的IO,從而影響性能.只有在DDL操作才會(huì)進(jìn)表的收縮.

對(duì)表進(jìn)行碎片整理，碎片整理方法有: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,使用導(dǎo)出和導(dǎo)入表 ;

實(shí)驗(yàn)如下：
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.

--先查詢表大小及統(tǒng)計(jì)信息：
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中已經(jīng)可以記錄表大小，而dba_tables則沒有。

--使用dbms_stats手機(jī)統(tǒng)計(jì)信息
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

發(fā)現(xiàn)dba_table中已有記錄BLOCKS塊大小記錄，但是沒有empty_blocks空塊記錄和AVG_SPACE值。

--需要使用analyze子句收集表t1的空塊信息。
DBMS_STATS包無法獲取EMPTY_BLOCKS統(tǒng)計(jì)信息，所以需要用analyze命令再收集一次統(tǒng)計(jì)信息

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

發(fā)現(xiàn)dba_tables中的 EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN字段有值了，且AVG_ROW_LEN的值發(fā)生了變化。

--計(jì)算表在高水位線下還有多少空間可用，這個(gè)值應(yīng)當(dāng)越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少，如下：
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

查看執(zhí)行計(jì)劃，全表掃描大概需要消耗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.


--刪除大部分?jǐn)?shù)據(jù)，收集統(tǒng)計(jì)信息，全表掃描依然耗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

發(fā)現(xiàn)dba_tables中的num_rows字段已經(jīng)更新了，其他字段沒有更新；而dba_segments視圖相關(guān)字段也沒有變化。這說明DML操作的刪除行操作，即使進(jìn)行了統(tǒng)計(jì)信息的更新，但是因?yàn)楸砝锎嬖谒槠员泶笮]有變化。

--使用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

還是沒有變化，結(jié)論如上。。。。。。。。。。。。。。。。。

--查看執(zhí)行計(jì)劃，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.

--再次估算表在高水位線下還有多少空間是無數(shù)據(jù)的，但在全表掃描時(shí)又需要做無用功的數(shù)據(jù)塊，如下：
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

發(fā)現(xiàn)表中碎片增長很多。。。。。。。。。。。。


--對(duì)表進(jìn)行碎片整理，重新收集統(tǒng)計(jì)信息，如下：
注：碎片整理方法有: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,使用導(dǎo)出和導(dǎo)入表 ;

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

發(fā)現(xiàn)經(jīng)過碎片整理后且在沒有收集統(tǒng)計(jì)信息的情況下dba_segments的塊大小已經(jīng)自動(dòng)更新了，而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

沒有變化。

--收集統(tǒng)計(jì)信息，使用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

發(fā)現(xiàn)經(jīng)過dbms_stat包收集統(tǒng)計(jì)信息后dba_tables的blocks、AVG_ROW_LEN字段已經(jīng)更新，且高水位下的碎片已經(jīng)回收了，但是EMPTY_BLOCKS、AVG_SPACE字段沒有更新

--使用analyze子句收集EMPTY_BLOCKS字段統(tǒng)計(jì)信息，如下；
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

發(fā)現(xiàn)經(jīng)過analyze子句收集統(tǒng)計(jì)信息后dba_tables的EMPTY_BLOCKS、AVG_SPACE字段更新了

--再次執(zhí)行sql，發(fā)現(xiàn)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表碎片的整理分析，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。