|
|
51CTO旗下网站
|
|
移动端

超详细的Oracle数据库表碎片整理规范,值得收藏

生产环境中,经常会遇到表由于数据不断插入,导致空间越来越大,由于前期配置问题,没有做分区或者其他优化,而且生产数据实时向表插入。要删除历史数据来释放空间。

作者:波波说运维来源:今日头条|2019-08-20 09:46

【大咖·来了 第7期】10月24日晚8点观看《智能导购对话机器人实践》

概述

生产环境中,经常会遇到表由于数据不断插入,导致空间越来越大,由于前期配置问题,没有做分区或者其他优化,而且生产数据实时向表插入。要删除历史数据来释放空间。所以DBA一般都需要定期去对Oracle表碎片做整理,简单整理表碎片整理流程如下:

1、定位存在碎片的对象

使用如下脚本,检查需要进行碎片整理的对象:

  1. --all tables(partition_tables + non_partition_tables ) 
  2. select a.owner, 
  3.  a.table_name, 
  4.  a.num_rows, 
  5.  a.avg_row_len, 
  6.  round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB, 
  7.  round(b.seg_bytes_mb, 2) seg_bytes_mb, 
  8.  decode(a.num_rows, 
  9.  0, 
  10.  100, 
  11.  (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  12.  b.seg_bytes_mb, 
  13.  2)) * 100) || '%' frag_percent 
  14.  from dba_tables a, 
  15.  (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb 
  16.  from dba_segments 
  17.  group by owner, segment_name) b 
  18.  where a.table_name = b.segment_name 
  19.  and a.owner = b.owner 
  20.  and a.owner not in 
  21.  ('SYS''SYSTEM''OUTLN''DMSYS''TSMSYS''DBSNMP''WMSYS'
  22.  'EXFSYS''CTXSYS''XDB''OLAPSYS''ORDSYS''MDSYS''SYSMAN'
  23.  and decode(a.num_rows, 
  24.  0, 
  25.  100, 
  26.  (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  27.  b.seg_bytes_mb, 
  28.  2)) * 100) > 30 
  29.  order by b.seg_bytes_mb desc

超详细的oracle数据库表碎片整理规范,值得收藏

2、统计信息检查

2.1 统计信息检查

查看统计信息收集日期,确保碎片查询结果准确:

  1. select owner,table_name,last_analyzed from dba_tables Where owner='<OWNER>' AND table_name='<TABLE_NAME>'

超详细的oracle数据库表碎片整理规范,值得收藏

2.2 统计信息收集

如果统计信息过旧,则重新收集统计信息:

  1. exec dbms_stats.gather_table_stats(ownname=>'<OWNER>', tabname =>'<TABLE_NAME>'); 

超详细的oracle数据库表碎片整理规范,值得收藏

3、表碎片整理

3.1 打开行移动

  1. alter table <TABLE_NAME> enable row movement ; 

3.2 进行表收缩

  1. alter table <TABLE_NAME> shrink space cascade ; 

3.3 失效对象编译

语句可能会造成引用表 的对象(如存储过程、包、视图等)变为无效。

运行如下脚本,重新编译失效对象。

  1. @?/rdbms/admin/utlrp.sql 

4、对象收缩后的结果检查

运行如下脚本,确认对象空间是否已经完成收缩。

  1. --all tables(partition_tables + non_partition_tables ) 
  2. select a.owner, 
  3.  a.table_name, 
  4.  a.num_rows, 
  5.  a.avg_row_len, 
  6.  round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB, 
  7.  round(b.seg_bytes_mb, 2) seg_bytes_mb, 
  8.  decode(a.num_rows, 
  9.  0, 
  10.  100, 
  11.  (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  12.  b.seg_bytes_mb, 
  13.  2)) * 100) || '%' frag_percent 
  14.  from dba_tables a, 
  15.  (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb 
  16.  from dba_segments 
  17.  group by owner, segment_name) b 
  18.  where a.table_name = b.segment_name 
  19.  and a.owner = b.owner 
  20.  and a.owner not in 
  21.  ('SYS''SYSTEM''OUTLN''DMSYS''TSMSYS''DBSNMP''WMSYS'
  22.  'EXFSYS''CTXSYS''XDB''OLAPSYS''ORDSYS''MDSYS''SYSMAN'
  23.  and decode(a.num_rows, 
  24.  0, 
  25.  100, 
  26.  (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  27.  b.seg_bytes_mb, 
  28.  2)) * 100) > 30 
  29.  order by b.seg_bytes_mb desc

5、性能监控

监控数据库会话,是否存在异常等待事件:

  1. select inst_id ,sid,serial#,sql_id,event,machine,module,program,seconds_in_wait from gv$session ; 
  2. --看会话在做什么操作 
  3. select sid, sql_text 
  4.  from v$session a, v$sql b 
  5.  where sid in(85,160) 
  6.  and(b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id); 

超详细的oracle数据库表碎片整理规范,值得收藏

超详细的oracle数据库表碎片整理规范,值得收藏

【编辑推荐】

  1. 5个资源超好的网站,百度云盘都不够装,最好悄悄的收藏
  2. Java程序计数器刨根问底,大部分程序员都收藏起来了
  3. 超详细的5个Shell脚本实例分享,值得收藏
  4. 一些值得收藏的开源框架
  5. 收藏!适合程序员的简历模板,总有需要用的时候
【责任编辑:华轩 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

92人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

361人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

739人订阅学习

读 书 +更多

非常网管——网络服务

本书使用通俗易懂的语言,通过大量的实例,从实际应用的角度出发,全面系统地介绍了网络服务操作系统平台、电子邮件系统、Web站点和FTP站点...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微