企业如何通过vue小程序开发满足高效运营与合规性需求
347
2023-12-30
oracle表碎片的整理分析
oracle表碎片的整理分析
本篇文章给大家分享的是有关oracle表碎片的整理分析，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
数据库在日常使用过程中，不断的insert，delete，update操作，导致表和索引出现碎片是在所难免的事情，碎片多了，sql的执行效率自然就差了，道理很简单，高水位线（HWL）下的许多数据块都是无数据的，但全表扫描的时候要扫描到高水位线的数据块!
表的碎片和文件系统的碎片化的是不一样的,当随着在一个表上的DML的上操作越来越多时,HWM之前可能有很多空闲空间,而在读取表时HWM以下的块都会被读进来,这样会产生更多的IO,从而影响性能.只有在DDL操作才会进表的收缩.
对表进行碎片整理，碎片整理方法有: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,使用导出和导入表 ;
实验如下：
SQL> create table t1 as select * from dba_objects; Table created.SQL> select count(*) from t1; COUNT(*)---------- 86956SQL> 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.--先查询表大小及统计信息：SQL> set lines 200SQL> COL TABLE_NAME FOR A15SQL> COL TABLESPACE_NAME FOR A15SQL> 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 SYSTEMSQL> COL SEGMENT_NAME FOR A15SQL> 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 35SYS IDX_T1_ID SYSTEM 4194304 512 19可以看到dba_segment中已经可以记录表大小，而dba_tables则没有。 --使用dbms_stats手机统计信息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:37SQL> 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 35SYS IDX_T1_ID SYSTEM 4194304 512 19发现dba_table中已有记录BLOCKS块大小记录，但是没有empty_blocks空块记录和AVG_SPACE值。--需要使用analyze子句收集表t1的空块信息。
DBMS_STATS包无法获取EMPTY_BLOCKS统计信息，所以需要用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 173912 2476 83 863 101 2017-10-26 05:38:18SQL> 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 35SYS IDX_T1_ID SYSTEM 4194304 512 19
发现dba_tables中的 EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN字段有值了，且AVG_ROW_LEN的值发生了变化。 --计算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少，如下：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查看执行计划，全表扫描大概需要消耗CPU 675SQL> 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.--删除大部分数据，收集统计信息，全表扫描依然耗cpu 673，如下：SQL> select count(*) from t1; COUNT(*)---------- 173912SQL> 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:29SQL> 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 35SYS IDX_T1_ID SYSTEM 4194304 512 19
发现dba_tables中的num_rows字段已经更新了，其他字段没有更新；而dba_segments视图相关字段也没有变化。这说明DML操作的删除行操作，即使进行了统计信息的更新，但是因为表里存在碎片，所以表大小没有变化。 --使用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:00SQL> 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 35SYS IDX_T1_ID SYSTEM 4194304 512 19
还是没有变化，结论如上。。。。。。。。。。。。。。。。。 --查看执行计划，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.--再次估算表在高水位线下还有多少空间是无数据的，但在全表扫描时又需要做无用功的数据块，如下：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
发现表中碎片增长很多。。。。。。。。。。。。 --对表进行碎片整理，重新收集统计信息，如下：注：碎片整理方法有: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,使用导出和导入表 ;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 UNUSABLESQL> 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:00SQL> 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 8SYS IDX_T1_ID SYSTEM 131072 16 2发现经过碎片整理后且在没有收集统计信息的情况下dba_segments的块大小已经自动更新了，而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没有变化。--收集统计信息，使用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:17SQL> 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 8SYS IDX_T1_ID SYSTEM 131072 16 2SQL> 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发现经过dbms_stat包收集统计信息后dba_tables的blocks、AVG_ROW_LEN字段已经更新，且高水位下的碎片已经回收了，但是EMPTY_BLOCKS、AVG_SPACE字段没有更新--使用analyze子句收集EMPTY_BLOCKS字段统计信息，如下；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:06SQL> 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 8SYS IDX_T1_ID SYSTEM 131072 16 2SQL> 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
发现经过analyze子句收集统计信息后dba_tables的EMPTY_BLOCKS、AVG_SPACE字段更新了 --再次执行sql，发现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表碎片的整理分析，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
最近发表
推荐文章
热评文章
暂时没有评论，来抢沙发吧~