首页 > 资讯 > 后端开发 > 其他教程 >详细聊聊Oracle表碎片对性能有多大的影响

491

分享到

详细聊聊Oracle表碎片对性能有多大的影响

2024-04-02 19:04:59 491人浏览薄情痞子

摘要

目录前言⛳️1.创建测试表?1.1建立表空间?1.2创建ASSM表空间?1.3创建表及索引⛳️2.查看表统计信息⛳️3.空块占用空间⛳️4.查看执行计划⛳️5.删除大量数据⛳️6.再

前言

本文通过对oracle 表碎片整理，对比了前后对数据库性能的影响。

⛳️ 1.创建测试表

?1.1 建立表空间

SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

?1.2 创建ASSM表空间

CREATE TABLESPACE “JEAMES” DATAFILE
‘/u01/app/oracle/oradata/EDB/jeames01' SIZE 50M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

?1.3 创建表及索引

##创建测试表t1,id列创建索引in_t1_id
create table t1 tablespace JEAMES as select level as id from dual connect by level<=300000;
create index in_t1_id on t1(id);
analyze table t1 compute statistics;
select count(*) from t1;

⛳️ 2.查看表统计信息

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

select sum(bytes)/1024/1024 from dba_segments where segment_name=‘IN_T1_ID';

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

总结：

查看表T1,段4M, 占用473个数据块,39个空块,索引IN_T1_ID段6M；

⛳️ 3.空块占用空间

查看没有数据的块占用的空间

DBMS_STATS 包无法获取 EMPTY_BLOCKS 统计信息，所以需要用 analyze 命令再收集一次统计信息，估算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少! !

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';

⛳️ 4.查看执行计划

查看全表扫描cost为131，基于成本

explain plan for select * from t1;
select * from table(dbms_xplan.display);

⛳️ 5.删除大量数据

删除大部分数据,并收集统计信息,查看T1占用数据块和空块都没有减少

delete from t1 where id>10;

analyze table t1 compute statistics;
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1';

⛳️ 6.再次查看执行计划

查看全表扫描cost为125，基于成本, 使用率几乎没有下降

explain plan for select * from t1;
select * from table(dbms_xplan.display);

⛳️ 7.再次空块占用空间

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';

⛳️ 8.整理表碎片

开启行迁移
alter table t1 enable row movement;
降低水位线
alter table t1 shrink space;
关闭行迁移
alter table t1 disable row movement;
SYS@EDB> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1'

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';

收集统计信息

analyze table t1 compute statistics;

⛳️ 9.效果确认

占用数据块及空闲数据块下降,并且cost使用也下降

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';

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

explain plan for select * from t1;
select * from table(dbms_xplan.display);

⛳️ 10.技能拓展

1.再用alter table table_name move 时，表相关的索引会失效，
所以之后还要执行 alter index index_name
rebuild online; 最后重新编译数据库所有失效的对象
2. 在用 alter table table_name shrink space cascade 时，
3. 他相当于 alter table table_name move 和
alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以；
4. Move 会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
5. shrink space 同样会移动高水位，
6. 但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
原理不一样，move 是以 block 为单位重组数据，
行的 rowid 都会跟着变化，而 shrink 是以”行“为单位重组
数据，他是根据复杂的算法从逻辑+物理重组数据
move 速度快于 shrink.
Move 相当于从 segment 底部 move 到头。
Shrink 相当于先 delete，然后再 insert 这样产生很多 undo,redo
通常首选 MOVE
语法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
k segment shrink 分为两个阶段：
1、数据重组(compact):通过一系列 insert、delete 操作，
将数据尽量排列在段的前面。在这个过程中需
要在表上加 RX 锁，即只在需要移动的行上加锁。由于涉及到 rowid 的改变，
需要 enable row movement.同时要 disable 基于 rowid 的 trigger.这一过程对业务影响比较小。
2、HWM 调整：第二阶段是调整 HWM 位置，释放空闲数据块。
此过程需要在表上加 X 锁，会造成表上的所有
DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
Shrink Space语句两个阶段都执行。Shrink
Space compact 只执行第一个阶段。
如果系统业务比较繁忙，可以先执行 Shrink Space compact
重组数据,然后在业务不忙的时候再执行 Shrink
Space 降低 HWM 释放空闲数据块。shrink 必须开启行迁移功能。