oracle表碎片查询整理

最新推荐文章于 2024-04-09 19:29:27 发布

夹心饼2024

最新推荐文章于 2024-04-09 19:29:27 发布

阅读量1.5k

点赞数 1

分类专栏：数据库运维案例分享文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/bing_yuan/article/details/129953917

版权

数据库运维案例分享专栏收录该内容

68 篇文章 0 订阅

订阅专栏

模拟插入数据

create table test as select * from dba_objects;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
commit;

收集统计信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

查询初始表的碎片率68126

SELECT table_name,
ROUND ( (blocks * 8), 2) "HW_space k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "real_space k",
ROUND ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
ROUND (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"waste_space k"
FROM user_tables
WHERE temporary = 'N' AND TABLE_NAME='TEST'
--and OWNER='TEST'
ORDER BY 5 DESC;

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176     68126.08      8917.6      12132.32

第一次模拟清理数据

delete from test where object_type in('SYNONYM','JAVA CLASS');
commit;

再次收集统计信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

第一次清理完查询表的碎片率18963

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176     18963.03      8917.6      61295.37

第二次模拟删除数据

delete from test where object_type in('VIEW','INDEX','TABLE','TYPE');
commit;

再次收集统计信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

第二次清理完查询表的碎片率6199，但是可以看到浪费的空间一直在增加74058.9，高水位一直没有变化89176

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176       6199.5      8917.6       74058.9

进行表碎片整理

15:21:46 SQL> alter table test enable row movement;

Table altered.

15:24:52 SQL> alter table test shrink space;

Table altered.

15:25:23 SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

PL/SQL procedure successfully completed.

15:25:47 SQL> SELECT table_name,
15:25:56   2  ROUND ( (blocks * 8), 2) "HW_space k",
15:25:56   3  ROUND ( (num_rows * avg_row_len / 1024), 2) "real_space k",
15:25:56   4  ROUND ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
15:25:56   5  ROUND (
15:25:56   6  ( blocks * 8
15:25:56   7  - (num_rows * avg_row_len / 1024)
15:25:56   8  - blocks * 8 * 10 / 100),
2)
15:25:56   9  15:25:56  10  "waste_space k"
FROM user_tables
15:25:56  11  15:25:56  12  WHERE temporary = 'N' AND TABLE_NAME='TEST'
15:25:56  13  --and OWNER='TEST'
15:25:56  14  ORDER BY 5 DESC;

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST             7408       6199.5       740.8         467.7

经过以上表碎片整理之后，可以看到表的高水位已经降下来7408，浪费的空间已经得到释放467.7