数据库优化之响应速度优化和空间回收

原创 2016年08月30日 15:49:00


 

 

系统环境

硬件环境:

服务器类型

虚拟机

Cpu

Inter (R) Xeon E7-4870 /6核

内存

8G

 

软件环境

操作系统

Windows Server 2008  R2  Enterprise

数据库版本

Oracle 10g 10.2.0.5.0

备份机制

归档模式+rman全量备份

一、问题现象

1、前台报表展现异常缓慢,出现无法正常展现曲线的情况。

2、 后台不加条件查询该表数据,执行时间超长4分钟以上。

3、 事实表数据超大3.3亿记录,历史数据查询用时响应在几小时以上。

二、优化过程

1、备份历史数据

将历史数据按年备份到备份表,根据业务特点,报表展现给用户的一般是近一年的数据,事实表中不建议保留太久的历史数据。本例中事实表(r_data)保留了2011年1月1日至今的数据,导致存储了3.3亿条数据。

向领导请示后约定事实表(r_data)保留近一年数据供查询,其余数据清理至历史表按照年份创建备份表r_data_20130713\ r_data_20140713\ r_data_20150713,2.7亿条记录总计耗时约5分钟。

insert/* +Append*/into r_data_20160713select * from r_datawhere r_date <=to_date('2013/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

insert/* +Append */into r_data_20140713select * from r_datawhere r_date <=to_date('2014/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

insert/* +Append */into r_data_20150713select * from r_datawhere r_date <=to_date('2015/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

注:为避免产生大量归档日志故选择了append 模式直接插入,节省时间,加速操作。

2、删除历史数据

 删除刚刚备份的2.7条记录,此时根据时间分批删除,案例中删除为按月删除,每次删除1个月的数据500w条左右耗时5分30s左右。

 altertable R_datanologging;--在此模式下轨当日志还是会产生

 delete from r_datawhere r_date <=to_date('2015/07/1300:00:00','yyyy/mm/ddhh24:mi:ss') ;

注:(1)、归档模式下大量删除将产生大量归档,所以这里采用分批删除的方法,保证归档日志不至于写爆存储。

(2)、由于r_data写入较频繁,长时间的删除容易产生死锁现象,分批删除时如果发现可以及时处理。

3、回收表占用空间

收缩表需要开启表的行迁移模式,此时记录的rowid会发生变化,shrink操作可以释放存储碎片,释放存储空间。

alter table r_data enable row movement;--开启行迁移

alter table r_datashrinkspace;--收缩表数据

注:删除的记录不会释放存储空间,如果对一张表执行了大量的delete操作,最好在适当时候收缩下表。

4、重建索引

收缩表以后,记录的rowid发生变化,此时需要重建索引,保证索引的有效

alterindex PK_R_DATArebuildonline;

alterindex IDX_R_DATA_DATErebuildonline;

注:如果不想影响表的dml操作,最好加上online 参数。

5、收集统计信息

对表的统计信息进行收集,可以查看优化结果,是优化生效。

exec dbms_stats.gather_table_stats('DATA_TRAN','R_DATA');--收集表的统计信息

三、优化结果

 

优化前

优化后

表名

R_data

R_data

记录数

33200万条

5100万条

占用空间

8.5G

1.51G

查询时间

260.216s

0.218s

表类型

普通表

分区表

 

四、优化总结

新系统上线时,往往响应速度很快,随着系统使用日久,数据量增大,导致部分报表或者数据加载异常缓慢,此时从数据角度可以进行如下优化。

1、 创建分区表,oracle11g分区表可以自动创建分区,分区表可以自动均衡数据,加快查询。本例中最后将r_data表转化成为了分区表,按照时间范围分区(为减少篇幅转换操作暂时未列)。

2、 建立合适的索引,合适的索引可以大大加速查询速度。

3、 如果事实表存储较为久远的数据,注意定时备份清理历史数据,可以使系统响应速度大大加快。

4、 数据删除工作量远大于数据插入。本例中插入2.7亿数据用时5分钟左右的样子,但每删除500万条记录需要5分钟,删除操作总计耗时2天,产生了约360G的归档日志。并且删除会产生大量的存储碎片,删除完所有记录以后还需要进行收缩表(shrink),收集表的统计信息,重建索引的操作。

版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

性能优化之数据库优化

转载自:http://www.trinea.cn/android/performance/ 本文为性能优化的第一篇——数据库性能优化,原理适用于大部分数据库包括Sqlite、Mysq...

性能优化之数据库优化

1、索引 简单的说,索引就像书本的目录,目录可以快速找到所在页数,数据库中索引可以帮助快速找到数据,而不用全表扫描,合适的索引可以大大提高数据库查询的效率。 (1). 优点 大大加快了数据库检索...

精选:深入理解 Docker 内部原理及网络配置

网络绝对是任何系统的核心,对于容器而言也是如此。Docker 作为目前最火的轻量级容器技术,有很多令人称道的功能,如 Docker 的镜像管理。然而,Docker的网络一直以来都比较薄弱,所以我们有必要深入了解Docker的网络知识,以满足更高的网络需求。

性能优化之数据库优化

数据库性能优化,原理适用于大部分数据库包括Sqlite、Mysql、Oracle、Sql server,详细介绍了索引(优缺点、分类、场景、规则)和事务,最后介绍了部分单独针对Sqlite的优化

性能优化之数据库优化

本文为性能优化的第一篇——数据库性能优化,原理适用于大部分数据库包括Sqlite、Mysql、Oracle、Sql server,详细介绍了索引(优缺点、分类、场景、规则)和事务,最后介绍了部分单独针...

数据库优化之sql优化

不在索引列进行数学运算或凼数运算 拒绝大SQL拆解成多条简单SQL 优先使用ENUM或SET - 可能值已知且有限 改写OR为IN 改写OR为UNION 避免 前缀模糊查询 同数据类型的列值比较 避...

性能优化之数据库优化

本文性能优化之数据库优化,原理适用于大部分数据库包括Sqlite、Mysql、Oracle、Sql server,详细介绍了索引(优缺点、分类、场景、规则)和事务,最后介绍了部分单独针对Sqlite的...

数据库优化<六>SQL优化之SELECT优化 ——filesort

在执行计划中,可能经常看到有fExtra列有filesort,这就是使用了文件排序,这当然是不好 的,应该优化,但是,了解一下他排序的原理也许很有帮助,下面看一下filesort的过程:       ...

数据库优化<三>SQL优化之SELECT优化 ——JOIN和LEFT JOIN 和 RIGHT JOIN

在数据库的应用中,我们经常需要对多表进行连表操作来获得关系型的数据,因此,应该更加的掌握好 SQL语句的join原理,因为,可以稍一不慎,可能一个不好的join导致数据库的全表扫描,查询出大量的 无用...

数据库优化

数据库优化:创建索引、分表、分区、SQL优化、使用别名
  • P_xin
  • P_xin
  • 2016-02-26 14:41
  • 265

数据库优化

在大数据时代,数据都是被存储在数据库中,我们这里有一些对数据库优化的方法:   1。数据分区   在我们海量数据中,其中一种重要的方式就是如何有效地存储并降低需要处理的数据规模,所以可以对海量数据...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)