关于Oracle表碎片整理

数据库在日常使用过程中，不断的insert，delete，update操作，导致表和索引出现碎片是在所难免的事情，碎片多了，sql的执行效率自然就差了，道理很简单，高水位线（HWL）下的许多数据块都是无数据的，但全表扫描的时候要扫描到高水位线的数据块，也就是说Oracle要做许多的无用功！因此oracle提供了shrink space碎片整理功能。对于索引，可以采取rebuild online的方式进行碎片整理，一般来说，经常进行DML操作的对象DBA要定期进行维护，同时注意要及时更新统计信息！

关于Oracle表碎片整理相关附件

具体下载目录在 /2013年资料/5月/25日/关于Oracle表碎片整理
 
一：准备测试数据，使用HR用户，创建T1表，插入约30W的数据，并根据object_id创建普通索引，表占存储空间34M左右，索引占6M左右的存储空间

1. SQL> conn /as sysdba
2. 已连接。
3. SQL> select default_tablespace from dba_users where username='HR';
5. DEFAULT_TABLESPACE
6. ------------------------------------------------------------
7. USERS
9. SQL> conn hr/hr
10. 已连接。
12. SQL> insert into t1 select * from t1;
13. 已创建 74812 行。
15. SQL> insert into t1 select * from t1;
16. 已创建 149624 行。
18. SQL> commit;
19. 提交完成。
21. SQL> create index idx_t1_id on t1(object_id);
22. 索引已创建。
24. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
25. PL/SQL 过程已成功完成。
27. SQL> select count(1) from t1;
29. COUNT(1)
30. ----------
31. 299248
33. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
34. SUM(BYTES)/1024/1024
35. --------------------
36. 34.0625
38. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
39. SUM(BYTES)/1024/1024
40. --------------------
41. 6

二：估算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少!

DBMS_STATS包无法获取EMPTY_BLOCKS统计信息，所以需要用analyze命令再收集一次统计信息

1. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
3. BLOCKS EMPTY_BLOCKS NUM_ROWS
4. ---------- ------------ ----------
5. 4302 0 299248
7. SQL> analyze table t1 compute statistics;
8. 表已分析。
10. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
12. BLOCKS EMPTY_BLOCKS NUM_ROWS
13. ---------- ------------ ----------
14. 4302 50 299248
16. SQL> col table_name for a20
17. SQL> SELECT TABLE_NAME,
18. 2 (BLOCKS * 8192 / 1024 / 1024) -
19. 3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
20. 4 FROM USER_TABLES
21. 5 WHERE table_name = 'T1';
23. TABLE_NAME Data lower than HWM in MB
24. -------------------- -------------------------
25. T1 5.07086182

三: 查看执行计划，全表扫描大概需要消耗CPU 1175

1. SQL> explain plan for select * from t1;
2. 已解释。
4. SQL> select * from table(dbms_xplan.display);
6. PLAN_TABLE_OUTPUT
7. --------------------------------------------------------------------------------
8. Plan hash value: 3617692013
9. --------------------------------------------------------------------------
10. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
11. --------------------------------------------------------------------------
12. | 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 |
13. | 1 | TABLE ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 |
14. --------------------------------------------------------------------------