当前位置：Gxlcms > 数据库问题 > 关于Oracle表碎片整理

关于Oracle表碎片整理

时间：2021-07-01 10:21:17 帮助过：5人阅读

  • 已连接。 
  • 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命令再收集一次统计信息

    1. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1‘; 
    2.  
    3.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    4. ---------- ------------ ---------- 
    5.       4302            0     299248 
    6.  
    7. SQL> analyze table t1 compute statistics; 
    8. 表已分析。 
    9.  
    10. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1‘; 
    11.  
    12.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    13. ---------- ------------ ---------- 
    14.       4302           50     299248 
    15.  
    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‘; 
    22.  
    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. 已解释。 
    3.  
    4. SQL> select * from table(dbms_xplan.display); 
    5.  
    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. -------------------------------------------------------------------------- 

    四：删除大部分数据，收集统计信息，全表扫描依然需要消耗CPU 1168

    1. SQL> delete from t1 where object_id>100; 
    2. 已删除298852行。 
    3.  
    4. SQL> commit; 
    5. 提交完成。 
    6.  
    7. SQL> select count(*) from t1; 
    8.  
    9.   COUNT(*) 
    10. ---------- 
    11.        396 
    12.  
    13. SQL>  exec dbms_stats.gather_table_stats(‘HR‘,‘T1‘,CASCADE=>TRUE); 
    14. PL/SQL 过程已成功完成。 
    15.  
    16. SQL> analyze table t1 compute statistics; 
    17. 表已分析。 
    18.  
    19. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1‘; 
    20.  
    21.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    22. ---------- ------------ ---------- 
    23.       4302           50        396 
    24.  
    25.  
    26. SQL> explain plan for select * from t1; 
    27. 已解释。 
    28.  
    29. SQL> select * from table(dbms_xplan.display); 
    30.  
    31. PLAN_TABLE_OUTPUT 
    32. ------------------------------------------------------------------------------ 
    33. Plan hash value: 3617692013 
    34. -------------------------------------------------------------------------- 
    35. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    36. -------------------------------------------------------------------------- 
    37. |   0 | SELECT STATEMENT  |      |   396 | 29700 |  1168   (1)| 00:00:15 | 
    38. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 | 
    39. -------------------------------------------------------------------------- 

    五：估算表在高水位线下还有多少空间是无数据的，但在全表扫描时又需要做无用功的数据

    1. SQL> SELECT TABLE_NAME, 
    2.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    3.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    4.   4    FROM USER_TABLES 
    5.   5   WHERE table_name = ‘T1‘; 
    6.  
    7. TABLE_NAME           Data lower than HWM in MB 
    8. -------------------- ------------------------- 
    9. T1                                  33.5791626 

    六：对表进行碎片整理，重新收集统计信息

    1. SQL> alter table t1 enable row movement; 
    2. 表已更改。 
    3.  
    4. SQL> alter table t1 shrink space cascade; 
    5. 表已更改。 
    6.  
    7. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1‘; 
    8.  
    9. SUM(BYTES)/1024/1024 
    10. -------------------- 
    11.                 .125 
    12.  
    13. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘IDX_T1_ID 
    14. ‘; 
    15.  
    16. SUM(BYTES)/1024/1024 
    17. -------------------- 
    18.                .0625 
    19.  
    20. SQL> SELECT TABLE_NAME, 
    21.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    22.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    23.   4    FROM USER_TABLES 
    24.   5   WHERE table_name = ‘T1‘; 
    25.  
    26. TABLE_NAME           Data lower than HWM in MB 
    27. -------------------- ------------------------- 
    28. T1                                  33.5791626 
    29.  
    30. SQL> exec dbms_stats.gather_table_stats(‘HR‘,‘T1‘,CASCADE=>TRUE); 
    31. PL/SQL 过程已成功完成。 
    32.  
    33. 这个时候，只剩下0.1M的无用功了，执行计划中，全表扫描也只需要消耗CPU 3 
    34. SQL> SELECT TABLE_NAME, 
    35.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    36.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    37.   4    FROM USER_TABLES 
    38.   5   WHERE table_name = ‘T1‘; 
    39.  
    40. TABLE_NAME           Data lower than HWM in MB 
    41. -------------------- ------------------------- 
    42. T1                                  .010738373 
    43.  
    44.  
    45. SQL> select * from table(dbms_xplan.display); 
    46.  
    47. PLAN_TABLE_OUTPUT 
    48. -------------------------------------------------------------------------------- 
    49. Plan hash value: 3617692013 
    50. -------------------------------------------------------------------------- 
    51. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    52. -------------------------------------------------------------------------- 
    53. |   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 | 
    54. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 | 
    55. -------------------------------------------------------------------------- 
    56.  
    57. 总共只有5个块，空块却有50个，明显empty_blocks信息过期 
    58. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name=‘T1‘; 
    59.  
    60.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    61. ---------- ------------ ---------- 
    62.          5           50        396 
    63.  
    64. SQL> analyze table t1 compute statistics; 
    65. 表已分析。 
    66.  
    67. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name=‘T1‘; 
    68.  
    69.  
    70.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    71. ---------- ------------ ---------- 
    72.          5            3        396 

    参考：http://surachartopun.com/2011/08/determine-hwm-and-reduce-it-by-shrink.html

    关于如何确定哪些表需要进行碎片整理，可以使用附件中的脚本去查询，具体请参考：http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTNN18/Default.aspx

    关于Oracle表碎片整理

    标签：

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！