高水位線（HWL）下的許多數據塊都是無數據的，但全表掃描的時候要掃描到高水位線的數據塊，也就是說oracle要做許多的無用功！因此oracle提供了shrink space碎片整理功能。對于索引，可以采取rebuild online的方式進行碎片整理，一般來說，經常進行DML操作的對象DBA要定期進行維護，同時注意要及時更新統計信息！
一：準備測試數據，使用HR用戶，創建T1表，插入約30W的數據，并根據object_id創建普通索引，表占存儲空間34M
復制代碼 代碼如下:
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;
已創建 74812 行。
SQL> insert into t1 select * from t1;
已創建 149624 行。
SQL> commit;
提交完成。
SQL> create index idx_t1_id on t1(object_id);
索引已創建。
SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
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
二：估算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少!
DBMS_STATS包無法獲取EMPTY_BLOCKS統計信息，所以需要用analyze命令再收集一次統計信息
復制代碼 代碼如下:
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
三: 查看執行計劃，全表掃描大概需要消耗CPU 1175
復制代碼 代碼如下:
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 |
--------------------------------------------------------------------------
四：刪除大部分數據，收集統計信息，全表掃描依然需要消耗CPU 1168
復制代碼 代碼如下:
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 過程已成功完成。
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 |
--------------------------------------------------------------------------
五：估算表在高水位線下還有多少空間是無數據的，但在全表掃描時又需要做無用功的數據
復制代碼 代碼如下:
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> 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 過程已成功完成。
這個時候，只剩下0.1M的無用功了，執行計劃中，全表掃描也只需要消耗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個塊，空塊卻有50個，明顯empty_blocks信息過期
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數據庫中表空間的基本管理操作小結
- Oracle的數據表中行轉列與列轉行的操作實例講解
- shell腳本操作oracle刪除表空間、創建表空間、刪除用戶
- Oracle 表空間查詢與操作方法
- oracle表的簡單操作步驟
