主頁(yè) > 知識(shí)庫(kù) > Oracle表碎片整理操作步驟詳解

Oracle表碎片整理操作步驟詳解
熱門標(biāo)簽：征服者火車站地圖標(biāo)注 word地圖標(biāo)注方向 百度地圖標(biāo)注素材 開封智能外呼系統(tǒng)廠家 阿爾巴尼亞地圖標(biāo)注app 外呼線路外顯本地號(hào)碼 美圖秀秀地圖標(biāo)注 征服眼公司地圖標(biāo)注 人工智能地圖標(biāo)注自己能做嗎

高水位線（HWL）下的許多數(shù)據(jù)塊都是無(wú)數(shù)據(jù)的，但全表掃描的時(shí)候要掃描到高水位線的數(shù)據(jù)塊，也就是說(shuō)oracle要做許多的無(wú)用功！因此oracle提供了shrink space碎片整理功能。對(duì)于索引，可以采取rebuild online的方式進(jìn)行碎片整理，一般來(lái)說(shuō)，經(jīng)常進(jìn)行DML操作的對(duì)象DBA要定期進(jìn)行維護(hù)，同時(shí)注意要及時(shí)更新統(tǒng)計(jì)信息！

一：準(zhǔn)備測(cè)試數(shù)據(jù)，使用HR用戶，創(chuàng)建T1表，插入約30W的數(shù)據(jù)，并根據(jù)object_id創(chuàng)建普通索引，表占存儲(chǔ)空間34M

復(fù)制代碼 代碼如下:

SQL> conn /as sysdba
已連接。
SQL> select default_tablespace from dba_users where username='HR';

DEFAULT_TABLESPACE
------------------------------------------------------------
USERS

SQL> conn hr/hr
已連接。

SQL> insert into t1 select * from t1;
已創(chuàng)建 74812 行。

SQL> insert into t1 select * from t1;
已創(chuàng)建 149624 行。

SQL> commit;
提交完成。

SQL> create index idx_t1_id on t1(object_id);
索引已創(chuàng)建。

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過(guò)程已成功完成。

SQL> select count(1) from t1;

  COUNT(1)
----------
    299248

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
             34.0625

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
                   6

二：估算表在高水位線下還有多少空間可用，這個(gè)值應(yīng)當(dāng)越低越好，表使用率越接近高水位線，全表掃描所做的無(wú)用功也就越少!

DBMS_STATS包無(wú)法獲取EMPTY_BLOCKS統(tǒng)計(jì)信息，所以需要用analyze命令再收集一次統(tǒng)計(jì)信息

復(fù)制代碼 代碼如下:

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302            0     299248

SQL> analyze table t1 compute statistics;
表已分析。

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302           50     299248

SQL> col table_name for a20
SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  5.07086182

三: 查看執(zhí)行計(jì)劃，全表掃描大概需要消耗CPU 1175

復(fù)制代碼 代碼如下:

SQL> explain plan for select * from t1;
已解釋。

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  |      |   299K|    28M|  1175   (1)| 00:00:15 |
|   1 |  TABLE ACCESS FULL| T1   |   299K|    28M|  1175   (1)| 00:00:15 |
--------------------------------------------------------------------------

四：刪除大部分?jǐn)?shù)據(jù)，收集統(tǒng)計(jì)信息，全表掃描依然需要消耗CPU 1168

復(fù)制代碼 代碼如下:

SQL> delete from t1 where object_id>100;
已刪除298852行。

SQL> commit;
提交完成。

SQL> select count(*) from t1;

  COUNT(*)
----------
       396

SQL>  exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過(guò)程已成功完成。

SQL> analyze table t1 compute statistics;
表已分析。

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302           50        396

 
SQL> explain plan for select * from t1;
已解釋。

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  |      |   396 | 29700 |  1168   (1)| 00:00:15 |
|   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 |
--------------------------------------------------------------------------

五：估算表在高水位線下還有多少空間是無(wú)數(shù)據(jù)的，但在全表掃描時(shí)又需要做無(wú)用功的數(shù)據(jù)

復(fù)制代碼 代碼如下:

SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  33.5791626

六：對(duì)表進(jìn)行碎片整理，重新收集統(tǒng)計(jì)信息

復(fù)制代碼 代碼如下:

SQL> alter table t1 enable row movement;
表已更改。

SQL> alter table t1 shrink space cascade;
表已更改。

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';

SUM(BYTES)/1024/1024
--------------------
                .125

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID
';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  33.5791626

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過(guò)程已成功完成。

這個(gè)時(shí)候，只剩下0.1M的無(wú)用功了，執(zhí)行計(jì)劃中，全表掃描也只需要消耗CPU 3
SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  .010738373

 
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  |      |   396 | 29700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

總共只有5個(gè)塊，空塊卻有50個(gè)，明顯empty_blocks信息過(guò)期
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5           50        396

SQL> analyze table t1 compute statistics;
表已分析。

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

 
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5            3        396

您可能感興趣的文章:
  • Oracle數(shù)據(jù)庫(kù)中表空間的基本管理操作小結(jié)
  • Oracle的數(shù)據(jù)表中行轉(zhuǎn)列與列轉(zhuǎn)行的操作實(shí)例講解
  • shell腳本操作oracle刪除表空間、創(chuàng)建表空間、刪除用戶
  • Oracle 表空間查詢與操作方法
  • oracle表的簡(jiǎn)單操作步驟

標(biāo)簽：葫蘆島 海北 酒泉 淮南 六安 孝感 泰安 宜春

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle表碎片整理操作步驟詳解》，本文關(guān)鍵詞  Oracle,表,碎片,整理,操作步驟,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Oracle表碎片整理操作步驟詳解》相關(guān)的同類信息！
  • 本頁(yè)收集關(guān)于Oracle表碎片整理操作步驟詳解的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章